Xbasic

SQL::Connection Class

IN THIS PAGE

Description

A SQL::Connection instance represents a potential or active database session.

Discussion

The SQL::Connection object is the heart of all database activity. Each instance of SQL::Connection represents a potential or active database session. In order to do anything with a SQL database you must first create (or dimension) a variable and associate it with a programming interface (API) and with a vendor syntax ( Oracle, Access, etc.).

Using the Connection Object

The following sample script creates a SQL::Connection object and opens a connection to a Microsoft Access database.

DIM c as SQL::Connection
DIM cs as C
cs = "{A5API='Access', A5Syntax='Access', FileName='C:\Program Files\a5v6\MDBFiles\Alphasports.mdb', UserName='Admin'}"
? c.Open(cs) 
= .T.

Properties

CurrentAPICharacter

The name of the current application programming interface being used. For example: Access, Oracle, ODBC, MySQL.

CurrentSyntaxCharacter

The dialect of SQL (and typically the vendor or product) being used to interact with the database.

IsOpenLogical

Returns true if the connection is currently open and false if it has not been opened yet or has been closed.

CallResultSQL::CallResult

Returns the results of the last call.

ResultSetSQL::ResultSet

Returns the currently active result set, or an error if there isn't one.

ConnectionStringCharacter

The current connection string in syntax format. Should be set by ::SetConnectString().

SchemaCacheEnabledLogical

Default = TRUE (.T.). When this property is set to TRUE (.T.), schema objects retrieved (such as SQL::TableInfo) will be cached for the connection (disconnecting and some update functions clear the cache), when false, all objects are not cached and subsequent requests for a schema object will go directly to the database.

BulkReadEnabledLogical

BulkReadEnabled property

BulkWriteEnabledLogical

BulkWriteEnabled property

PortableSQLEnabledLogical

Default = FALSE (.F.). When this property is set to true, SQL statements passed to the ApplyData(), Execute(), ToOpenTable(), ToGridComponentDataObject(), ToOpenTable(), ToPropertyArray(), ToString(), ToXML(), and ValidateQuery() methods are checked to see if they are supported Portable SQL Statements and, if so, then translated to native syntax before being processed. Statements that are not supported are passed directly through to the underlying database driver as if PortableSQLEnabled were false.

LoginTimeoutCharacter

Timeout values are "Default", "Infinite", or a string containing an integer specifying the timeout in seconds. Must be set prior to opening the connection.

ConnectionTimeoutCharacter

Timeout values are "Default", "Infinite", or a string containing an integer specifying the timeout in seconds. Must be set prior to opening the connection.

QueryTimeoutCharacter

Timeout values are "Default", "Infinite", or a string containing an integer specifying the timeout in seconds. Must be set prior to executing a query.

TraceSQLLogical

TraceSQL property

TraceContextLogical

TraceContext property

InitialCommandCharacter

InitialCommand property

CurrentProviderCharacter

CurrentProvider property

CurrentProviderDatasourceCharacter

CurrentProviderDatasource property

SSLLogical

SSL property

UseServerSidePrepareLogical

UseServerSidePrepare property

ReportNotFoundAsWarningLogical

Default = .F.. Defines whether or not to return an error if no records are modified when performing an Update or Delete operation. If .F., SQL::Connection.execute() will treat "no records found" as an error (default behavior). If .T., SQL::Connection.execute() will treat "no records found" as a warning. See SQL::Connection.execute() for more information. (Requires build 4958)

Methods

AddEventScript Method

Add a connection event script with functions to handle one or more events.

AddUser Method

Create a new user in the currently connected database or server.

AddUserGroup Method

Create a new user group in the currently connected database or server.

AddUserToGroup Method

Make the user a member of the group in the currently connected database or server.

AffectedRows Method

Return affected rows.

ApplyData Method

Apply the query to a table in the currently connected database using the result set provided.

BeginTransaction Method

Begin a batch of SQL updates, inserts and/or deletes that need to succeed or fail as a group.

ClearSchemaCache Method

Clear any schema data cached by the connection.

Close Method

Close a SQL connection.

CommitTransaction Method

Apply a set of changes started with BeginTransaction.

CopyFrom Method

Copy tables and data from one connection to another.

CreateDatabase Method

The CreateDatabase() method creates a new database. The contents of the database string are database specific

CreateIndex Method

Create an index on the specified table in the currently connected database using the TableInfo and IndexInfo specifications.

CreateTable Method

Create a table in the currently connected database using the TableInfo specification.

DeleteData Method

Delete rows from a table in the currently connected database using a result set.

DropDatabase Method

Remove a database.

DropIndex Method

Drop the index named from table specified in the currently connected database.

DropTable Method

Drop a table from the currently connected database.

DropUser Method

Remove the user named from the currently connected database or server.

DropUserFromGroup Method

Remove the user from the group in the currently connected database or server.

DropUserGroup Method

Remove the user group named from the currently connected database or server.

Execute Method

Execute a SQL statement on the connection.

FreeResult Method

Release the result set from the last SQL select statement.

GenerateAddUserGroupStatement Method

Generate a SQL Add User Group statement.

GenerateAddUserStatement Method

Generate a SQL Add User statement.

GenerateAddUserToGroupStatement Method

Generate a SQL Add User To Group statement.

GenerateCreateDatabaseStatement Method

Generate a SQL Create Database statement for the table passed in.

GenerateCreateIndexStatement Method

Generate a SQL Create Index statement for the table and index passed in.

GenerateCreateTableStatement Method

Generate a SQL CREATE TABLE statement for the table passed in.

GenerateDeleteStatement Method

Generate a SQL Delete statement for the table passed in.

GenerateDropDatabaseStatement Method

Generate a SQL Drop Database statement for the table passed in.

GenerateDropIndexStatement Method

Generate a SQL Drop Index statement for the table passed in.

GenerateDropTableStatement Method

Generate a SQL DROP TABLE statement for the table passed in.

GenerateDropUserFromGroupStatement Method

Generate a SQL Drop User From Group statement.

GenerateDropUserGroupStatement Method

Generate a SQL Drop User Group statement.

GenerateDropUserStatement Method

Generate a SQL Drop User statement.

GenerateGrantAccessStatement Method

Generate a SQL Grant Access statement.

GenerateInsertStatement Method

Generate a SQL Insert statement for the table passed in.

GenerateLastInsertedIdentityStatement Method

Generate a SQL statement to retrieve the identity from the last insert statement (may be specific to the table passed in).

GenerateNativeColumnName Method

Generate a formatted column name suitable for a SQL query in using the current syntax.

GenerateNativeTableName Method

Generate a formatted table name suitable for a SQL query in using the current syntax.

GenerateNativeTypeString Method

Generate a SQL type string for type passed in using the current syntax. TableInfo is optional, but provides additional information that is useful.

GenerateRetrieveRowQuery Method

Generate a SQL statement to retrieve a specific row for the table passed in. Key columns are generated as arguments.

GenerateRevokeAccessStatement Method

Generate a SQL Revoke Access statement.

GenerateRowQualifiers Method

Generate a crlf delimited list of row qualifiers in the format:

GenerateSelectStatement Method

Generate a SQL Select statement for the table passed in.

GenerateUpdateStatement Method

Generate a SQL Update statement for the table passed in.

GenerateWhereClause Method

Generate a SQL WHERE clause for a table, based on the update criteria specified.

GeographySupported Method

Returns true if the syntax name passed is supported for geography features.

GetConnectDialogScript Method

Retrieve the API specific XDialog syntax for the dialog to prompt for a connection string

GetDefaultSyntaxHandlerScript Method

Retrieves the syntax handler source for a particular syntax name.

GetExampleSyntaxScript Method

Retrieve the syntax handler source for an example syntax handler.

GetPreferredSyntax Method

Return a the most appropriate syntax for the server specified or an empty string if not known.

GetSchema Method

Get a schema definition.

GetTableInfo Method

Get the definition of a table.

GetTableInfoFromDBF Method

Get the definition of a DBF table from an open table or by name.

GetTemplateSyntaxScript Method

Retrieve the template syntax handler source for creating your own syntax handler.

GrantAccess Method

Grant to a user or group the access specified, optionally, on the resource named.

InsertData Method

Apply new rows to a table in the currently connected database using a result set.

LastInsertedIdentity Method

Returns the last inserted identity for a specific table (where possible) after executing an INSERT statement.

ListAPIs Method

Create a crlf() delimited list of installed SQL Application Programming Interfaces.

ListAutoIncrementImplementations Method

Create a crlf() delimited list of auto-increment implementations for the current SQL syntax.

ListDatabases Method

Return a list of databases from the current server.

ListDrivers Method

Lists installed ODBC drivers.

ListPortableSQLFunctions Method

Returns a list of supported Portable SQL functions for the connection Syntax.

ListProcedureLanguages Method

Create a crlf() delimited list of procedure languages for the current SQL syntax.

ListProperties Method

Create a list of all connection property names and values in the format <name><tab><value><crlf>

ListServers Method

