Stored procedure fails in esqlc prog but not in dbaccess
In SE v7.24 I have written a simple? stored procedure to run through a
list of items and process them by calling another stored procedure.
When I run the stored procedure in dbaccess, it runs fine. When I call
it from an esqlc program, it returns a -201 error. Any thoughts on what
I might be doing wrong would be greatly appreciated.
TIA
Here is the procedure:
CREATE PROCEDURE qtrpcalcscurs ( p_friday date )
DEFINE p_ticker CHAR(8);
DEFINE p_cusip CHAR(8);
DEFINE p_recpri SMALLFLOAT;
DEFINE p_mktcap SMALLFLOAT;
DEFINE p_cnt, numqtrs SMALLINT;
LET numqtrs = 0;
FOREACH QCUSIP FOR
SELECT
c.ticker, c.cusip, c.recpri, c.mktcap, count(*)
INTO
p_ticker, p_cusip, p_recpri, p_mktcap, p_cnt
FROM spccid c, spcqtr q
WHERE c.cusip = q.cusip and c.mktcap > 5.00
and c.recpri > 0
GROUP by c.ticker, c.cusip, c.recpri, c.mktcap
HAVING count(*) > 11
ORDER BY c.cusip
IF p_mktcap BETWEEN 15 AND 175 THEN
INSERT INTO curr_month ( cusip, recpri, friday )
VALUES ( p_cusip, p_recpri, p_friday ); END IF;
CALL updqtrpcalcs( p_cusip ) RETURNING numqtrs ;
END FOREACH;
END PROCEDURE;
And here is the call from esqlc:
main( int argc, char *argv[] )
{
EXEC SQL BEGIN DECLARE SECTION;
long friday;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL execute procedure qtrpcalcscurs( friday );
if ( sqlca.sqlcode != 0 ) {
fprintf(lgf,"\\n qtrpcalcscurs SQLCODE = %ld\\n", sqlca.sqlcode);
fclose(lgf);
exit(1);
}
Try declaring a cursor for EXECUTing the store procedure and Execute the
cursor.
Musaddique Qazi
In article <37FCFDBA.93D665E@cornercap.com>,
KONE <kolney@cornercap.com> wrote:
> In SE v7.24 I have written a simple? stored procedure to run through a
> list of items and process them by calling another stored procedure.
> When I run the stored procedure in dbaccess, it runs fine. When I
call
> it from an esqlc program, it returns a -201 error. Any thoughts on
what
> I might be doing wrong would be greatly appreciated.
> TIA
>
> Here is the procedure:
> CREATE PROCEDURE qtrpcalcscurs ( p_friday date )>
> DEFINE p_ticker CHAR(8);
> DEFINE p_cusip CHAR(8);
> DEFINE p_recpri SMALLFLOAT;
> DEFINE p_mktcap SMALLFLOAT;
> DEFINE p_cnt, numqtrs SMALLINT;
>
> LET numqtrs = 0;
>
> FOREACH QCUSIP FOR
> SELECT
> c.ticker, c.cusip, c.recpri, c.mktcap, count(*)
> INTO
> p_ticker, p_cusip, p_recpri, p_mktcap, p_cnt
> FROM spccid c, spcqtr q
> WHERE c.cusip = q.cusip and c.mktcap > 5.00
> and c.recpri > 0
> GROUP by c.ticker, c.cusip, c.recpri, c.mktcap
> HAVING count(*) > 11
> ORDER BY c.cusip
>
> IF p_mktcap BETWEEN 15 AND 175 THEN
> INSERT INTO curr_month ( cusip, recpri, friday )
> VALUES ( p_cusip, p_recpri, p_friday );> END IF;
> CALL updqtrpcalcs( p_cusip ) RETURNING numqtrs ;
> END FOREACH;
> END PROCEDURE;
>
> And here is the call from esqlc:
> main( int argc, char *argv[] )
> {
> EXEC SQL BEGIN DECLARE SECTION;
> long friday;
> EXEC SQL END DECLARE SECTION;
> ...
> EXEC SQL execute procedure qtrpcalcscurs( friday );
> if ( sqlca.sqlcode != 0 ) {
> fprintf(lgf,"\\n qtrpcalcscurs SQLCODE = %ld\\n",
sqlca.sqlcode);
> fclose(lgf);
> exit(1);
> }
>
Sent via Deja.com http://www.deja.com/
Before you buy.
You could do a SET DEBUG FILE TO '/filename' and turn TRACE ON, which will
record every executing statement in the procedure. That way, you could see
exactly which line is returning the syntax error.
Also, the syntax error could be in your updqtrpcalcs stored procedure.
--Steven
KONE wrote:
> In SE v7.24 I have written a simple? stored procedure to run through a
> list of items and process them by calling another stored procedure.
> When I run the stored procedure in dbaccess, it runs fine. When I call
> it from an esqlc program, it returns a -201 error. Any thoughts on what
> I might be doing wrong would be greatly appreciated.
> TIA
>
> Here is the procedure:
> CREATE PROCEDURE qtrpcalcscurs ( p_friday date )>
> DEFINE p_ticker CHAR(8);
> DEFINE p_cusip CHAR(8);
> DEFINE p_recpri SMALLFLOAT;
> DEFINE p_mktcap SMALLFLOAT;
> DEFINE p_cnt, numqtrs SMALLINT;
>
> LET numqtrs = 0;
>
> FOREACH QCUSIP FOR
> SELECT
> c.ticker, c.cusip, c.recpri, c.mktcap, count(*)
> INTO
> p_ticker, p_cusip, p_recpri, p_mktcap, p_cnt
> FROM spccid c, spcqtr q
> WHERE c.cusip = q.cusip and c.mktcap > 5.00
> and c.recpri > 0
> GROUP by c.ticker, c.cusip, c.recpri, c.mktcap
> HAVING count(*) > 11
> ORDER BY c.cusip
>
> IF p_mktcap BETWEEN 15 AND 175 THEN
> INSERT INTO curr_month ( cusip, recpri, friday )
> VALUES ( p_cusip, p_recpri, p_friday );> END IF;
> CALL updqtrpcalcs( p_cusip ) RETURNING numqtrs ;
> END FOREACH;
> END PROCEDURE;
>
> And here is the call from esqlc:
> main( int argc, char *argv[] )
> {
> EXEC SQL BEGIN DECLARE SECTION;
> long friday;
> EXEC SQL END DECLARE SECTION;
> ...
> EXEC SQL execute procedure qtrpcalcscurs( friday );
> if ( sqlca.sqlcode != 0 ) {
> fprintf(lgf,"\\n qtrpcalcscurs SQLCODE = %ld\\n", sqlca.sqlcode);
> fclose(lgf);
> exit(1);
> }
--
-----------------------------------------------------
Steven Mastandrea stevem@cstech.com
Systems Designer 847.397.7300
CSTech, Inc. Schaumburg, IL
You could do a SET DEBUG FILE TO '/filename' and turn TRACE ON, which will
record every executing statement in the procedure. That way, you could see
exactly which line is returning the syntax error.
Also, the syntax error could be in your updqtrpcalcs stored procedure. Hard
to tell without the trace.
--Steven
KONE wrote:
> In SE v7.24 I have written a simple? stored procedure to run through a
> list of items and process them by calling another stored procedure.
> When I run the stored procedure in dbaccess, it runs fine. When I call
> it from an esqlc program, it returns a -201 error. Any thoughts on what
> I might be doing wrong would be greatly appreciated.
> TIA
>
> Here is the procedure:
> CREATE PROCEDURE qtrpcalcscurs ( p_friday date )>
> DEFINE p_ticker CHAR(8);
> DEFINE p_cusip CHAR(8);
> DEFINE p_recpri SMALLFLOAT;
> DEFINE p_mktcap SMALLFLOAT;
> DEFINE p_cnt, numqtrs SMALLINT;
>
> LET numqtrs = 0;
>
> FOREACH QCUSIP FOR
> SELECT
> c.ticker, c.cusip, c.recpri, c.mktcap, count(*)
> INTO
> p_ticker, p_cusip, p_recpri, p_mktcap, p_cnt
> FROM spccid c, spcqtr q
> WHERE c.cusip = q.cusip and c.mktcap > 5.00
> and c.recpri > 0
> GROUP by c.ticker, c.cusip, c.recpri, c.mktcap
> HAVING count(*) > 11
> ORDER BY c.cusip
>
> IF p_mktcap BETWEEN 15 AND 175 THEN
> INSERT INTO curr_month ( cusip, recpri, friday )
> VALUES ( p_cusip, p_recpri, p_friday );> END IF;
> CALL updqtrpcalcs( p_cusip ) RETURNING numqtrs ;
> END FOREACH;
> END PROCEDURE;
>
> And here is the call from esqlc:
> main( int argc, char *argv[] )
> {
> EXEC SQL BEGIN DECLARE SECTION;
> long friday;
> EXEC SQL END DECLARE SECTION;
> ...
> EXEC SQL execute procedure qtrpcalcscurs( friday );
> if ( sqlca.sqlcode != 0 ) {
> fprintf(lgf,"\\n qtrpcalcscurs SQLCODE = %ld\\n", sqlca.sqlcode);
> fclose(lgf);
> exit(1);
> }
--
-----------------------------------------------------
Steven Mastandrea stevem@cstech.com
Systems Designer 847.397.7300
CSTech, Inc. Schaumburg, IL