SQLFetchResultSetValues issue
I found an issue with the above method when there are text fields in a query that are not the last columns in the resultset.
You get a SQL error Invalid Descriptor Index
Searching the web found this article which even though very old seems still pertinent.
[url]http://forums.codeguru.com/showthread.php?127276-Invalid-Descriptor-Index%92[/url]
The help needs to be updated to reflect this issue with a warning re text colums in result set
The same issue exists with SQLFetchRowValues
Re: SQLFetchResultSetValues issue
I think it is an odbc limitation for as long as i can remember
Re: SQLFetchResultSetValues issue
It happens when you have a field of type 'text' in MS-SQL. This was a SQL-2005 feature and deprecated since. You're now supposed to use the fieldtype varchar.
Re: SQLFetchResultSetValues issue
Even with a varchar column if it is not last in last column in the select statement the error happens.
It is a limitation of the ODBC driver that I believe is used for DAW SQL CLI classes.
This is why developed a ado recordset class way back
I was testing to see if I could retire that class and uses DAWs but it seems not dues to this issue
Re: SQLFetchResultSetValues issue
With varchar it probably goes wrong when the content is over a certain length. It surely works for me in several projects.
Re: SQLFetchResultSetValues issue
Ok there is a bug here.
DAWS tools themselves converts an embedded table to MSSQL setting text fields to varchar(max).
This fails the sqFetresultSetValues as detailed in original post.
However if the table is changed to be varchar(Size) , say varchar(2048) the query returns correctly without the error.
Can you confirm that you have a size set on the varchar columns?
If so see what happens with a MAX size.
Re: SQLFetchResultSetValues issue
I ue varchar(max) too, but it looks like that if the content of the field is 8000 or less, it will work. I get the '[COLOR=#333333]Invalid Descriptor Index' error if the content of one of those fields is over 8000 though. When I change the query to something like [[/COLOR]select top 10 id, convert(varchar(8000),pflege), gewicht from pflanzen where pflege <> ''] it will work again.
That also explains why I hadn't seen that error yet with varchar(max). It is not so common to have actually have that much text in a field. I'm assuming that most of the time a productdescription or a freetext-note are smaller then 8000.
Re: SQLFetchResultSetValues issue
It's common for us. We have thousands of comment records with text up to 64K.
Re: SQLFetchResultSetValues issue
Hi Chris,
I had to look up that function in the help and then I remembered I had come across this bug a long time ago and it was probably the reason I wrote my own custom class for handling the data. Here's a code snippet that I use all the time. Note I have use a predefined iMaxChunk size which works in my systems (I almost never have Text type fields bigger than this) I have been thinking of improving this function for the Text to actually chunk all the data from the column but just haven't had the need.
[CODE]
Function Call_ESQL_Resultset String sSQLStmt Returns String[][]
Handle hoSQLManager hDbC hStmt
Integer iArg iCols iFetchResult iRow iCol iColType iMaxChunk
String[][] asResultSet
String sDriver sConnectString
Move 1024 to iMaxChunk
#IFDEF Is$WebApp
#ELSE
Send Cursor_Wait of Cursor_Control
#ENDIF
Get Create (RefClass(cSQLHandleManager)) to hoSQLManager
Get psConnectString to sConnectString
If (hoSQLManager) Begin
Get SQLConnect of hoSQLManager MSSQLDRV_ID sConnectString to hDbC
If (hdbc) Begin
Get SQLOpen of hDbC to hStmt
If (hstmt) Begin
Send SQLExecDirect of hStmt sSQLStmt
Get SQLStmtAttribute of hstmt SQLSTMTATTRIB_COLUMNCOUNT to iCols
If (iCols > 0) Begin
Repeat
Get SQLFetch of hstmt to iFetchResult
// Fetch record as callback to object
If (iFetchResult <> 0) Begin
For iCol from 1 to iCols
Get SQLColAttribute of hstmt iCol SQLCOLATTRIB_SQLTYPE to iColType
If ((iColType>0) or (iColType=SQL_SS_TIME2)) Begin // -154=time should read them here
Get SQLColumnValue of hstmt iCol to asResultSet[iRow][(iCol-1)]
// SQL Date
If ((iColType=SQL_TYPE_DATE)) Begin // 91 = Date
Get SQLDateToDFDate of hStmt asResultSet[iRow][(iCol-1)] to asResultSet[iRow][(iCol-1)]
End
If ((iColType=SQL_TYPE_TIMESTAMP) ) Begin // 93 = DateTime SQL_DATETIME
Get SQLDateTimeToDFDateTime asResultSet[iRow][(iCol-1)] to asResultSet[iRow][(iCol-1)]
End
End // TEXT field
Else Begin
Get SQLGetData of hstmt iCol iMaxChunk to asResultSet[iRow][(iCol-1)]
End
Loop
Increment iRow
End
Until (iFetchResult = 0)
End
Send SQLClose to hStmt
End
Send SQLDisconnect to hDbC
End
Send Destroy of hoSQLManager
End
#IFDEF Is$WebApp
#ELSE
Send Cursor_Ready of Cursor_Control
#ENDIF
Function_Return asResultSet
End_Function
[/CODE]
Re: SQLFetchResultSetValues issue
Hi Chuck
in the end I went back to my ado class I wrote years ago, still works with latest mssql and does not have this issue.