|
SQL() |
  
|
|
SQL() |
|
SQL(1,2,3)
PURPOSE
Use this function to Read and Write SQL Data From your TAS Profesional 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.
sqlGetTableNames -A - Returns the Table 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 record sets, 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 record set will overwrite
the first.
Example 2:
borderid = sql(sqlgetdata,'OrderID',1)
prodcode = sql(sqlgetdata,'ProductID',1)
prodprice = sql(sqlgetdata,'UnitPrice',1)
//msg "Click for Next"
dummy_l=sql(sqlmovenext,sqlmoveNext,1) If you use more then one table this will work..
dummy_l=sql(sqlmovenext,1) This will not work same for other moving functions...
Pervasive SQL Notes
because Btrieve/Pervasive is one of the two native databases in TAS Professional As we find interesting things or problems we will try to document them here.
Using a date restriction in a WHERE clause does not return records even though the correct date seems to exist in table.
Problem Description:
Using a date restriction in a WHERE clause does not return records even though the correct date seems to exist in table.
Problem Environment:Pervasive.SQL V8
Pervasive.SQL 2000i
Pervasive.SQL ver 9 and 10
Pervasive OLEDB
ADO
Cause of this problem:
The problem may be that an incorrect date format is being used. Pervasive.SQL uses the date format based on ODBC 2.5 standards. The date format is YYYY-MM-DD.
Here is a function we used to correct the problem.
define sqldate type d size 8
define sqldateA type a size 10
func sql_date sqldate
sqldate1 = dtos(sqldate)
// year month day
sqldateA = MID(sqldate1,1,4) + '-' * MID(sqldate1,5,2)*'-' * MID(sqldate1,7,2)
//msg sqldateA
ret sqldateA
The above function will covert the date to YYYY-MM-DD format for use in your sql statement dealing with dates.
EXAMPLES
// Sample sql select statement
SQL_str = "Select * from BKARHINV_B " +\
" where BKAR_INV_INVCD <> 'V' "+\
" and '" *bkar.custcode* "' = bkar_inv_cuscod " +\
" and BKAR_INV_INVDTE >= '" * sql_date(fromdate) * "' "+\
" and BKAR_INV_INVDTE <= '" * sql_date(thrudate) * "' "+\
" and bkar_inv_slsp >= '" * frep *"' " +\
" and bkar_inv_slsp <= '" * trep *"' " +\
" Order By bkar_inv.cuscod , BKAR_INV_INVDTE "
SQL(sqlQuery, sql_Str, recordsetNumber) // This will query the above select Statment
Sql_find_first 0
while dummy_l = .t.
//do what ever you need to here.
Sql_Find_Next 0
endw
ret
// The sql_date() is documented above
See also SQL folder example included in TAS Professional Powered by CAS.
there are two examples there SQLTEST and SQLNORTHWINDWS
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.
Sources for Connection strings.
Sources for SQL Commands
www.1keydata.com/sql/sql-commands.html
There are hundreds maybe thousands of sources for sql on the internet. We have only listed a few here.