Advanced database settings for Datacap Server

Datacap can use custom connection strings from which it can target any database type that has a supported database provider.

Connection strings

The supported database providers for which you can use these connection strings are Microsoft Access, DB2®, Microsoft SQL Server, and Oracle. The old Datacap format connection strings are still supported to provide compatibility with an earlier version to your existing database connections.

The following examples describe OLEDB connection strings for each of the supported database providers:

DB2 using Standard Authentication

"Provider=IBMDADB2;Data Source= database alias; UID=******; PWD=******; 
CurrentSchema=db2admin;"

DB2 using Windows Authentication

N/A

Microsoft Access using Standard or Windows Authentication

"Provider=microsoft.jet.oledb.4.0;data source=C:\Datacap\MyApp\MyAppadm.mdb;
persist security info=false;"

SQL Server using Standard Authentication

"Provider=sqloledb;data source=myServerAddress;Initial Catalog= myDataBase;
User Id=myUsername;Password=myPassword;"

SQL Server using Windows Authentication

"Provider=sqloledb;data source=myServerAddress;Initial Catalog= myDataBase;
Integrated Security=SSPI;"

Oracle using Standard Authentication

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;
Password=myPassword;

Oracle using Windows Authentication

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;

Registry settings

Special cases in which database might act differently than usual are controlled by a set of settings in the registry. Datacap uses these settings when database providers must use alternative logic implementations. Otherwise, Datacap uses the default behavior. There are special settings for different database behavior that is not default.

The following settings are in the InterThread section of TMS registry profile at HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Datacap\TMS\InterThread.

TO_DATE

Formats the date and time field values in TO_DATE format in SQL statements for the providers. This example contains the default TO_DATE setting for Oracle.

ORACLE;ODBCORACLE;MSORACLE;ODBCMSORACLE;OraOLEDB.Oracle;MSDAORA;IBMDADB2;DB2OLEDB

ROWNUM

Use this ROWNUM syntax instead of SELECT TOP in SQL statements for these providers. This example contains the default ROWNUM setting for Oracle.

ORACLE;ODBCORACLE;MSORACLE;ODBCMSORACLE;OraOLEDB.Oracle;MSDAORA;IBMDADB2;DB2OLEDB

#DATETIME#

Use date and time field values that are wrapped in a # sign-in SQL statements for these providers. Some of the databases want the date and time field values in # signs. This example contains the default #DATETIME# setting for MS Access.

MSACCESS;ODBCMSACCESS;Microsoft.Jet.OLEDB.4.0;Microsoft.ACE.OLEDB.12.0

"FIELD NAME"

Use field names wrapped in quotes in SQL statements for these providers. Some database types need quoted fields in SQL statements. This example contains the default "FIELD NAME" setting for Oracle.

ORACLE;ODBCORACLE;MSORACLE;ODBCMSORACLE;OraOLEDB.Oracle;MSDAORA;IBMDADB2;DB2OLEDB

"AUDIT"

Use audit table references wrapped in quotes in SQL statements for these providers. In some databases Audit is a system/internal/key word, so you must wrap Audit in quotes to differentiate these databases. This example contains the default "AUDIT" setting for Oracle.

ORACLE;ODBCORACLE;MSORACLE;ODBCMSORACLE;OraOLEDB.Oracle;MSDAORA

MOVE_BY_ONE

When you must move the cursor on a recordset more than one row, you do it by moving by one row at the time in a loop. Some providers might have issues trying to move several rows at once, so you must do this loop procedure for them. This example contains the default MOVE_BY_ONE setting for Oracle.

ORACLE;ODBCORACLE;MSORACLE;ODBCMSORACLE;OraOLEDB.Oracle;MSDAORA

SELECT_COUNT

Use the SQL syntax for SELECT COUNT instead of using ADO API GetRecordCount because GetRecordCount does not work for some databases. This example contains the default SELECT_COUNT setting for Oracle.

ORACLE;ODBCORACLE;MSORACLE;ODBCMSORACLE;OraOLEDB.Oracle;MSDAORA

RECORD_FORWARD_ONLY

Open ADO recordsets by using the forward only setting. The default setting is static bidirectional, but some databases have issues with static bidirectional recordsets. This example contains the default RECORD_FORWARD_ONLY setting for Oracle.

ORACLE;ODBCORACLE;MSORACLE;ODBCMSORACLE;OraOLEDB.Oracle;MSDAORA