Navigation:  Reference > Functions >


Print this Topic Previous pageReturn to chapter overviewNext page



Use this function to Read and Write SQL Data From your TAS Profesional Application.



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




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.



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)








SqlGetFieldname - (optional) number of the Query to use (note 1)

sqlCloserecordset ,





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)



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




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


Call CommitTrans to save any changes made during the current transaction and to end the transaction.


Call RollbackTrans to cancel any changes made during the current transaction and to end the transaction.


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.


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.



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) !!!



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



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.



// 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



                    // 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



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


There are hundreds maybe thousands of sources for sql on the internet. We have only listed a few here.




Page url: