Scripting - How to access an external Database

Issue

How to access an external database by script?

Solution

External databases can be accessed via ODBC using MS-ADO. Below is a sample of how to do this in KTM script. However, keep in mind that this is merely invoking the ADO COM object in WinWrap script. Like any other external COM library, the specifics of how it works and whether it works properly depend on the library itself. Full explaination or troubleshooting of ADO or ODBC are far beyond the scope of this sample.

First you need to add a reference to MS-ADO to the script for every class the database is being used with:

• Open the script window of the appropriate class.
• Choose "Edit" from the main menu and then "References."
• Add "Microsoft ActiveX Data Objects 2.8 Library (2.8)" (%CommonProgramFiles%\system\ado\msado15.dll), or something similar.

Add the following code to the project script:

Dim m_DatabaseConnection As ADODB.Connection
' example for accessing a named datasource
Const m_ConnectionString1 As String = "DSN=TEST;UID=test;PWD=test"
' example for using a ODBC driver directly (MySQL)
'Const m_ConnectionString2 As String = "Provider=MSDASQL;Driver={MySQL ODBC 3.51 Driver};Server=localhost;UID=test;PWD=test;database=test"
' example for using a ODBC driver directly (MS Access)
'Const m_ConnectionString3 as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Filename.mdb;User Id=test;Password=test"
Public Function GetDatabaseConnection() As ADODB.Connection
   If Not m_DatabaseConnection Is Nothing Then
      Set GetDatabaseConnection = m_DatabaseConnection
      Exit Function
   End If
   On Error GoTo ConnectionFailed
   Set m_DatabaseConnection = New ADODB.Connection
   m_DatabaseConnection.ConnectionString = m_ConnectionString1
   m_DatabaseConnection.Open
   Set GetDatabaseConnection = m_DatabaseConnection
   Exit Function
ConnectionFailed:
   Set m_DatabaseConnection = Nothing
   Set GetDatabaseConnection = Nothing
End Function

Add the following code to a class script:

Private Sub Document_AfterExtract(pXDoc As CASCADELib.CscXDocument)
   Dim DatabaseConnection As ADODB.Connection
   Dim SQL As String
   Dim Cmd As ADODB.Command
   Dim Param As ADODB.Parameter
   Dim Recordset As ADODB.Recordset
   Dim i As Integer
   Set DatabaseConnection = GetDatabaseConnection
   If DatabaseConnection Is Nothing Then
      ' error
      Exit Sub
   End If
   ' insert simple data
   SQL = "INSERT INTO test (TimeStamp) VALUES('2006-01-01 10:00:00')"
   DatabaseConnection.Execute(SQL)
   ' insert parameterized data
   SQL = "INSERT INTO test (TimeStamp) VALUES(?)"
   Set Cmd = New ADODB.Command
   Set Cmd.ActiveConnection = DatabaseConnection
   Cmd.CommandText = SQL
   Set Param = Cmd.CreateParameter
   Param.Type = adDBTimeStamp
   Param.Value = Now
   Cmd.Parameters.Append Param
   Cmd.Execute
   
   ' retrieve data
   SQL = "SELECT TimeStamp FROM test"
   Set Recordset = DatabaseConnection.Execute(SQL)
   Dim s As String
   While Not Recordset.EOF
      s = Recordset(0) ' 0-based -> 1st column
      MsgBox "Record #" + CStr(i) + ": " + s
      Recordset.MoveNext
      i=i +1
   Wend
End Sub

Level of Complexity 

High

Applies to  

References

Add any references to other internal or external articles