FOR UPDATE using Sqlstringconnect RETURNING -1

Dear,

any one know why "for update" is returning -1 if using sqlconnect?

lcStringConn="Driver={MySQL ODBC 8.0 ANSI Driver};Port=3306;NO_SSPS=1;Option=67108864"+;
";Server="+lcServer+;
";Database="+lcDatabase+;
";Uid="+usr+;
";Pwd="+pswd+;
";sslca="+_SSL_CA+;
";sslcert="+_SSL_CERT+;
";sslkey="+_SSL_KEY+;
";sslverify=1"+;
";Option=3"
ServerStrConn=Sqlstringconnect(lcStringConn)

SQLCommand="SELECT COUNTER FROM MS_COUNTER WHERE row_id=?nRowI_D "+;
"FOR UPDATE; "+;
"UPDATE MS_COUNTER SET COUNTER = COUNTER +1, USRUP=?lcUSP, DTUP=?lcDUP "+;
"WHERE row_id=?nRowI_D "

SQLOK=SQLExec( ServerStrConn, SQLCommand, "vw_counter" )

this SQLOK always return -1, thou in mysql it works fine.
any advise ?
thanks.

3 answers

Things can change so fast with third party products,Not sure if you are interested in 3rd party product but here is the solution SSIS Postgresql Read

Thanks Anjali,

I'm in a process of reviewing my codes, hope to find why this happened.
thank you.

Hello Harry,

i think that problem is in SQLCommand.
Try write like this:

SQLCommand=" SELECT COUNTER
FROM MSCOUNTER
WHERE rowid=?nRowI
FOR UPDATE;
UPDATE MSCOUNTER
SET COUNTER = COUNTER +1,
USRUP=?lcUSP,
DTUP=?lcDUP
WHERE rowid=?nRowID"

SQLCommand=replace(SQLCommand,"?nRowID",?nRowID)

SQLCommand=replace(SQLCommand,"?lcUSP",?lcUSP)

SQLCommand=replace(SQLCommand,"?lcDUP ",?lcDUP )

Maybe this help solve your problem.