SQL XOP Functions

SQL Handle Functions


SQLAllocHandle	SQLFreeHandle
SQLFreeStmt

SQL Connection Functions


SQLDataSources	SQLBrowseConnect
SQLConnect	SQLDisconnect
SQLDriverConnect	SQLDrivers

SQL Information Functions


SQLGetTypeInfo	SQLForeignKeys
SQLGetInfoNum	SQLGetInfoStr
SQLGetFunctions
SQLNativeSql
SQLSpecialColumns	SQLPrimaryKeys
SQLStatistics
SQLTables	SQLTablePrivileges


SQLBindCol	SQLColumnPrivileges
SQLColumns	SQLSpecialColumns
SQLProcedureColumns	SQLPrimaryKeys

SQL Parameter Functions


SQLDescribeParam	SQLNumParams
SQLBindParameter
SQLParamData	SQLPutData

SQL Execution Functions


SQLBulkOperations
SQLExecute	SQLExecDirect
SQLEndTran	SQLCancel
SQLProcedures	SQLProcedureColumns
SQLRowCount

SQL Cursor Functions


SQLGetCursorName	SQLSetCursorName
SQLSetPos	SQLCloseCursor

SQL Result Set Functions


SQLFetch	SQLFetchScroll
SQLMoreResults	SQLNumResultCols
SQLNumResultRowsIfKnown	SQLNumRowsFetched
SQLGetDataNum	SQLGetDataStr
SQLUpdateBoundValues	SQLDescribeCol

SQL Diagostic Functions


SQLGetDiagRec

SQL Attribute Functions


SQLColAttributeNum	SQLColAttributeStr
SQLGetConnectAttrNum	SQLGetConnectAttrStr
SQLSetConnectAttrNum	SQLSetConnectAttrStr
SQLGetEnvAttrNum	SQLGetEnvAttrStr
SQLSetEnvAttrNum	SQLSetEnvAttrStr
SQLGetStmtAttrNum	SQLGetStmtAttrStr
SQLSetStmtAttrNum	SQLSetStmtAttrStr

SQL Descriptor Functions


SQLGetDescFieldNum	SQLGetDescFieldStr
SQLSetDescFieldNum	SQLSetDescFieldStr
SQLGetDescRec	SQLSetDescRec

Igor-Specific Functions


SQLBinaryWavesToTextWave	SQL2DBinaryWaveToTextWave
SQLTextWaveToBinaryWaves	SQLTextWaveTo2DBinaryWave
SQLReinitialize	SQLNumResultRowsIfKnown
SQLNumRowsFetched	SQLUpdateBoundValues

SQLAllocHandle (handleType,inputHandleRefNum,outputHandleRefNum)

This is a generic function that allocates an environment, connection, statement or description handle. The function returns a standard result code and an integer reference number is returned via outputHandleRefNum.

Parameters

handleType is one of SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT or SQL_HANDLE_DESC.

inputHandleRefNum is the reference number of the relevant input handle or zero if handleType is SQL_HANDLE_ENV.

outputHandleRefNum is passed by reference. On return it contains the reference number of the newly allocated handle.

Details

You must call SQLFreeHandle to release any handle returned by SQLAllocHandle.

Example

#include <SQLUtils>
Function FetchUsingSQLGetData()
    Variable environmentRefNum=0,connectionRefNum=0,statementRefNum=0
    Variable result
    try
        // Create an environment handle. This returns an environment refNum in environmentRefNum.
        result = SQLAllocHandle(SQL_HANDLE_ENV,0,environmentRefNum)
        if (result)
        Print "Unable to allocate ODBC environment handle."
        endif
        AbortOnValue result!=0, 1

        // Set ODBC version attribute.
        result = SQLSetEnvAttrNum(environmentRefNum, SQL_ATTR_ODBC_VERSION, 3)
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_ENV,environmentRefNum,1)
        endif
        AbortOnValue result!=0, 2

        // Get a connection refNum in connectionRefNum.
        result = SQLAllocHandle(SQL_HANDLE_DBC,environmentRefNum,connectionRefNum)
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_ENV,environmentRefNum,1)
        endif
        AbortOnValue result!=0, 3

        // Connect to the database.
        result=SQLConnect(connectionRefNum,"IgorDemo1","DemoUser","Demo")
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_DBC,connectionRefNum,1)
        endif
        AbortOnValue result!=0, 4

        // Create a statement refNum in statementRefNum.
        result=SQLAllocHandle(SQL_HANDLE_STMT,connectionRefNum,statementRefNum)
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_DBC,connectionRefNum,1)
        endif
        AbortOnValue result!=0, 5

        // Execute it and parse the results. This returns a statement refNum in statementRefNum.
        result=SQLExecDirect(statementRefNum, "Select * from sampleTable;")
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_STMT,statementRefNum,1)
        else
        ParseSQLResults(statementRefNum)        // This routine is provided by SQLUtils.ipf
        endif
        AbortOnValue result!=0, 6
    catch
        Print "Execution aborted with code ",V_AbortCode
    endtry

    if (statementRefNum != 0)
        SQLFreeHandle(SQL_HANDLE_STMT,statementRefNum)
    endif

    if (connectionRefNum != 0)
        SQLDisconnect(connectionRefNum)
        SQLFreeHandle(SQL_HANDLE_DBC,connectionRefNum)
    endif

    if (environmentRefNum != 0)
        SQLFreeHandle(SQL_HANDLE_ENV,environmentRefNum)
    endif

    return result
End

SQLAllocStmt ()

This function has been superceded by SQLAllocHandle.

SQLBinaryWavesToTextWave (baseName, numBinaryWaves, textWaveNameStr)

SQLBinaryWavesToTextWave packs the raw data from a series of numeric waves into a single text wave. This is used with SQLHighLevelOp to pass a column of binary data to the database server. See Handling Binary Data With SQLHighLevelOp for an explanation of why and when this would be used.

The function result is always 0.

Parameters

baseName is the base name for a series of input numeric waves in the current data folder.

numBinaryWaves is the number of input numeric waves in the series.

textWaveNameStr contains the name that SQLBinaryWavesToTextWave will use for the output text wave.

Details

The input waves are a series of numeric waves with names like binaryWave0, binaryWave1, . . . in the current data folder.

Often these will be unsigned byte waves previously created by SQLTextWaveToBinaryWaves. They also might be waves you created to contain data that you want to store as binary data using and SQL INSERT or UPDATE command executed using SQLHighLevelOp.

SQLBinaryWavesToTextWave does not care about the dimensionality or data type of the numeric input waves. It treats them merely as an array of bytes to be stored in rows of the output text wave.

The output text wave is created in the current data folder.

note

When the output text wave is created, any wave with the same name in the current data folder is overwritten.

SQLBinaryWavesToTextWave stores the raw binary data in the output text wave. Each row of the output text wave stores the data for one of the input binary waves.

The data type, dimensionality and other properties of the input numeric waves are not stored in the output text wave. Only the raw data is stored.

Example

Function TestTextToBinary()
    // Make some test numeric waves.
    Make/N=10/W/U binaryWaveA0 = p      // 16-bit signed data
    Make/N=10/W/U binaryWaveA1 = p + 1
    Make/N=10/W/U binaryWaveA2 = p + 2

    // Pack the numeric waves into a text wave.
    SQLBinaryWavesToTextWave("binaryWaveA", 3, "binaryPackedTextWave")

    // Unpack the text wave into binary waves.
    SQLTextWaveToBinaryWaves(binaryPackedTextWave, "binaryWaveB")   // Creates unsigned byte waves.

    // Redimension the data to get back to 16-bit signed data.
    Redimension/E=1/N=10/W/U binaryWaveB0, binaryWaveB1, binaryWaveB2

    // Test waves for equality.
    if (EqualWaves(binaryWaveA0,binaryWaveB0,3) == 0)
        Print "binaryWaveA0 != binaryWaveB0"
    endif

    if (EqualWaves(binaryWaveA1,binaryWaveB1,3) == 0)
        Print "binaryWaveA1 != binaryWaveB10"
    endif

    if (EqualWaves(binaryWaveA2,binaryWaveB2,3) == 0)
        Print "binaryWaveA2 != binaryWaveB2"
    endif

    // Kill all the waves.
    KillWaves binaryPackedTextWave
    KillWaves binaryWaveA0, binaryWaveA1, binaryWaveA2
    KillWaves binaryWaveB0, binaryWaveB1, binaryWaveB2
End

SQL2DBinaryWaveToTextWave (input, textWaveNameStr)

SQL2DBinaryWaveToTextWave packs the raw data from a single 2D numeric wave into a single text wave. This is used with SQLHighLevelOp to pass a column of binary data to the database server. See Handling Binary Data With SQLHighLevelOp for an explanation of why and when this would be used.

The function result is always 0.

Parameters

input is the 2D input numeric wave.

textWaveNameStr contains the name that SQL2DBinaryWaveToTextWave will use for the output text wave.

Details

SQL2DBinaryWaveToTextWave does not care about the number of rows and columns or data type of the input numeric wave. It treats each column merely as an array of bytes to be stored in a row of the output text wave.

The output text wave is created in the current data folder.

note

When the output text wave is created, any wave with the same name in the current data folder is overwritten.

SQL2DBinaryWaveToTextWave stores the raw binary data in the output text wave. All rows of each column of the input numeric wave are stored in the corresponding row of the output text wave.

The data type, dimensionality and other properties of the input numeric waves are not stored in the output text wave. Only the raw data is stored.

Example

Function Test2DTextToBinary()
    // Make a test 2D numeric wave.
    Make/N=(5,3)/W/U matA = p           // 16-bit signed data

    // Pack the numeric wave into a text wave.
    SQL2DBinaryWaveToTextWave(matA, "binaryPackedTextWave")
    
    // Now we unpack the text wave into binary waves.
    SQLTextWaveTo2DBinaryWave(binaryPackedTextWave, "matB") // Creates 2D unsigned byte wave.
    
    // Redimension the data to get back to 16-bit signed data.
    Redimension/E=1/N=(5,3)/W/U matB
    
    // Test waves for equality.
    if (EqualWaves(matA,matB,3) == 0)
        Print "matA != matB"
    endif
    
    // Kill all the waves.
    KillWaves matA, matB, binaryPackedTextWave
End

SQLBindCol (statementRefNum, colNum, targetDataType, dataWave, targetDataSize, indicatorWave)

Bind a column in the result set to an IGOR wave.

SQLBindCol can also unbind a column (if dataWave is NULL). It can also bind an already-bound column to a different wave.

The function returns zero if successful or an SQL result code.

Parameters

statementRefNum, a reference number to a valid statement handle previously allocated with SQLAllocHandle.

colNum is the column to which the parameters are bound. SQL data columns start at 1.

targetDataType is the SQL type code for for a C data type, as shown in the table below.

dataWave is the wave that ultimately receives the output data. If dataWave is NULL ($""), SQLBindCol unbinds any waves previously bound to the specified column.

targetDataSize is the number of bytes for one element in the wave as shown in the table below.

indicatorWave is the wave that takes as an output either the number of bytes in the returned data or a value that indicates that the corresponding data field is null. indicatorWave must be a signed 32-bit integer wave as would be made using Make/I.

Details

When binding waves to a result set, SQL XOP marks the waves as in-use so that they cannot be killed until you unbind the column using SQLBindCol or free the statement using SQLFreeHandle or SQLReinitialize.

