SQLHighLevelOp
SQLHighLevelOp [ flags ] statement
SQLHighLevelOp is a high-level, one-stop operation for executing a statement on a database and storing any results in Igor waves or variables. It is intended as an easy-to-use alternative to learning the details of ODBC and should be sufficient for most applications.
SQLHighLevelOp can connect to a database, execute the statement, fetch the results, if any, and disconnect from the database, all in one call. Alternately, it can use an existing connection, in which case it just executes the statement and fetches the results, if any.
If /CONN is present, SQLHighLevelOp uses an existing connection reference number that you created using SQLConnect or SQLDriverConnect. In this case, SQLHighLevelOp uses the existing connection and does not create or close a connection.
If /CONN is not present or if connectionRefNum is -1, SQLHighLevelOp opens a connection by passing the connection string specified by /CSTR={connectionStr, driverCompletion} to SQLDriverConnect and closes the connection after the statement is complete.
In either case, SQLHighLevelOp fetches results if any are generated by the statement. It creates waves to hold the results and stores the result data in the waves unless you use the /VARS flag in which case it stores result data in variables. If the /E flag is used, it displays the waves in a table.
Either /CONN or /CSTR is required. If both are omitted, SQLHighLevelOp returns an error.
In addition to this documentation, see also SQL XOP High-Level Programming.
Parameters
statement is the SQL statement sent by SQLHighLevelOp to the database via the SQLExecDirect ODBC function. This may be any valid SQL statement, such as a SELECT, UPDATE, INSERT or DELETE statement.
If statement uses parameters, such as in an UPDATE or INSERT command containing parameter markers ("?"), you must supply either parameter waves or parameter variables.
By default, if you supply parameter waves using the /PWAV flag or the /PLST flag, SQLHighLevelOp executes the statement once for each row in the parameter waves. See High-Level Updating Data From Waves Example for an example.
If you supply parameter variables using the /PVAR flag, SQLHighLevelOp uses parameter variables instead of parameter waves and executes the statement one time. The /PVAR flag requires Igor Pro 6.10 or later.
Flags
| /CONN = connectionRefNum | |||||||||
| connectionRefNum is a connection reference number that you obtained by calling SQLAllocHandle and which you have set up for use by calling SQLConnect or SQLDriverConnect. It identifies the database to be used for the statement. | |||||||||
| If /CONN is specified and connectionRefNum is non-negative, SQLHighLevelOp uses the specified connection and does not create or close a connection. | |||||||||
| If connectionRefNum is -1, it acts as if the /CONN flag were omitted. | |||||||||
| /CSTR = {connectionStr, driverCompletion} | |||||||||
| connectionStr is a connection string as you would specify when calling SQLDriverConnect. | |||||||||
| driverCompletion is an SQLDriverConnect mode, namely, 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 in doubt, use SQL_DRIVER_PROMPT. | |||||||||
| If /CSTR is present and /CONN is not present or connectionRefNum as specified with /CONN is -1, SQLHighLevelOp passes connectionStr and driverCompletion, to SQLDriverConnect to open a connection. | |||||||||
| /E = editCmd | editCmd = 0: As if /E had not been specified (the created waves, if any, aren't put in any table). | ||||||||
| |||||||||
| The test for "if the waves did not exist before SQLHighLevelOp was executed" is really based solely on the first wave loaded. If it did exist, it is assumed that the remaining waves also did exist. If it did not exist, it is assumed that the remaining waves also did not exist. | |||||||||
| /IWAV = {wave, wave, . . .} | |||||||||
| If the statement uses parameter markers, the waves listed using the /IWAV flag hold the length/indicator values for the corresponding parameter markers. Length/indicator values are used to specify the length of character or binary data or to specify null or default as a parameter value. SQLHighLevelOp calls the ODBC SQLBindParameter function to bind the indicator waves during the execution of the statement. | |||||||||
You need to use /IWAV only if you want to specify that null (SQL_NULL_DATA) or default (SQL_DEFAULT_PARAM) is to be used as the parameter for a particular row instead of the value in the corresponding row of the parameter wave. If /IWAV is omitted, SQLHighLevelOp automatically passes the correct length values. | |||||||||
If /IWAV is used, the indicator waves must be 32-bit signed integer (Make/I) and each point of each wave must contain either a non-negative length, SQL_NULL_DATA or SQL_DEFAULT_PARAM. The non-negative length is used only for character and binary data. It is ignored for numeric data and you can specify 0. | |||||||||
| You can pass $"" for any of the waves in the list to indicate that there is no indicator wave for the corresponding parameter. In this case, SQLHighLevelOp will act as if /IWAV were omitted for that parameter. | |||||||||
| Note that this flag has nothing to do with the /LIND flags. /LIND controls whether indicator waves are created when fetching results whereas /IWAV and /ILST specify whether you are providing indicator waves when executing a statement that contains parameter markers. | |||||||||
| /ILST = indicatorWaveList | |||||||||
| /ILST serves the same function as /IWAV except that the waves are specified in a string containing a semicolon-separated list of wave names with a semicolon after each name. | |||||||||
| When there are a large number of indicator waves, /ILST will be more convenient than /IWAV. Each item in the list is the simple name of a wave in the current data folder, not a path to a wave. Liberal names must not be quoted. | |||||||||
| If there is no indicator wave for a particular parameter, pass zero characters for the corresponding list item. For example, this specifies an indicator wave for parameters 1 and 3 but not for parameter 2: "ind1;;ind3;". | |||||||||
| Note that this flag has nothing to do with the /LIND flags. /LIND controls whether indicator waves are created when fetching results whereas /IWAV and /ILST specify whether you are providing indicator waves when executing a statement that contains parameter markers. | |||||||||
| /LIND = loadIndicators | |||||||||
| |||||||||
You would use /LIND=1 if you want to check for NULL values in one or more database columns. | |||||||||
When a result set is generated after a SELECT or other statement that produces results, ODBC produces a result array and a length/indicator array for each column in the result set. The length/indicator array contains the length of each element in the result array or a special negative value such as SQL_NULL_DATA (-1) indicating that the corresponding result value is NULL (missing). | |||||||||
| When /LIND=1 is specified, in addition to creating a result wave for each column in the result set, SQLHighLevelOp produces a corresponding indicator wave for each column. The name of the indicator wave will be the same as the name of the result wave with the suffix "Ind" except that this rule will be violated if the resulting name would be too long or would conflict with the name of another wave. | |||||||||
| Note that this flag has nothing to do with the /IWAV and /ILST flags. /LIND controls whether indicator waves are created when fetching results whereas /IWAV and /ILST specify whether you are providing indicator waves when executing a statement that contains parameter markers. | |||||||||
| /MFL = maxFieldLength | |||||||||
maxFieldLength tells SQLHighLevelOp the maximum size in bytes of any field fetched by a SELECT statement. It is used to improve memory allocation in certain situations when loading binary data. | |||||||||
| See Improving Memory Management With SQLHighLevelOp for details. | |||||||||
| /NAME = nameList | |||||||||
| If /NAME is omitted, SQLHighLevelOp automatically generates output wave names based on the database result set columns. If /NAME is present, it specifies the names that SQLHighLevelOp will use when creating output waves. nameList is a semicolon-separated list of wave names with a semicolon after each name. | |||||||||
| If nameList contains empty names, the corresponding output wave's name is determined by the corresponding column in the result set. For example /NAME="wave0;;wave2;" will create output waves named wave0 and wave2 but the name of the middle output wave will be determined by the corresponding column in the result set. | |||||||||
| See Fetching Data Into Waves With SQLHighLevelOp for details. | |||||||||
| /O = [overwrite] | If /O or /O=1 is used, SQLHighLevelOp overwrites existing waves when creating waves to contain output from the statement. | ||||||||
| If /O is omitted or /O=0 is used, SQLHighLevelOp generates unique wave names when creating waves to contain output from the statement. | |||||||||
| /OPTS = options | options is a bitwise parameter, defined as follows: | ||||||||
| |||||||||
| All other bits are reserved and must be set to zero. | |||||||||
| See Setting Bit Parameters for details about bit settings. | |||||||||
| /PVAR = {var, var, . . .} | |||||||||
| The /PVAR flag requires Igor Pro 6.10 or later. With earlier versions of Igor Pro, SQLHighLevelOp with /PVAR will return an error. | |||||||||
| If the statement uses parameter markers, the variables listed using the /PVAR flag hold the parameter values for the corresponding parameter markers. SQLHighLevelOp calls the ODBC SQLBindParameter function to bind the parameter variables during the execution of the statement. | |||||||||
| A parameter variable may be a local variable or global variable referenced by an NVAR or SVAR reference. You can also use function parameters, which behave like local variables whose values are set by the calling function. | |||||||||
| It is an error to pass a nonexistent variable (e.g., $"" or an uninitialized NVAR or SVAR reference). | |||||||||
| You can pass either a numeric or string variable for any given parameter so long as ODBC is capable of doing the conversion to the parameter's type. ODBC can do the conversions that make sense, such as converting from a double (an Igor numeric variable) to an SQL integer. If you pass a numeric variable for a parameter of type binary, on the other hand, ODBC may either do nothing, return an error or misbehave completely, depending on the ODBC implementation. To be safe, use a numeric variable when dealing with a numeric parameter and a string variable for other types such as char, date/time and binary. | |||||||||
| There is one exception to the preceding caveat. If you pass a numeric variable when the corresponding SQL parameter is of type date, time or date/time, SQLHighLevelOp assumes that the numeric variable contains a value in Igor date/time format (seconds since January 1, 1904). It transparently converts the Igor date/time value to a text string containing a representation of the date/time which it passes to ODBC. However, this feature does not work with MySQL as of MyODBC version 3.51 because the MyODBC driver does not properly identify the type of the parameter so SQLHighLevelOp has no way to know that it is dealing with a date/time value. Consequently, if you want your code to work with MySQL, you must do the date/time to text conversion yourself and use a string variable instead of a numeric variable. | |||||||||
| See High-Level Inserting Data From Variables Example for an example. | |||||||||
| /PWAV = {wave, wave, . . .} | |||||||||
| For each row in the parameter waves, each parameter marker in the statement is bound to the corresponding parameter wave. Thus each row of the parameter waves supplies a set of parameter values each time the statement is executed. | |||||||||
| It is an error to pass a nonexistent wave (e.g., $"" or an uninitialized WAVE reference). | |||||||||
By default, the number of rows in the first parameter wave determines the number of times the INSERT statement is executed. You can override this using the /ROWS flag. | |||||||||
| By default, SQLHighLevelOp executes the statement once for each row in the first parameter wave. You can request that it process multiple rows at a time using the rowsPerBatch parameter of the /ROWS flag. See /ROWS for details. | |||||||||
Normally all parameter waves should have the same number of rows. In the event that some parameter waves have fewer rows than the first parameter wave, SQLHighLevelOp sets the corresponding values in the database to NULL. | |||||||||
| /PWAV is mainly of use when inserting or updating multiple rows of an SQL table. It is not much use when fetching data because the result set is overwritten each time the statement is executed so you receive only the result set from the last iteration. | |||||||||
| See High-Level Updating Data From Waves Example for an example. | |||||||||
| /PLST = parameterWaveList | |||||||||
| /PLST serves the same function as /PWAV except that the waves are specified in a string containing a semicolon-separated list of wave names with a semicolon after each name. | |||||||||
| When there are a large number of parameter waves, /PLST will be more convenient than /PWAV. Each item in the list is the simple name of a wave in the current data folder, not a path to a wave. Liberal names must not be quoted. | |||||||||
| /Q = [quiet] | If /Q is omitted or /Q=0 is used, SQLHighLevelOp prints diagnostic information in Igor's history area in the event of an error. | ||||||||
| If /Q is present or /Q=1 is used, SQLHighLevelOp does not print diagnostic information. | |||||||||
| A general diagnostic is returned via S_Diagnostics regardless of /Q. | |||||||||
| For most purposes, you should omit /Q so that you will get full diagnostics if an error occurs. | |||||||||
| /ROWS = {startRow, endRow } | |||||||||
| Determines which rows of the parameter waves specified by /PWAV or /PLST are used. | |||||||||
| If you omit /ROWS, statement is executed n times for rows 0 to n-1 where n is the number of rows in the parameter waves. | |||||||||
| Using /ROWS, you can control which rows are used. For example, if you specify /ROWS={1,2} then, by default, statement will be executed two times, once using the values from row 1 of the parameter waves and once using the values from row 2 of the parameter waves. | |||||||||
| Passing -1 for startRow causes the startRow to be the first row of the parameter waves (row 0). Passing -1 for endRow causes the endRow to be the last row of the parameter waves (row n-1). | |||||||||
| Other than using -1 as described, it is an error to specify rows that do not exist in the parameter waves. | |||||||||
| rowsPerBatch is optional and requires SQL XOP 1.60 (first shipped with Igor Pro 9.00) or later. It controls the number of rows processed at at time. The default for rowsPerBatch is 1 and is clipped to the range 1 to n-1 where n is the number of points in the parameter waves. See Batch Row Processing Using Parameter Waves for details. | |||||||||
| /STMT = statementRefNum | |||||||||
| statementRefNum is a statement reference number that you obtained by calling SQLAllocHandle. This is intended for use with the /VARS flag to fetch multiple rows of a result set into variables as illustrated under High-Level Fetching Multiple Rows Into Variables Example. | |||||||||
| If /STMT is specified and statementRefNum is non-negative, SQLHighLevelOp uses the specified statment and does not create or free a statement. | |||||||||
| If statementRefNum is -1, it acts as if the /STMT flag were omitted. | |||||||||
| /VARS = {var, var, . . .} | |||||||||
| SQLHighLevelOp normally fetches data into waves. This is convenient when you are fetching multi-row data. For fetching a single row of data, you may prefer to use local or global variables. To do this you must use the /VARS flag. | |||||||||
| See Fetching Data Into Variables With SQLHighLevelOp for further discussion. | |||||||||
| /Z = mode | mode specifies how to handle fatal errors. | ||||||||
| A fatal error is a programming error such as passing a parameter that is out-of-bounds or passing a null (uninitialized) string or a runtime error such as out-of-memory. This is in contrast to an SQL error, which is not fatal, such as trying to access a table for which you do not have sufficient privileges or trying to access a field that does not exist. | |||||||||
| mode can take the following values: | |||||||||
| |||||||||
| For a discussion of error handling, see SQLHighLevelOp Error Handling. | |||||||||
Details
SQLHighLevelOp sets the following variables:
| V_flag | Set to 0 if no fatal error occurred or to a non-zero value if a fatal error occurred. If V_flag is non-zero then a fatal error that would normally stop procedure execution occurred. However, procedure execution is not stopped if you use /Z=1 in which case your code can perform error checking and handling. See SQLHighLevelOp Error Handling for details. | |
| V_numWaves | Set to the number of output waves created. This includes result waves and also indicator waves if /LIND=1 is specified. | |
| S_waveNames | Semicolon-separated list of the names of waves created. This includes result waves and also indicator waves if /LIND=1 is specified in which case the name of the indicator wave follows the name of the corresponding result wave in the list. | |
| V_numColumns | The number of columns of data in the result set or 0 if no result set was generated by the statement. | |
| V_numVars | The number of output variables set when using /VARS. Zero if not using /VARS. | |
| V_SQLRowCount | ||
| Contains the number of database rows affected by the statement if it was an INSERT, UPDATE or DELETE statement. This value is not valid for any other type of statement. Note that a database row is considered affected by an UPDATE statement only if the the UDPATE statement causes a value in the database row to change. | ||
| V_SQLResult | Contains the last return code returned by ODBC to SQL XOP before SQLHighLevelOp returned to Igor. If V_SQLResult is SQL_SUCCESS then the operation succeeded fully. Otherwise some warning (SQL_SUCCESS_WITH_INFO) or error (e.g., SQL_ERROR) was returned by ODBC. If V_flag is zero then this is a non-fatal error. If V_flag is non-zero then this is a fatal error that would normally stop procedure execution but did not because you used the /Z=1 flag. | |
| S_Diagnostics | If an error occurred, this contains a clue as to what went wrong, if applicable. If no error occurred or no diagnostics are available, it is set to "". S_Diagnostics contains just a general diagnostic from the SQL XOP, not the more details diagnostics from the ODBC driver printed in the history if /Q is omitted. | |
Depending on the parameters you use with it, the /CSTR flag may cause ODBC to display a dialog asking the user to provide information needed to complete the connection. It is possible for the user to cancel out of that dialog. You can detect this by testing V_flag. If it is 10039 then the user cancelled out of a connection dialog. However this may not work with some drivers that do not follow the rules. 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 MyODBC 3.51.19 driver returns SQL_ERROR, not SQL_NO_DATA in this event and this prevents SQLHighLevelOp from detecting that the user cancelled from the dialog.
Examples
SQL XOP High-Level Programming
High-Level Fetching Data Into Waves Example
High-Level Fetching One Row Into Variables Example
High-Level Fetching Multiple Rows Into Variables Example
High-Level Inserting Data From Waves Example
High-Level Inserting Data From Variables Example
High-Level Inserting Data From Variables Example
High-Level Multiple-Commands-Per-Connection Method Example
High-Level Updating Data From Waves Example