How to programmatically create System DSN for VFP

[jkentakgyris]

Greetings! Hope it's the right forum as VB forum could not help.
It is a simple step to programmatically create a system DSN for SQL Server in VB6 (code below).
Does anyone know how to create a System DSN for a Microsoft FoxPro VFP Driver (*.dbf) with a free table directory (No database, No server) in VB6?

TIA!

******************************** CODE ********
DataSourceName = "mySQL"
Description = ""
DriverPath = "C:\WINNT\SYSTEM32\"
DatabaseName = "MBA"
LastUser = "Admin"
Server = "EAGLE"
DriverName = "SQL Server"

'Create the new DSN key.

lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
DataSourceName, hKeyHandle)

frmStart.lbxLog.AddItem ("Creating DSN key for " + strName)
'Set the values of the new DSN key.

lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
ByVal DatabaseName, Len(DatabaseName))
lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
ByVal Description, Len(Description))
lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
ByVal Server, Len(Server))

'Close the new DSN key.

lResult = RegCloseKey(hKeyHandle)
**************************** END CODE *********

 

[rgbean]

Why do you want to create a DSN? Why not simply use a DSN-less connection?

Rick

 

[jkentakgyris]

MUST create DSN as a part of a setup program for another application that uses this DSN. Modifying that other program to use DSN-less connection is out of question for many reasons.

 

[MarciaAkins]

