The use of the Oracle database db_odbc database retrieval is not the problem.Compiled successfully, but no data at run time. Here is my code, for loop in the body did not perform, it does not seem to retrieve the data. How to use it correctly, thank you very much!
import db_odbc
var theDb = open("dbpos", "dbcxj", "dbcxj", "dbpos")
for x in theDb.fastRows(sql"select * from goods where rownum<10"):
echo x
theDb.close()Thank you for volunteering as a tester ;-)
Try getting a single known column value from a table:
import db_odbc
var theDb = open("dbpos", "dbcxj", "dbcxj", "dbpos")
echo theDb.getRow(sql"select 1 from dual")
I recommend downloading a free odbc testing tool (eg odbctest ), so you can try this by hand (by doing the commands that the library does), listed here without their parameters
SQLAllocHandle
SQLPrepare
SQLExecute
SQLFetch
SQLNumResultCols
SQLRowCount
SQLGetData using SQL_C_CHAR
SQLFetchScroll using SQL_FETCH_NEXT
I used your code to test, but the following error occurred while using the "sqlplus" to test the same "select" statement, the result is correct. I think it should be a problem with the "db_odbc" module.
Traceback (most recent call last)
cx.nim(4) cx
db_odbc.nim(353) getRow
gc.nim(180) nimGCunrefNoCycle
SIGSEGV: Illegal storage access. (Attempt to read from nil?)
I used the "getAllRows" function, the code is as follows:
import db_odbc
var theDb = open("dbpos", "dbcxj", "dbcxj", "dbpos")
echo theDb.getAllRows(sql"select * from goods")
theDb.close()I open the ODBC data source tracking, get the Odbc log, "SQL.log", From log found "SQLRowCount" to get the number of rows is -1, the problem is here, how to solve?the contents are as follows:
cx 824-11cc ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x0063FDEC
cx 824-11cc EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x0063FDEC ( 0x00C46E70)
cx 824-11cc ENTER SQLSetEnvAttr
SQLHENV 0x00C46E70
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER 0
cx 824-11cc EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 0x00C46E70
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER 0
cx 824-11cc ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00C46E70
SQLHANDLE * 0x0063FDE8
cx 824-11cc EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00C46E70
SQLHANDLE * 0x0063FDE8 ( 0x00C46EF0)
cx 824-11cc ENTER SQLConnectW
HDBC 0x00C46EF0
WCHAR * 0x00C404C0 [ 5] "dbpos"
SWORD 5
WCHAR * 0x6ECB1BFC [ -3] "******\ 0"
SWORD -3
WCHAR * 0x6ECB1BFC [ -3] "******\ 0"
SWORD -3
cx 824-11cc EXIT SQLConnectW with return code 0 (SQL_SUCCESS)
HDBC 0x00C46EF0
WCHAR * 0x00C404C0 [ 5] "dbpos"
SWORD 5
WCHAR * 0x6ECB1BFC [ -3] "******\ 0"
SWORD -3
WCHAR * 0x6ECB1BFC [ -3] "******\ 0"
SWORD -3
cx 824-11cc ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00C46EF0
SQLHANDLE * 0x0042F2F0
cx 824-11cc EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00C46EF0
SQLHANDLE * 0x0042F2F0 ( 0x00C4AB08)
cx 824-11cc ENTER SQLPrepare
HSTMT 0x00C4AB08
UCHAR * 0x0016A030 [ 36] "select Goodsid,goodsname from goods;"
SDWORD 36
cx 824-11cc EXIT SQLPrepare with return code 0 (SQL_SUCCESS)
HSTMT 0x00C4AB08
UCHAR * 0x0016A030 [ 36] "select Goodsid,goodsname from goods;"
SDWORD 36
cx 824-11cc ENTER SQLExecute
HSTMT 0x00C4AB08
cx 824-11cc EXIT SQLExecute with return code 0 (SQL_SUCCESS)
HSTMT 0x00C4AB08
cx 824-11cc ENTER SQLFetch
HSTMT 0x00C4AB08
cx 824-11cc EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 0x00C4AB08
cx 824-11cc ENTER SQLNumResultCols
HSTMT 0x00C4AB08
SWORD * 0x0063FE64
cx 824-11cc EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)
HSTMT 0x00C4AB08
SWORD * 0x0063FE64 (2)
cx 824-11cc ENTER SQLRowCount
HSTMT 0x00C4AB08
SQLLEN * 0x0063FE60
cx 824-11cc EXIT SQLRowCount with return code 0 (SQL_SUCCESS)
HSTMT 0x00C4AB08
SQLLEN * 0x0063FE60 (-1)
cx 824-11cc ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00C4AB08
cx 824-11cc EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00C4AB08
cx 824-11cc ENTER SQLDisconnect
HDBC 0x00C46EF0
cx 824-11cc EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)
HDBC 0x00C46EF0
cx 824-11cc ENTER SQLFreeHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00C46EF0
cx 824-11cc EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x00C46EF0
cx 824-11cc ENTER SQLFreeHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00C46E70
cx 824-11cc EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00C46E70
Yes, we need to determine why the row count returns -1
Reason: Since row count returned (rCnt) is less than 1, it doesn't try to get any data, and the excellent debug you provided show that it doesn't call SQLGetData()
db.SqlCheck(SQLNumResultCols(db.stmt, cCnt))
db.SqlCheck(SQLRowCount(db.stmt, rCnt))
result = @[]
for rNr in 1..rCnt:
rowRes = @[]
buf[0] = '\0'
for colId in 1..cCnt:
db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
cast[SqlPointer](buf.addr), 4095.TSqlSmallInt, sz
.addr))
rowRes.add($buf.cstring)
db.SqlCheck(SQLFetchScroll(db.stmt, SQL_FETCH_NEXT, 1))
Every ODBC SQL statement is returning a SUCCESS, so I can't see what the problem is, unless the SQL statement "select Goodsid,goodsname from goods;" returns no data ???? :-/
Found the issue. Can't use SQLRowCount for select statements. (It worked for me on my limited testing - but obviously its not the way to do it).
I'll put a PR to fix this. (EDIT: PR#3769)
Can you test tryInsertId() (it is untested for Oracle), as the tryInsertId() relies on "SELECT id.currval FROM DUAL;" to get the inserted ID, so this definitely needs checking.
Also, tryInsertId() relies on identifying the database, so check the following:
assert sqlGetDBMS().tolower() == "oracle"