cObject
---Array
------cCLIHandler
---------cDB2Handler
---------cMSSQLHandler
---------cODBCHandler
The cCLIHandler class implements functionality common for the CLI based Connectivity Kits (SQL Server, DB2 and ODBC), the cMSSQLHandler class implements functionality specific for SQL Server.
When creating an object of the cCLIHandler class you must set the psDriverID property.
In order for the class to work with the ODBC Connectivity Kit the property should be set to "ODBC_DRV".
Get Create U_cCLIHandler To hoCliHandler Set psDriverID Of hoCLIHandler To "ODBC_DRV"
In order for the class to work with the Connectivity Kit for SQL Server, the property should be set to "MSSQLDRV".
Get Create U_cCLIHandler To hoCliHandler Set psDriverID Of hoCLIHandler To "MSSQLDRV"
In order for the class to work with the Connectivity Kit for DB2, the property should be set to "DB2_DRV".
Get Create U_cCLIHandler To hoCliHandler Set psDriverID Of hoCLIHandler To "DB2_DRV"
If you use the cMSSQLHandler, cDB2Handler or cODBCHandler classes (subclasses of this class) instead, this property is already set for you.
As of DataFlex 19.0, Connection Ids are handled via Managed Connections, making Connection ID-related method in the CLI Handler class obsolete. All of these techniques still work, but they have been replaced with better techniques that are also managed via the Studio and other DataFlex tools. See the cConnection class and Managed Connections for more.
There are a number of functions available that return the current Connectivity Kit revision.
Function CKRevision Returns String Function CKMajorRevision Returns Integer Function CKMinorRevision Returns Integer Function CKReleaseRevision Returns Integer Function CKBuildRevision Returns Integer Function IsMinimalRevision Integer iMajor Integer iMinor Integer iRelease Integer iBuild Returns Integer
You can use these functions to display the current revision number of the Connectivity Kit.
Procedure ShowCKRevision String sDRiverID
Handle hoCLIHandler
Get Create U_cCLIHandler To hoCLIHandler
Set psDriverID To sDRiverID
Showln (CKRevision(hoCLIHandler))
Send Destroy Of hoCLIHandler
End_Procedure // ShowCKRevision
You can also check the current Connectivity Kit revision and act upon that information. If, for example, certain functionality only works for version 3.0.0.48 or higher of the MS SQL Connectivity Kit, one could program:
Procedure DoVersionDependentThing
Handle hoCLIHandler
Get Create U_cCLIHandler To hoCLIHandler
Set psDriverID Of hoCLIHandler To "MSSQLDRV"
If (IsMinimalRevision(hoCLIHandler, 3, 0, 0, 48)) ;
Send DoMyThing
Else ;
Send Stop_Box "Function not supported by this CK"
End_Procedure // DoVersionDependentThing
There are a number of functions that return registration information.
Function RegistrationName Returns String Function SerialNumber Returns Integer Function MaxUsers Returns Integer
It is possible to dump the current status of the Connectivity Kit. This is intended as a debugging aid. In some cases the point where a problem occurs is known. In such cases one can call the DumpStatus procedure.
Procedure DumpStatus String sFileName
The DumpStatus procedure expects a disk file name as parameter. If the disk file already exists, the current status will be appended to that disk file.
Procedure ShowStatus
Handle hoCLI
Get Create U_cCLIHandler To hoCLI
Set psDriverID Of hoCLI To MSSQLDRV_ID
Send DumpStatus Of hoCLI "c:\status\mssqldump.txt"
Send Destroy Of hoCLI
End_Procedure // ShowStatus
A number of attributes can be set to a value from an enumeration list. Functions are available to convert the enumeration list values to strings and vice-versa.
Function TextToRIMValue String sText Returns Integer Function RIMValueToText Integer iAttrValue Returns String Function TextToROValue String sText Returns Integer Function ROValueToText Integer iAttrValue Returns String
To show the current value of the DF_FIELD_READ_ONLY attribute one could program something like:
Procedure ShowRO Handle hoCLIHandler ;
Handle hTable ;
Handle hColumn
Integer eReadOnly
Get_Attribute DF_FIELD_READ_ONLY hTable hColumn ;
To eReadOnly
Showln "Read only attribute is set to " ;
(ROValueToText(hoCLIHandler, eReadOnly))
End_Procedure // ShowRO
Error texts generated by SQL Server and other client/server databases are generally larger than those generated by the embedded database. In character mode environments, it happens that the text is larger than the supported maximum error text length. In those cases, you can get the complete error text by using the LastDriverError function.
Function LastDriverError Returns String
To show the last error text, one would program:
Procedure ShowLastSQLErrorText
Handle hoCLIHandler
String sLastError
Get Create U_cCLIHandler To hoCLIHandler
If (hoCLIHandler > 0) Begin
Set psDriverID Of hoCLIHandler To MSSQLDRV_ID
Get LastDriverError Of hoCLIHandler To sLastError
End
Send Destroy Of hoCLIHandler
Showln "ERROR: " sLastError
End_Procedure // ShowLastSQLErrorText
The driver configuration file is read once in a session. When the Connectivity Kit initializes, it reads the configuration file mssqldrv.int. You can force the configuration file to be read by using the ReadConfiguration procedure.
Procedure ReadConfiguration
When this procedure is called all driver level settings will be set to their default value, then the driver configuration file is read.
For more information on driver level attributes, see Appendix B - Configuration file MSSQLDRV.INT
Several functions are available that enumerate table and column information. They can be used to show all tables that exist in a database regardless if there is a connection to these tables.
Function EnumerateTables String sLogin Returns Integer Function TableName Integer iIndex Returns String Function SchemaName Integer iIndex Returns String Function TableType Integer iIndex Returns String Function TableComment Integer iIndex Returns String Function EnumerateColumns String sLogin String sTableName Returns Integer Function ColumnName Integer iIndex Returns String
If we want to show all tables in a Microsoft SQL Server database and for every table all column names, we would program:
Procedure ShowAllTablesAndColumns String sDriver String sLogin
Handle hoCLIHandler
Integer iNumTables
Integer iNumColumns
Integer iTableCount
Integer iColumnCount
Get Create U_cCLIHandler To hoCLIhandler
If (hoCLIHandler > 0) Begin
Set psDriverID Of hoCLIHandler To sDriver
Get EnumerateTables Of hoCLIHandler sLogin To iNumTables
For iTableCount From 1 To iNumTables
Show (TableName(hoCLIHandler, iTableCount)) "; "
Show (SchemaName(hoCLIHandler, iTableCount)) "; "
Show (TableType(hoCLIHandler, iTableCount)) "; "
Showln (TableComment(hoCLIHandler, iTableCount))
Get EnumerateColumns Of hoCLIHandler sLogin ;
(TableName(hoCLIHandler, iTableCount)) To iNumColumns
For iColumnCount From 1 To iNumColumns
Showln " " (ColumnName(hoCLIHandler, iColumnCount))
Loop
Showln
Loop
Send Destroy Of hoCLIHandler
End
End_Procedure // ShowAllTablesAndColumns
If you want to use the above procedure for database D on server X with user account Y and password Z it would be called in the following way:
Send ShowAllTablesAndColumns MSSQLDRV_ID ;
"DRIVER={SQL Server};SERVER=X;UID=Y;PWD=Z;DATABASE=D"
See Also