Here is some code that we use to programmatically create DSNs. Hope it helps:

  ********************************************************************
  *** [P] WRITEDSN(): Create the registry entries for a new DSN
  ********************************************************************
  PROTECTED FUNCTION WriteDSN( toDSNDef )
  LOCAL llRetVal, lcName, lcDatabase, lcDriver, lcServer, lcDescrip, lnStatus, lcMsg
    llRetVal = .T.
    *** Unpack the parameter object
    WITH toDSNDef
      lcName     = ALLTRIM( .cDSN )
      lcDatabase = ALLTRIM( .cDatabase )
      lcDriver   = ALLTRIM( .cDriver )
      lcDFile    = ALLTRIM( .cDFile )
      lcServer   = ALLTRIM( .cServer )
      lcDescrip  = ALLTRIM( .cDesc )
    ENDWITH
    WITH This.oReg
      *** Create the Key (Last param allows creation if not already present)
      lnStatus = .OpenKey( 'SOFTWARE\ODBC\ODBC.INI\' + lcName , .nUserKey, .T. )
      IF lnStatus # 0
        lcMsg = "Cannot open, or create, Registry Key: " + 'SOFTWARE\ODBC\ODBC.INI\' + lcName
        This.LogError( "DSNMgr09", lcMsg, PROGRAM() )
        llRetVal = .F.
      ELSE
        *** Add the lower level keys
        lnKey = .nCurrentKey
        lnStatus = IIF( lnStatus = 0, .SetKeyValue( 'Database', lcDatabase ), lnStatus)
        lnStatus = IIF( lnStatus = 0, .SetKeyValue( 'Driver', lcDFile ), lnStatus)
        lnStatus = IIF( lnStatus = 0, .SetKeyValue( 'Server', lcServer ), lnStatus)
        lnStatus = IIF( lnStatus = 0, .SetKeyValue( 'Description', lcDescrip), lnStatus)
        llRetVal = (lnStatus = 0)
      ENDIF
      IF llRetVal
        *** Add the entry to put the DSN Into the ODBC Administrator too
        lnStatus = .OpenKey( 'SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources', .nUserKey, .T. )
        IF lnStatus # 0
          lcMsg = "Cannot Write DSN Details to Registry "
          This.LogError( "DSNMgr10", lcMsg, PROGRAM() )
          llRetVal = .F.
        ELSE
          *** Add the detail key
          lnKey = .nCurrentKey
          lnStatus = IIF( lnStatus = 0, .SetKeyValue( lcName, lcDriver ), lnStatus)
          llRetVal = (lnStatus = 0)
        ENDIF
      ENDIF
      RETURN llRetVal
    ENDWITH
  ENDFUNC

And here is the code from our registry handling object that wrtie the key to the registry:

  PROCEDURE SetKeyValue( tcKeyItem, tcValue )
    *** Sets the specified key value (Strings only)
    LOCAL lnValueSize 

    DO CASE
      CASE VARTYPE( This.nCurrentKey  ) # 'N' OR EMPTY( This.nCurrentKey )
        RETURN ERROR_BADKEY
      CASE VARTYPE( tcKeyItem ) # "C" OR VARTYPE( tcValue ) # "C"
        RETURN ERROR_BADPARM
      CASE EMPTY( tcKeyItem ) OR EMPTY( tcValue )
        RETURN ERROR_BADPARM
    ENDCASE

    *** Strings must be null-terminated
    tcValue = tcValue + CHR(0)
    lnValueSize = LEN( tcValue )

    *** And try and set the value here
    lnErrNum = RegSetValueEx( This.nCurrentKey, tcKeyItem, 0, REG_SZ, tcValue, lnValueSize)
    IF lnErrNum # ERROR_SUCCESS
      RETURN lnErrNum
    ENDIF

    RETURN lnErrNum
  ENDPROC

This should be enough to get you started.



Marcia G. Akins

http://www.TightlineComputers.com

 

[bhoyan]

Dear MarciaAkins

I'm The Beginner Programer

May i ask you about function writeDSN(toDSNdef)

What is the variable toDSNdef??

when i'll try ur procedure, appear error data type mismatch..?

thank's for your attention

 

[craigsboyd]

Here's a FAQ I wrote on this very subject sometime back...

Add or Remove DSN
faq184-4064

http://www.sweetpotatosoftware.com

 

[Mike Lewis]

jkentakgyris,

If you are able to create the connection string, just save it to a text file with the extension DSN. That's all a (file) DSN is -- it's just the connection string stored in a text file.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[MarciaAkins]

Hello bhoyan.

You have to create a parameter object that contains the information required to write to the regustry. That is what the parameter toDSNdef is:

*** Create a parameter object for the DSN Definition
loDSNDef = CREATEOBJECT( 'line' )
WITH loDSNDef
  .AddProperty( 'cDSN', << DSN Name >> )
  .AddProperty( 'cDesc', << DSN Description >> )
  .AddProperty( 'cDatabase', << Database >> )
  .AddProperty( 'cServer', << Server Name >> )
  .AddProperty( 'cDriver', << Driver Name >> )
  .AddProperty( 'cDFile', << Driver File >> )
ENDWITH
llRetVal = .WriteDSN( loDSNDef )

If you need more details, this si part of the sample code in Chapter 13 of <a href="

http://www.hentzenwerke.com/catalog/megafox.htm">MegaFox:

1002 Things You Wanted to Know About Extending VFP</a>

Here is the table of contents from that Chapter:

Chapter 13: Working with remote data
Running the examples
Connecting to remote data
How do I connect to a database using ODBC (Example: ConODBC.scx)
Connection Strings
Using SQLStringConnect()
Named Connections
Using SQLConnect()
How do I connect to a database using OLEDB (Example: ConOLEDB.scx)
Which is better, ODBC or OLEDB?
How can I be sure users have the correct settings? (Example: DSNMgr.prg)
Registry structure for System DSNs
Creating DSNS programmatically with DSNMgr class
How do I use remote views in Visual FoxPro?
[1] Configure the connection
[2] Configure the remote data handling
[3] Define a remote view
[4] Create the form (Example: RVForm.scx)
Summary
So, what is SQL Pass Through?
FoxPro’s SPT functions
Connection management (Example: ConMgr.prg)
Connection properties
Command execution (Example: ExecDemo.prg)
Transaction management (Example: TxnDemo.prg)
Miscellaneous
Should I run in synchronous or asynchronous mode?
Synchronous Batch Mode
Synchronous Non-Batch Mode
Asynchronous Batch Mode
Asynchronous Non-Batch Mode
What’s wrong with remote views?
What should I use instead of remote views then?
How can I make a cursor updateable? (Example: UpdCurs.scx)
What are the data classes?
Design considerations for the data classes
The custom cursor class
Defining cursors (Example: Curdefs.scx)
Managing cursors, the dataset class
The data class implementation model
How do I use the data classes? (Example: Dataclass Directories)
Setting up the example databases
The example form (Example: FrmAccts.scx)
Cursor definitions (Example: Curdefs.dbf)
Dataset Definition (Example: Datasets.dbf, DSetLink.dbf and CurNames.dbf)
Setting up the environment
Running the form (Example: FrmAccts.scx)
Conclusion



Marcia G. Akins

http://www.TightlineComputers.com