Class wwSQL

The wwSQL class is a wrapper around SQL Passthrough that provides solid error handling and connection recovery services as well as an easier interface to make SQL passthrough calls. Rather than managing a SQLConnect handle you simply hang on to a class reference.

The class is fairly simple and only performs base tasks, but it exposes the underlying SQL handle so you have full access to all the features of the SQLPassthrough mechanism for setting connection properties etc. This class is mainly geared at making common tasks more manageable.

DO wwSql
loSql = CREATEOBJECT("wwSQL")

loSQL.Connect("server=.;database=webstore;integrated security=true")

*** Query
lnCount = loSql.Execute("select * from wws_items","TQuery") 
IF (lnCount < 0)
   ? "Error: " + loSql.cErrorMsg
   RETURN
ENDIF

browse nowait  && Cursor TQuery

*** Insert, Update, Delete
pcSku = "WCONNECT"
ptTime = DATETIME()
IF (!loSQL.ExecuteNonQuery("update wws_items set expected=?ptTime  where sku=?pcSKU "))
   ? "Error: " + loSql.cErrorMsg
   RETURN
ENDIF

? loSql.nAffectedRecords

*** Auto-closes when object goes out of scope
* loSql.Close()
Custom
  wwSql

Class Members

MemberDescription

AddParameter

o.wwsql.AddParameter(lvValue,lcName, lcDirection, lnAdoType, lnSize,lnPrecision)

AddSql

Adds text to the current SQL String in cSQL. Pass CLEAR to clear the cSQL property.

o.addsql(lcCode)

BeginTransaction

Begins a SQL transaction.

o.BeginTransaction(lcTransactionName)

Close

Closes an open SQL connection if one is still open. The connection is also automatically closed if the wwSQL object is destroyed.

o.Close()

Commit

Commits a SQL transaction.

o.Commit(lcTransactionName)

Connect

Connects to a datasource via connection or connect string.

o.Connect(lcConnectString, llAsynch, llNoAutoTransactions)

EnableUnicodeToAnsiMapping

This method enables automatic Unicode support for SQL Passthrough operation by picking up nText, nChar and nVarChar data and automatically converting it to the current codepage.

Works only in VFP 9 and later. Applies only to Sql Passthrough operation.

o.EnableUnicodeToAnsiMapping(llSet)

Execute

o.execute(lcSQL)

ExecuteNonQuery

o.wwsql.ExecuteNonQuery(lcSQL)

ExecuteStoredProcedure

Calls a stored procedure.

o.ExecuteStoredProcedure(lcProcedure,lcCursor)

InsertBinaryField

Inserts binary data into a SQL Server Image field.This method updates a single image field on a specific record specified by a WHERE clause.

o.InsertBinaryField(lcImageData,lcField,lcTable,lcWhereClause)

InsertDataFromCursor

Imports data from a Fox cursor into a matching SQL table, provided the structures match and data types are compatible.

o.InsertDataFromCursor(lcTable)

ReadBinaryField

Reads binary data from a SQL Server Image field and returns it as a Fox string.

o.ReadBinaryField(lcField,lcTable,lcWhereClause)

Rollback

Rolls back a SQL transaction started with BeginTransaction.

o.Rollback(lcTransactionName)

SetAsyncMode

Sets the connection into Async operation.

o.setasynchmode(llAsynch)

SQLBuildInsertStatementFromObj

Creates a SQL INSERT string from a SCATTER NAME type object.

o.SQLBuildInsertStatementFromObject(loData,lcTablename,loSQLRecord, llUseNamedParameters)

SqlBuildUpdateStatementFromObj

This method builds a SQL UPDATE string to be sent to the backend application from a SCATTER NAME type object. The object must contain a key field in order for this method to work.

o.SqlBuildUpdateStatementFromObject(loData, lcFileName, lcPkField, loOrigData, loSQLRecord,llUseNamedParameters)

SqlCancel

Cancels the currently running SQL request if running in Async mode.

o.sqlcancel()

aErrors

Contains full error information after an error occurs.

cConnectString

An ODBC connection string. Either a DSN or full SQL Connect String.

cErrorMsg

Contains an error message if an error occurs. cErrorMsg2 contains more error info.

cErrorMsg2

Contains an error message if an error occurs. cErrorMsg contains more primary info.

cSkipFieldsForUpdates

Used to exclude fields from the auto field list generated in the SQLBuildInsert/UpdateStatementFromObject methods.

cSql

The SQL command to be run.

csqlcursor

Cursor to send the result to. If the cursor exists it's overwritten otherwise it's created.

lError

Error flag set .t. if an error occurs.

lNamedParameters

If set adds parameters in AddParameters as Named Parameters which allows stored procedures to be called with partial parameter lists if optional parameters are supported by the command or Stored Procedure.

lUseConnection

.T. if a connection was used to connect, .f. if a DSN, UID, PWD was used. Used internally in reconnection.

nAffectedRecords

Returns the number of records affected by the last operation. For a query this is the number of records returned. For a non-query operation this is the number of records updated.

nErrorNo

Error number if an error occurs.

nOdbcErrorNo

ODBC Error if an error occurs.

nSqlHandle

The ODBC handle you can use directly with VFP's internal SQL Passthrough properties.

nSqlResult

SQL Result value from SQLExecute or any other command executed.

nTimeout

Determines the connection timeout for SQL connections in seconds.

The default value is 15 seconds which is the FoxPro default.

Requirements

Assembly: wwSql.prg

© West Wind Technologies, 1996-2019 • Updated: 11/08/18
Comment or report problem with topic