I've updated my local development Windows/Ashell from 1673.0 to 1679.3 and find my ASQL no longer connects to the Microsoft SQL server.. I only updated the ashw32.exe was there anything else?
It looks as if the programs is not going any further than this...
Code
! first init the lib
cmdhdr.dbmsid = dbmsid
xcall SQL, (SQLOP_INIT_LIB), cmdhdr
if (cmdhdr.rc # 0 and cmdhdr.rc # SQLERR_ALREADYINITED) then
if (SQLCONF_NOERRMSG) = 0 then
xcall MSGBOX, "Unable to init library for DBMS type = "+cmdhdr.dbmsid &
+ chr(13) + Fn'SQL'ErrMsg$(cmdhdr), &
"SQL Error", MBTN_OK, MBICON_STOP, MBMISC_TASKMODAL
endif
exitfunction
endif
Same program under 1673.0 works fine, updated 1679.3 does not connect.
Looking on the bright side, good job on the beta testing!!! The SQL interface does have a few quirks that may be unique among other xcalls, so it seems likely that the parameter passing revisions in 1679.2 and .3 need a bit more tuning. Stay tuned...
Point Five solved the issue and SQL now my TestSQL routine Connects, and executes as expected. Thank-you!
But, stand by for a "possible" Point Six as I have to dig further to work out why my MadicsSQL is erroring between reading a random file record and executing a SQL update/insert.... - "this bit is to be continued"
After a little time digging ive located a SQL issue on executing, in this case a SQL Create Table so on experimenting on the most simplest table I could find of:
Out of paranoia I did also try it in Microsofts sql management studio and thats ok. Also Ashell 1673.0 executes this fine.
I first thought it was related to xcall SQL,SQLOP_QUERY,cmdhdr,querystring but this works for a drop table, unless its related to querystring length as its mapped as MAP1 querystring,s,0 (a lot are mapped that way)
Code snippets below if it helps, Im happy go digging further but thought something your end may spark to mind first...
Create Table Function Errors
Code
FUNCTION FN'SQLX'CREATE(querystring as s0) AS F6
! OUT: 0=Failed else 1=Created
ON ERROR GOTO LOCAL'TRAP
++PRAGMA AUTO_EXTERN "TRUE"
FN'SQLX'CREATE=0
CALL FN'SQLX'LOG("Create Table, Query: "+querystring,"D")
xcall SQL,SQLOP_QUERY,cmdhdr,querystring
if cmdhdr.rc = 0 then
FN'SQLX'CREATE=1
else
CALL FN'SQLX'LOG("Create Table Failed: "+querystring,"E")
call SQL'Show'Status(cmdhdr)
endif
cmdhdr.opflags = SQL_CLOSE
xcall SQL,SQLOP_FREE_RESULT,cmdhdr
EXITFUNCTION
LOCAL'TRAP:
CALL FN'FUNCTION'ERROR'TRAP$(.LAST_ROUTINE,ERR(0),ERR(1),ERR(2),ERR(8))
RESUME ENDFUNCTION WITH_ERROR 99
ENDFUNCTION
Drop Function - Drops OK
Code
FUNCTION FN'SQLX'DROP'TABLE(tablename as s0) AS F6
! IN: Table Name
! OUT: 0=Failed, 1=Dropped, 2=Did not exists
ON ERROR GOTO LOCAL'TRAP
++PRAGMA AUTO_EXTERN "TRUE"
MAP1 querystring,s,0
FN'SQLX'DROP'TABLE=0
cmdhdr.opflags = 0
querystring = "drop table "+tablename
CALL FN'SQLX'LOG("Drop Table, Query: "+querystring,"D")
xcall SQL,SQLOP_QUERY,cmdhdr,querystring
if cmdhdr.rc = 0 then
FN'SQLX'DROP'TABLE=1
elseif cmdhdr.rc # 0 and cmdhdr.sqlstate = "42S02" then
FN'SQLX'DROP'TABLE=2
else
FN'SQLX'DROP'TABLE=0
call SQL'Show'Status(cmdhdr)
endif
cmdhdr.opflags = SQL_CLOSE
xcall SQL,SQLOP_FREE_RESULT,cmdhdr
EXITFUNCTION
LOCAL'TRAP:
CALL FN'FUNCTION'ERROR'TRAP$(.LAST_ROUTINE,ERR(0),ERR(1),ERR(2),ERR(8))
RESUME ENDFUNCTION WITH_ERROR 99
ENDFUNCTION
Hmmm...., it certainly does appear that there is some lingering issue with dynamic strings in ASQL. But based on the error message, I suspect that there's some kind of premature termination issue. Can you try explicitly adding a space to the end of your create table statement?
Thanks for confirming that - it certainly appears that there is an "off-by-one" error in the receiving of dynamic strings in SQL.SBR, probably requiring an update to the libashodbc and libashmysql libraries. Fortunately spaces aren't significant in SQL queries, so the workaround, while ridiculous, is entirely harmless.
Ok, I think this problem was limited to the SQLOP_QUERY function in the ODBC version of the ASQL library. It has probably been there a long time, but it surfaces in only limited situations, and only when the querystring was passed as an argument to a function that then did the XCALL SQL. (The details have to do with efforts to maintain negative substring interoperability between dynamic S and X variables in parameter passing, which affects whether there is a trailing null or not. The reason why it only affected this call is that it is the one ASQL call where the length of the string argument could literally be thousands of characters. So instead of making a local copy like most XCALLs would do, it just forced a trailing null on the end and used the querystring as is. Which would be fine except in when it wasn't.) Here's the patched library module:
Updated libashodbc.dll (version 1.4.113.0) and that solved the issue, Saves me tweaking the programs. Thanks! Now I have the rest of the weekend to catch up on a TV Series someone called Frank recommend.