The targetDataType and targetDataSize Parameters

The targetDataType and targetDataSize parameters depend on the data wave as follows:

Data Wave Type	Target Data Type	Target Data Size
Double float	SQL_C_DOUBLE	8
Single float	SQL_C_FLOAT	4
32-bit integer, signed	SQL_C_SLONG	4
32-bit integer, unsigned	SQL_C_ULONG	4
16-bit integer, signed	SQL_C_SSHORT	2
16-bit integer, unsigned	SQL_C_USHORT	2
8-bit integer, signed	SQL_C_STINYINT	1
8-bit integer, unsigned	SQL_C_UTINYINT	1
Text	SQL_C_CHAR	Variable

SQL C data types not listed above are not currently supported by SQLBindCol.

SQLBindCol ignores the targetDataSize parameter for fixed length data types. It is OK for you to pass 0 as the targetDataSize parameter for fixed length data types.

SQL_C_CHAR is the targetDataType to use for variable length string data. When you specify SQL_C_CHAR, dataWave must be a text wave. In this case, targetDataSize is the maximum number of characters that you want to read for this column from each row of the result set. For example, if you are binding to a column that represents the name of a month, you might pick 20 for targetDataSize because you know that all month names can fit in 20 characters. If you pick a targetDataSize that is too small then you will receive truncated data.

When you specify any type other than SQL_C_CHAR, dataWave must be a numeric wave.

Transfering Data from the SQL XOP Buffer into the Wave

SQLBindCol does not change the size of the wave. When using a block cursor to retrieve multiple rows at once, if the wave is not big enough to hold all of the rows in the result set, excess rows are discarded. Therefore you should make the wave big enough to hold all of the rows. If you don't know how many rows will be fetched, make the wave big enough to hold the maximum number of rows that you expect.

Examples

// See the SQL Low-Level Demo experiment for a full-worked-out example.
// Here is an abbreviated example. Error testing omitted for brevity.
SQLBindCol(statementRefNum,1,SQL_C_SLONG,scorewave,8,indicatorwave)
SQLExecute(statementRefNum)
SQLFetch(statementRefNum)
Print scoreWave[0],indicatorwave[0]

SQLBindParameter (statementRefNum, parameterNumber, ioType, valueType, paramType, columnSize, decimalDigits, valueWave, bufSizeInBytes, indicatorWave)

Binds a wave to a parameter marker in an SQL statement. The parameter will usually be an input parameter in an SQL statement such as:

INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?)

In this example there are three parameter markers so you would make three calls to SQLBindParameter. Each call would bind one wave to one parameter. When you execute the SQL statement using SQLExecDirect or SQLExecute, ODBC will replace the ? parameter markers with values from the bound value wave.

In advanced applications, the parameter can also be an input/output or output parameter if the SQL statement is a procedure call.

SQLBindParameter can also rebind a parameter to a different wave.

valueWave is the wave bound to the parameter. valueWave may not be NULL.

indicatorWave, which must be of type signed 32-bit integer, is bound to the length/indicator for the value. This is an input to ODBC if the parameter type is input, an output if the parameter type is output, or both and input and an output if the parameter type is input/output.

SQL XOP marks both waves as in-use so that you can't kill them while they are bound. The waves are unmarked when you call SQLFreeHandle on the associated statement or call SQLReinitialize.

Parameters

statementRefNum is an integer reference number previously allocated by a call to SQLAllocHandle.

parameterNumber is the parameter marker number starting from 1.

ioType is a constant that defines the input/output type. It is one of: SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, SQL_PARAM_INPUT_OUTPUT.

valueType is a C-data type of the parameter. This must match the wave type for valueWave. See the table below.

paramType is an SQL type of the parameter. The supported types are:


SQL_CHAR	Text
SQL_SMALLINT	Signed or unsigned 16-bit integer
SQL_INTEGER	Signed or unsigned 32-bit integer
SQL_REAL	32-bit floating point
SQL_DOUBLE	64-bit floating point
SQL_BIT	Single bit binary.
SQL_TINYINT	Signed or unsigned 8-bit integer

The following are not yet supported:


SQL_DECIMAL	Fixed-point number.
SQL_NUMERIC	Fixed-point number.
SQL_BINARY	Fixed-length binary data.
SQL_VARBINARY	Variable-length binary data.
SQL_TYPE_DATE
SQL_TYPE_TIME
SQL_TYPE_TIMESTAMP

Also not supported are all interval types and any other type not listed as supported.

See the Microsoft ODBC documentation for details on SQL data types.

columnSize is the size of the SQL data type associated with the parameter marker. For character and binary types, this is the length of the character or binary data. For numeric types, it is the number of digits that would be needed to display the largest unsigned value of that type. For example, it is 3 for 1-byte numeric data (max 255), 5 for two-byte numeric data (max 65535), 10 for 32-bit numeric data (max 4294967295), 7 for single-precision floating point and 15 for double-precision floating point. columnSize is ignored for integer numeric types so you can pass zero for those types. See the Microsoft ODBC documentation for details.

decimalDigits is an integer specifying the maximum number of digits to the right of the decimal point which is also called the "scale" of the data. decimalDigits is 0 for all integer data types. It is undefined and is ignored for all other SQL data types except SQL_NUMERIC, SQL_DECIMAL and time-related types so you can pass zero in most cases. See the Microsoft ODBC documentation for details.

valueWave is a wave that is to receive the bound parameter data.

bufSizeInBytes is the number of bytes for one element in the value wave as shown in the table below.

