SQLGetDataToUChar - cSQLStatement

Get data of a column to a UChar array, can be used with binary data

Type: Function
Return Data Type: UChar[]

Parameters

Parameter	Type	Description
iCol	Integer	Column to get data from
iLen	Integer	Length of data to get

Syntax

Function SQLGetDataToUChar Integer iCol Integer iLen Returns UChar[]

Call Example

Get SQLGetDataToUChar iCol iLen to UChar[]Variable

Description

SQLGetDataToUchar can be used to retrieve character data to a UChar array. This may be needed if the data is retrieved in chunks and multi-byte Unicode characters get split in 2 parts over 2 chunks.

When retrieving character data with SQLGetDataToUchar, data is returned in a UChar array as a wide string (wstring) (2 bytes per character).

Sample

Example for retrieving binary data with SQLGetDataToUchar:

Procedure DemoSQLGetDataToUCharBinary    
    Handle  hoConnect hoStmt
    Integer iFetchResult iNumCols iCol iResult iNextSetResult iColumnType iChunkSize
    String  sValue
    UChar[] uaTotalBinary uaChunk

    Get SQLConnect of oSQLHandler "MSSQLDRV" "DFCONNID=MSSQLOrderEntryId" to hoConnect
    If (hoConnect > 0) Begin
        Get SQLOpen of hoConnect to hoStmt        
        If (hoStmt <> 0) Begin
            Send SQLExecDirect of hoStmt "Select ID, varbinarymax10 from TestBinaryTable Order By ID"
            Repeat
                Get SQLStmtAttribute of hoStmt SQLSTMTATTRIB_COLUMNCOUNT to iNumCols
                If (iNumCols > 0) Begin
                   Repeat
                        Get SQLFetch of hoStmt to iFetchResult
                        If (iFetchResult <> 0) Begin
                            For iCol from 1 to iNumCols
                                Get SQLColumnType of hoStmt iCol to iColumnType
                                If ( (iColumnType = SQL_BINARY) or (iColumnType = SQL_VARBINARY) or (iColumnType = SQL_LONGVARBINARY) ) Begin
                                    Repeat
                                        Move 100 to iChunkSize
                                        Get SQLGetDataToUChar of hoStmt iCol iChunkSize to uaChunk
                                        If (SQLResult = 1) Begin 
                                            Showln (sFormat("iCol=%1,Binary chunk: length = %2",iCol,(SizeOfArray(uaChunk))))
                                            Move (AppendArray(uaTotalBinary,uaChunk)) to uaTotalBinary
                                        End
                                    Until (SQLResult <> 1)
                              End
                          Loop
                    End
                Until (iFetchResult = 0)
                End
                Get SQLNextResultSet of hoStmt to iNextSetResult
            Until (iNextSetResult = 0)
            Send SQLClose of hoStmt
        End
        Send SQLDisconnect of hoConnect
    End
End_Procedure

Sample

This sample retrieves character data using SQLGetDataToUchar. The example retrieves the data of a Unicode varchar column (nvarchar(max) on SQL Server) in chunks of 10. If the data contains non-bmp characters (4 byte characters in UTF16, for example smiley characters), this can be split across chunks. The first 2 bytes of the character can be in one chunk, the last 2 bytes in the next chunk.

Procedure DemoSQLGetDataToUCharVarchar
    Handle  hoConnect hoStmt
    Integer iFetchResult iNumCols iCol iResult iNextSetResult iColumnType iChunkSize
    String  sValue
    UChar[] uaTotalVarchar uaChunk

    Get SQLConnect of oSQLHandler "MSSQLDRV" "DFCONNID=MSSQLOrderEntryId" to hoConnect
    If (hoConnect > 0) Begin
        Get SQLOpen of hoConnect to hoStmt        
        If (hoStmt <> 0) Begin
            Send SQLExecDirect of hoStmt "Select ID, NVarcharMaxColumn from TestSQLGetDataTable Order By ID"
            Repeat
                Get SQLStmtAttribute of hoStmt SQLSTMTATTRIB_COLUMNCOUNT to iNumCols
                If (iNumCols > 0) Begin
                    Repeat
                        Get SQLFetch of hoStmt to iFetchResult
                        If (iFetchResult <> 0) Begin
                            For iCol from 1 to iNumCols
                                Get SQLColumnType of hoStmt iCol to iColumnType
                                If ( (iColumnType = SQL_WVARCHAR) or (iColumnType = SQL_WLONGVARCHAR) ) Begin
                                    Move (ResizeArray(uaTotalVarchar,0)) to uaTotalVarchar
                                    Repeat
                                        Move 10 to iChunkSize
                                        Get SQLGetDataToUChar of hoStmt iCol iChunkSize to uaChunk
                                        If (SQLResult = 1) Begin 
                                            Showln (sFormat("iCol=%1,Binary chunk: length = %2",iCol,(SizeOfArray(uaChunk))))
                                            Move (AppendArray(uaTotalVarchar,uaChunk)) to uaTotalVarchar
                                        End
                                    Until (SQLResult <> 1)

                                    // uaTotalVarchar will contain varchar data stored as wstring
                                    // Convert back to regular string
                                    Pointer pWideBuf
                                    String sString
                                    Integer iTotalLen
                                    Move (SizeOfArray(uaTotalVarchar)) to iTotalLen

                                    // Add a WString zero terminor (2 bytes)
                                    Move (iTotalLen + 2) to iTotalLen
                                    Move (ResizeArray(uaTotalVarchar,iTotalLen)) to uaTotalVarchar

                                    Move (AddressOf(uaTotalVarchar[0])) to pWideBuf
                                    Move (PointerToWString(pWideBuf)) to sString
                                    Showln (SFormat(" sString = [%1]",sString))
                                End
                            Loop
                        End
                    Until (iFetchResult = 0)
                End
                Get SQLNextResultSet of hoStmt to iNextSetResult
            Until (iNextSetResult = 0)
            Send SQLClose of hoStmt
        End
        Send SQLDisconnect of hoConnect
    End
End_Procedure

Return Value

Returns data from a column.