ODBC DSN Creation 1

[Guntruck]

Here is the code im testing out? why doesnt it work??
it also doesnt provide for user name / password..
it seems no matter what I try here nothing really works for a dynamic connection to a SQL server.. I fail to see why its so hard to create a connection to a server and mess with some data... when data manipulation so easy with just plain .dbf's. Maybe the simple answer im looking for just doesnt exist.

#define ODBC_ADD_DSN 1
#define ODBC_CONFIG_DSN 2
#define ODBC_REMOVE_DSN 3
#define ODBC_ADD_SYS_DSN 4
#define ODBC_CONFIG_SYS_DSN 5
#define ODBC_REMOVE_SYS_DSN 6
#define ODBC_REMOVE_DEFAULT_DSN 7

#define SQL_NO_DATA 100
#define SQL_SUCCESS_WITH_INFO 1
#define SQL_SUCCESS 0
#define SQL_ERROR -1


DECLARE Integer SQLConfigDataSource IN odbccp32.dll Integer, Short, String @, String @


lc_driver = "SQL Server" + CHR(0)
lc_dsn = "dsn=testdsn" + CHR(0) + ;
"server=127.0.0.1" + CHR(0) + ;
"database=tracker" + CHR(0) + ;
"network=DBMSSOCN" + CHR(0) && DBMSSOCN = TCP/IP connection

fResult = SQLConfigDataSource(0, ODBC_ADD_DSN, @lc_driver, @lc_dsn)

IF fResult = -1 OR fResult = 1
* Call the SQLInstallerError API for error Information.
ENDIF

 

[DEDMOD]

I don't know how to make your code work, but why are you doing it in code if you just want to 'mess with' some data? It's very easy to create a connection using windows and VFP. You create the data source in the window's Control Panel and then create the connection from the VFP Project Manager. --Dave

 

[Guntruck]

Thats what I was trying to get around... I wanted to be able to create my own connections on the fly.. its a very good thing to be able to do with a client thats going to be widely distributed.. so the server can be reconfigured on the fly using the client.. I am now using ADO just fine.. so thanks anyway...



dave

 

[jonscott8]

If you are still interested, their is a class, ODBC_DSN, created by Mark McCasland at universalthread.com that provides this functionality.

FWIW, a DSN simply houses a connection string for ODBC. The same as a UDL file does for OLE DB(ADO). Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[foxdev]

Follow this example to create a connection on the fly, without a DSN (this is detailed in a FAQ on this forum):

[tt]nHandle = sqlstringconnect("driver={SQL SERVER};SERVER=128.14.16.3;
UID=myuser;PWD=mypwed;DATABASE=mydb;trusted_connection=No" [/tt]

I've broken the statement into two lines in the example above, but it is all one string, including the semicolons.

Once you have successfully connected (nHandle>0), you can then use SQL pass through statements like this:

[tt]sqlexec(nHandle, "select * from Mytable&quot[/tt]
Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[krisb]

We have a SQL server and use FoxPro access by having the driver created in:
Control Panel
ODBC
(Very easily set up)

Then in VFP:

?sqlconnect('servername')
?sqlexec('handle,"select * from database name&quot

Then once you are "done" with the server, you can disconnect:
?sqldisconnect(handle)

Hope this helps - Kris

 

[DEDMOD]

By the way, here's some code I've been using recently which you may find useful:

oConn = CREATE("adodb.recordset")

oConn.open("SELECT * FROM YourTable WHERE YourField='XXX'", GetDEDstr(),2,3)

Here GetDEDstr() returns:

"driver=SQL Server; Server=YourServer; uid=sa;pwd=; database=YourDB"

and 'XXX' doesn't exist in YourField, so you're not returning anything at this point

Dave Dardinger

 

[Guntruck]

Ya, I use basically the same exact code now... and Krisb I cant use the ODBC util because i wanted to create a dynamic connection as noted above. ADO works very nicely.



Dave