indicatorWave is a 32-bit integer wave (Make/I that is used to provide the length/indicator data. indicatorWave must be a signed 32-bit integer wave as would be made using Make/I. For input parameters, you use it to specify the length of character and binary data types. For output parameters, it receives the length of the output value. It can also store special values that indicate abnormal conditions.

The valueType and bufSizeInBytes Parameters

The valueType and bufSizeInBytes parameters depend on the data wave as follows:

Data Wave Type	Value Type	Buf Size In Bytes
Double float	SQL_C_DOUBLE	8
Single float	SQL_C_FLOAT	4
32-bit integer, signed	SQL_C_SLONG	4
32-bit integer, unsigned	SQL_C_ULONG	4
16-bit integer, signed	SQL_C_SSHORT	2
16-bit integer, unsigned	SQL_C_USHORT	2
8-bit integer, signed	SQL_C_STINYINT	1
8-bit integer, unsigned	SQL_C_UTINYINT	1
Text	SQL_C_CHAR	Variable

SQL C data types not listed above are not currently supported by SQLBindParameter.

SQLBindParameter ignores the bufSizeInBytes parameter for fixed length data types which include all of the types shown above except for SQL_C_CHAR. It is OK for you to pass 0 as the bufSizeInBytes parameter for fixed length data types.

SQL_C_CHAR is the valueType to use for variable length string data. When you specify SQL_C_CHAR, valueWave must be a text wave. In this case, bufSizeInBytes is the maximum number of characters that you want to pass to ODBC for the corresponding parameter. For example, if you are providing data for a column that represents the name of a month, you might pick 20 for bufSizeInBytes because you know that all month names can fit in 20 characters. If you pick a bufSizeInBytes that is too small then the parameter will be truncated.

When you specify any type other than SQL_C_CHAR, valueWave must be a numeric wave.

Transfering Data from Waves into the SQL XOP Buffer

For technical reasons, SQL XOP actually binds parameters to internal buffers rather than directly to waves. However, in almost all cases this is transparent to you and you can ignore the internal buffers. The SQLExecDirect and SQLExecute functions automatically transfer the data from your bound waves to the internal buffers before calling the corresponding ODBC functions.

SQLBrowseConnect (connectionRefNum, connectionTextIn, connectionTextOut, connectionBufLen)

This function retrieves connection information. It can be used iteratively to enumerate connection attributes. The function itself returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is an integer reference number for a previously allocated connection handle.

connectionTextIn is an input string which contains server and user information. For example, if the host computer name is "myComputer", the user name is "Smith" and the password is "smithPWD" then the string format is: "HOST=myComputer;UID=Smith;PWD=smithPWD".

connectionTextOut is a pass-by-reference string which upon return contains the next batch of data used for the connection.

connectionBufLen is the maximum number of characters that you allow the driver to write into connectionTextOut.

SQLBulkOperations (statementRefNum, operation)

Update, delete or fetch a set of rows identified by a bookmark. The function itself returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

operation is a variable set to one of: SQL_ADD, SQL_UPDATE_BY_BOOKMARK, SQL_DELETE_BY_BOOKMARK, SQL_FETCH_BY_BOOKMARK.

SQLCancel (statementRefNum)

This function terminates the data-at-execution sequence corresponding to the specified statement. The function returns 0 if successful or an SQL result code.

Parameters

statementRefNum is a valid reference number of an open statement.

SQLCloseCursor (statementRefNum)

Closes the cursor and discards pending results. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a valid reference number of an open statement handle.

SQLColAttributeNum (statementRefNum, colNumber, fieldID, numAttr)

This function provides a numeric column descriptor information for a column in a result set. The function is the numeric version of SQLColAttributeStr. The function returns 0 if successful or an SQL result code.

Parameters

statementRefNum is a reference number to a previously allocated statement handle.

colNumber is the number of the column whose descriptor information is required. Column numbers start at 1.

fieldID is a constant that identifies the field, e.g., SQL_DESC_CONCISE_TYPE. You should make sure that the field identifier that you use corresponds to a numeric output. See SQLColAttributeStr if the field type corresponds to string output.

numAttr is a pass-by-reference variable which upon successful return contains the numeric value of the attribute.

SQLColAttributeStr (statementRefNum, colNumber, fieldID, attributeStr, bufSizeInBytes )

This function gets descriptor information for a column in an SQL result set. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number to a previously allocated statement handle.

colNumber is the number of the column whose descriptor information is required. Column numbers start at 1.

fieldID is a constant that identifies the field, e.g., SQL_DESC_CATALOG_NAME. You should make sure that the field identifier that you use corresponds to a string output. See SQLColAttributeNum if the field type corresponds to numeric output.

attributeStr is a local string that is passed by reference to the function. On return it contains the result sent from the driver. The string does not have to be initialized before passing it to the function. Upon return it will contain at most bugSizeInBytes characters. It is best to allow bufSizeInBytes to be large enough so there is no need to re-execute the call to obtain the whole string.

bufSizeInBytes is the maximum number of characters that you allow the driver to write into the attributeStr string.

SQLColumnPrivileges (statementRefNum, catalogName, schemaName, tableName, columnName)

This function creates an SQL result set containing a list of columns and their associated privileges. The function itself returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number to a previously allocated statement handle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database.

schemaName is the second part of the table name. "" should be accepted if it does not apply.

tableName is the string containing the name of the table.

columnName this could be a string containing a pattern to be matched.

Note: for each one of the strings that you pass to this function SQL XOP passes a null terminated string to the ODBC driver together with the corresponding length of the string.

SQLColumns (statementRefNum, catalogName, schemaName, tableName, columnName)

This function creates an SQL result set containing a list of columns in the specified tables. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number to a previously allocated statement handle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database. This parameter should not contain a string matching pattern.

schemaName is the second part of the table name. "" should be accepted if it does not apply. This parameter may contains a string search pattern for schema names.

tableName is the string containing the name of the table.

columnName could be a literal column name or string containing a pattern to be matched. "%" matches all columns.

If you pass "<NULL>" for any of the parameters, SQL XOP passes NULL for the corresponding parameter to ODBC.

This command seems to work reasonably well for most drivers:

SQLColumns(statementRefNum, "<NULL>", "<NULL>", <tableName>, "%")

where <tableName> is the name of a specific table.

SQLConnect (connectionRefNum, dsnName, userName, userPW)

Establishes a connection with a database. Returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is the reference number of a previously allocated connection handle (see SQLAllocHandle).

dsnName is the DSN (data source name).

userName and userPW are the name and password respectively of a user privileged to connect to the the database.

Example

// example without error checking:
Variable environmentRefNum,connectionRefNum
SQLAllocHandle(SQL_HANDLE_ENV,0,environmentRefNum)
SQLSetEnvAttrNum (environmentRefNum,SQL_ATTR_ODBC_VERSION,3)
SQLAllocHandle(SQL_HANDLE_DBC,environmentRefNum,connectionRefNum)
SQLConnect(connectionRefNum,txt,"myUserName","myPassword")

SQLDataSources (environmentRefNum, direction, dataSourceName, nameLen, description, descLen)

Returns in dataSourceName the name of one data source as entered into Data Sources on Windows. Returns in description descriptive information about the data source.

This is typically used to present to a user a list of data sources that he has created.

Parameters

environmentRefNum is the reference number of a previously allocated (see SQLAllocHandle) environment handle.

direction is one of the following on the first call to SQLDataSources: SQL_FETCH_FIRST, SQL_FETCH_FIRST_USER, SQL_FETCH_FIRST_SYSTEM. On subsequent calls, pass SQL_FETCH_NEXT.

dataSourceName is a local string variable passed by reference to this function. Upon successful return it contains the name of a data source. This string does not have to be initialized.

nameLen is the maximum number of characters that you allow the ODBC driver to return in the dataSourceName string. Any large value such as 100 will do.

description is a local string variable passed by reference to this function. Upon successful return it contains a description of the data source. This string does not have to be initialized.

descLen is the maximum number of characters that you allow the ODBC driver to write into the description string. Any large value such as 100 will do.

Details

When there are no more data sources to be returned, SQLDataSources returns SQL_NO_DATA as the function result.

Example

Function PrintDataSources()
    Variable environmentRefNum = -1
    Variable rc

    Variable result = 0
    try
        rc = SQLAllocHandle(SQL_HANDLE_ENV, 0, environmentRefNum)
        if (rc != SQL_SUCCESS)
        Print "Error while creating environment refnum."
        AbortOnValue 1, 1
        endif

        SQLSetEnvAttrNum(environmentRefNum, SQL_ATTR_ODBC_VERSION, 3)   // Specifies ODBC version 3.

        Variable index = 0

        do
            Variable direction
            String dataSourceName, description
            direction = index == 0 ? SQL_FETCH_FIRST : SQL_FETCH_NEXT
            rc = SQLDataSources(environmentRefNum,direction,dataSourceName,100,description,100)
            if (rc == SQL_SUCCESS)
            Print dataSourceName, description
            else
            if (rc == SQL_NO_DATA)
            break
            else
            Printf "Unexpected result from SQLDataSources: %d\r", rc
            AbortOnValue 1, 2
            endif
            endif
            index += 1
        while(1)
    catch
    result = V_abortCode
    Printf "Error %d\r", result
    endtry

    if (environmentRefNum != -1)
    SQLFreeHandle(SQL_HANDLE_ENV, environmentRefNum)
    endif

    return result
End

SQLDescribeCol (statementRefNum, columnNumber, columnName, bufSizeInBytes, dataType, columnSize, decimalDigits, isNullable)

This function provides information about a column in an existing result set. When successful the function returns 0 result with the remaining information returned in the pass-by-reference parameters. If the function fails it returns an SQL error code.

Parameters

statementRefNum is a reference number to a the statement handle which gave rise to the current result set.

columnNumber is an integer (1 based) number of the column to be described.

columnName is a local string passed by reference which will be used to return the name of the column.

bufSizeInBytes is the maximum number of characters that you allow the ODBC driver to write into the string columnName.

dataType is a pass-by-reference variable that upon successful return contains the integer code for an SQL data type.

columnSize is a pass-by-reference variable that returns the precision of the column.

decimalDigits is a pass-by-reference variable that returns the scale of the column in case it is decimal, numeric or timestamp data.

isNullable is a pass-by-reference variable that indicates if nulls are allowed in this column.

Example

// Printing the structure of the table:
String 	colName
Variable i,columnCount
Variable dataType,columnSize,decDigits,isNullable
SQLNumResultCols(statementRefNum,columnCount)

for(i=0;i<columnCount;i+=1)
    SQLDescribeCol(statementRefNum,i+1,colName,256,dataType,columnSize,decDigits,isNullable)
    print "name=",colName,"\tdataType=",dataType,"\tcolumnSize="
    Print columnSize,"\tdecDigits=",decDigits,"\tisNullable=",isNullable
endfor

SQLDescribeParam (statementRefNum, paramNumber, dataType, paramSize, decimalDigits, isNullable)

Gets a description of a parameter associated with a prepared statement. The function itself returns 0 if successful or an SQL error code.

According to Microsoft's ODBC documentation, "SQLDescribeParam is not widely supported" by database systems.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

paramNumber is a 1-based index of the parameter.

dataType is a pass-by-reference variable containing the SQL_DESC_CONCISE_TYPE record.

paramSize is a pass-by-reference variable which receives the size of the column or expression of the corresponding parameter marker.

decimalDigits is a pass-by-reference variable which receives the number of decimal digits of the column or expression as defined by the data source.

isNullable is a pass-by-reference variable which receives a value indicating if the parameter allow nulls. It can be one of the following: SQL_NO_NULLS, SQL_NULLABLE, SQL_NULLABLE_UNKNOWN.

SQLDisconnect (connectionRefNum)

This function closes a connection. The function returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is an integer reference to a valid connection handle. Note that although the connection is closed, the connection handle remains allocated.

SQLDriverConnect (connectionRefNum, inConnectionStr, outConnectionStr, outConnectionStrRequiredLength, driverCompletion)

Establishes a connection with a database using various techniques, depending on inConnectionStr.

Returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is the reference number of a previously allocated connection handle (see SQLAllocHandle).

inConnectionStr may contain information needed to connect to the database server, such as the data source name, user name and password. A typical form for it might be "DSN=;UID=;PWD=;". With some drivers, "DSN=" is sufficient.

If inConnectionStr does not provide enough information to make the connection and driverCompletion is SQL_DRIVER_NOPROMPT, SQLDriverConnect returns an error. If driverCompletion is other than SQL_DRIVER_NOPROMPT, the ODBC driver will display a connection dialog to prompt the user for missing information. However, some drivers do not support prompting.

If inConnectionStr fully specifies the connection information, you do not need to use a DSN. Here is an example:

String connectionStr = ""
connectionStr += "DRIVER=MySQL ODBC 5.3 ANSI Driver;"
connectionStr += "SERVER=bu.wavemetrics.com;"
connectionStr += "DATABASE=Demo1;"
connectionStr += "USER=DemoUser;"
nectionStr += "PWD=Demo;"
connectionStr += "CHARSET=UTF8;"		// For Igor7 or later
SQLHighLevelOp /CSTR={connectionStr,SQL_DRIVER_COMPLETE} <statement>

See the Microsoft ODBC documentation for SQLDriverConnect for the details of the inConnectionStr parameter.

On return, outConnectionStr contains the driver connection string used to make the connection, taking into account any information entered by the user through a driver dialog. This string can be used later to reconnect without a dialog.

outConnectionStrRequiredLength is a pass-by-reference output parameter that receives the required length to hold the output connection string. If (strlen(outConnectionStr)<outConnectionStrRequiredLength) then outConnectionStr has been truncated but this should rarely if ever happen.

driverCompletion is the completion mode: SQL_DRIVER_NOPROMPT, SQL_DRIVER_COMPLETE, SQL_DRIVER_PROMPT, or SQL_DRIVER_COMPLETE_REQUIRED. This parameter determines what SQLDriverConnect does if the information provided by inConnectionStr is not sufficient to make the connection. If you expect inConnectionStr to be sufficient to make the connection, use SQL_DRIVER_NOPROMPT. See the Microsoft ODBC documentation for SQLDriverConnect for the details of the driverCompletion parameter.

Details

According to Microsoft's documentation, the driver is supposed to return SQL_NO_DATA if the user cancels from a dialog displayed by the driver. However, the the MySQL driver prior to version 5.1.5 returns SQL_ERROR, not SQL_NO_DATA in this event.

See also The One-Command-Per-Connection Method for a discussion of driver connection strings.

Example

// This function assumes that a DSN named IgorDemo1 exists.
#include <SQLUtils>
Function ConnectToDemoDatabase()
    Variable environmentRefNum, connectionRefNum
    SQLAllocHandle(SQL_HANDLE_ENV, 0, environmentRefNum)
    SQLSetEnvAttrNum (environmentRefNum, SQL_ATTR_ODBC_VERSION, 3)
    SQLAllocHandle(SQL_HANDLE_DBC, environmentRefNum, connectionRefNum)

    String inConnectionStr
    inConnectionStr = "DSN=IgorDemo1;UID=DemoUser;PWD=Demo;"

    String outConnectionStr
    Variable outConnectionStrRequiredLength
    Variable result
    result = SQLDriverConnect(connectionRefNum, inConnectionStr, outConnectionStr, outConnectionStrRequiredLength, SQL_DRIVER_NOPROMPT)
    switch(result)
        case SQL_SUCCESS:
            Print outConnectionStr
            SQLDisconnect(connectionRefNum)
            break
        case SQL_SUCCESS_WITH_INFO:
            PrintSQLDiagnostics(SQL_HANDLE_DBC,connectionRefNum,1)
            Print outConnectionStr
            SQLDisconnect(connectionRefNum)
            break
        case SQL_NO_DATA:
            // The driver is supposed to return SQL_NO_DATA if the user cancels.
            // However, the MySQL driver prior to 5.1.5 returns SQL_ERROR, not SQL_NO_DATA in this event.
            Print "User cancelled."
            break
        default:                // Error
            PrintSQLDiagnostics(SQL_HANDLE_DBC,connectionRefNum,1)
            break
    endswitch

    SQLFreeHandle(SQL_HANDLE_DBC, connectionRefNum)
    SQLFreeHandle(SQL_HANDLE_ENV, environmentRefNum)
End

SQLDrivers (environmentRefNum, direction, driverDescription, dDbufLength, driverAttributes, dABufLen)

Lists driver description and driver attribute. The function itself returns 0 if successful or an SQL error code.

Parameters

environmentRefNum is an integer reference to a previously allocated environment handle.

direction is an integer that specifies which description is returned by the driver. Valid choices are SQL_FETCH_NEXT and SQL_FETCH_FIRST.

driverDescription is a pass-by-reference string which upon return contains the description of the driver.

dDbufLength is the number of characters which you allow the driver to write into the driverDescription string.

driverAttributes is a pass-by-reference string which upon return contains the requested attribute (based on direction).

dABufLen is the maximum number of characters which you allow the driver to write into the string driverAttributes.

Example

// listing available drivers:
Variable environRefNum
String ddesc,attr
SQLAllocHandle(SQL_HANDLE_ENV,0,environRefNum)
SQLSetEnvAttrNum(environRefNum,SQL_ATTR_ODBC_VERSION,3)
SQLDrivers(environRefNum,SQL_FETCH_FIRST,ddesc,256,attr,256)
print ddesc,attr

SQLEndTran (handleType, referenceNumber, completionType)

This function ends transactions associated with a connection or an environment. The function returns 0 if successful or an SQL error code.

Parameters

handleType is either SQL_HANDLE_ENV or SQL_HANDLE_DBC.

referenceNumber is the reference number of the handle whose type is passed in the first parameter.

completionType is an integer which may be one of: SQL_COMMIT or SQL_ROLLBACK.

SQLError ()

This function has been superceded by SQLGetDiagRec.

SQLExecute (statementRefNum)

Execute a previously prepared statement. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number of a previously allocated and prepared statement.

SQLExecDirect (statementRefNum, statementText)

Directly executes the specified text. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number of a previously allocated statement.

statementText is a string containing the SQL statement to execute.

Example

Variable statementRefNum
sqlAllocHandle(SQL_HANDLE_STMT,connectionRefNum,statementRefNum)
String txt="Select * from golfScoresTable where score<72"
SQLExecDirect(statementRefNum,txt)

SQLFetch (statementRefNum)

Use this function to advance the cursor to the next row of a result set.

If the statement included bound values they are updated for the new row.

The function returns 0 if successful or an SQL result code.

SQLFetchScroll (statementRefNum, fetchOrientation, fetchOffset)

Fetches the specified rowset of data from the result set.

Returns data for all bound columns.

The function returns 0 if successful or an SQL result code.

For most applications, SQLFetch is sufficient. SQLFetchScroll is for advanced programmers only.

Parameters

statementRefNum is a reference number of a previously allocated and prepared statement.

fetchOrientation is one of the predefined SQL constants e.g., SQL_FETCH_NEXT.

fetchOffset is a number. Its interpretation depends on the choice of fetchOrientation.

SQLForeignKeys (statementRefNum, catalogName, schemaName, tableName, FKCatalogName, FKSchemaName, FKTableName)

Gets information about foreign keys for the specified table. The information is returned as an SQL result set. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database.

schemaName is the second part of the table name. "" should be accepted if schemaName does not apply.

tableName is the string containing the name of the table of the primary key.

FKCatalogName is the catalog qualifier of a 3 part foreign key table name. Use an empty string ("") if this is not supported by your database.

FKSchemaName is the second part of the (foreign key) table name. "" should be accepted if it does not apply.

FKTableName is the name of the table containing the foreign key.

SQLFreeConnect ()

This function has been superceded by SQLFreeHandle.

SQLFreeEnv ()

This function has been superceded by SQLFreeHandle.

SQLFreeHandle (handleType, handleRefNum)

This function frees resources associted with the specified handle (it reverses SQLAllocHandle) and returns 0 if successful or an SQL error code.

Parameters

handleType is one of SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT or SQL_HANDLE_DESC.

handleRefNum is the reference number of the specific handle which was returned from SQLAllocHandle.

Details

The first role of this function is to release previously allocated handles. In the case of statement handles or descriptor handles the function also releases associated memory buffers and unlocks any waves that may have been locked by binding data to columns or to parameters.

SQLFreeStmt (statementRefNum, option)

This function ends the processing on the specified statement. Additional actions depend on the contents of the option parameter. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number of a previously allocated statement.

option is one of SQL_CLOSE, SQL_DROP, SQL_UNBIND, SQL_RESET_PARAMS. You should not use the freed handle after this call.

SQLGetConnectAttrNum (connectionRefNum, attribute, valueNum)

Retrieves the current value of a numeric connection attribute. The function returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is the reference number of the connection handle.

attribute is an integer specifying the attribute value to retrieve.

valueNum is a pass-by-reference variable that is used to return numeric attribute value.

SQLGetConnectAttrStr (connectionRefNum, attribute, attrText, bufLenInBytes)

Retrieves the current setting of a string connection attribute. The function returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is the reference number of the connection handle.

attribute is an integer specifying the attribute value to retrieve.

attrText is a pass-by-reference string which upon successful return contains the current value of the attribute.

bufLenInBytes is the maximum number of characters that you expect to receive in attrText.

SQLGetCursorName (statementRefNum, cursorNameStr)

Retrieves the name of the cursor associated with the specified statement. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number of a previously allocated statement.

cursorNameStr is a pass-by-reference string that upon successful return contains the cursor's name.

SQLGetDataStr (statementRefNum, columnNumber, dataStr, bufSizeInBytes, indicator)

Retrieves text data for a single column in the current row of the result set. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number of a previously allocated and executed statement.

columnNumber is the integer (1 based) column number to retrieve.

dataStr is a pass-by-reference string that returns the actual data.

bufSizeInBytes is the maximum number of bytes that you allow the driver to store in dataStr.

indicator is a pass-by-reference variable that is set to SQL_NULL_DATA if the data value for the column is null.

Example

// Read a number from col 1 and a string from col 2:
Variable numVal,indicator
String strVal
SQLFetch(statementRefNum)	// Fetch next row
SQLGetDataNum(statementRefNum,1,numVal,indicator)
SQLGetDataStr(statementRefNum,2,strVal,512,indicator)
Print numVal, strVal

SQLGetDataNum (statementRefNum, columnNumber, outValue, indicator)

Retrieves numeric data for a single column in the current row of the result set. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number of a previously allocated and executed statement.

columnNumber is the integer (1 based) column number to retrieve.

outValue is a pass-by-reference variable that returns the actual data. Internally SQL XOP specifies the output as SQL_C_DOUBLE.

indicator is a pass-by-reference variable that is set to SQL_NULL_DATA if the data value for the column is null.

Example

// Read a number from col 1 and a string from col 2:
Variable numVal,indicator
String strVal
SQLFetch(statementRefNum)	// Fetch next row
SQLGetDataNum(statementRefNum,1,numVal,indicator)
SQLGetDataStr(statementRefNum,2,strVal,512,indicator)
Print numVal, strVal

SQLGetDescFieldNum (descriptorRefNum, recordNumber, fieldIdentifier, outValue)

Retrieves the current text setting of a single field of a descriptor record. The function returns 0 if successful or an SQL error code.

Parameters

descriptorRefNum is an integer reference of a previously allocated descriptor handle.

recordNumber is a zero based record number (0 is the bookmark record).

fieldIdentifier is an integer identified the field of the descriptor that is retrieved.

outValue is a pass-by-reference variable that returns the value of a double precision number retrieved from teh field value.

SQLGetDescFieldStr (descriptorRefNum, recordNumber, fieldIdentifier, dataStr, bufSizeInBytes)

Retrieves the current text setting of a single field of a descriptor record. The function returns 0 if successful or an SQL error code.

Parameters

descriptorRefNum is an integer reference of a previously allocated descriptor handle.

recordNumber is a zero based record number (0 is the bookmark record).

fieldIdentifier is an integer identified the field of the descriptor that is retrieved.

dataStr is a pass-by-reference string that upon successful return contains the text value.

bufSizeInBytes is the maximum number of characters you allow the ODBC driver to return in dataStr.

SQLGetDescRec (descriptorRefNum, recNumber, name, nameBufLen, type, length, precision, scale, isNullable)

Retrieves the settings of various fields of the descriptor record. The function returns 0 if successful or an SQL error code.

Parameters

descriptorRefNum is an integer reference to a descriptor record previously allocated with SQLAllocHandle.

recNumber is a zero based record number (0 is the bookmark record).

name is a pass-by-reference string which upon return contains the name of the record.

nameBufLen is the maximum number of bytes that you allow the driver to write into the name string.

type is a pass-by-reference variable which upon return contains the SQL descriptor type.

length is a pass-by-reference variable which upon return contains the SQL_DESC_OCTET_LENGTH field for the descriptor record.

precision is a pass-by-reference variable which upon return contains the SQL_DESC_PRECISION field of the descriptor record.

scale si a pass-by-reference variable which upon return contains the SQL_DESC_SCALE field of the descriptor record.

isNullable is a pass-by-reference variable which upon return contains the SQL_DESC_NULLABLE field fo the descriptor record.

SQLGetDiagFieldNum (handleRefNum, handleType, recNumber, diagIdentifier, outValue)

Retrieves a numeric value of a field of the diagnostic data structure for the specific handle. The function returns 0 if successful or an SQL error code.

Parameters

handleRefNum is an integer reference number to a handle previously allocated with SQLAllocHandle.

handleType is the type of the handle (one of: SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT or SQL_HANDLE_DESC.)

recNumber is an integer status record. These records are 1 based. The value 0 is reserved for diagIdentifier values pointing to the diagnostic header recrod.

diagIdentifier is a variable containing the identifier of the field whose value is returned.

outValue is a pass-by-reference variable that upon successful return contains the numeric value of the diagnostic data structure.

SQLGetDiagFieldStr (handleRefNum, handleType, recNumber,diagItentifier, diagText, bufLenInBytes)

Retrieves text value of a field of the diagnostic data structure for the specific handle. The function returns 0 if successful or an SQL error code.

Parameters

handleRefNum is an integer reference number to a handle previously allocated with SQLAllocHandle.

handleType is the type of the handle (one of: SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT or SQL_HANDLE_DESC.)

recNumber is an integer status record. These records are 1 based. The value 0 is reserved for diagIdentifier values pointing to the diagnostic header recrod.

diagIdentifier is a variable containing the identifier of the field whose value is returned.

diagText is a pass-by-reference string which upon return contains diagnostic information

bufLenInBytes is the number of bytes which you allow the driver to write into diagText.

SQLGetDiagRec (handleRefNum, handleType, recNumber, sqlState, nativeError, diagText, bufLen)

Retrieves several fields of the diagnostic record for error, warning or status information. The function returns 0 if successful or an SQL error code.

Parameters

handleRefNum is an integer reference number to a handle previously allocated with SQLAllocHandle.

handleType is the type of the handle (one of: SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT or SQL_HANDLE_DESC.)

recNumber is an integer status record. These records are 1 based. The value 0 is reserved for diagIdentifier values pointing to the diagnostic header recrod.

sqlState is a pass-by-reference string which upon successful return contains a five character SQL code for the diagnostic record number.

nativeError is a pass-by-reference variable which upon return contains the native ODBC error code for the specific data source.

diagText is a pass-by-reference string which upon return contains the text of the message.

bufLen is the maximum number of characters which you allow the driver to write into the diagText string.

Example

// Use in error checking.
Variable rc
rc=SQLPrepare(refNum,txt)
if(rc)
    String state,messageText
    Variable error
    SQLGetDiagRec(refNum,SQL_HANDLE_STMT,1,state,error,messageText,256)
    Print error,messageText
endif

SQLGetEnvAttrStr (environRefNum, attribute, attrText, bufLenInBytes)

Retrieves the current setting of a specific environment attribute. This is the string version of the function. For numeric attributes use SQLGetEnvAttrNum. The function returns 0 if successful or an SQL error code.

Parameters

environRefNum is an integer reference to a previously allocated environment.

attribute is an integer constant specifying the particular attribute that you want to get.

attrText is a pass-by-reference string which upon successful return contains the current value of the attribute.

bufLenInBytes is the maximum number of characters that you expect to receive in attrText.

SQLGetEnvAttrNum (environRefNum, attribute, attrValue)

Retrieves the current setting of a specific environment attribute. This is the numeric version of the function. Use SQLGetEnvAttrStr to retrieve strings. The function returns 0 if successful or an SQL error code.

Parameters

environRefNum is an integer reference to a previously allocated environment.

attribute is an integer constant specifying the particular attribute that you want to get.

attrValue is a pass-by-reference variable which upon successful return contains the value of the attribute.

SQLGetFunctions (connectionRefNum, functionID, isSupported, outWave)

Queries the database if a specific function or a set of functions are supported. The function returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is an integer reference to a connection handle.

functionID is an integer specifying which function is being queried. You can pass SQL_API_ALL_FUNCTIONS in which case the result is an array of 100 functions returned in outWave. You can also pass SQL_API_ODBC3_ALL_FUNCTIONS_SIZE in which case the result is an array of 250 values.

isSupported is a pass-by-reference variable which is used when the statement references a single function. In this case the value is set to SQL_TRUE is the function is supported and SQL_FALSE if it is not. When the statement references multiple functions the contents of this variable are undefined.

outWave is a numeric wave which is used to return the statement results for an array of functions specified in functionID. If functionID references a single function you can pass a null wave for this parameter.

SQLGetInfoNum (connectionRefNum, infoType, outValue)

This function retrieves numeric information about the database to which it is connected. The function returns 0 if successful.

Parameters

connectionRefNum is an integer reference to a connection handle.

infoType is an integer specifying the type of information to be retrieved.

outValue is a pass-by-reference variable which is set to the requested numberical value. Note that internally this value is read as a 32bit unsigned integer.

SQLGetInfoStr (connectionRefNum, infoType, infoText, bufLenInBytes)

This function retrieves text information about the database to which it is connected. The function returns 0 if successful.

Parameters

connectionRefNum is an integer reference to a connection handle.

infoType is an integer specifying the type of information to be retrieved.

infoText is a pass-by-reference string which upon return contains the requested text information.

bufLenInBytes is the maximum number of characters that you allow the driver to write into the infoText string.

SQLGetStmtAttrNum (statementRefNum, attribute, outValue)

Retrieves the numeric setting of a statement attribute. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement.

attribute is an integer specification of the attribute to retrieve.

outValue is a pass-by-reference variable which upon successful return contains the statement attribute value.

SQLGetStmtAttrStr (statementRefNum, attribute, valueText, bufLenInBytes)

Retrieves a text setting of a statement attribute. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement.

attribute is an integer specification of the attribute to retrieve.

valueText is a pass-by-reference string which upon successful return contains the statement attribute.

bufLenInBytes is the maximum number of characters which you allow the driver to write to the string.

SQLGetTypeInfo (statementRefNum, dataType)

This function queries the DBMS for a list of data types that it supports. The information is returned as an SQL result set. The function itself returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

dataType is an SQL data type e.g., SQL_BLOB, etc. Pass SQL_ALL_TYPES to list all data types supported by the DBMS.

SQLMoreResults (statementRefNum)

SQLMoreResults is used only for advanced queries that return multiple result sets. It discards the current result set and makes the next result set available.

Multiple result sets are generated by:

A batch query statement
An query called with an array of input parameter values
A stored procedure that is returning result sets for a batch query or array of input parameters.

The function returns SQL_SUCCESS if another result set is available, SQL_NO_DATA if all the results have been processed.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

SQLNativeSQL (connectionRefNum, inSqlText, outSqlText)

Translates the input SQL statement to a syntax native to the driver. The function itself returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is an integer reference to a previously allocated and initialized connection to a database.

inSqlText is a string containing the input SQL statement.

outSqlText is a pass-by-reference string which upon return contains the driver modified SQL statement.

SQLNumParams (statementRefNum, numParams)

Retrieves the number of parameter markers in an SQL statement. The function itself returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously prepared SQL statement.

numParams is a pass-by-reference variable which upon successful return contains the resulting number of parameter markers.

SQLNumResultCols (statementRefNum, columnCount)

Retrieves the number of columns in the result set. The function itself returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

columnCount is a pass-by-reference variable which upon return contains the number of columns in the result set.

SQLNumResultRowsIfKnown (connectionRefNum, statementRefNum, type, numRows)

Returns via the pass-by-reference parameter numRows the number of rows in the result set if that is supported by the ODBC driver for the particular database being used.

The function result 0 if successful or an SQL error code.

This function does not correspond to an ODBC function. It is a WaveMetrics-supplied utility that calls multiple ODBC functions.

Parameters

connectionRefNum is an integer reference to a previously allocated connection handle.

statementRefNum is an integer reference to a previously allocated statement handle.

type is one of the following symbols (which are defined in SQLConstants.ipf):

SQL_DYNAMIC_CURSOR_ATTRS2
SQL_FORWARD_ONLY_CURSOR_ATTRS2
SQL_KEYSET_CURSOR_ATTRS2
SQL_STATIC_CURSOR_ATTRS2

type must be consistent with the type of cursor you are using. The default cursor type is forward-only so if you have not changed the cursor type, use SQL_FORWARD_ONLY_CURSOR_ATTRS2 for type.

numRows is a pass-by-reference variable which upon return contains the number of rows in the result set or -1 if that information is not available from the driver.

Details

When you fetching data from a database into waves, it would be nice to know how many rows of data you are fetching in advance so you can make the waves the right size. Unfortunately, ODBC does not provide a simple or foolproof way to do this. At first glance, it would appear that SQLRowCount would do the trick and, in fact, often it will. But, if you read the Microsoft documentation carefully, you will see that SQLRowCount is guaranteed to return a valid row count only after you execute an UPDATE, INSERT or DELETE statement, not after you execute a SELECT statement.

A Microsoft ODBC topic ("Was a Result Set Created?"), provides a clue as to how to work around this problem. The workaround involves calling SQLGetDiagField to get the value associated with the SQL_DIAG_CURSOR_ROW_COUNT header field. However, this does not work with all ODBC drivers.

SQLNumResultRowsIfKnown implements this workaround. You can call it after executing a statement that contains a SELECT and before you fetch the results. If it returns a non-negative number via numRows then you know the number of result rows and you can make your waves with that number of rows.

If SQLNumResultRowsIfKnown returns a negative value then the number of result rows is not known and you have to use another techique.

If the number of result rows is not know but you can specify the largest number of rows expected then you can make your waves with that many points, do a single block cursor fetch, and then truncate your waves to the correct size. See Using A Block Cursor To Fetch Multiple Rows At One Time for details.

If the number of result rows is not known but you can not specify the largest number of rows expected then you must call SQLFetch in a loop. See Using Binding To Fetch One Row At One Time for details.

SQLNumRowsFetched (statementRefNum, numRowsFetched)

Returns via the pass-by-reference parameter numRowsFetched the number of rows fetched by the most recent call to SQLFetch or SQLFetchScroll for the specified statement.

The function result is 0 if successful or an SQL error code.

This function does not correspond to an ODBC function. It is a WaveMetrics-supplied utility that provides the functionality of the ODBC SQL_ATTR_ROWS_FETCHED_PTR statement attribute.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

numRowsFetched is a pass-by-reference parameter which upon return contains the number of rows fetched by the most recent call to SQLFetch or SQLFetchScroll for the specified statement.

Details

This function is of use when you use block cursors. You may specify a block size of n rows but, if there are fewer than n rows in the result set, SQLFetch and SQLFetchScroll will return fewer than n rows. This function provides a way for you to determine the number of rows fetched. You can use this information to truncate any bound waves to the correct size.

SQLParamData (statementRefNum, name)

This function is used to retrieve the name of the next parameter for which data has not been assigned. In standard ODBC the function return the address of the next parameter that should be updated. Due to the structure of SQL XOP, it returns in the pass-by-reference name string the name of the previously bound wave whose data is expected. The function returns SQL_NEED_DATA if there is at least one parameter for which data has not been assigned.

SQLPrepare (statementRefNum, statementText)

Associates an SQL statement in the form of text with an SQL statement handle and prepare it for execution by SQLExecute. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

statementText is an SQL statement passed in a string.

SQLPrimaryKeys (statementRefNum, catalogName, schemaName, tableName)

Creates an SQL result set containing a list of column names that comprise the primary key for a table. The function itself returns 0 if successful or an SQL result code.

Parameters

statementRefNum is an integer reference number previously allocated by a call to SQLAllocHandle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database.

schemaName is the second part of the table name. "" should be accepted if it does not apply.

tableName is the string containing the name of the table.

SQLProcedures (statementRefNum, catalogName, schemaName, procedureName)

Creates an SQL result set containing a list of stored procedure names that have been registered with the server and which match a specific pattern.

Parameters

statementRefNum is an integer reference number previously allocated by a call to SQLAllocHandle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database.

schemaName is the second part of the table name. "" should be accepted if a schema name does not apply.

procedureName is a string search pattern for procedure names.

SQLProcedureColumns (statementRefNum, catalogName, schemaName, procedureName, columnName)

Creates an SQL result set containing a list of input and output parameters associated with a stored procedure.

Parameters

statementRefNum is an integer reference number previously allocated by a call to SQLAllocHandle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database.

schemaName is the second part of the table name. "" should be accepted if it does not apply.

tableName is the string containing the name of the table.

procName is a string containing a pattern value to qualify the result set by a procedure name. It is valid to pass "" for this parameter

columnName is a string containing a pattern value to further qualify the result set by parameter name.

SQLPutData (statementRefNum, dataWave, indicatorValue)

Use this function to provide parameter data after SQLParamData() returns SQL_NEED_DATA. The function returns 0 when successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

dataWave provides the actual data passed to the driver.

indicatorValue is a variable in which you specify the length of the data in dataWave. It is a good idea to pass the total number of bytes although some constants e.g., SQL_NULL_DATA are also supported.

SQLReinitialize ()

This function can be called to perform a complete reinitialization of SQL XOP.

All handles (including environment handles, connection handles, statement handles and descriptor handles) are deallocated. All bound values are unlocked and all associated memory allocations are freed.

The function returns 0.

Under normal circumstances, all handles, bindings and other resources will be freed when you call the appropriate function (e.g., SQLFreeHandle). SQLReinitialize is needed only if you do not free handles, possibly because of an error condition.

SQLRowCount (statementRefNum, rowCount)

Gets the number of rows affected by an INSERT, UPDATE or DELETE statement.

The function returns 0 if successful or an SQL error code.

SQLRowCount is valid only after an INSERT, UPDATE or DELETE statement. Although some ODBC drivers return a valid row count after a SELECT statement, this is not guaranteed. Try SQLNumResultRowsIfKnown instead.

According to Microsoft's ODBC documentation (topic "Was a Result Set Created?"), the use of SQLRowCount to determine the number of rows in a result set is not supported by all database systems. The Microsoft documentation for SQLRowCount warns "Many data sources cannot return the number of rows in a result set before fetching them; for maximum interoperability, applications should not rely on this behavior."

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

rowCount is a pass-by-reference variable which upon successful return contains the number of rows in the result set.

SQLSetConnectAttrStr (connectionRefNum, attribute, valueText)

Sets text type attribute for the specified connection. The function returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is an integer reference for a previously allocated connection handle.

attribute is an integer attribute identifier.

valueText is a string containing the set attribute value.

SQLSetConnectAttrNum (connectionRefNum, attribute, value)

Sets numeric type attribute for the specified connection. The function returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is an integer reference for a previously allocated connection handle.

attribute is an integer attribute identifier.

value is an integer-type numeric value.

SQLSetCursorName (statementRefNum, cursorName)

Sets the name of the name of the cursor fo the specified statement. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

cursorName is a string containing the assigned name.

SQLSetDescFieldNum (descriptorRefNum, recNumber, fieldIdentifier, value)

Sets a numeric value of a single field of a descriptor record. The function returns 0 if successful or an SQL error code.

Parameters

descriptorRefNum is an integer reference to a previously allocated descriptor handle.

recNumber is an integer record number starting at 1. 0 is reserved for the bookmark record.

fieldIdentifier is an integer identifying the field to be set. You have to make sure that this is a numeric field; otherwise use SQLSetDescFieldStr.

value is a variable containing the value assigned to the descriptor field.

SQLSetDescFieldStr (descriptorRefNum, recNumber, fieldIdentifier, textValue)

Sets the text value of a single field of a descriptor record. The function returns 0 if successful or an SQL error code.

Parameters

descriptorRefNum is an integer reference to a previously allocated descriptor handle.

recNumber is an integer record number starting at 1. 0 is reserved for the bookmark record.

fieldIdentifier is an integer identifying the field to be set. You have to make sure that this is a text field; otherwise use SQLSetDescFieldNum.

textValue is a string containing the value that you want to assign to the descriptor field.

SQLSetDescRec (descriptorRefNum, recNumber, type, subType, length, precision, scale, dataStr, dataLen, indicator)

Sets multiple descriptor fields for a single record. The function returns 0 if successful or an SQL error code.

Parameters

descriptorRefNum is an integer reference to a previously allocated descriptor handle.

recNumber is an integer record number starting at 1. 0 is reserved for the bookmark record.

type is an integer containing SQL_DESC_TYPE field of the descriptor record.

subType is a variable containing the SQL_DESC_DATETIME_INTERVAL_CODE. It is used when the record type is SQL_DESC_TYPE.

length is the value setting for SQL_DESC_OCTET_LENGTH field.

precision is a variable setting for SQL_DESC_PRECISION field.

scale is a variable setting for SQL_DESC_SCALE field.

dataStr is a possibly empty or even null string that is used to set the SQL_DESC_DATA_PTR. Although this and the following two parameters are expected to be deferred inputs, you do not need to do anything special because SQL XOP provides pointers to copies of the data that you pass to this function.

Make sure to call SQLFreeHandle to release the associated memory when it is no longer needed.

SQLSetEnvAttrNum (evironmentRefNum, attribute, attributeValue)

Sets an environment attribute to attributeValue. If the attribute requires a string then use SQLSetEnvAttrStr. The function returns 0 if successful or an SQL error code.

Parameters

environmentRefNum is an integer reference number for a previously allocated environment handle.

attribute is an integer constant identifying the specific attribute to set, e.g., SQL_ATTR_OUTPUT_NTS.

attributeValue is the new integer value for the attribute .

SQLSetEnvAttrStr (evironmentRefNum, attribute, attributeString)

Sets an environment attribute to the passed string. If the attribute requires a numberic value use SQLSetEnvAttrNum. The function returns 0 if successful or an SQL error code.

Parameters

environmentRefNum is an integer reference number for a previously allocated environment handle.

attribute is an integer constant identifying the specific attribute to set, e.g., SQL_ATTR_CURRENT_CATALOG.

attributeString is a string containing the new setting of the attribute.

SQLSetPos (statementRefNum, rowNumber, operation, lockType)

Sets the cursor's position in a rowset and performs the operation specified in the operation parameter. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference number previously allocated by a call to SQLAllocHandle.

rowNumber is an integer position in the rowset on which to perform the specified operation. A value of 0 applies to all rows in the rowset.

operation specifies the operation. It is one of the following constants: SQL_POSITION, SQL_REFRESH, SQL_UPDATE, SQL_DELETE.

lockType specifies how to lock the row after performing the operation. Valid values include: SQL_LOCK_NO_CHANGE, SQL_LOCK_EXCLUSIVE, SQL_LOCK_UNLOCK.

Details

If operation is SQL_UPDATE, SQL XOP updates its internal buffers from any bound parameter waves (bound by SQLBindParameter) before calling the ODBC SQLSetPos function. If operation is SQL_REFRESH, SQL XOP updates any bound result waves (bound by SQLBindCol) after calling the ODBC SQLSetPos function.

SQLSetStmtAttrNum (statementRefNum, attribute, attributeValue)

Sets an attribute of a statement handle to an integer value. If the attribute should be set to a string use SQLSetStmtAttrStr. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

attribute is an integer specifying the particular attribute to be set.

attributeValue is the new integer value for the attribute.

Details

Whereas the ODBC SQLSetStmtAttr function has a fourth parameter, this function has only three. The fourth parameter is not needed for numeric attributes.

SQLSetStmtAttrStr (statementRefNum, attribute, attributeText)

Sets an attribute of a statement handle to some text. If you need to set the attribute to a numeric value use SQLSetStmtAttrNum. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

attribute is an integer specifying the particular attribute to be set.

attributeText is a string containing the text value for the attribute.

Details

Whereas the ODBC SQLSetStmtAttr function has a fourth parameter, this function has only three. The fourth parameter is not needed because this function automatically determines the length of attributeText .

SQLSpecialColumns (statementRefNum, identifierType, catalogName, schemaName, tableName, scope, isNullable)

Gets unique row identifier information for a table. The information is returned as an SQL result set. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

identifierTypeis an integer. The only supported value is SQL_BEST_ROWID.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database.

schemaName is the second part of the table name. "" should be accepted if it does not apply.

tableName is the string containing the name of the table.

scope is an integer specifying the minimum required duration for which the unique row identifier is valid. It can be SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION or SQL_SCOPE_SESSION.

isNullable is a variable that determines if the returned special columns can have nulls. The value must be one of: SQL_NO_NULLS or SQL_NULLABLE.

SQLStatistics (statementRefNum, catalogName, schemaName, tableName, unique, reserved)

Retrieves index information for the specified table. The information is returned in an SQL result set. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database.

schemaName is the second part of the table name. "" should be accepted if it does not apply.

tableName is the string containing the name of the table.

unique is a variable which selects the type of index information. It is either: SQL_INDEX_UNIQUE or SQL_INDEX_ALL.

reserved is a variable that should be set to SQL_QUICK. You can also use SQL_ENSURE but that will most likely be reset to SQL_QUICK anyway.

SQLTables (statementRefNum, catalogName, schemaName, tableName, tableType)

Gets a list of table names and associated information as an SQL result set. The function itself returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

catalogName, schemaName, and tableName comprise the three part full identification for the table in question.

tableType is a string that may contain one or more values which qualifies the set of returned tables. The list is comma-separated uppercase values, e.g., "TABLE, VIEW". You can also use an empty string ("") to allow all table type possibilities although this may not work with some ODBC drivers.

Details

This function creates a result set with column names TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, and REMARKS.

Empirically, we found that the behavior of this function is different for different ODBC drivers. The Microsoft documentation for SQLTables leaves a lot to the imagination. It can be found here:

http://msdn.microsoft.com/en-us/library/ms711831.aspx

"%" signifies "all" for the catalogName, schemaName, and tableName parameters and "" signifies "not used". However, empirically we found that this was not consistent across ODBC drivers.

If you pass "<NULL>" for any of the parameters, SQL XOP passes NULL for the corresponding parameter to ODBC. For the first three parameters, passing "<NULL>" should be the same as passing "%", but we found that this was not the case for all drivers.

This command seems to work reasonably well for most drivers:

SQLTables(statementRefNum, "<NULL>", "<NULL>", "<NULL>", "TABLE,VIEW")

If you are using Microsoft SQL Server, you most likely should pass "dbo" for the schemaName parameter.

SQLTablePrivileges (statementRefNum, catalogName, schemaName, tableName)

Gets the list of tables and associated privileges in an SQL result set. The function itself returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference number previously allocated by a call to SQLAllocHandle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database.

schemaName is the second part of the table name. "" should be accepted if it does not apply.

tableName is the string containing the name of the table.

SQLTextWaveToBinaryWaves (textWave , baseName)

SQLTextWaveToBinaryWaves unpacks a single text wave into a series of numeric waves. This is used to unpack a column of binary data received from the database server through a call to SQLHighLevelOp. See Handling Binary Data With SQLHighLevelOp for an explanation of why and when this would be used.

The function result is always 0.

Parameters

textWave is the the text wave containing the packed binary data.

baseName is the base name to use for the output numeric waves which will be created in the current data folder.

Details

The input text wave will usually be previously created by SQLHighLevelOp when it executes a query that returns a column of binary data (e.g., BLOB data).

SQLTextWaveToBinaryWaves stores the raw binary data from each row of the input text wave in a newly-created output numeric wave of type unsigned byte.

The output numeric waves are created in the current data folder.

note

When the output numeric waves are created, any waves with the same name in the current data folder are overwritten.

If you use SQLTextWaveToBinaryWaves as originally conceived, the text wave will contain unsigned byte data stored in it by SQLHighLevelOp. SQLTextWaveToBinaryWaves will unpack the unsigned byte data into a series of numeric unsigned byte waves.

If you previously created the text wave by calling SQLBinaryWavesToTextWave on your own data, you need to know that the data type, dimensionality and other properties of the original numeric waves are not stored by SQLBinaryWavesToTextWave and therefore are not restored by SQLTextWaveToBinaryWaves. Only the raw data is restored.

What you do with the unpacked binary data depends on its nature. For example, if the binary data represents an image in JPEG format, you may want to write the binary data to a .jpg image file using FBinWrite and then read it into Igor as a picture using LoadPICT. If the binary data represents 16-bit signed integer wave data, you can change it from 8-bit unsigned to 16-bit signed like this:

Redimension /E=1 /Y=(0x10) /N=(numpnts(w)/2) w

Example

Function TestTextToBinary()
    // Make some test numeric waves.
    Make/N=10/W/U binaryWaveA0 = p      // 16-bit signed data
    Make/N=10/W/U binaryWaveA1 = p + 1
    Make/N=10/W/U binaryWaveA2 = p + 2

    // Pack the numeric waves into a text wave.
    SQLBinaryWavesToTextWave("binaryWaveA", 3, "binaryPackedTextWave")

    // Unpack the text wave into binary waves.
    SQLTextWaveToBinaryWaves(binaryPackedTextWave, "binaryWaveB")	// Creates unsigned byte waves.

    // Redimension the data to get back to 16-bit signed data.
    Redimension/E=1/N=10/W/U binaryWaveB0, binaryWaveB1, binaryWaveB2

    // Test waves for equality.
    if (EqualWaves(binaryWaveA0,binaryWaveB0,3) == 0)
        Print "binaryWaveA0 != binaryWaveB0"
    endif

    if (EqualWaves(binaryWaveA1,binaryWaveB1,3) == 0)
        Print "binaryWaveA1 != binaryWaveB10"
    endif

    if (EqualWaves(binaryWaveA2,binaryWaveB2,3) == 0)
        Print "binaryWaveA2 != binaryWaveB2"
    endif

    // Kill all the waves.
    KillWaves binaryPackedTextWave
    KillWaves binaryWaveA0, binaryWaveA1, binaryWaveA2
    KillWaves binaryWaveB0, binaryWaveB1, binaryWaveB2
End

SQLTextWaveTo2DBinaryWave (textWave , outNameStr )

SQLTextWaveToBinaryWaves unpacks a single text wave into a single 2D numeric wave. This is used to unpack a column of binary data received from the database server through a call to SQLHighLevelOp. See Handling Binary Data With SQLHighLevelOp for an explanation of why and when this would be used.

The function result is always 0.

Parameters

textWave is the the text wave containing the packed binary data.

outNameStr is the name to use for the 2D output numeric wave which will be created in the current data folder.

Details

The input text wave will usually be previously created by SQLHighLevelOp when it executes a query that returns a column of binary data (e.g., BLOB data).

SQLTextWaveTo2DBinaryWave stores the raw binary data from each row of the input text wave in the corresponding column of a newly-created 2D output numeric wave of type unsigned byte.

The output numeric wave is created in the current data folder.

note

When the output numeric waves is created, any wave with the same name in the current data folder is overwritten.

If you use SQLTextWaveTo2DBinaryWave as originally conceived, the text wave will contain unsigned byte data stored in it by SQLHighLevelOp. SQLTextWaveTo2DBinaryWave will unpack the unsigned byte data into a 2D numeric unsigned byte wave.

If you previously created the text wave by calling SQL2DBinaryWaveToTextWave on your own data, you need to know that the data type, dimensionality and other properties of the original numeric waves are not stored by SQL2DBinaryWavesToTextWave and therefore are not restored by SQLTextWaveTo2DBinaryWave. Only the raw data is restored.

What you do with the unpacked binary data depends on its nature. For example, if the binary data represents a 5x3 16-bit signed integer matrix wave data, you can change it from 8-bit unsigned to 16-bit signed like this:

Redimension /E=1 /Y=(0x10) /N=(5,3) w

Example

Function Test2DTextToBinary()
    // Make a test 2D numeric wave.
    Make/N=(5,3)/W/U matA = p   // 16-bit signed data

    // Pack the numeric wave into a text wave.
    SQL2DBinaryWaveToTextWave(matA, "binaryPackedTextWave")

    // Now we unpack the text wave into binary waves.
    SQLTextWaveTo2DBinaryWave(binaryPackedTextWave, "matB")     // Creates 2D unsigned byte wave.

    // Redimension the data to get back to 16-bit signed data.
    Redimension/E=1/N=(5,3)/W/U matB

    // Test waves for equality.
    if (EqualWaves(matA,matB,3) == 0)
        Print "matA != matB"
    endif

    // Kill all the waves.
    KillWaves matA, matB, binaryPackedTextWave
End

SQLUpdateBoundValues (statementRefNum, whichWay, numRowsFetched)

This function transfers data from SQL XOP's internal buffers into the bound waves.

This function is generally not needed because it is called internally by SQL XOP.

statementRefNum is an integer reference number for a previously allocated statement handle.

whichWay is 0 to copy parameter data from waves bound by SLQBindParameter into the internal SQL XOP buffers or 1 to copy result data from the internal SQL XOP buffers into waves bound by SQLBindCol.

numRowsFetched is an output which is set to the number of rows that were fetched by the SQLFetch, SQLFetchScroll, SQLSetPos or SQLBulkOperations call preceding the SQLUpdateBoundValues call. In other words, this is the number of rows available to be transfered from SQL XOP's internal buffer into the bound waves. If the bound waves are big enough to receive the data, it is also the number of rows actually transfered into the bound waves.

The function returns 0.

Details

For technical reasons, SQL XOP actually binds columns to internal buffers rather than directly to waves. However, in almost all cases this is transparent to you and you can ignore the internal buffers. The SQLExecDirect and SQLExecute functions automatically transfer parameter data from your bound waves into the internal buffers. The SQLFetch and SQLFetchScroll functions automatically transfer result data from the internal buffers to your bound waves. Therefore you need to call this function only if explicitly directed to call it in the documentation for another function.

SQLXOPCheckState (state)

SQLXOPCheckState determines if SQL XOP performs a check to make sure that SQLGetDataNum is called only when fetching numeric data and SQLGetDataStr is called only when fetching string data.

If state is non-zero, a check is done each time SQLGetDataNum or SQLGetDataStr is called to make sure that the data being fetched is of the right type. If the data type is wrong, SQLGetDataNum or SQLGetDataStr returns an error.

If state is zero, no check is done. If the data type is wrong, a crash may occur.

By default, checking is turned on. You can turn checking off to increase the speed of SQLGetDataNum and SQLGetDataStr so long as you are careful to call the right function for a given data type.

The function returns the previous state.

SQLAllocHandle (handleType,inputHandleRefNum,outputHandleRefNum)

Parameters

handleType is one of SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT or SQL_HANDLE_DESC.

inputHandleRefNum is the reference number of the relevant input handle or zero if handleType is SQL_HANDLE_ENV.

outputHandleRefNum is passed by reference. On return it contains the reference number of the newly allocated handle.

Details

You must call SQLFreeHandle to release any handle returned by SQLAllocHandle.

Example

#include <SQLUtils>
Function FetchUsingSQLGetData()
    Variable environmentRefNum=0,connectionRefNum=0,statementRefNum=0
    Variable result
    try
        // Create an environment handle. This returns an environment refNum in environmentRefNum.
        result = SQLAllocHandle(SQL_HANDLE_ENV,0,environmentRefNum)
        if (result)
        Print "Unable to allocate ODBC environment handle."
        endif
        AbortOnValue result!=0, 1

        // Set ODBC version attribute.
        result = SQLSetEnvAttrNum(environmentRefNum, SQL_ATTR_ODBC_VERSION, 3)
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_ENV,environmentRefNum,1)
        endif
        AbortOnValue result!=0, 2

        // Get a connection refNum in connectionRefNum.
        result = SQLAllocHandle(SQL_HANDLE_DBC,environmentRefNum,connectionRefNum)
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_ENV,environmentRefNum,1)
        endif
        AbortOnValue result!=0, 3

        // Connect to the database.
        result=SQLConnect(connectionRefNum,"IgorDemo1","DemoUser","Demo")
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_DBC,connectionRefNum,1)
        endif
        AbortOnValue result!=0, 4

        // Create a statement refNum in statementRefNum.
        result=SQLAllocHandle(SQL_HANDLE_STMT,connectionRefNum,statementRefNum)
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_DBC,connectionRefNum,1)
        endif
        AbortOnValue result!=0, 5

        // Execute it and parse the results. This returns a statement refNum in statementRefNum.
        result=SQLExecDirect(statementRefNum, "Select * from sampleTable;")
        if (result)
        PrintSQLDiagnostics(SQL_HANDLE_STMT,statementRefNum,1)
        else
        ParseSQLResults(statementRefNum)        // This routine is provided by SQLUtils.ipf
        endif
        AbortOnValue result!=0, 6
    catch
        Print "Execution aborted with code ",V_AbortCode
    endtry

    if (statementRefNum != 0)
        SQLFreeHandle(SQL_HANDLE_STMT,statementRefNum)
    endif

    if (connectionRefNum != 0)
        SQLDisconnect(connectionRefNum)
        SQLFreeHandle(SQL_HANDLE_DBC,connectionRefNum)
    endif

    if (environmentRefNum != 0)
        SQLFreeHandle(SQL_HANDLE_ENV,environmentRefNum)
    endif

    return result
