CimDbap.SubmitTableDSNRequest (method)

Gets CIMPLICITY Database Logger ODBC DSN connection information for a CIMPLICITY Logging table.

Syntax:

object.SubmitTableDSNRequest project, table_id, pResponse, timeout

Parameters:

project As String - String containing the CIMPLICITY project.

table_id As String - String containing the CIMPLICITY Logging table.

pResponse As CimDbapTableDSNResponse - A CimDbapTableDSNResponse object, which will be populated with the DSN configuration data.

Optional timeout As long - An optional value defining how many milliseconds to attempt to make a connection with the CIMPLICITY project and retrieve the data. If the specified timeout elapses before successfully connecting and retrieving the data, then an error will be returned indicating that the timeout period has elapsed. The default value is to wait indefinitely, however it is recommended that a timeout value be used to avoid indefinitely hanging your application.

Description:

Use this method to retrieve the ODBC DSN configuration information required to make a connection to the database where the CIMPLICITY Logging table exists.

Example:

'Declaration of functions prototyped in sqlinst.h

Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwndParent&, ByVal fRequest&, ByVal lpszDriver$,
ByVal lpszAttributes$) As Boolean

Declare Function SQLInstallerError Lib "odbccp32.dll" (ByVal
iError&, ByRef fErrorCode&, ByVal ErrorMsg$, ByVal
cbErrorMsgMax&, ByRef cbErrorMsg&) As Long

'Constants Defined in sqlinst.h

Const ODBC_ADD_DSN& = 1 ' Add a user data
source

Const ODBC_CONFIG_DSN& = 2 ' Configure (edit) a user data
source

Const ODBC_REMOVE_DSN& = 3 ' Remove a user data
source

Const ODBC_ADD_SYS_DSN& = 4 ' add a system DSN

Const ODBC_CONFIG_SYS_DSN& = 5 ' Configure a system
DSN

Const ODBC_REMOVE_SYS_DSN& = 6 ' remove a system
DSN

Const ODBC_REMOVE_DEFAULT_DSN& = 7 ' remove the Default
DSN

'Constants defined in sql.h
Const SQL_SUCCESS& = 0
Const SQL_SUCCESS_WITH_INFO& = 1
Const SQL_NO_DATA& = 100
Const SQL_ERROR& = -1
Const SQL_MAX_MESSAGE_LENGTH& = 512

Sub Main()

Dim oCimDbap As Object
Dim oCimDbapTableDSNResponse As Object
Dim haveDSN As Boolean
Dim Attribs As String

Set oCimDbap = CreateObject("DbapDisp.CimDbap")

Set oCimDbapTableDSNResponse =
CreateObject("DbapDisp.CimDbapTableDSNResponse")

'Submit request. Timeout of 30 seconds

oCimDbap.SubmitTableDSNRequest "VCRNWM", "DATA_LOG",
oCimDbapTableDSNResponse, 30000

If oCimDbapTableDSNResponse.TableType = DbapInvalidTable
Then

MsgBox "Invalid CIMPLICITY table."
Exit Sub
End If

'Add temporary DSN NAME to the list of attributes

Attribs = "DSN=MYDSN;" +
oCimDbapTableDSNResponse.DSNAttributes

'Attributes are delimited by semicolon. Replace each with
Null byte

Attribs = Replace(Attribs, ";", Chr$(0))

'Attempt to create the ODBC Datasource

haveDSN = SQLConfigDataSource(0, ODBC_ADD_DSN,
oCimDbapTableDSNResponse.ODBCDriverName, Attribs)

If haveDSN Then

'Success. Connect and fetch some data, then delete the
temporary ODBC Datasource

Dim a() As Variant
Dim connect$, sql$

connect$ = "DSN=MYDSN;UID=" &
oCimDbapTableDSNResponse.UserId & ";PWD=" &
oCimDbapTableDSNResponse.Password

sql$ = "Select TOP 100 * from " &
oCimDbapTableDSNResponse.TableName

l& = SQLRequest(connect$, sql$, a)
MsgBox "Returned " & Ubound(a) + 1 & " rows."

haveDSN = SQLConfigDataSource(0, ODBC_REMOVE_DSN,
oCimDbapTableDSNResponse.ODBCDriverName, Attribs)

Else
'Failure. Show error.
Dim ErrorCode&, cbErrorMsg&, ret&

'Allocate a string with a large enough buffer to hold error
message

Dim ErrorMsg As String * SQL_MAX_MESSAGE_LENGTH

ret = SQLInstallerError(1, ErrorCode, ErrorMsg,
SQL_MAX_MESSAGE_LENGTH-1, cbErrorMsg)

MsgBox "Failed to configure DSN. ErrorCode=" & ErrorCode
& " ErrorMsg: " & ErrorMsg

End If

End Sub

'Helper function to replace one delimiter with
another

Public Function Replace$(Haystack$, Needle$,
Replacement$)

Dim s$
Dim i&
s = Haystack
While InStr(s, Needle) > 0
i = InStr(s, Needle)
If i = 1 Then
s = Replacement & Mid$(s, i + Len(Needle))
Else

s = Left$(s, i - 1) & Replacement & Mid$(s, i +
Len(Needle))

End If
Wend
Replace = s
End Function