Database Execution error?

[golyg]

Hi all,
I am trying to execute some commands on a sql anywhere 5.0 database, but...
I get the following error:
"Error: Database command has not been succesfully prepared at line xx in clicked event ...."
here is some of the code:

sg_SQLStmnt =
"ALTER TABLE t_attach ADD battery_id varchar(5);"

//Connect to the database.
CONNECT USING NEW_DB;

st_status.text = "test1"
DECLARE fv_cursor DYNAMIC CURSOR FOR SQLSA;
IF NEW_DB.SQLCode=-1 THEN GOTO QLErr_FV1
PREPARE SQLSA FROM :sg_SQLStmnt;
IF NEW_DB.SQLCode=-1 THEN GOTO SQLErr_FV2
OPEN fv_cursor ;
IF NEW_DB.SQLCode=-1 THEN GOTO SQLErr_FV3
COMMIT;

CLOSE fv_cursor;
IF NEW_DB.SQLCode=-1 THEN GOTO SQLErr_FV4


It's throwing the error message at the 'Open' Cursor line
I am using pb6.0

hope i gave enough info,
TIA,
g

 

[golyg]

well i got it fixed sort of..
i took out the word 'DYNAMIC',
but it still is not performing the operation of 'ALTER....'
still no idea,
any ideas are always welcome here..

TIA,

 

[golyg]

I'm making progress...
Now the execute statement is complaining.
I receive an error message stating:
'Procedure/cursor, sqlsa, is not defined.'

any suggestions?

here is the new code:


DECLARE fv_cursor DYNAMIC CURSOR FOR SQLSA;
IF NEW_DB.SQLCode=-1 THEN GOTO SQLErr_FV1

PREPARE SQLSA FROM :sg_SQLStmnt;
IF NEW_DB.SQLCode=-1 THEN GOTO SQLErr_FV2

EXECUTE SQLSA FROM :sg_SQLStmnt;
IF NEW_DB.SQLCode=-1 THEN GOTO SQLErr_FV4

OPEN DYNAMIC fv_cursor ;
IF NEW_DB.SQLCode=-1 THEN GOTO SQLErr_FV3

CLOSE fv_cursor;
IF NEW_DB.SQLCode=-1 THEN GOTO SQLErr_

Thanks,
g

 

[BetterLateThanNever]

You might have the order of things wrong. From the PB help (Dynamic SQL Format 3):

integer Emp_id_var
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM "SELECT emp_id FROM employee" ;
OPEN DYNAMIC my_cursor ;
FETCH my_cursor INTO :Emp_id_var ;
CLOSE my_cursor ;

You shouldn't need to execute the SQL since it is part of the cursor definition.

Cheers.

 

[rawhide58]

In my humble opinion you are using the wrong level of embedded SQL to do an table alter.

I would:

Execute Immediate "alter this add that"

Just make sure your autocommit is set correctly, since data definition (DDL) should/must not be running within a transaction.