End

SQLAllocStmt ()

This function has been superceded by SQLAllocHandle.

SQLBinaryWavesToTextWave (baseName, numBinaryWaves, textWaveNameStr)

The function result is always 0.

Parameters

baseName is the base name for a series of input numeric waves in the current data folder.

numBinaryWaves is the number of input numeric waves in the series.

textWaveNameStr contains the name that SQLBinaryWavesToTextWave will use for the output text wave.

Details

The input waves are a series of numeric waves with names like binaryWave0, binaryWave1, . . . in the current data folder.

SQLBinaryWavesToTextWave does not care about the dimensionality or data type of the numeric input waves. It treats them merely as an array of bytes to be stored in rows of the output text wave.

The output text wave is created in the current data folder.

note

When the output text wave is created, any wave with the same name in the current data folder is overwritten.

SQLBinaryWavesToTextWave stores the raw binary data in the output text wave. Each row of the output text wave stores the data for one of the input binary waves.

The data type, dimensionality and other properties of the input numeric waves are not stored in the output text wave. Only the raw data is stored.

Example

Function TestTextToBinary()
    // Make some test numeric waves.
    Make/N=10/W/U binaryWaveA0 = p      // 16-bit signed data
    Make/N=10/W/U binaryWaveA1 = p + 1
    Make/N=10/W/U binaryWaveA2 = p + 2

    // Pack the numeric waves into a text wave.
    SQLBinaryWavesToTextWave("binaryWaveA", 3, "binaryPackedTextWave")

    // Unpack the text wave into binary waves.
    SQLTextWaveToBinaryWaves(binaryPackedTextWave, "binaryWaveB")   // Creates unsigned byte waves.

    // Redimension the data to get back to 16-bit signed data.
    Redimension/E=1/N=10/W/U binaryWaveB0, binaryWaveB1, binaryWaveB2

    // Test waves for equality.
    if (EqualWaves(binaryWaveA0,binaryWaveB0,3) == 0)
        Print "binaryWaveA0 != binaryWaveB0"
    endif

    if (EqualWaves(binaryWaveA1,binaryWaveB1,3) == 0)
        Print "binaryWaveA1 != binaryWaveB10"
    endif

    if (EqualWaves(binaryWaveA2,binaryWaveB2,3) == 0)
        Print "binaryWaveA2 != binaryWaveB2"
    endif

    // Kill all the waves.
    KillWaves binaryPackedTextWave
    KillWaves binaryWaveA0, binaryWaveA1, binaryWaveA2
    KillWaves binaryWaveB0, binaryWaveB1, binaryWaveB2
