|
SQL() |
  
|
|
SQL() |
|
SQL(1,2,3)
PURPOSE
Use this function to Read and Write SQL Data From your TASPRO Application.
PARTS
1 sac Required - What to do:
sqlOpenConnection - This is connection String to the Database
sqlQuery - Standard Query to the database.
sqlQueryForward – This creates a ForwardONLY Query to the database ( sqlmovefirst and sqlmoveprevious will not work)
sqlCloseRecordSet – Closes the Current Recordset/Query
sqlCloseConnection – Closes the Connection to the Database
sqlGetRecordCount – Get the current Record count for the Query
sqlExecute – Execute SQL Commands
sqlExecuteScalar – execute SQL Commands that Return a Single Value
sqlMoveFirst - Move the to the First Position in the Query
sqlMoveNext – Moves to the Next Position in the Query
sqlMovePrevious – Move to the Previous Position in the Query
sqlMoveLast – Move to the Last Position in the Query
sqlGetData – Retrieves the Data for a Field.
sqlBind - Binds fields to the query (Very useful also see Autobind).
sqlGetFieldCount – Give you the number for fields in your Query.
sqlGetFieldname – Give you the field name by position.
sqlGetFieldType - Gives you the field type (Alpha, Number Etc..)
sqlgetFieldSize - Gives you the field size.
sqlGetTableCount - Gives you the number of Non-System Tables.
sqlGetTableNames - Gives you the names of the tables in the SQL database.
sqlGetErrorCount – Get the Error Count
sqlGetError - Get the Error String
sqlGetNativeError - This gets the native error code from the sql database.
2 f/c/e Depends on the operation:
sqlCloseConnection - Connection Number (Default 0)
sqlOpenConnection - has the Connection String to the Server.
sqlQuery - The Query string to the server.
sqlQueryForward – same a sqlQuery
sqlCloserecordset – (optional) The number of the recordset/Query to close (note 1)
sqlGetRecordCount – (optional) the recordset to get the record count of (note 1)
sqlExecute – sql Statement to Execute
sqlExecuteScalar – Same as sqlExecute but returns a value
sqlMoveFirst,
sqlmoveNext,
sqlMovePrevious,
sqlMoveLast – (optional) AutoBindMode (enter sqlBind here and it will auto bind)
sqlGetData - string with SQL Field name to retrieve data from.
sqlBind ,
sqlgetFieldCount – (optional) number of the Query to use (note 1)
sqlGetFieldName – number of the field to return the name from.
sqlGetTableCount,
sqlGetTableNames,
sqlGetErrorCount – (optional) Connection number (Default 0)
sqlGetError - Error number (*)
sqlGetNativeError - Error number (*)
* These are a count like first error, second error, ETC.
3 f/c/e Depends on the operation:
sqlOpenConnection - (optional) Connection number (Default 0)
sqlQuery,
sqlQueryForward,
sqlMoveFirst,
sqlmoveNext,
sqlMovePrevious,
sqlMoveLast,
SqlGetData,
SqlGetFieldname - (optional) number of the Query to use (note 1)
sqlCloserecordset ,
sqlGetRecordCount,
sqlGetFieldCount,
sqlExecute,
sqlExecuteScalar,
sqlGetError – (optional) Connection number (Default 0)
sqlGetNativeError - (optional) Connection number (Default 0)
4 f/c/e Depends on the operation:
sqlQuery – (Optional) Connection number (Default 0)
RETURN TYPE - Varies
The return value depends on the option set in part 1.
sqlOpenConnection - L - Returns True if the Connection on process is successful and False if not.
sqlQuery sqlQueryforward - L - Returns True if the Query process is successful and False if not.
sqlCloseConnect – L – Returns True if the Connection is closed successful
sqlCloseRecordSet – L – Returns True if the Record set/Query is close successfully.
sqlGetRcordCount - I - Returns the number of Records in the Query.
sqlExecute – L – Returns True if the Process is Executed Correctly.
sqlExecuteScalar – A/N/I/R/D/T/L – Depends on the results Field
sqlMoveFirst,
sqlMovePrevous,
sqlMoveNext,
sqlMoveLast - L – Returns True is record has moved.(returns False if moved to beginning or End)
sqlGetData – A/N/I/R/D/T/L – Depends on the results Field
sqlBind – L – Return True..
sqlGetFieldCount - I - Returns the number of Field in the Recordset/Query.
sqlFieldName - A - returns the Field name.
SQLgetErrorCount - I - The numbers of Errors
sqlGetError - A - The ADO Error Descriptions.
dqlGetNativeError - I - the Native Error code from the Connected database.
NEW SQL Commands:
SQLBeginTrans :
Call BeginTrans to start a new transaction in the data store the ADO connection component is connected to.
BeginTrans returns a value of type integer, indicating the nesting level of the new transaction
SQLCommitTrans:
Call CommitTrans to save any changes made during the current transaction and to end the transaction.
SQLRollBackTrans
Call RollbackTrans to cancel any changes made during the current transaction and to end the transaction.
SQLInTrans
A transaction is will be active if the BeginTrans method has been called to initiate a transaction but that transaction has not either been consummated by calling CommitTrans or canceled by calling the RollbackTrans method. Use the InTransaction property to prevent calling BeginTrans when a transaction has already been initiated
SQLGetIsolation (Pass Level in Position 3)
Use IsolationLevel to specify the transaction isolation level for a connection. The transaction isolation level determines how a transaction interacts with other simultaneous transactions when they work with the same tables, and how much a transaction sees of the work performed by other transactions. The default value for IsolationLevel is ilCursorStability.
SQLSetIsolation
Read IsolationLevel after the transaction has been activated to determine the actual transaction isolation level used. It is possible that a server will force an isolation level other than that requested if the level requested is not supported.
Isolation TYPES
ilUnspecified Server is using a an isolation level other than what was requested and the specific isolation level cannot be determined.
ilChaos Changes from more highly isolated transactions cannot be overwritten by the current connection.
ilReadUncommitted Uncommitted changes in other transactions are visible.
ilBrowse Uncommitted changes in other transactions are visible.
ilCursorStability Changes from other transactions only visible after being committed.
ilReadCommitted Changes from other transactions only visible after being committed.
ilRepeatableRead Changes made in other transactions not visible, but requerying can retrieve new recordsets.
ilSerializable Transactions conducted in isolation from other transactions.
ilIsolated Transactions conducted in isolation from other transactions.
COMMENTS
NOTE: The special alpha constants above (sqlOpenConnection through sqlGetFieldCount) are defined in COMPILERCONSTANTS.TXT.
NOTE1: You can have up to 10 Different Query's to the Same database/Connection open Starting at 0 Which is also the default and not required.
NOTE2: This function available in version 7.2 build 4 and above.
NOTE3: If you want to use multiple recordsets, you've got to use the third
parameter in the SQL command:
SQL(sqlQuery, sqlString, recordsetNumber) !!!
Example:
msg SQL(sqlQuery, "SELECT * FROM table1", 0) //First recordset
msg SQL(sqlQuery, "SELECT * FROM table2", 1) //2nd recordset
msg SQL(sqlGetRecordCount, 0)
msg SQL(sqlGetRecordCount, 1)
msg SQL(sqlCloseRecordSet, 0)
msg SQL(sqlCloseRecordSet, 1)
If you use SQL(sqlQuery,... ) without the third parameter Tas will use
the recordsetNumber=0 as default and a 2nd recordset will overwrite
the first.
EXAMPLES
See SQL folder example included in TAS Professional Powered by CAS.
Ongoing
We are looking into the possibility of a SQL Statement Builder to be added to the IDE. Not sure when this feature will be available.
Some Sources for Connection strings.