Sql

String

System

UAPI

UAPISESSION

Class: Sql

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.
GetTimeData() and ResetTimeData() to get/reset timing data used for SQL transaction statistics 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

AUTOCOMMIT

A commit flag for ODBC.

CACHE

Cache the cursor.

CACHEDYNAMIC

COMMIT

Commit a transaction.

EXPECT_100S

Expect hundreds of rows result from the select.

EXPECT_10S

Expect tens of rows result from the select.

EXPECT_1S

Expect on the order of a single result from the select.

MAX_INCLAUSE_NUM

Maximum number of IN clause for a sql statement.

MAXBIGINT

The maximum value of BIGINT.

MAXDATAIDINT

The maximum value for DataID.

MAXDBIGINT

MAXINT

The maximum value of INT ( or INTEGER).

MAXSHORTINT

The maximum value of SMALLINT.

MAXTINYINT

The maximum value of TINYINT.

MINBIGINT

The minimum value of BIGINT.

MINDATAIDINT

The minimum value for DataID.

MINDBIGINT

MININT

The minimum value of INT ( or INTEGER).

MINSHORTINT

The minimum value of SMALLINT.

MINTINYINT

The minimum value of TINYINT.

MULTIEXEC

NOCACHE

Don't cache the cursor.

NOLOG

Statement shouldn't be logged.

NOTRANSWARN

Turn off warnings if statement is not in the context of a transaction.

ROLLBACK

An error returned by SQL.Fetch() to indicate completion and/or passed to SQL.EndTransaction() to rollback a transaction.

SLOWMODE

Class Methods

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

Allocates a connection object.

AllocFromConnect( CAPICONNECT connect )

Allocates a connection object from a CAPIConnect

Bind( SqlCursor cursor, List values )

Binds a list of values to an SqlCursor.

Close( SqlCursor cursor )

Closes an SqlCursor.

ColNames( SqlCursor cursor )

Returns the names of the columns referenced by a cursor.

Cols( SqlCursor cursor )

Returns the data from the last SQL.Fetch() execution.

Connect( SqlConnection connect, String srvName, String dbName, String usrName, String pword )

Connects an allocated connection object to the database.

DatabaseVersion( Dynamic connect )

Returns the database version.

Declare( SqlConnection connect, String stmt, [Integer flags] )

Declares an SqlCursor.

Disconnect( SqlConnection connect )

Drops the database connection.

EndTransaction( SqlConnection connect, Dynamic value )

Ends a database transaction, allowing commitment or rollback.

Exec( SqlConnection connect, String stmt, [Dynamic sub] )

Executes an SQL statement.

ExecN( SqlConnection connect, String stmt, RecArray params )

Executes an SQL statement multipled times for a RecArray of parameterized values.

Execute( SqlCursor cursor )

Executes an SqlCursor.

Fetch( SqlCursor cursor )

Fetches a row of data specified by the cursor.

GetTimeData( String prefix )

Returns an assoc containing SQL transaction statistics or an error.

IsSelect( SqlCursor cursor )

Determines whether the cursor contains a select statement.

NCols( SqlCursor cursor )

Determines the number of columns the SQL statement will return.

Now( SqlConnection connect )

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

Open( SqlCursor cursor )

Opens an SqlCursor.

Prepare( SqlCursor cursor )

Prepares an SqlCursor.

ResetTimeData( )

Resets all timing data used for SQL transaction statistics.

RowCount( SqlCursor cursor )

Returns the number of rows affected by the execution of an SQL statement contained in an SqlCursor.

StartTransaction( SqlConnection connect )

Starts a database transaction.

Class Attributes

Integer AUTOCOMMIT

A commit flag for ODBC.

Integer CACHE

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

Integer CACHEDYNAMIC

Not used.

Integer COMMIT

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

Integer EXPECT_100S

Expect hundreds of rows result from the select.

Integer EXPECT_10S

Expect tens of rows result from the select.

Integer EXPECT_1S

Expect on the order of a single result from the select.

Integer MAX_INCLAUSE_NUM

Maximum number of IN clause for a sql statement.

MAXBIGINT

Integer MAXDATAIDINT

The maximum value for DataID:223,372,036,854,775,807.

Integer MAXDBIGINT

The maximum value of BIGINT:223,372,036,854,775,807.

Integer MAXINT

The maximum value of INT:223,372,036,854,775,807.

Integer MAXSHORTINT

The maximum value of SMALLINT:2,147,483,647.

Integer MAXTINYINT

The maximum value of SMALLINT:255.

MINBIGINT

Integer MINDATAIDINT

The minimum value for DataID:-223,372,036,854,775,808.

Integer MINDBIGINT

The minimum value of BIGINT:-223,372,036,854,775,808.

Integer MININT

The minimum value of INT:-223,372,036,854,775,808.

Integer MINSHORTINT

The minimum value of SMALLINT:-2,147,483,648.

Integer MINTINYINT

The minimum value of SMALLINT:0.

Integer MULTIEXEC

Not used.

Integer NOCACHE

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

Integer NOLOG

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

Integer NOTRANSWARN

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

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.

Integer SLOWMODE

Not used.

Class Methods

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.

Example

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

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.

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

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.

DatabaseVersion

String DatabaseVersion( Dynamic connect )

Returns the version of the database serer being connected.

Parameters

connect

A valid SqlConnection or CAPIConnect object.

Returns:

The database version; Error otherwise.

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.

Disconnect

Integer Disconnect( SqlConnection connect )

Drops or disconnects a database connection.

Parameters

connect

An SqlConnection to be dropped.

Returns:

0 if successful, Error otherwise.

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.

Exec

RecArray Exec( SqlConnection connect,
String stmt,
[Dynamic sub] )

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

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.

Example

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

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

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.

An assoc containing SQL transaction statistics or an error. If returned, the assoc has the following structure:

Total_SQL_Time_Logged	Integer
Total_SQL_Time	Integer
Total_Fetch_Time	Integer
Total_Execute_Time	Integer
Number_of_Select_Statements
Number_of_Update_Statements	Integer
Number_of_Insert_Statements	Integer
Number_of_Delete_Statements	Integer
Number_of_Other_Statements	Integer
Total_SQL_Statements	Integer
Number_of_Rows_Fetched	Integer

ResetTimeData

Dynamic ResetTimeData()

Resets all timing data used for SQL transaction statistics.

Returns:

0(zero) if successful; Error otherwise.

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.

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.