End

SQL2DBinaryWaveToTextWave (input, textWaveNameStr)

The function result is always 0.

Parameters

input is the 2D input numeric wave.

textWaveNameStr contains the name that SQL2DBinaryWaveToTextWave will use for the output text wave.

Details

The output text wave is created in the current data folder.

note

When the output text wave is created, any wave with the same name in the current data folder is overwritten.

SQL2DBinaryWaveToTextWave stores the raw binary data in the output text wave. All rows of each column of the input numeric wave are stored in the corresponding row of the output text wave.

The data type, dimensionality and other properties of the input numeric waves are not stored in the output text wave. Only the raw data is stored.

Example

Function Test2DTextToBinary()
    // Make a test 2D numeric wave.
    Make/N=(5,3)/W/U matA = p           // 16-bit signed data

    // Pack the numeric wave into a text wave.
    SQL2DBinaryWaveToTextWave(matA, "binaryPackedTextWave")
    
    // Now we unpack the text wave into binary waves.
    SQLTextWaveTo2DBinaryWave(binaryPackedTextWave, "matB") // Creates 2D unsigned byte wave.
    
    // Redimension the data to get back to 16-bit signed data.
    Redimension/E=1/N=(5,3)/W/U matB
    
    // Test waves for equality.
    if (EqualWaves(matA,matB,3) == 0)
        Print "matA != matB"
    endif
    
    // Kill all the waves.
    KillWaves matA, matB, binaryPackedTextWave
