creation of procedure

Database
Fan

creation of procedure

Hi,

I begin with the creation of procedure.
I write this simple code. But i don't understand where is the probleme. I try to make like in the example, but it is not good.

Thank for your solutions.

#!/bin/bash

main ()
{

FIC_LOG=/users/tmp.log

bteq<>${FIC_LOG} 2>>$FIC_LOG
.LOGON ${MY_LOGON},${MY_PASS};
.SET SESSION CH****T 'ASCII'
database MY_DATABASE;

CREATE PROCEDURE proc_insert(in num_line integer)
BEGIN
INSERT INTO MY_TABLE
VALUES(:num_line);
END;

CALL proc_insert(1);

.IF ERRORCODE <> 0 THEN .GOTO end_ko

-- end OK de la session
.LOGOFF
.EXIT 0

-- end KO de la session
.LABEL fin_ko
.LOGOFF
.EXIT 2

EndOfBteq

Code=$?
if [ ${Code} -eq 0 ]
then
echo -e "\n\n#############################" >>${FIC_LOG}
echo "OK" >>${FIC_LOG}
echo "#############################" >>${FIC_LOG}
else
echo -e "\n\n####################################" >>${FIC_LOG}
echo "KO" >>${FIC_LOG}
echo "####################################" >>${FIC_LOG}
fi

echo -e "\n\nEND : "`date` >>${FIC_LOG}

exit 0
}

main $*

The program return this message :

BTEQ 08.02.02.00 Fri Oct 12 09:48:21 2007

+---------+---------+---------+---------+---------+---------+---------+----
.LOGON my_logon,

*** Logon successfully completed.
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
.SET SESSION CH****T 'ASCII'
+---------+---------+---------+---------+---------+---------+---------+----
database MY_DATABASE;

*** New default database accepted.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

CREATE PROCEDURE proc_insert(in num_line integer)
BEGIN
INSERT INTO MY_TABLE
VALUES(:num_line);

CREATE PROCEDURE proc_insert(in num_line integer)
$
*** Failure 3706 Syntax error: Invalid SQL statement.
Statement# 1, Info =51
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
END;
*** Failure 3707 Syntax error, expected something like a name or a 'TRANSAC
TION' keyword or a 'FASTEXPORT' keyword or a 'LOADING' keyword or a 'LOGGING'
keyword between the 'END' keyword and ';'.
Statement# 1, Info =6
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

CALL proc_insert(1);
*** Failure 5495 Stored Procedure 'MY_DATABASE.proc_insert' does not exist.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

.IF ERRORCODE <> 0 THEN .GOTO fin_ko
.GOTO fin_ko
+---------+---------+---------+---------+---------+---------+---------+----

-- fin OK de la session
.LOGOFF
*** Skipped.
+---------+---------+---------+---------+---------+---------+---------+----
.EXIT 0
*** Skipped.
+---------+---------+---------+---------+---------+---------+---------+----

-- fin KO de la session
.LABEL fin_ko
+---------+---------+---------+---------+---------+---------+---------+----
.LOGOFF
*** You are now logged off from the DBC.
+---------+---------+---------+---------+---------+---------+---------+----
.EXIT 2
*** Exiting BTEQ...
*** RC (return code) = 2

2 REPLIES
Enthusiast

Re: creation of procedure

Did you try this ?

INSERT INTO my_table (my_column)
VALUES(:num_line);

... See you later on developpez ;-)
Fan

Re: creation of procedure

I have a solution with this line
.compile file=myfile
The procedure is written in the file and now it's ok.
But, I try with a cursor, but I have an error.
My procedure is :
BEGIN
DECLARE liste CURSOR FOR
DECLARE p1b integer;
DECLARE p2b integer;

SELECT p1, p2
FROM ma_table;

OPEN liste;
while (sqlcode=0)
FETCH liste INTO p1b, p2b;
INSERT INTO MA_TABLE2
VALUES(p1b, p2b);
end while;
END;

I have an error with the instruction while.
Missing/Invalid sql statement E(3706)

Regards.