Database Search
If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.
TechHome
Sample Code
- Create an SQL Server DSN
- DSN less connection
- Get Row Count From Resultset
- Get Rowset Size
- ODBC Example
- ODBC with Access
- Retrieve All Columns From the Last Row of the table
- To Obtain Information on Columns
- Use qGetData to Get all Records into an Array
Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Get Rowset Size
Question:
Is there a way to fetch more than one row of a rowset at a time to speed up fetching of large rowsets? The SQLSetStmtAttr system call includes an SQL_ATTR_ROW_ARRAY_SIZE statement attribute, but I don't see a corresponding function in the ODBC Extender.Fetching one field from about 2000 records takes about 2 seconds (over network); when that increases as our database grows that will become even more of a problem.
Binding to buffer instead of variable improved the performance slightly, but I need more speed. I believe the WinBatch interpreter overhead in the fetch loop is probably the culprit. I'd love to be able to slam the entire rowset into a buffer in a few statements.
Answer:
You will have to do DLLCalls not just for setting the row array size, but also to replace both qBindCol and qFetch.Here's the function:
#DefineFunction readBlock(hbuf, dataSrcName, tableName, nCol, nFldCharsMax, nRowsPerFetch)
; (Error handling omitted for brevity)
x7FFF = (1 << 15) - 1
x10000 = (1 << 16)
SQL_ATTR_ROW_ARRAY_SIZE=27
SQL_CHAR = 1
SQL_FETCH_NEXT = 1
nBufBytes = nFldCharsMax * nRowsPerFetch
henv = qAllocEnv()
hdbc = qAllocConnect(henv)
qConnect(hdbc, dataSrcName,"","")
hstmt = qAllocStmt(hdbc)
hstmtReal = qSpecial(2, hstmt, 0, 0, 0)
result=DllCall("ODBC32.dll",word:"SQLSetStmtAttr",long:hstmtReal,long:SQL_ATTR_ROW_ARRAY_SIZE,long:nRowsPerFetch,long:0)
If result > x7FFF Then result = result - x10000
result=DLLCall("ODBC32.dll",word:"SQLBindCol",long:hstmtReal,word:nCol,word:SQL_CHAR,lpbinary:hbuf,long:nFldCharsMax,long:0)
If result > x7FFF Then result = result - x10000
query = StrCat("SELECT * FROM ", tableName)
qExecDirect(hstmt, query)
result=DLLCall("ODBC32.dll",word:"SQLFetchScroll",long:hstmtReal,word:SQL_FETCH_NEXT,long:0)
If result > x7FFF Then result = result - x10000
BinaryEodSet(hbuf, nBufBytes)
qFreeStmt(hstmt, 1)
qDisconnect(hdbc)
qFreeConnect(hdbc)
qFreeEnv(henv)
#EndFunction ; readBlock
Article ID: W15851
File Created: 2004:03:30:15:41:08
Last Updated: 2004:03:30:15:41:08