Using ExecuteSQL() to add a new record to a database

[unixkd]

Hi Roger,

I tried to use your example below to add record to table as follows:

Local cStatement, cStatus
TEXT INTO cStatement WRAP
INSERT INTO PPCODES ( CODEID, CODENAME, DESC, MNEMONIC, DATEOPEN, SEARCHTYPE, ALLOWPOST, PROPERTIES, USERLOGIN, TIMESTAMP, Comment, Record_ID )
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
'0204',
'01 ',
'Hhhg ',
'Hhhg ',
'2012-03-05',
'HHH',
TRUE,
'',
'j ',
'05-03-2012 07:38 PM',
'hhy',
' '
ENDTEXT
ExecuteSQL( cStatement, @cStatus )
Return nil
*
I get the following error, what I am I doing wrong:

Error 7200: AQE Error: State = 42000
NativeError = 2115
[iAnywhere Solutions][Advantage SQL Engine]Expected lexical element not found: Your statement must start with
CREATE, DROP, SELECT, INSERT, UPDATE, ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, SET, DEBUG or a script statement.


FUNCTION TL_Exception( cType, cRef1, cRef2, cRef3, cRef4, cErrorInfo )

LOCAL cStatement, aParams, cStatus

DEFAULT cType := 'UNKNOWN', ;
cRef1 := '', ;
cRef2 := '', ;
cRef3 := '', ;
cRef4 := '', ;
cErrorInfo := ''

cRef1 := Strtran(cRef1,"'",'')
cRef2 := Strtran(cRef2,"'",'')
cRef3 := Strtran(cRef3,"'",'')
cRef4 := Strtran(cRef4,"'",'')

cErrorInfo := Strtran(cErrorInfo,"'","''")

TEXT INTO cStatement WRAP
INSERT INTO EXCEPTION ( type, ref1, ref2, ref3, ref4, errorinfo, date, time )
VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) ;
ENDTEXT

aParams := { cType, cRef1, cRef2, cRef3, cRef4, cErrorInfo, Date(), Time() }

cStatement := ApplySQLParams( cStatement, aParams )

ExecuteSQL( cStatement, @cStatus )

RETURN nil
*

[rdonnay]

Code: Select all

TEXT INTO cStatement WRAP
INSERT INTO PPCODES ( CODEID, CODENAME, DESC, MNEMONIC, DATEOPEN, SEARCHTYPE, ALLOWPOST, PROPERTIES, USERLOGIN, TIMESTAMP, Comment, Record_ID )
VALUES ( 
'0204',
'01 ',
'Hhhg ',
'Hhhg ',
'2012-03-05',
'HHH',
TRUE,
'',
'j ',
'05-03-2012 07:38 PM',
'hhy',
' ' )

[unixkd]

Yes I did that and get another error. It has to do with the fact that one of my field is a BLOB and need to be casted before INSERT INTO ... is executed.

How do we deal with INSERT INTO that has BLOB fields generally ?

Thanks.

Joseph

[rdonnay]

I have never used BLOBs with SQL.

I suggest reading the Advantage Docs about this.