ODBC Datasources ...

[xIRC]

Is there a way, that I can programatically setup (create/delete/edit) ODBC datasources, even if this will be using API calls ... ?

Thanx in advance ...

xIRC

[xIRC]

Should I maybe write a c/c++ wrapper for this, since, I know how to do this in c/c++ ... ????

[Twodogs]

Do you want to programatically create a database & tables? If so, look at ADOX

[xIRC]

No, I know how to do that using ADOX ...

What I want to do, is instead of the user having click on Start » Administration » (ODBC) Datasources,
and add a new Datasource.

I would like to do this programatically, I know also how to use this in creating a .reg file, ect.

But I want to add my own ODBC datasource programatically ... using VB, here is a sample of how to do this in c++ from microsoft's MSDN ... »

Examples
A. Create a data source using SQLConfigDataSource
#include <stdio.h>
#include <windows.h>
#include "sql.h"
#include <odbcinst.h>

int main()
{
RETCODE retcode;

UCHAR *szDriver = "SQL Server";
UCHAR *szAttributes =
"DSN=MyDSN\0DESCRIPTION=SQLConfigDSN Sample\0"
"SERVER=MySQL\0ADDRESS=MyServer\0NETWORK=dbmssocn\0"
"DATABASE=pubs\0";

retcode = SQLConfigDataSource(NULL,
ODBC_ADD_DSN,
szDriver,
szAttributes);

B. Create a file data source
Use the SAVEFILE keyword in SQLDriverConnect to create a file data source, and then use SQLDriverConnect to connect with the file data source. This example has been simplified by removing error handling.

#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>

#define MAXBUFLEN 255

SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc1 = SQL_NULL_HDBC;

int main() {

RETCODE retcode;

// This format of the SAVEFILE keyword saves a successful
// connection as the file Myfiledsn.dsn in the ODBC default
// directory for file DSNs.
SQLCHAR szConnStrIn[MAXBUFLEN] =
"SAVEFILE=MyFileDSN;DRIVER={SQL Server};SERVER=MySQL;"
"NETWORK=dbmssocn;UID=sa;PWD=MyPassWord;";

SQLCHAR szConnStrOut[MAXBUFLEN];
SQLSMALLINT cbConnStrOut = 0;

// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

//Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

// Allocate an ODBC connection handle and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
retcode = SQLDriverConnect(hdbc1, // Connection handle
NULL, // Window handle
szConnStrIn, // Input connect string
SQL_NTS, // Null-terminated string
szConnStrOut, // Addr of output buffer
MAXBUFLEN, // Size of output buffer
&cbConnStrOut, // Address of output length
SQL_DRIVER_NOPROMPT);

// Disconnect, set up a new connect string, and then test file DSN.
SQLDisconnect(hdbc1);
strcpy(szConnStrIn, "FILEDSN=MyFileDSN;UID=sa;PWD=MyPassWord;");
retcode = SQLDriverConnect(hdbc1, // Connection handle
NULL, // Window handle
szConnStrIn, // Input connect string
SQL_NTS, // Null-terminated string
szConnStrOut, // Addr of output buffer
MAXBUFLEN, // Size of output buffer
&cbConnStrOut, // Address of output length
SQL_DRIVER_NOPROMPT);

/* Clean up. */
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return(0);

I want to know is this possible to add/delete/edit your own datasource, with VB?

Thanx in advance ...
xIRC

[Sudhir Jain]

Use SQLConfigDataSource() ODBC API

[Sudhir Jain]

you can use any of the windows API in your VB Programm.
search for how to use windows api in vb in msdn

[drm15]

Look up DSN-less connections. Depending on the language and your driver you can build data sources on the fly.

Here is an example in Java for SQL Server:
query = "SELECT w_number, ip_address, port_number FROM w" + pathway + "num WHERE w_number = '" + wnum + "'";
Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbcdbcRIVER={SQL Server};SERVER=rchsql53;Database=ONCALL;UID=uid;PWD=pwd;");
stmt = con.createStatement();
rs = stmt.executeQuery(query);

I did have problems creating a DSN-less connection when using a certain driver so I followed another method of building the DSN on the fly in the registry. DSN's are usually stored in the registry so you can build it on the fly, use it, then destroy it when you are done. This is a slight security risk, but sometimes it is the only option (as far as I know).