HOW TO ADD DSN IN ODBC USING VFP

[remsor]

Hello Vfp Experts,

Anybody have an experience on how to insert/add dsn in OBDC using vfp6.

thanks

 

[Mike Gagnon]

Remsor

One way would to create a textfile using FCREATE() and using FPUTS() inserting the appropriate init string and changing the extention of the textfile to ".udl"
Here is an example (note the connection string should be a valid one)

cFile = Sys(2023) + Sys(3) + '.UDL'
If File(m.cFile)
    Erase (m.cFile)
Endif
nFh = Fcreate(m.cFile)
If m.nFh < 0
    Wait &quot;Can't create a temporary file for the email&quot; Window Nowait
    Return
Endif
= Fputs(m.nFh,'[oledb]')
= Fwrite(m.nFh,&quot;Provider=MSDASQL.1;Persist Security Info=False;Data Source=MS Access Database;Mode=ReadWrite&quot;)
= Fflush(m.nFh)
= Fclose(m.nFh)
Run /N2 Start &cFile
Return .T.
Endfunc

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[craigsboyd]

Remsor,

I've always just used a udl file in the past (similar to the approach Mike outlined). So, your question intrigued me since there has to be a way to do it from code. As I suspected it is a pretty straight forward and simple API call. I've worked up a little program below to show how it is done. Cut-n-paste the code below into a prg file and run it from within VFP.


PUBLIC oForm
oForm = CREATEOBJECT("clsvfpdsn&quot
oForm.show()

DEFINE CLASS clsvfpdsn AS form

Top = 0
Left = 0
Height = 92
Width = 309
DoCreate = .T.
Caption = "VFP CREATED DSN"
WindowType = 1
Name = "Form1"
AutoCenter = .T.

ADD OBJECT command1 AS commandbutton WITH ;
Top = 45, ;
Left = 37, ;
Height = 27, ;
Width = 100, ;
Caption = "Add DSN", ;
Name = "Command1"

ADD OBJECT command2 AS commandbutton WITH ;
Top = 45, ;
Left = 171, ;
Height = 27, ;
Width = 100, ;
Caption = "Remove DSN", ;
Name = "Command2"

ADD OBJECT check1 AS checkbox WITH ;
Top = 19, ;
Left = 37, ;
Height = 17, ;
Width = 82, ;
AutoSize = .T., ;
BackStyle = 0, ;
Caption = "Silent Mode", ;
Value = 1, ;
Name = "Check1"

PROCEDURE Load
DECLARE INTEGER SQLConfigDataSource IN odbccp32;
INTEGER hwndParent,;
INTEGER fRequest,;
STRING lpszDriver,;
STRING lpszAttributes
ENDPROC

PROCEDURE command1.Click
#DEFINE ODBC_ADD_DSN 1
*!* #DEFINE ODBC_CONFIG_DSN 2
*!* #DEFINE ODBC_REMOVE_DSN 3

Local nReturnValue, cDriver, cAttribs

cDriver = "SQL Server"
cAttribs = "SERVER=SomeServer" + Chr(0)
cAttribs = cAttribs + "DESCRIPTION=Temporary DSN created with VFP code" + Chr(0)
cAttribs = cAttribs + "DSN=VFP_CREATED_DSN" + Chr(0)
cAttribs = cAttribs +"DATABASE=pubs" + Chr(0)

IF thisform.check1.value = 0
nReturnValue = SQLConfigDataSource(thisform.hwnd, ODBC_ADD_DSN, cDriver, cAttribs)
ELSE
nReturnValue = SQLConfigDataSource(0, ODBC_ADD_DSN, cDriver, cAttribs)
ENDIF

If nReturnValue = 1
Messagebox("VFP_CREATED_DSN Created",64,"SUCCESS&quot
Else
Messagebox("Creation of VFP_CREATED_DSN Failed",64,"FAILURE&quot
Endif
ENDPROC

PROCEDURE command2.Click
#DEFINE ODBC_REMOVE_DSN 3
*!* #DEFINE ODBC_ADD_DSN 1
*!* #DEFINE ODBC_CONFIG_DSN 2

Local nReturnValue, cDriver, cAttribs

cDriver = "SQL Server"
cAttribs = "DSN=VFP_CREATED_DSN" + Chr(0)

IF thisform.check1.value = 0
nReturnValue = SQLConfigDataSource(thisform.hwnd, ODBC_REMOVE_DSN, cDriver, cAttribs)
ELSE
nReturnValue = SQLConfigDataSource(0, ODBC_REMOVE_DSN, cDriver, cAttribs)
ENDIF

If nReturnValue = 1
Messagebox("VFP_CREATED_DSN Deleted",64,"SUCCESS&quot
Else
Messagebox("Deletion of VFP_CREATED_DSN Failed",64,"FAILURE&quot
Endif
ENDPROC

ENDDEFINE



Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[craigsboyd]

Pre-existing FAQ I found after submitting my own:

faq184-1538

New FAQ I just created for the code I posted in this thread:

faq184-4064


Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]