Xs2Ado - FAQ

Which providers are available for Ado?

OLE-DB providers are offered by various sources:

Microsoft	Other vendors
Microsoft Microsoft SQL Server Oracle Microsoft Active Directory Microsoft Index Server Microsoft SNA Server and more	DB2/400 AS/400 Oracle  Sybase Informix   OpenIngres Rdb Ingres,  Red Brick Non-Stop SQL/MP Non-Stop SQL/MX CISAM  RMS DBMS	MUMPS Jasmine Adabas Enscribe Flat file data sources Text file data sources Any MAPI-based data store  ObjectStore databases xBASE (FoxPro, Clipper and dBase) Microsoft Exchange Lotus Notes Lotus cc:Mail and more

How must I setup my connect string to connect to a certain provider?

The Vo2Ado help file contains a list of samples, that you can find from the Contents page. 

Some samples:

ODBC using DSN 

    oConn:Open("DSN=AdvWorks;UID=Admin;PWD=;",NIL,NIL,NIL) 

OLE DB Provider for Microsoft Jet (Access)

    oConn:Open("Provider=Microsoft.Jet.OLEDB.4.0;" + ; 
        "Data Source=.mdb;User Id=admin;Password=;",NIL,NIL,NIL) 

OLE DB Provider for SQL Server 

    oConn:Open("Provider=sqloledb;" + ; 
    "Data Source=robert;Initial Catalog=pubs;User Id=sa;Password=;",NIL,NIL,NIL) 

Another very good source of information is the ADO FAQ website that you can find at: http://www.able-consulting.com/ADO_Faq.htm

How can I improve the performance of my Ado application?

There is an article on "Improve the Performance of Your MDAC Application" by Suresh Kannan that can be found on the Ms Data Access website. This article contains a lot of performance tips.

• Bind columns to field objects when looping through the records
• Use cursor-based updating only if needed
• Use a stored procedure with output parameters instead of singleton selects 
• If you have to use a cursor, use the Collect methods for singleton selects 
• Query only for the data you need 
• Choose your cursor location, cursor type, and lock type with care 
• Tune your Recordset CacheSizeproperty 
• Release your ADO objects ASAP 
• Describe command parameters yourself 
• Dissociate your client cursor from the connection for R/O and long use scenarios 
• Use adExecuteNoRecords option for non-row returning commands 
• Use Connection:Execute for single execution

A detailed explanation of these tips can be found on the Ms website.

How do I programmatically create an ODBC connection (for example to access my data in ReportPro)?

The following code contains two functions. One function to install an ODBC driver (in this case the SqlServer driver) and One function to install an ODBC datasource (DSN).

// This writes the connection info
FUNCTION WriteConnToRegistry(cDSN AS STRING, cServer AS STRING, cDatabase AS STRING)
LOCAL pszFile AS PSZ
LOCAL lOk AS LOGIC
LOCAL pszDSN AS PSZ
pszDSN  := String2Psz(cDSN)
pszFile := String2Psz("ODBC.INI")
SQLWriteDSNToIni(String2Psz(cDSN), String2Psz("SQL Server"))
lOk := SQLWritePrivateProfileString(pszDSN, String2Psz("Server"), String2Psz(cServer), ;
   pszFile)
lOk := lOk .and. SQLWritePrivateProfileString(pszDSN, String2Psz("Database"), ;
   String2Psz(cDatabase), pszFile)
lOk := lOk .and. SQLWritePrivateProfileString(pszDSN, String2Psz("AutoTranslate"), ;
   String2Psz("Yes"), pszFile)
RETURN lOk

// this makes sure the driver is there and installs it
FUNCTION InstallSQLServer()
LOCAL cDriverInfo               AS STRING
LOCAL cDLL                      AS STRING
LOCAL pszPath                   AS PSZ
LOCAL cSystemDir                AS STRING
LOCAL lLen                      AS LONG
LOCAL lOk                       AS LOGIC
cSystemDir              := SystemDirectory()
cDLL := AddSlash(cSystemDir)+"SQLSRV32.DLL"
IF ! File(cDLL)        
   Warning("Cannot find SQL Server ODBC Driver: "+cDLL)        
   lOk := TRUE
ELSE   
   cDriverInfo := "SQL Server"+_chr(0)+ ;                                
   "Driver=SQLSRV32.DLL"+_chr(0)+ ;                                
   "Setup=SQLSRV32.DLL" +_chr(0)+ ;                                
   "APILevel=1"+_chr(0)+ ;                                
   "ConnectFunctions=YYN"+_chr(0)+ ;                                       
   "SQLLevel=1" + _chr(0) + _chr(0)   
   pszPath := MemAlloc(_MAX_PATH)   
   lOk := SQLInstallDriver(NULL, String2Psz(cDriverInfo), pszPath, _MAX_PATH,lLen)   
   MemFree(pszPath)   
   IF ! lOk        
      Warning("Cannot Install driver ")   
   ENDIF
ENDIF
RETURN lOk 
       
// ODBC API

_DLL FUNCTION SQLInstallDriver(pszInfFile AS PSZ, pszDriver AS PSZ, pszPath AS PSZ, ;        
   liPathMax AS LONG, liPathOut AS LONG) ;        
   AS LOGIC PASCAL:odbccp32.SQLInstallDriver
_DLL FUNCTION SQLInstallDriverManager(pszPath AS PSZ, liPathMax AS LONG, liPathOut AS LONG) ;        
   AS LOGIC PASCAL:odbccp32.SQLInstallDriverManager
_DLL FUNCTION SQLRemoveDSNFromIni(pszDSN AS PSZ) AS ;
   LOGIC PASCAL:odbccp32.SQLRemoveDSNFromIni
_DLL FUNCTION SQLWriteDSNToIni(pszDSN AS PSZ, pszDriver AS PSZ) AS ;
   LOGIC PASCAL:odbccp32.SQLWriteDSNToIni
_DLL FUNCTION SQLWritePrivateProfileString(pszSecrion AS PSZ, pszEntry AS PSZ, ;                
   pszValue AS PSZ, pszFile AS PSZ) AS LOGIC PASCAL:odbccp32.SQLWritePrivateProfileString
_DLL FUNCTION SQLConfigDataSource (hWndParent AS PTR, liRequest AS LONG, ;   
   pszDriver AS PSZ, pdzAttributes AS STRING) ;        
   AS LOGIC PASCAL:odbccp32.SQLConfigDataSource

How can I use AdoRecordSet:Find() with a condition that has more than 1 column.

Unfortunately recordset:Find in Ado is limited to a single expression. RecordSet:Filter on the other hand allows more complex filters.
If you don't want to set a filter on your recordset you can try to clone
the recordset, set a filter on the clone and read the bookmark from the
clone.Something alonge the lines of:

oRs2 := oRs:Clone(adLockReadOnly)
oRs2:Filter := "ModelKey=" + ntrim(nModelKey) + " and subtype like '" + cSubType + "'"
oRs2:MoveFirst()
oRs1:BookMark := oRs2:BookMark
oRs2:Destroy()

Can I use Xs2Ado to access Access databases?

Yes you can use Xs2Ado to access Access databases. You need to use the Microsoft.Jet OLEDB Provider that you can download (and distribute) for free from the Microsoft Website.

Then you need a connectionstring that looks like:

  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myapp\mydata.mdb;"