End

SQLBindCol (statementRefNum, colNum, targetDataType, dataWave, targetDataSize, indicatorWave)

Bind a column in the result set to an IGOR wave.

SQLBindCol can also unbind a column (if dataWave is NULL). It can also bind an already-bound column to a different wave.

The function returns zero if successful or an SQL result code.

Parameters

statementRefNum, a reference number to a valid statement handle previously allocated with SQLAllocHandle.

colNum is the column to which the parameters are bound. SQL data columns start at 1.

targetDataType is the SQL type code for for a C data type, as shown in the table below.

dataWave is the wave that ultimately receives the output data. If dataWave is NULL ($""), SQLBindCol unbinds any waves previously bound to the specified column.

targetDataSize is the number of bytes for one element in the wave as shown in the table below.

Details

The targetDataType and targetDataSize Parameters

The targetDataType and targetDataSize parameters depend on the data wave as follows:

Data Wave Type	Target Data Type	Target Data Size
Double float	SQL_C_DOUBLE	8
Single float	SQL_C_FLOAT	4
32-bit integer, signed	SQL_C_SLONG	4
32-bit integer, unsigned	SQL_C_ULONG	4
16-bit integer, signed	SQL_C_SSHORT	2
16-bit integer, unsigned	SQL_C_USHORT	2
8-bit integer, signed	SQL_C_STINYINT	1
8-bit integer, unsigned	SQL_C_UTINYINT	1
Text	SQL_C_CHAR	Variable

