Blob as parameter in stored procedure

Can I use a blob as a parameter in a stored procedure in Interbase 7.5.1.80 (see cut-down procedure below)? The description field is a blob sub_type 1 segment size 80. I have searched the forums and have read that it shouldn't work and it will work, but haven't seen any recent posts on this topic. Can this safely be used without damaging the database? If not, could I just define the description parameter as varchar(500)?

Thanks,
Stan Walker



CREATE PROCEDURE PPT_ADDNEWREC (
    P_ITEMID INTEGER,
    P_PARTNUMBER VARCHAR (20) CHARACTER SET NONE,
    P_PITTYPE CHAR (1) CHARACTER SET NONE,  
    P_DESCRIPTION BLOB sub_type 1 segment size 80 CHARACTER SET NONE)
AS
BEGIN   

   INSERT INTO TPT_WORK
       (PitId, ItemId, PartNumber, Status, RecordDate,
        RecordBy, type, description)
   VALUES
       (GEN_ID( GPT_PITID,1), :P_ITEMID, :P_PARTNUMBER, 'N',
        'NOW', USER,:P_PITTYPE, :P_DESCRIPTION);

END
0
Stan
10/12/2009 8:27:04 PM
embarcadero.interbase.sql 224 articles. 0 followers. Follow

5 Replies
776 Views

Similar Articles

[PageSpeed] 50

Stan Walker wrote:

> Can I use a blob as a parameter in a stored procedure in Interbase
> 7.5.1.80 (see cut-down procedure below)? The description field is a
> blob sub_type 1 segment size 80. I have searched the forums and have
> read that it shouldn't work and it will work, but haven't seen any
> recent posts on this topic. Can this safely be used without damaging
> the database? 

	The documentation for InterBase states that this is not supported.
However, in the example case you gave, it will work. It's harder to
explain where it doesn't work, which is probably why it is not
supported. Essentially, blobs are pointers, and if you try to put the
same value in two different places, bad things can happen.

> If not, could I just define the description parameter
> as varchar(500)?

	I don't believe this works in InterBase 7.5. The ability to
transliterate a blob value into a string, or vice versa, was introduced
in a later version of InterBase, if I recall correctly.

-- 
Craig Stuntz · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz/
0
Craig
10/12/2009 8:53:15 PM
Thanks Craig
0
Stan
10/12/2009 9:10:33 PM
Craig,
 
One more quick question. In my Delphi (2007) code would I assign the parameter as:

sp.ParamByName('P_DESCRIPTION').AsString := memDesc.Text;

where memDesc is a TMemo component?  AsBlob is certainly not legal.

Thank,
Stan





> CREATE PROCEDURE PPT_ADDNEWREC (
>     P_ITEMID INTEGER,
>     P_PARTNUMBER VARCHAR (20) CHARACTER SET NONE,
>     P_PITTYPE CHAR (1) CHARACTER SET NONE,  
>     P_DESCRIPTION BLOB sub_type 1 segment size 80 CHARACTER SET NONE)
> AS
> BEGIN   
> 
>    INSERT INTO TPT_WORK
>        (PitId, ItemId, PartNumber, Status, RecordDate,
>         RecordBy, type, description)
>    VALUES
>        (GEN_ID( GPT_PITID,1), :P_ITEMID, :P_PARTNUMBER, 'N',
>         'NOW', USER,:P_PITTYPE, :P_DESCRIPTION);
> 
> END
0
Stan
10/13/2009 12:38:37 AM
Stan Walker wrote:

> sp.ParamByName('P_DESCRIPTION').AsString := memDesc.Text;
> 
> where memDesc is a TMemo component?  AsBlob is certainly not legal.

	AsBlob and AsString are the same, last I checked. Anyway, AsString is
fine.

-- 
Craig Stuntz · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz/
0
Craig
10/13/2009 12:34:46 PM
> {quote:title=Craig Stuntz wrote:}{quote}
> Stan Walker wrote:
> 
> > sp.ParamByName('P_DESCRIPTION').AsString := memDesc.Text;
> > 
> > where memDesc is a TMemo component?  AsBlob is certainly not legal.
> 
> 	AsBlob and AsString are the same, last I checked. Anyway, AsString is
> fine.
> 
> -- 
> Craig Stuntz · Vertex Systems Corp. · Columbus, OH
> Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz/


Thanks again, Craig
0
Stan
10/13/2009 1:52:01 PM
Reply:

Similar Artilces:

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

Blob from Parameter in Stored Procedure
Hi I am trying to get an image from a blob returned in a parameter in a stored proc using an TIBStoredProc CreateBlobStream does not work as it requires a field type in the parameter and won't accept a Param What is the work around to this please. Thanks Alan Alan Scott wrote: > Hi > > I am trying to get an image from a blob returned in a parameter in a stored proc using an TIBStoredProc > > CreateBlobStream does not work as it requires a field type in the parameter and won't accept a Param > > What is the work around to this please. >...

SQL Stored Procedure Issue
This is the Stored Procedure below ->  SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/ /****** Object:  Stored Proc...

SQL Stored Procedure to MySQL Stored Procedure Conversion
Hi, I am trying to convert a stored procedure written for sql to one that will work in mysql. I understand that I have to set the variables as IN , but I don't know what to do with the rest of the code. The following is the sql stored procedure that I am trying to convert to msql stored procedure: CREATE PROCEDURE Register_User (@userName Varchar(50), (@PassWord Varchar(50), (@FirstName Varchar(50), (@LastName Varchar(50) ) AS IF EXISTS(SELECT u_ID FROM User_ID Where u_UserName=@UserName) RETURN -1 ELSE INSERT User_ID( u_User...

MS SQL stored procedures inside another stored procedure
Hi,  Do you know how to write stored procedures inside another stored procedure in MS SQL.   Create procedure spMyProc inputData varchar(50) AS  ----- some logical    procedure spMyProc inputInsideData varchar(10) AS    --- some logical   ---  go ------- What exactly are tou trying to do? ***********************Dinakar NethiLife is short. Enjoy it.*********************** Like Function, you can have one function inside another another function. Function1 () {    Function2() } How about store procedure ? spProc1 { &n...

How to convert Sql Server Stored Procedures into Oracle Stored Procedures
Hi All, I am migrating from sql server2000 to oracle. I have moved all table into oracle manually. Now I need to move stored procedure. I don't know how to convert sql server stored procedure into oracle stored procedure form. Is there any tool which will convert directly. Please some one help me. Thank you.    Hi there,Please use the tool bellow, which does the job you want. http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html thanks sharp guy...

sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...

Oracle stored procedure with BLOB parameter
I have two simple Oracle7 stored procedures that will store LONG RAW data into and retrieve from an Oracle table. They are declared as transaction object's local external function - something like put_raw( long id, BLOB b, REF int returncode) and get_raw(long id, REF BLOB b, REF int returncode) The problem I am having is that put_raw() works just fine. Get_raw() seems to work, and to verify , I write the BLOB variable into a file. It is supposed to be a JPG file, but the written file is not a JPG. Secondly, before invoking get_raw(), I must do: b = BLOB(space(32000)) ...

Convert SQL Stored Procedure to InterBase
Hi Is there any possible way to convert SQL Stored Procedure to Interbase Stored Procedures by executing a SQL comand Thanks Maju "Maju Poulose" wrote in message news:16323@forums.codegear.com... > > Is there any possible way to convert SQL Stored Procedure to Interbase > Stored Procedures by executing a SQL comand No, the semantics used in MS SQL and Interbase for procs and triggers is totally different. There's very little that could be successfully automated. -- Wayne Niddery - TeamB (www.teamb.com) Tips to Get Published: http://dn.codegear...

Empty parameter in a SQL stored procedure
Hello, How can I establish in a stored procedure that a parameter (date, integer and string) is empty?  Hi, which object or code you are using to run stored procedure from asp.net?Ку! Hi,emont Try This create table Test_Empty_Table (id int identity ,[date] datetime, [Amount] int, [name] varchar(100)) Go insert into Test_Empty_Table select '2008/12/12',123,'Def' union all select '',123,'sdf' union all select '2008/12/12',123,'dfgh' union all select '2008/12/12',123,'dfg' union all select '',0,'...

Passing Parameters to a SQL Stored Procedure
I need help with a really basic task. I have a formview that works OK for selecting/updating records in the table.  When the user edits the data and clicks submit, the updates post to the table along with two parameter values I add using this in the code-behind: Protected Sub DS_EditMasterOrg_Updating1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles DS_EditMasterOrg.Updating e.Command.Parameters("@Last_Update_By").Value = User.Identity.Name e.Command.Parameters("@Last_Update_Dt").Value = System.DateTime.Now End Sub The next step,...

ASP.NET and SQL Server Stored Procedure: Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'GetProductsOnCatalogPromotion' expects parameter '@ProductsPerPage', which was no
Hi, I am receiving an exception that i've been working on after I add a user control.  I don't have any syntax errors anywhere in my project. I checked and everything works before I add this user control to the website. I also checked the stored procedure and the GenericDataAccess class files and there are not syntax errors. I have also received no compilation errors. But, I do receive this runtime error below. Any help from anyone would be greatly appreciated. I also added the stored procedure 'GetProductsOnCatalogPromotion' and the class file 'GenericDataAcces...

Passing an optional sql parameter to a stored procedure
 I have an sql procedure    1 ALTER PROCEDURE [dbo].[proc_companyLoadByName]2 (3 @name VARCHAR(100),4 @type int = null5 )6 AS7 BEGIN8 SET NOCOUNT ON9 DECLARE @Err int10 11 SELECT12 [id],13 [name],14 [phone],15 [fax],16 [address],17 [city],18 [province],19 [pcode],20 [type_id]21 FROM [company]22 WHERE23 ([name] LIKE @name) AND24 (@type IS NULL OR type_id = @type)25 26 SET @Err = @@Error27 28 RETURN @Err29 END   If I run it from the Sql managment studio it works whether I sp...

Calling stored procedure from Pervasive SQL with parameter
I am very new to asp.net. I am trying to call stored procedure from Pervasive Sql and I am getting following error: ERROR [HY000] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in expression: :myticket ERROR [HY104] [Pervasive][ODBC Client Interface]Invalid precision value. Here is my source code: Dim sConnectionString As String = "Provider=MSDASQL;DSN=Craftsman" Dim cn As New Odbc.OdbcConnection(sConnectionString) Dim cmd As New Odbc.OdbcCommand("{call getinfo(:myticket)}", cn) Dim prmticketn...

Web resources about - Blob as parameter in stored procedure - embarcadero.interbase.sql

Parameter - Wikipedia, the free encyclopedia
A parameter (from the Ancient Greek παρά , "para", meaning "beside, subsidiary" and μέτρον , "metron", meaning "measure"), in its common meaning, ...

fb_source Parameter - Facebook-Entwickler
This document lists the complete list of values for the fb_source parameter that is returned with th...

Google Webmaster Tools Parameter Handling - Flickr - Photo Sharing!
You aren't signed in Sign In Help Home The Tour Sign Up Explore Explore Home Last 7 Days Interesting Popular Tags Calendar Most Recent Uploads ...

Cat 8: TIA performance standards committee chairman previews parameters of forthcoming twisted-pair cable ...
Category 8, the next-generation twisted-pair cabling specification, is still in development, but the outlook looks quite positive that it will ...

Philippine president asks for 'parameters' of Mayon Volcano tours
Philippine president asks for 'parameters' of Mayon Volcano tours People's Daily Online ... 26, 27, 28, 29, 30, 31. Jan, Feb, Mar, Apr, May, ...

Alternatives to boolean parameters
... ) { PrintLine ( item . Name ); if ( printDetails ) { PrintLine ( item . Description ); } } } Aha! Now can see that the boolean parameter ...

Exporting Error (Error in Parameter...: Apple Support Communities
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability ...

Apple tech monitors device aging, adjusts operating parameters to ensure optimal performance
... describing a method of monitoring the aging of a device's electronics, comparing the data with a global average and modifying operating parameters ...

Is it poor programming practice to pass parameters as objects?
... a free, community-powered network of 100+ Q&A sites . Risser asked: So, we've got a guy who likes to write methods that take Objects as parameters, ...

Parameter Short Film Competition sponsored by Grolsch
Grolsch presents Parameter The A.V. Club Short Film Competition Please Enter Your Date of Birth Ineligible entry Enter For people over the age ...

Resources last updated: 11/23/2015 10:18:49 PM