Create DSN from Client to Prevasive Server.

[zemp]

I have used DTO and VB to create a database called 'TEST' on a pervasive server form a pervasive client machine.

Using DTO I was able to create a DSN for TEST located on the server. I also need a DSN pointing to TEST on the client machine. How can I create a pervasive system DSN pointing to a database ona server?

I have tried...

sDriver = "Pervasive ODBC Engine Interface"
sAttributes = "Server=MySerer" & Chr$(0)
sAttributes = sAttributes & "DESCRIPTION=test DSN" & Chr$(0)
sAttributes = sAttributes & "DSN=MyDSN" & Chr$(0)
sAttributes = sAttributes & "OpenMode='NORMAL'" & Chr$(0)
sAttributes = sAttributes & "IS_ENGINE_DSN=False" & Chr$(0)
sAttributes = sAttributes & "IS_SYSTEM_DSN=True" & Chr$(0)
sAttributes = sAttributes & "DATABASE=TEST" & Chr$(0)
sAttributes = sAttributes & "UID=" & User & Chr$(0)
sAttributes = sAttributes & "PWD=" & Password & Chr$(0)
retValue = SQLConfigDataSource(0&, ODBC_ADD_SYS_DSN, sDriver, sAttributes)

and using DTO, but no success so far. Any help is appriciated.

Thanks and Good Luck!

zemp

 

[zemp]

Well, I found the solution. Place the code below in a module and then you can call the functions from anywhere.

Public Enum DSNType
CreateUserDSN& = 1 ' Add User data source
ModifyUserDSN& = 2 ' Configure existing DSN
DeleteUserDSN& = 3 'Delete data source
'ODBC Version 2.5 & higher
CreateSystemDSN& = 4 'Add system data source
ModifySystemDSN& = 5 'Modify an existing system data source
DeleteSystemDSN& = 6 'Remove an existing system data source
'ODBC Version 3.0
'DeleteDefaultDSN& = 7 ' Remove the default data source. Experienced users only!
End Enum

Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwndParent As Long, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long

Private Declare Function SQLWriteFileDSN Lib "odbccp32.dll" _
(ByVal lpszFileName As String, ByVal lpszAppName As String, _
ByVal lpszKeyName As String, ByVal lpszString As String) As Long

Public gblUser As String
Public gblPassword As String

Public Function CreateCliDSN(dDSNType As DSNType, DSNName As String, ServerName As String, _
ServerDSN As String, TransportHint As String, _
Optional TCPPort As String, Optional ArrayFetchOn As Integer, _
Optional ArrayBufferSize As Integer, _
Optional UID As String, Optional PWD As String) As Boolean
On Error GoTo errHandler
Dim strDriver As String
Dim lRetVal As Long
Dim sAttrib As String
If (dDSNType < CreateUserDSN) Or (dDSNType > DeleteSystemDSN) Then
CreateCliDSN = False
Exit Function
End If

sAttrib = &quot;DSN=&quot; & DSNName & vbNullChar
If TCPPort = &quot;&quot; Then
sAttrib = sAttrib & &quot;ServerName=&quot; & ServerName & vbNullChar
sAttrib = sAttrib & &quot;TCPPort=1583&quot; & vbNullChar
Else
sAttrib = sAttrib & &quot;ServerName=&quot; & ServerName & &quot;.&quot; & TCPPort & vbNullChar
End If
sAttrib = sAttrib & &quot;ServerDSN=&quot; & ServerDSN & vbNullChar
sAttrib = sAttrib & &quot;TransportHint=&quot; & TransportHint & vbNullChar
If ArrayFetchOn > 0 Then
sAttrib = sAttrib & &quot;ArrayFetchOn=&quot; & ArrayFecthOn & vbNullChar
sAttrib = sAttrib & &quot;ArrayBufferSize=&quot; & ArrayBufferSize & vbNullChar
Else
sAttrib = sAttrib & &quot;ArrayFetchOn=0&quot; & vbNullChar
sAttrib = sAttrib & &quot;ArrayBufferSize=0&quot; & vbNullChar
End If
'If UID <> &quot;&quot; Then
sAttrib = sAttrib & &quot;uid=&quot; & UID & vbNullChar
sAttrib = sAttrib & &quot;pwd=&quot; & PWD & vbNullChar
'End If
sAttrib = sAttrib & &quot;AutoDoubleQuote=0&quot; & vbNullChar
sAttrib = sAttrib & &quot;Description=Pervasive ODBC Client Interface&quot; & vbNullChar
sAttrib = sAttrib & &quot;TranslationDLL=&quot; & vbNullChar
sAttrib = sAttrib & &quot;TranslationOption=&quot; & vbNullChar
strDriver = &quot;Pervasive ODBC Client Interface&quot;
lRetVal = SQLConfigDataSource(0&, dDSNType, strDriver, sAttrib)
If lRetVal Then
CreateCliDSN = True 'Execute ok
Else
CreateCliDSN = False
MsgBox &quot;Error: &quot; & vbCrLf & &quot;Invalid attributes&quot; & vbCrLf & &quot;lRetVal= &quot; & lRetVal
End If
Exit Function
errHandler:
MsgBox &quot;Error: &quot; & Err.Number & vbCrLf & _
&quot;Source: &quot; & Err.Source & vbCrLf & _
&quot;Description: &quot; & Err.Description
CreateCliDSN = False
End Function