SQL C data types not listed above are not currently supported by SQLBindCol.

SQLBindCol ignores the targetDataSize parameter for fixed length data types. It is OK for you to pass 0 as the targetDataSize parameter for fixed length data types.

When you specify any type other than SQL_C_CHAR, dataWave must be a numeric wave.

Transfering Data from the SQL XOP Buffer into the Wave

Examples

// See the SQL Low-Level Demo experiment for a full-worked-out example.
// Here is an abbreviated example. Error testing omitted for brevity.
SQLBindCol(statementRefNum,1,SQL_C_SLONG,scorewave,8,indicatorwave)
SQLExecute(statementRefNum)
SQLFetch(statementRefNum)
Print scoreWave[0],indicatorwave[0]

SQLBindParameter (statementRefNum, parameterNumber, ioType, valueType, paramType, columnSize, decimalDigits, valueWave, bufSizeInBytes, indicatorWave)

Binds a wave to a parameter marker in an SQL statement. The parameter will usually be an input parameter in an SQL statement such as:

INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?)

In advanced applications, the parameter can also be an input/output or output parameter if the SQL statement is a procedure call.

SQLBindParameter can also rebind a parameter to a different wave.

valueWave is the wave bound to the parameter. valueWave may not be NULL.

SQL XOP marks both waves as in-use so that you can't kill them while they are bound. The waves are unmarked when you call SQLFreeHandle on the associated statement or call SQLReinitialize.

