fbpx
Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

TOPIC:

How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 11 Jun 2021 16:34 #18798

  • ic2


  • Posts: 995
  • I have I used this code for years: first I retrieve SQL queries using WCF and then I execture the query using ADS to add/modify a DBF:
    nStatus:=ACE2.AdsOpenTable101(hConn,cDbfFullPath,hTable) // ACE.AE_SUCCESS
    		If nStatus==0
    			nStatus := ACE.AdsCreateSQLStatement(hConn, hStmt) // ACE.AE_SUCCESS
    			If nStatus=0
    				nStatus := ACE2.AdsExecuteSQLDirectW( hStmt, cSQLStatement, hPtr)
    				If nStatus<>0
    					pBuf := Char[]{256}
    					nLastError:=ACE.AdsGetLastError(pError, pBuf,pBufLen)
    .....

    For 1 client I had a couple of SQL statements which did append a record but nStatus returned 7200. I looked up 7200 and saw:

    devzone.advantagedatabase.com/dz/webhelp...antage_sql_error.htm

    Problem: The problem might be caused by an SQL statement error or some other limitation in the way Advantage processes SQL statements.
    Solution: Use AdsGetLastError to retrieve a detailed description of the error.

    So I checked ADSGetLastError and this was 0, which is the same result as trying the query from within the Advantage Data Architect, which worked. Incorrectly, my program hence didn't return a successful addition and the record was being added every time again.

    Now the weird thing. When I lookup the return value from AdsCreateSQLStatement in the help it says:

    After the SQL statement has been executed a cursor handle will be returned. This cursor handle provides access to the rowset returned.

    Now I first can't imagine that I have interpreted the return value to be 0 on success without a good reason and second, it most statements return 0 and the faulty one 7200 which doesn't look to me like a cursor but it does look like the ADS error code.

    In short: I wonder how I can determine for sure that an AdsCreateSQLStatement actually added a record in DBF. For now i keep checking the return value: if this is 0 or it is non zero but GetLastError is 0 then I consider the record added successfully.

    But is this the best way?

    Dick

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 12 Jun 2021 08:39 #18805

    • ic2


  • Posts: 995
  • Addition to the above: I've done some tests and it is certainly not the best method because:

    1 When everything works fine, AdsExecuteSQLDirectW returns 0. Fine so far.
    2 When I misspell a field name, AdsExecuteSQLDirectW returns indeed 7200. But AdsGetLastError returns 0 so despite what the documentation says this doesn't help.
    3 When I change the WHERE to a non existing key, AdsExecuteSQLDirectW still returns 0, so the return value can certainly not be used to determine a successful addition.

    Addition: I've also tried Transactions, see code below. But when trying the query mentioned in 3, which can not possibly be executed, the program did not reach the Catch but simply continued with the Commit.

    I am under the impression that a lot of things do not work in ADS as expected. So I am still without a reliable method to know if a query was executed successfully.
    	nStatus := ACE2.AdsConnect101( cConnectStr, Null_object,Ref hConn)
    	If nStatus== 0
    		cDbfFullPath:=cDataPath+cDBF+".dbf"
    		nStatus:=ACE2.AdsOpenTable101(hConn,cDbfFullPath,hTable)
    		If nStatus==0
    			nStatus := ACE.AdsCreateSQLStatement(hConn, hStmt)
    			If nStatus=0
    				ace.AdsBeginTransaction(hConn)
    				Try
    					nStatus := ACE2.AdsExecuteSQLDirectW( hStmt, cSQLStatement, hPtr)
    					ace.AdsCommitTransaction(hConn)
    				Catch
    					// Consider the  Query failed and do something, one of the options being could be ace.AdsRollbackTransaction(hConn)
    				End



    I hope someone using ADS has a proven better method.

    Dick

    Please Log in or Create an account to join the conversation.

    Last edit: by ic2.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 12 Jun 2021 19:25 #18816

    • Jamal


  • Posts: 241
  • Dick,

    Try calling/checking AdsGetLastError() result immediately after
    nStatus := ACE.AdsCreateSQLStatement(hConn, hStmt)

    "According to the ADS docs, AdsGetLastError() gets cleared after every Ads function/method call:

    devzone.advantagedatabase.com/dz/webhelp..._adsgetlasterror.htm

    "The error code returned by this function will be the same as the one returned by the last Advantage Client Engine function call. The first action of each Advantage Client Engine function is to clear the previous error if there is one. Thus, a call to AdsGetLastError is valid only for the most recent function call as opposed to the most recently occurring error. If no error occured on the last Advantage Client Engine function call, AE_SUCCESS is returned in pulErrCode.
    "


    HTH,
    Jamal

    Please Log in or Create an account to join the conversation.

    How to check if creation of DBF record succeeded using AdsExecuteSQLDirectW 12 Jun 2021 19:57 #18817

    • ic2


  • Posts: 995
  • Hello Jamal,

    Thanks for the suggestion, but I already tried multiple combinations and AdsGetLastError doesn't do anything. It returns 0 in the code below and even in both situations I described above (non existing WHERE and using a non existing field, only in the latter case ADSExecuteSQLDirect return 7200 instead of 0).

    No idea if there's something else wrong in the code which I miss or if ADS is full of non working methods....

    Dick
    	If nStatus== 0 
    		cDbfFullPath:=cDataPath+cDBF+".dbf"
    		nStatus:=ACE2.AdsOpenTable101(hConn,cDbfFullPath,hTable) 
    		If nStatus==0
    			nStatus := ACE.AdsCreateSQLStatement(hConn, hStmt) 
    			If nStatus=0
    				ace.AdsBeginTransaction(hConn)
    				Try
    					pBuf := Char[]{256}
    					pError:=0
    					pBufLen:=256
    					nStatus := ACE2.AdsExecuteSQLDirectW( hStmt, cSQLStatement, hPtr)	
    					nLastError:=ACE.AdsGetLastError(Ref pError, pBuf,Ref pBufLen)
    					ace.AdsCommitTransaction(hConn)
    				Catch
    					// Consider the  Query failed and do something, one of the options being could be ace.AdsRollbackTransaction(hConn)
    				End

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1