Public Function CreateEngDSN(dDSNType As DSNType, DSNName As String, Description As String, _
DBN As String, OpenMode As Integer) As Boolean
'CreateEngDSN(DSNTyp, txtEngDSN.Text, txtEngDesc.Text, txtEngDBN.Text, iOpenMode)
On Error GoTo errHandler
Dim strDriver As String
Dim lRetVal As Long
Dim sAttrib As String
If (dDSNType < CreateUserDSN) Or (dDSNType > DeleteSystemDSN) Then
CreateEngDSN = False
Exit Function
End If

sAttrib = &quot;DSN=&quot; & DSNName & vbNullChar
sAttrib = sAttrib & &quot;Description=&quot; & Description & vbNullChar
sAttrib = sAttrib & &quot;DBQ=&quot; & DBN & vbNullChar
sAttrib = sAttrib & &quot;OpenMode=&quot; & OpenMode & vbNullChar
strDriver = &quot;Pervasive ODBC Engine Interface&quot;
lRetVal = SQLConfigDataSource(0&, dDSNType, strDriver, sAttrib)
If lRetVal Then
CreateEngDSN = True 'Execute ok
Else
CreateEngDSN = False
MsgBox &quot;Error: &quot; & vbCrLf & &quot;Invalid attributes&quot; & vbCrLf & &quot;lRetVal= &quot; & lRetVal
End If
Exit Function
errHandler:
MsgBox &quot;Error: &quot; & Err.Number & vbCrLf & _
&quot;Source: &quot; & Err.Source & vbCrLf & _
&quot;Description: &quot; & Err.Description
CreateEngDSN = False
End Function

These
can obviously be modified slightly to meet your needs. Below is my modification of the Create client DSN function.

Private Function Create_ClientDSN(pDSNName As String, pServerName As String, pServerDSN As String) As Boolean
'// Create the client DSN pointing to the server database.
Dim l_strDriver As String
Dim l_lngRetVal As Long
Dim l_strAttributes As String

On Error GoTo ERR_CreateClientDSN

l_strDriver = &quot;Pervasive ODBC Client Interface&quot;
l_strAttributes = &quot;DSN=&quot; & pDSNName & vbNullChar
l_strAttributes = l_strAttributes & &quot;ServerName=&quot; & pServerName & vbNullChar
l_strAttributes = l_strAttributes & &quot;TCPPort=1583&quot; & vbNullChar
l_strAttributes = l_strAttributes & &quot;ServerDSN=&quot; & pServerDSN & vbNullChar
l_strAttributes = l_strAttributes & &quot;TransportHint=TCP:SPX&quot; & vbNullChar
l_strAttributes = l_strAttributes & &quot;ArrayFetchOn=0&quot; & vbNullChar
l_strAttributes = l_strAttributes & &quot;ArrayBufferSize=0&quot; & vbNullChar
l_strAttributes = l_strAttributes & &quot;uid=&quot; & vbNullChar
l_strAttributes = l_strAttributes & &quot;pwd=&quot; & vbNullChar
l_strAttributes = l_strAttributes & &quot;AutoDoubleQuote=0&quot; & vbNullChar
l_strAttributes = l_strAttributes & &quot;Description=Quote Manager ODBC link to &quot; & pServerDSN & vbNullChar
l_strAttributes = l_strAttributes & &quot;TranslationDLL=&quot; & vbNullChar
l_strAttributes = l_strAttributes & &quot;TranslationOption=&quot; & vbNullChar
l_lngRetVal = SQLConfigDataSource(0&, CreateSystemDSN&, l_strDriver, l_strAttributes)
If l_lngRetVal Then
Create_ClientDSN = True '// Successful.
Else
Create_ClientDSN = False '// Falied.
End If
Exit Function

ERR_CreateClientDSN:
Create_ClientDSN = False
End Function



Thanks and Good Luck!

zemp

 

[mirtheil]

Check out

http://www.pervasive.com/developerzone/access_methods/dtidto.asp.

You'll need to download the sample called &quot;ODBC & DSN&quot;. I've used it as a guide. It uses the ODBC API to create client DSNs.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician