Board index » delphi » Stored Procedure

Stored Procedure

How do I do a stored procedure that will receive as a parameter a memo field?
Regards,
Eduardo Tavares
www.tavareswebsite.cjb.net

 

Re:Stored Procedure


Quote
Eduardo Tavares wrote:

> How do I do a stored procedure that will receive as a parameter a memo field?

        Declare it.

        The hard part comes when you want to do anything useful with the memo
inside the stored proc.  Blob parameters to a stored proc are allowed,
but the stored proc and trigger language gives you no facilities
whatsoever to deal with their contents.  You can set them to NULL;
that's about it.  You'll need to use UDFs for anything else.

        HTH,

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:Stored Procedure


But how do I declare it?
I had tried like this :
CREATE PROCEDURE SP_DIU_CUSTOMER
(OPERACAO INTEGER, CUST_NO INTEGER, CUSTOMER VARCHAR(40), CONTACT VARCHAR(25), PHONE VARCHAR(15), CITY VARCHAR(15) )

AS begin if (operacao=1) then
delete from customer where cust_no=:cust_no;

if (operacao=2) then
insert into customer(cust_no,customer,contact_first,phone_no,city)
values (:cust_no,:customer,:contact,:phone,:city);

if (operacao=3) then
update customer set cust_no=:cust_no, customer=:customer, contact_first=:contact, phone_no=:phone, city=:city where cust_no=:cust_no;
end

but I couldnt do it because I always got an error message.

That is what I want send to the stored procedure all the parameters.IF its wrong, what is the best way?
I tried doing like :
query.sql.add(' insert into...');

But when i tried to post, the code jumped to the except statment.

Regards,
Eduardo Tavares
www.tavareswebsite.cjb.net

Re:Stored Procedure


Quote
Eduardo Tavares wrote:

> But how do I declare it?
> I had tried like this :
> CREATE PROCEDURE SP_DIU_CUSTOMER
> (OPERACAO INTEGER, CUST_NO INTEGER, CUSTOMER VARCHAR(40), CONTACT VARCHAR(25), PHONE VARCHAR(15), CITY VARCHAR(15) )

        I don't see any Blob parameters there.  Try this:

CREATE PROCEDURE SP_DIU_CUSTOMER
  (OPERACAO INTEGER, CUST_NO INTEGER, CUSTOMER BLOB SUB_TYPE 1, ...

        (Or whichever column is your Blob column.)

        Also, please tell what the error you see is -- the exact text.  Please
don't just say "I get an error."  This will help me help you out.

        HTH,

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:Stored Procedure


The error message I am getting is this one.
Dynamic SQL Error
SQL error code =-104
Unexpected end of command
Statement:Create procedure fazer(operaco integer,tipid integer,tipo varchar(35),titulo varchar(60),tp blob sub_type 1)
as begin
if (operaco=1) then
insert into tips(tip_id,kind,title,tip) values(:tipid,:tipo,:titulo,:tp)

This is the error message and my procedure is this.

Create procedure fazer(operaco integer,tipid integer,tipo varchar(35),titulo varchar(60),tp blob sub_type 1)
as begin
if (operaco=1) then
insert into tips(tip_id,kind,title,tip) values(:tipid,:tipo,:titulo,:tp)
if (operacao=2) then
delete from tips where tip_id=:tipid;
if (operacao=3) then
update tips set tip_id=:tipid,kind=:tipo,title=:titulo,tip=:tp;
end

Please help me!

Eduardo Tavares
www.tavareswebsite.cjb.net

Re:Stored Procedure


Quote
Eduardo Tavares wrote:

> Unexpected end of command

        Are you doing this from IBConsole?  If so, you need to temporarily
change the terminator character when declaring the stored proc.  E.g.:

SET TERM ^ ;

CREATE PROCEDURE ...

SET TERM ; ^

        HTH,

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:Stored Procedure


Now I get this error message:
Dynamic SQL Error
SQL error code=-104
Token unknown -line 8,char 62
)
statement : and all the procedure code goes here

Regards,
Eduardo Tavares
www.tavareswebsite.cjb.net

Re:Stored Procedure


Hi, all.
There is lost ";" in line
insert into tips(tip_id,kind,title,tip) values(:tipid,:tipo,:titulo,:tp)
Don't know about IBConsole (I can't use it) but there is now some tools that allows syntax checking and debugging for
stored procedures, I know IBExpert.
Best regards.

Re:Stored Procedure


Quote
Eduardo Tavares wrote:

> Now I get this error message:
> Dynamic SQL Error
> SQL error code=-104
> Token unknown -line 8,char 62
> )
> statement : and all the procedure code goes here

        You have a syntax error in your procedure.  Fix it.

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:Stored Procedure


I know, but where...

Take a look at old messages to get the stored procedure.

Regards,
Eduardo Tavares
"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote in message
news:3ACC9D49.8D6B41D6@no_spam.vertexsoftware.com...

Quote

> Eduardo Tavares wrote:

> > Now I get this error message:
> > Dynamic SQL Error
> > SQL error code=-104
> > Token unknown -line 8,char 62
> > )
> > statement : and all the procedure code goes here

> You have a syntax error in your procedure.  Fix it.

> -Craig

> --
> Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
> Delphi/InterBase weblog:   http://delphi.weblogs.com
> Use Borland servers; posts via others are not seen by TeamB.
> For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:Stored Procedure


Quote
Eduardo Tavares wrote:

> I know, but where...

> Take a look at old messages to get the stored procedure.

        I can't.  Depending upon how you formatted your script, the number in
the error message you gave could mean anything.  Particularly because
there is no ')' on line 8 of the procedure you posted.

        I do notice that a semicolon seems to be missing for the first INSERT
statement.

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Other Threads