The builtin-in functions in the SQL package perform connection and transaction operations, cursor allocation and management, and SQL statement operations with generic SQL databases.

The major functionalities offered by the SQL Package are the following:

  • Alloc() and AllocFromConnect() to allocate an SqlConnection, Connect() to connect one, and Disconnect() to terminate one.
  • StartTransaction() and EndTransaction() to designate transactions.
  • Declare() to declare an SqlCursor, Open() to open it, Prepare() to prepare it after opening, Bind() to bind values to it, Fetch() to retrieve data from it, Cols() to return the data, and Close() to close it.
  • ColNames() to return the column names referenced by a cursor and RowCount() to return the number of rows affected by an SQL statement in a cursor.
  • Exec() executes an SQL statement, or ExecN() to execute a parameterized statement.
  • IsSelect() to determine if an SQL statement is a select statement, and NCols() to determine the number of rows it will return.
  • Now() to return the current time and date as defined by the database server.
    •

    Since much of the functionality of the SQL package is interdependent, a single code sample showing correct usage and sequence of fundamental package calls is provided below. In the example below, and arbitrary error handling routine from the DBWizAPI OSpace is being used. Error handling will, of course, vary depending on implementation and usage.

    Example:

    // Retrieve values from DTree using a "fetching" cursor, and insert values into DTree
// using a second "inserting" cursor.

Integer		status
List		colData

string		ok = undefined
SqlCursor	fetchCursor = undefined
SqlCursor	insertCursor = undefined
Boolean		done = FALSE
SqlConnection	sqlCnct = SQL.AllocFromConnect( $Connect )


// Start a transaction

status = SQL.StartTransaction( sqlCnct )
		
//Declare the fetching cursor

fetchCursor = SQL.Declare(\
		sqlCnct,\
		"Select OwnerID,DataID,DComment from " +\
			"DTree " +\
			"where " +\
			"( DataType = 0 ) and ( SubType in (130,134) )" )

ok = $DBWizAPI.ConnectPkg.Check( fetchCursor, "Error Declaring FetchCursor] )

if IsDefined( ok )
	fetchCursor = undefined
end
	
if IsUndefined( ok )
	
	// Open the fetching cursor
	
	status = SQL.Open( fetchCursor )
	
	ok = $DBWizAPI.ConnectPkg.Check( status, "Error Opening Fetch Cursor" )
end
	
if IsUndefined( ok )
	
	// Prepare the fetching cursor
	
	status = SQL.Prepare( fetchCursor )
	
	ok = $DBWizAPI.ConnectPkg.Check( status, "Error Preparing Fetch Cursor" )
end
	
if IsUndefined( ok )
	
	// Execute the fetching cursor
	
	status = SQL.Execute( fetchCursor )
	
	ok = $DBWizAPI.ConnectPkg.Check( status, "Error Executing Fetch Cursor" )
end
	
if IsUndefined( ok )

	// Declare the insert cursor (note the value to be bound at a future point)

	insertCursor = SQL.Declare( sqlCnct, "Insert into DiscussionText values ( :A1, :A2, :A3 )" )
	
	ok = $DBWizAPI.ConnectPkg.Check( insertCursor, "Error Declaring Insert Cursor" )
	
	if IsDefined( ok )
		insertCursor = undefined
	end
end
	
if IsUndefined( ok )
	
	// Open the insert cursor
	
	status = SQL.Open( insertCursor )
	
	ok = $DBWizAPI.ConnectPkg.Check( status, "Error Opening Insert Cursor" )
end
	
if IsUndefined( ok )
	
	// Prepare the insert cursor
	
	status = SQL.Prepare( insertCursor )
	
	ok = $DBWizAPI.ConnectPkg.Check( status, "Error Preparing Insert Cursor" )
end
	
while ok && !done

	// Fetch results using the fetch cursor

	status = SQL.Fetch( fetchCursor )
			
	if IsError( status )
		done = TRUE
	else

		// Retrieve column data from the fetching cursor

		colData = SQL.Cols( fetchCursor )
		
		if IsDefined( coldata[ 3 ] )

			// Bind the values retrieved by the fetching cursor into the
			// inserting cursor

			status = SQL.Bind( insertCursor, colData )
			
			ok = $DBWizAPI.ConnectPkg.Check( status, "Error Binding Values"] )

			if IsUndefined( ok )

				// Execute the inserting cursor (whose statement has been bound with values from the
				// fetching cursor

				status = SQL.Execute( insertCursor )
				ok = $DBWizAPI.ConnectPkg.Check( status, "Error Executing Insert Cursor" )
			end
		end
	end
