Solutions Company Statit Training Home
 



Statit & ODBC: Connection String

Abstract: General information about the ODBC mechanism used by Statit.

Products: Statit e-QC, Statit e-Server, Statit Custom QC, Statit Express QC

dbconnect

The Statit command dbconnect and the Statit function dbconnect() are used to make connections to the database. The typical method of making a connection is to use a DSN or Data Source Name. The DSN is set up either through the Control Panel or through the DSN manager included with Statit. It is set up as a System DSN.

With a DSN it is simple to make a connection:

dbconnect /DSN="StatitDemoDb"
dbconnect /DSN="StatitDemoDb" /UID="guym" /PWD="tiger"
call dbconnect("StatitDemoDB","","guym","tiger","",0)

The difference between the dbconnect proc and the dbconnect() function is that the function is silent. The proc will return an error if the connection fails. The error information will include the connect string which will often include the Userid and Password.

In most cases, the DSN can be set up to require no more then the DSN and perhaps the Userid and Password. Listed here are some of the options for some of the ODBC connection strings you may use. Not all options need to be specified, so you need only use those options which are required to make your ODBC connection.

For dbconnect command:

/DSN = "data source name"
/SRVR= "server"
/DB= "database"
/DBQ= location
/UID= "user ID"
/PWD= "password"
/Other= "attributes"
/DLG= [ yes | no | ifneeded ]

For dbconnect() function:

$return = dbconnect(%dbDSN, %dbDB, %dbUID, %dbPWD, %dbOTHER, $dbDLG)

Notice that the function has a return value that indicate success (0) or an enumeration of the type of error.

dbdisconnect

It is good practice to disconnect from the database once you have retrieved the data you require. Particularly for limited connection databases such as Microsoft Access, this will free up database resources for other users.

Hidden Connect Strings

It is sometimes desirable to hide the connection string to a database. This can be accomplished by writing a function or proc to make the connection. For example:

function statitlog()
%dbdsn="StatitDemoDB"
%dbUID = "guym"
%dbPWD = "fred"
$status = dbconnect(%dbDSN,"", %dbUID, %dbPWD,"", 0)
%db_connect= ""
if $status = 0 then
return (1)
else
return (0)
endif

Place the above code in a text file, called something like gmlog.fcn. Compile it from the Custom QC command line with:

compile gmlog.fcn gmlog.stf

Place the gmlog.stf in the appropriate fcns folder. For Statit Custom QC, Program Files/Statit Custom QC/fcns. For Statit e-QC and Statit e-Server, Program Files/Statit e-Server/fcns.

DSN-less connections

It is sometimes necessary to make a connection when a DSN is not available. Below are some sample connections:

Access:

dbconnect /dbq="D:\Program Files\Statit e-Server\dat\StatitDemoDB.mdb" ;
/OTHER = "DRIVER=Microsoft Access Driver (*.mdb)"

SQLServer:

dbconnect /other="Driver={SQL Server};Server=MSXP300;Database=Northwind" /Uid="user1" _ /Pwd="user1" /dlg=no

Oracle:

dbconnect /other="DRIVER={Microsoft ODBC for Oracle}`;`SERVER=dataware.abc.com" _ /UID="QAREPORTS" /PWD="REPORT4U" /DLG=no

Data Source ODBC Drivers

Microsoft provides ODBC drivers for Access and SQL Server. Statit does not ship ODBC drivers with our software. Other database vendors provide drivers for their products. These will require an installation on the client machine. For example, Oracle requires the installation of a client kit on each machine that will access an Oracle db. For Statit e-QC, this installation would take place on the e-QC server, enabling the e-QC application to query the data. Statit Custom QC, Statit e-Server and Statit Express will require an client installation on each machine that runs these applications.

In addition there are third party vendors who provide ODBC drivers for the major databases. A GOOGLE on ODBC drivers will provide a large number of these connections.