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