Return a list of servers for the current connection Application Programming Interface(API).

ListSyntaxes Method

Create a crlf() delimited list of installed SQL Syntaxes.

ListTables Method

Return a list of tables for the current database.

ListTablesWithTypes Method

Return a list of tables for the current database suffixed with (<type>).

MaskPassword Method

Changes the characters in a password string so they will not display well.

Open Method

Connect to a database. (See help for SetConnectionString for details on constructing a connect string.)

PrimaryKeyExpression Method

Return the primary key expression for a Table.

RemoveEventScript Method

Remove an event script previously added with AddEventScript.

ResultSetFromADORecordSet Method

Create a SQL::ResultSet object for an ADODB RecordSet.

ResultSetFromDBF Method

Create a SQL::ResultSet object for a table. This object can be passed to SQL update functions.

ResultSetFromJSON Method

Create a SQL::ResultSet from JSON.

RevokeAccess Method

Revoke from a user or group the access specified, optionally, on the resource named.

RollBackTransaction Method

Undo a set of changes started with BeginTransaction, but not yet applied (CommitTransaction hasn't been called).

SetConnectionString Method

Set the connection string with required parameters. The actual values will depend on how you are connecting to your database.

SetSyntaxHandlerScript Method

Assign a handler script for the connection to override the default behavior of some or all of the event functions below:

ToCSV Method

Fetch data using a SQL query and return the data formatted as a CSV string.

ToCSVFile Method

Fetch data using a SQL query and save the data to file as a CSV formatted string.

ToDBF Method

Fetch data to a local .DBF table using a SQL query

ToGridComponentDataObject Method

Fetch data to a Grid Component Data Object using a SQL Query.

ToJSON Method

Fetch data to a JSON formatted string using a SQL query.

ToOpenTable Method

Fetch data to an open .DBF table using a SQL query.

ToPropertyArray Method

Fetch data to a property array using a SQL query.

ToString Method

Fetch data to a string using a SQL query.

ToXML Method

Fetch data to an XML formatted string using a SQL query.

UpdateData Method

Apply changes to existing rows in a table in the currently connected database using a result set.

ValidateQuery Method

Validate a SQL query on the connection.

An Important Note About Assigning the ResultSet Property to Variables

Except for a few database providers, the client driver does not support multiple active result sets. Attempting to execute a new query while processing the results of a previous query does not work.

In order to simplify the most common case, AlphaDAO connections automatically disassociate from an active ResultSet and reconnect to the database when a new query is executed, but there is still a variable referencing the active result set.

This is fine in most cases, but can cause unexpected behavior within a transaction. Because the SQL::Connection object "disconnects" from the referenced result set (and the connection that owns it), the transaction is effectively lost.

In order to prevent this behavior, use the DELETE keyword on any variable that has been assigned a ResultSet property prior to executing another query.

Below are some examples of code that can cause a problem with connections showing how to avoid the problem.

' Basic example showing two active result sets
' Note: At the end of this section there are two active result sets 
' and two active connections.
DIM c as SQL::Connection
DIM cs as C
cs = "{A5API='Access', A5Syntax='Access', FileName='C:\Program Files\a5v6\MDBFiles\Alphasports.mdb', UserName='Admin'}"
? c.Open(cs) 
= .T.
?c.Execute("select * from Customers")
rs = c.ResultSet
?c.Execute("select * from Orders")

What happens if we are in a transaction?

At the end of this section there are two active connections. The UPDATE statements are executed on two different connections, the latter is NOT in the transaction.

DIM c as SQL::Connection
DIM cs as C
cs = "{A5API='Access', A5Syntax='Access', FileName='C:\Program Files\a5v6\MDBFiles\Alphasports.mdb', UserName='Admin'}"
? c.Open(cs) 
= .T.
?c.BeginTransaction()
?c.Execute("Update ...")
?c.Execute("select * from Orders")
rs = c.ResultSet
?c.Execute("update ...")

How do we fix the problem?

By using the DELETE statement, we release the result set and the SQL::Connection object will not reconnect to the database.

At the end of this section there is a single connection. The UPDATE statements are executed on the same connection.

DIM c as SQL::Connection
DIM cs as C
cs = "{A5API='Access', A5Syntax='Access', FileName='C:\Program Files\a5v6\MDBFiles\Alphasports.mdb', UserName='Admin'}"
? c.Open(cs) 
= .T.
?c.BeginTransaction()
?c.Execute("Update ...")
?c.Execute("select * from Orders")
rs = c.ResultSet
'.. do something with the result set
DELETE rs
?c.Execute("update ...")

See Also