SqlGetOff%

Checks for the existence of Transact-SQL statements in the command buffer.

Syntax

SqlGetOff% ( sqlconn%, offtype%, startfrom% )

where

sqlconn%

Is a SQL Server connection. The value of sqlconn% is returned by SqlOpen%.

offtype%

Is the type of offset you want to find. The types are off_select, off_from, off_order, off_compute, off_table, off_procedure, off_statement, off_param, and off_exec.

For details, see DB-Library for Visual Basic Options.

startfrom%

Is the point in the buffer from which to start looking. The command buffer begins at 0.

Returns

The character offset into the command buffer for a specified offset. If the offset is not found, -1 is returned.

Remarks

If the SQLOFFSET option has been set (see DB-Library for Visual Basic Options.), SqlGetOff% can check for the location of certain Transact-SQL statements in the command buffer.

Examples

In example A, assume that the program doesn't know the contents of the command buffer but needs to know where the SQL keyword SELECT appears:

A.

       Dim SelectOffset (9) As Integer
       Dim LastOffset As Integer
       Dim i AS Integer 
       'Set the offset option.
       SqlSetOption% (Sqlconn%, SQLOFFSET, "select")

       'Execute the option on the server.
       SqlExec%(Sqlconn%)

       'Read the returned results.
       Do Until SqlResults%(Sqlconn%) = NOMORERESULTS

          'Assume the command buffer contains the following SELECTs:
          Result% = SqlCmd%(Sqlconn%, "SELECT x = 100 SELECT y = 5")

    'Send the statement to SQL Server and start execution.
          Result% = SqlExec%(Sqlconn%)

          'Process the statement results.
          Result% = SqlResults%(Sqlconn%)

          'Get all the offsets to the SELECT keyword.
          LastOffset% = 0
          i% = 0
          Do Until LastOffset% = -1
             LastOffset% = SqlGetOff(Sqlconn%, OFF_SELECT, LastOffset%)
             SelectOffset% (i) = LastOffset%  1
             i% = i%  1
          Loop
       Loop

In example B, the function SqlGetOff% does not recognize SELECT statements in a subquery. So, if the command buffer contains the following program fragment, the second SELECT statement goes unrecognized:

B.    

       select pub_name
       from publishers
       where pub_id not in
       (select pub_id
       from titles
       where type = "business")

See Also

SqlCmd%, SqlGetChar$, SqlSetOpt%, SqlStrCpy%, SqlStrLen%; Text and image Functions