end

// Close the cursors

if IsDefined( fetchCursor )
	SQL.Close( fetchCursor )
end
		
if IsDefined( insertCursor )
	SQL.Close( insertCursor )
end
	
// End the transaction

status = SQL.EndTransaction( sqlCnct, !IsDefined( ok ) )

    Class Attributes Index

    o AUTOCOMMIT
    A commit flag for ODBC.
    o CACHE
    Cache the cursor.
    o CACHEDYNAMIC
    Not used.
    o COMMIT
    Commit a transaction.
    o MULTIEXEC
    Not used.
    o NOCACHE
    Don't cache the cursor.
    o NOLOG
    Statement shouldn't be logged.
    o NOTRANSWARN
    Turn off warnings if statement is not in the context of a transaction.
    o ROLLBACK
    An error returned by SQL.Fetch() to indicate completion and/or passed to SQL.EndTransaction() to rollback a transaction.
    o SLOWMODE
    Not used.

    Class Methods Index

    o Alloc( Integer conType, CAPILOG logObj, CAPIERR errObj, Integer num )
    Allocates a connection object.
    o AllocFromConnect( CAPICONNECT connect )
    Allocates a connection object from a CAPIConnect
    o Bind( SqlCursor cursor, List values )
    Binds a list of values to an SqlCursor.
    o Close( SqlCursor cursor )
    Closes an SqlCursor.
    o ColNames( SqlCursor cursor )
    Returns the names of the columns referenced by a cursor.
    o Cols( SqlCursor cursor )
    Returns the data from the last SQL.Fetch() execution.
    o Connect( SqlConnection connect, String srvName, String dbName, String usrName, String pword, String applStr, String hostStr )
    Connects an allocated connection object to the database.
    o Declare( SqlConnection connect, String stmt, [Integer flags] )
    Declares an SqlCursor.
    o Disconnect( SqlConnection connect )
    Drops the database connection.
    o EndTransaction( SqlConnection connect, Dynamic value )
    Ends a database transaction, allowing commitment or rollback.
    o Exec( SqlConnection connect, String stmt, [Dynamic sub<n>], [Dynamic sub<n>] )
    Executes an SQL statement.
    o ExecN( SqlConnection connect, String stmt, RecArray params )
    Executes an SQL statement multipled times for a RecArray of parameterized values.
    o Execute( SqlCursor cursor )
    Executes an SqlCursor.
    o Fetch( SqlCursor cursor )
    Fetches a row of data specified by the cursor.
    o GetBlob( SqlConnection connect, String arg2, Integer arg3, Integer arg4 )
    Internal use only.
    o IsSelect( SqlCursor cursor )
    Determines whether the cursor contains a select statement.
    o NCols( SqlCursor cursor )
    Determines the number of columns the SQL statement will return.
    o Now( SqlConnection connect )
    Returns the current date and time as defined by the database.
    o Open( SqlCursor cursor )
    Opens an SqlCursor.
    o Prepare( SqlCursor cursor )
    Prepares an SqlCursor.
    o PutBlob( SqlConnection connect, String arg2, Integer arg3, Integer arg4 )
    Internal use only.
    o RowCount( SqlCursor cursor )
    Returns the number of rows affected by the execution of an SQL statement contained in an SqlCursor.
    o StartTransaction( SqlConnection connect )
    Starts a database transaction.

    Class Attributes

    o AUTOCOMMIT 
     Integer AUTOCOMMIT

    A commit flag for ODBC.

    o CACHE 
     Integer CACHE

    This attribute directs SQL.Declare() to cache the cursor, allowing for rows to be fetched from the database more efficiently.

    o CACHEDYNAMIC 
     Integer CACHEDYNAMIC

    Not used.

    o COMMIT 
     Integer COMMIT

    A constant passed to Sql.EndTransaction() indicating a transaction should be committed.

    o MULTIEXEC 
     Integer MULTIEXEC

    Not used.

    o NOCACHE 
     Integer NOCACHE

    Directs SQL.Declare() to not cache the cursor.

    o NOLOG 
     Integer NOLOG

    Directs SQL.Declare() to turn off statement logging within the cursor.

    o NOTRANSWARN 
     Integer NOTRANSWARN

    Directs SQL.Declare() to turn off warnings if the cursor is being used outside the context of a transaction.

    o ROLLBACK 
     Error ROLLBACK

    An error passed to Sql.EndTransaction() indicating a transaction should be rolled back. SQL.Fetch() also returns SQL.ROLLBACK to indicated no further data can be fetched.

    o SLOWMODE 
     Integer SLOWMODE

    Not used.

    Class Methods

    o Alloc 
     SqlConnection Alloc(
                  Integer conType,
                  CAPILOG logObj,
                  CAPIERR errObj,
                  Integer num )

    Allocates an SqlConnection object. This data structure is used to hold information about the database connection.

    Parameters:
    conType  - 

    An Integer specifying the connection type. CAPI.CT_ORACLE indicates Oracle and CAPI.CT_MSDBLIB indicates MSSQL Server.
    logObj  -  A CAPILog object for logging transactions.
    errObj  -  A CAPIErr object for logging errors.
    num  -  This parameter must be zero.
    Returns:
    An allocated SqlConnection, otherwise an error.

    Remember that Alloc() merely allocates the connection, and that Connect() must be called afterwards to actually connect to the database.

    o AllocFromConnect 
     SqlConnection AllocFromConnect(
                        CAPICONNECT connect )

    Allocates an SqlConnection object from a CAPICONNECT. This SqlConnection is used to hold information about the database connection and is required for most SQL functions in this package.

    Parameters:
    connect  -  A CAPIConnect, used to allocate an SqlConnection.
    Returns:
    An allocated SqlConnection, or an error.
    o Bind 
     Integer Bind(
            SqlCursor cursor,
            List values )

    Binds a list of Strings, Integers, and/or Dates to parameters in the SQL statement contained in the cursor. This function must be called before the execution and fetching of the cursor, but after SQL.Prepare() is called. If there are no parameters in the SQL statement, pass an empty list.

    Parameters:
    cursor  -  The SqlCursor to be bound.
    values  -  A list of values to bind to the parameters in the SQL statement within the cursor, or an empty list. These values can be Strings, Integers, or Dates.
    Returns:
    0 if successful, Error otherwise.
    o Close 
     Integer Close(
            SqlCursor cursor )

    Closes the specified SqlCursor after processing is complete.

    Parameters:
    cursor  -  The SqlCursor to be closed.
    Returns:
    0 if successful, Error otherwise.
    o ColNames 
     List ColNames(
           SqlCursor cursor )

    Returns a List of column names referenced by the SQL statement within the cursor.

    Parameters:
    cursor  -  The SqlCursor from which column information will be extracted.
    Returns:
    A List of column names referenced by the SQL statement within the cursor.
    o Cols 
     List Cols(
         SqlCursor cursor )

    Returns a List of the data from the last cursor fetch.

    Parameters:
    cursor  -  An SqlCursor.
    Returns:
    A List containing the data from the last fetch of the cursor. The list contains one element for each row of data, and the values can be Strings, Integers, and/or Dates.
    o Connect 
     Integer Connect(
             SqlConnection connect,
             String srvName,
             String dbName,
	     String usrName,
	     String pword,
	     String applStr,
             String hostStr )

    Connects an allocated SqlConnection to the database.

    Parameters:
    connect  -  The SqlConnection to be connected.
    srvName  -  The String name of the server to which the connection is made.
    dbName  -  The String name of the database to which the connection is made. For an Oracle databases, a NULL string must be used.
    usrName  -  The String name of the person user being connected.
    pword - The password of the user being connected.
    Returns:
    0 if successful, Error otherwise.
    o Declare 
     SqlCursor Declare(
               SqlConnection connect,
               String stmt,
              [Integer flags] )

    Declares an SqlCursor for later use. The statement may contain parameters for binding in the form of ":A1", ":A2", etc. Repeated values must be expressed as separate parameters, so that ":A1" cannot be reused later in the SQL statement even if the bound value is identical in both instances.

    Parameters:
    connect  -  An SqlConnection.
    stmt  -  An SQL statement.
    flags  -  Flag(s) to indicate how the statement and cursor will be handled. SQL.CACHE, SQL.NOCACHE, SQL.NOLOG, and SQL.NOTRANSWARN are all valid flags (See Attributes section above for more information on these flags) .
    Returns:
    The declared cursor if successful, Error otherwise.
    o Disconnect 
     Integer Disconnect(
               SqlConnection connect )

    Drops or disconnects a database connection.

    Parameters:
    connect  -  An SqlConnection to be dropped.
    Returns:
    0 if successful, Error otherwise.
    o EndTransaction 
     Integer EndTransaction(
                 SqlConnection connect,
                 Dynamic value )

    Ends a database transaction, allowing commitment or rollback of the transaction. See also SQL.EndTransaction().

    Parameters:
    connect  -  An SqlConnection.
    value  -  A value indicating a commitment or a rollback of the transaction. SQL.COMMIT and SQL.ROLLBACK are commonly passed. If the value is of type Error, rollback will occur.
    Returns:
    0 if successful, Error otherwise.
    o Exec 
     RecArray Exec(
             SqlConnection connect,

             String stmt,
            [Dynamic sub<n>],
            [Dynamic sub<n>] )

    Executes an SQL statement.

    Parameters:
    connect  -  An SqlConnection.
    stmt  -  An SQL statement. Parameterized values can be included in this statement, with the parameter syntax consisting of a colon followed by alphanumeric characters (":A1", ":A2", etc.). Up to 30 parameters can be passed.
    sub<n>  -  the values of the optional parameters included in the SQL statement. Either all the parameters can be passed in a List for this argument, or they may be passed as arguments, since this is a variable-argument function (like Str.Format()).
    Each sub<n> parameter specifies a value to be substituted in the SQL statement for the respective parameter. Values are substituted from left to right, regardless of variable name or parameter characters.
    Returns:
    For SELECT statements: A RecArray containing the data from the executed SQL statement is returned. For INSERT, UPDATE, or DELETE statements: An Integer indicating the number of rows affected by the executed statement is returned. If the execution of the SQL statement fails, Error is returned.

    If a parameterized statement is executed multiple times for various data, ExecN() should be used instead since it is optimized for such cases.

    o ExecN 
     RecArray ExecN(
             SqlConnection connect,
             String stmt,
             RecArray params )

    Executes a parameterized SQL statement multiple times; once for each row in parms, a RecArray of parameter values. ExecN() may be optimized in some databases to perform better than calling Exec() for each iteration.

    Parameters:
    connect  -  An SqlConnection.
    stmt  -  An SQL statement. Parameterized values can be included in this statement, with the parameter syntax consisting of a colon followed by alphanumeric characters (":A1", ":A2", etc.). Up to 30 parameters can be passed.
    params  -  A RecArray of parameters. The parameterized SQL statement is executed using parameters from each row in the RecArray, in left-to-right column order in a given row.
    Returns:
    For SELECT statements: A RecArray containing the data from the executed SQL statement is returned. For INSERT, UPDATE, or DELETE statements: An Integer indicating the number of rows affected by the executed statement is returned. If the execution of the SQL statement fails, Error is returned.
    o Execute 
     Integer Execute(
             SqlCursor cursor )

    Executes an SqlCursor. This function differs from SQL.Fetch() in that it is performed once, after the declaration, preparation, and binding of the cursor. All the data that will be obtained from the database by the SQL statement contained in the cursor is retrieved during SQL.Execute(). See also SQL.Prepare(), SQL.Bind(), and SQL.Fetch().

    Parameters:
    cursor  -  An SqlCursor to execute.
    Returns:
    0 if successful, Error otherwise.
    o Fetch 
     Integer Fetch(
            SqlCursor cursor )

    Fetches a row of data based on the specified SqlCursor. After an SQL.Execute() has been performed on a cursor, SQL.Fetch() is used to retrieve the data incrementally. In the case of a cached cursor, rows of data are retrieved in blocks for greater efficiency. See also SQL.Prepare(), SQL.Bind(), and SQL.Execute().

    Parameters:
    cursor  -  An SqlCursor upon which the fetch is to be performed.
    Returns:
    0 if successful, Error otherwise. When the SQL.ROLLBACK is returned, the fetch is complete.
    o GetBlob 
     Integer GetBlob(
             SqlConnection arg1,
             String arg2,
             Integer arg3,
	     Integer arg4 )

    Internal use only.

    Parameters:

    connect

    		  -  An SqlConnection.
    arg2  -  Internal use only
    arg3  -  Internal use only
    arg4  -  Internal use only
    Returns:
    Internal use only.
    o IsSelect 
     Integer IsSelect(
              SqlCursor cursor )

    Indicates whether the SQL statement within an SqlCursor is a SELECT statement. The SQL statement must have been parsed with SQL.Prepare() before this method is called.

    Parameters:
    cursor  -  The SqlCursor in question.
    Returns:
    TRUE if the statement is a SELECT, FALSE otherwise.
    o NCols 
     Integer NCols(
            SqlCursor cursor )

    Determines the number of columns that the SQL statement in the cursor will return.

    Parameters:
    cursor  -  The SqlCursor in question.
    Returns:
    An Integer specifying the number of columns that the SQL statement will return.
    o Now 
     Date Now(
        SqlConnection connect )

    Returns the current date and time as defined by the database server.

    Parameters:
    connect  -  An SqlConnection.
    Returns:
    The current date and time as defined by the database server.
    o Open 
     Integer Open(
            SqlCursor cursor )

    Opens the SqlCursor after it has been declared.

    Parameters:
    cursor  -  The SqlCursor to be opened.
    Returns:
    0 if successful, Error otherwise.
    o Prepare 
     Integer Prepare(
             SqlCursor cursor )

    Triggers the server to parse the SQL statement within the cursor. The SqlCursor must be parsed before most other methods can be performed with it.

    Parameters:
    cursor  -  The SqlCursor to be parsed/prepared.
    Returns:
    0 if successful, Error otherwise.
    o PutBlob 
     Dynamic PutBlob(
             SqlConnection connect,
             String arg2,
             Integer arg3,
             Integer arg4 )

    Internal use only.

    Parameters:
    connect  -  The SqlConnection.
    arg2  -  Internal use only.
    arg3  -  Internal use only.
    arg4  -  Internal use only.
    Returns:
    Internal use only.

     

    o RowCount 
     Integer RowCount(
              SqlCursor cursor )

    Determines the number of rows affected by the execution of an SQL statement defined within an SqlCursor. SQL.Prepare() must have been called before this method can be used.

    Parameters:
    cursor  -  The SqlCursor whose affected rows are to be counted.
    Returns:
    An Integer specifying the number of rows affected by the SQL statement.
    o StartTransaction 
     Integer StartTransaction(
                  SqlConnection connect )

    Starts a database transaction, allowing multiple SQL statements to be grouped together. Should an error occur during the execution of any statement after the beginning of the transaction and before the SQL.EndTransaction(), all of the intervening statements can be rolled back or canceled.

    Parameters:
    connect  -  An SqlConnection.
    Returns:
    0 if successful, Error otherwise.