Parameters

statementRefNum is an integer reference number previously allocated by a call to SQLAllocHandle.

parameterNumber is the parameter marker number starting from 1.

ioType is a constant that defines the input/output type. It is one of: SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, SQL_PARAM_INPUT_OUTPUT.

valueType is a C-data type of the parameter. This must match the wave type for valueWave. See the table below.

paramType is an SQL type of the parameter. The supported types are:


SQL_CHAR	Text
SQL_SMALLINT	Signed or unsigned 16-bit integer
SQL_INTEGER	Signed or unsigned 32-bit integer
SQL_REAL	32-bit floating point
SQL_DOUBLE	64-bit floating point
SQL_BIT	Single bit binary.
SQL_TINYINT	Signed or unsigned 8-bit integer

The following are not yet supported:


SQL_DECIMAL	Fixed-point number.
SQL_NUMERIC	Fixed-point number.
SQL_BINARY	Fixed-length binary data.
SQL_VARBINARY	Variable-length binary data.
SQL_TYPE_DATE
SQL_TYPE_TIME
SQL_TYPE_TIMESTAMP

Also not supported are all interval types and any other type not listed as supported.

See the Microsoft ODBC documentation for details on SQL data types.

valueWave is a wave that is to receive the bound parameter data.

bufSizeInBytes is the number of bytes for one element in the value wave as shown in the table below.

The valueType and bufSizeInBytes Parameters

The valueType and bufSizeInBytes parameters depend on the data wave as follows:

Data Wave Type	Value Type	Buf Size In Bytes
Double float	SQL_C_DOUBLE	8
Single float	SQL_C_FLOAT	4
32-bit integer, signed	SQL_C_SLONG	4
32-bit integer, unsigned	SQL_C_ULONG	4
16-bit integer, signed	SQL_C_SSHORT	2
16-bit integer, unsigned	SQL_C_USHORT	2
8-bit integer, signed	SQL_C_STINYINT	1
8-bit integer, unsigned	SQL_C_UTINYINT	1
Text	SQL_C_CHAR	Variable

SQL C data types not listed above are not currently supported by SQLBindParameter.

When you specify any type other than SQL_C_CHAR, valueWave must be a numeric wave.

Transfering Data from Waves into the SQL XOP Buffer

SQLBrowseConnect (connectionRefNum, connectionTextIn, connectionTextOut, connectionBufLen)

This function retrieves connection information. It can be used iteratively to enumerate connection attributes. The function itself returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is an integer reference number for a previously allocated connection handle.

connectionTextOut is a pass-by-reference string which upon return contains the next batch of data used for the connection.

connectionBufLen is the maximum number of characters that you allow the driver to write into connectionTextOut.

SQLBulkOperations (statementRefNum, operation)

Update, delete or fetch a set of rows identified by a bookmark. The function itself returns 0 if successful or an SQL error code.

Parameters

statementRefNum is an integer reference to a previously allocated statement handle.

operation is a variable set to one of: SQL_ADD, SQL_UPDATE_BY_BOOKMARK, SQL_DELETE_BY_BOOKMARK, SQL_FETCH_BY_BOOKMARK.

SQLCancel (statementRefNum)

This function terminates the data-at-execution sequence corresponding to the specified statement. The function returns 0 if successful or an SQL result code.

Parameters

statementRefNum is a valid reference number of an open statement.

SQLCloseCursor (statementRefNum)

Closes the cursor and discards pending results. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a valid reference number of an open statement handle.

SQLColAttributeNum (statementRefNum, colNumber, fieldID, numAttr)

Parameters

statementRefNum is a reference number to a previously allocated statement handle.

colNumber is the number of the column whose descriptor information is required. Column numbers start at 1.

numAttr is a pass-by-reference variable which upon successful return contains the numeric value of the attribute.

SQLColAttributeStr (statementRefNum, colNumber, fieldID, attributeStr, bufSizeInBytes )

This function gets descriptor information for a column in an SQL result set. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number to a previously allocated statement handle.

colNumber is the number of the column whose descriptor information is required. Column numbers start at 1.

bufSizeInBytes is the maximum number of characters that you allow the driver to write into the attributeStr string.

SQLColumnPrivileges (statementRefNum, catalogName, schemaName, tableName, columnName)

This function creates an SQL result set containing a list of columns and their associated privileges. The function itself returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number to a previously allocated statement handle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database.

schemaName is the second part of the table name. "" should be accepted if it does not apply.

tableName is the string containing the name of the table.

columnName this could be a string containing a pattern to be matched.

Note: for each one of the strings that you pass to this function SQL XOP passes a null terminated string to the ODBC driver together with the corresponding length of the string.

SQLColumns (statementRefNum, catalogName, schemaName, tableName, columnName)

This function creates an SQL result set containing a list of columns in the specified tables. The function returns 0 if successful or an SQL error code.

Parameters

statementRefNum is a reference number to a previously allocated statement handle.

catalogName is the catalog qualifier of a 3 part table name. Use an empty string ("") if this is not supported by your database. This parameter should not contain a string matching pattern.

schemaName is the second part of the table name. "" should be accepted if it does not apply. This parameter may contains a string search pattern for schema names.

tableName is the string containing the name of the table.

columnName could be a literal column name or string containing a pattern to be matched. "%" matches all columns.

If you pass "<NULL>" for any of the parameters, SQL XOP passes NULL for the corresponding parameter to ODBC.

This command seems to work reasonably well for most drivers:

SQLColumns(statementRefNum, "<NULL>", "<NULL>", <tableName>, "%")

where <tableName> is the name of a specific table.

SQLConnect (connectionRefNum, dsnName, userName, userPW)

Establishes a connection with a database. Returns 0 if successful or an SQL error code.

Parameters

connectionRefNum is the reference number of a previously allocated connection handle (see SQLAllocHandle).

dsnName is the DSN (data source name).

userName and userPW are the name and password respectively of a user privileged to connect to the the database.

Example

// example without error checking:
Variable environmentRefNum,connectionRefNum
SQLAllocHandle(SQL_HANDLE_ENV,0,environmentRefNum)
SQLSetEnvAttrNum (environmentRefNum,SQL_ATTR_ODBC_VERSION,3)
SQLAllocHandle(SQL_HANDLE_DBC,environmentRefNum,connectionRefNum)
SQLConnect(connectionRefNum,txt,"myUserName","myPassword")

SQLDataSources (environmentRefNum, direction, dataSourceName, nameLen, description, descLen)

Returns in dataSourceName the name of one data source as entered into Data Sources on Windows. Returns in description descriptive information about the data source.

This is typically used to present to a user a list of data sources that he has created.

Parameters

environmentRefNum is the reference number of a previously allocated (see SQLAllocHandle) environment handle.

direction is one of the following on the first call to SQLDataSources: SQL_FETCH_FIRST, SQL_FETCH_FIRST_USER, SQL_FETCH_FIRST_SYSTEM. On subsequent calls, pass SQL_FETCH_NEXT.

dataSourceName is a local string variable passed by reference to this function. Upon successful return it contains the name of a data source. This string does not have to be initialized.

nameLen is the maximum number of characters that you allow the ODBC driver to return in the dataSourceName string. Any large value such as 100 will do.

description is a local string variable passed by reference to this function. Upon successful return it contains a description of the data source. This string does not have to be initialized.

descLen is the maximum number of characters that you allow the ODBC driver to write into the description string. Any large value such as 100 will do.

Details

When there are no more data sources to be returned, SQLDataSources returns SQL_NO_DATA as the function result.

Example

Function PrintDataSources()
    Variable environmentRefNum = -1
    Variable rc

    Variable result = 0
    try
        rc = SQLAllocHandle(SQL_HANDLE_ENV, 0, environmentRefNum)
        if (rc != SQL_SUCCESS)
        Print "Error while creating environment refnum."
        AbortOnValue 1, 1
        endif

        SQLSetEnvAttrNum(environmentRefNum, SQL_ATTR_ODBC_VERSION, 3)   // Specifies ODBC version 3.

        Variable index = 0

        do
            Variable direction
            String dataSourceName, description
            direction = index == 0 ? SQL_FETCH_FIRST : SQL_FETCH_NEXT
            rc = SQLDataSources(environmentRefNum,direction,dataSourceName,100,description,100)
            if (rc == SQL_SUCCESS)
            Print dataSourceName, description
            else
            if (rc == SQL_NO_DATA)
            break
            else
            Printf "Unexpected result from SQLDataSources: %d\r", rc
            AbortOnValue 1, 2
            endif
            endif
            index += 1
        while(1)
    catch
    result = V_abortCode
    Printf "Error %d\r", result
    endtry

    if (environmentRefNum != -1)
    SQLFreeHandle(SQL_HANDLE_ENV, environmentRefNum)
    endif

    return result
End