Class wwSQL - West Wind Client Tools

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

Member		Description
	AddParameter	Used to set named parameters to be passed into SQL statements. 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. o.EnableUnicodeToAnsiMapping(llSet)
	Execute	Runs a SQL statement specified in cSQL or passed in as a parameter. This method returns the SQL error code, as well as error information in cErrorMsg, cErrorMsg2 and aErrors. Any connection errors are retried several times. o.execute(lcSQL, lcCursorName, llStoredProcedure)
	ExecuteNonQuery	Executes a SQL statement that doesn't return a cursor result. This method is more efficient than Execute when dealing with INSERT/UPDATE and Stored procedures that do not return data. 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)
	SQLBuildInsertStatementFromObject	Creates a SQL INSERT string from a `SCATTER NAME` or `EMPTY` object. o.SQLBuildInsertStatementFromObject(loData,lcTablename,loSQLRecord, llUseNamedParameters)
	SqlBuildUpdateStatementFromObject	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	Comma delimited list of properties/fields that are skipped for Insert and Update statements built on the fly using `SqlBuildInsertStatementFromObject()` and `SqlBuildUpdateStatementFromObject()`.
	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.
	lParameterReset	Internal flag used to determine whether to automatically clear any Named Parameters after a SQL command has executed, so the next command starts with a clean parameter list.
	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.

Requirements

Assembly: wwSql.prg