Using ODBC to connect to a remote database in a C program

[bigi]

Hello all,

My first time posting here.
The title is basically exactly what I'm trying to do.

I have really no experience at this. I've written code in PHP before that connects to a remote MySQL database and also code in Java Servlets that connects to a remote MySQL database, but not in C. (I imagine connecting to an Oracle database via ODBC is the same idea as connecting to a MySQL database via JDBC, so I won't ask about that part) I also already have a pretty solid understanding of how database systems work and how programs usually connect to one.

Basically I've downloaded Microsoft's free Visual C++ Toolkit 2003, and I've also downloaded MDAC (Data Access Component) and Platforms SDK, so I've got sql.h, sqlext.h, sqltypes.h and all that needed functions to send SQL queries and get results back. In fact I've even got a little sample program that shows how to do all of that in the simplest way possible.

However I still need to find out how to connect to a remote database. Let's say my database resides in some server 123.45.67.89. sql.h and stuff lets me define a DSN (Data Source Name?) to connect to but not a network address.

Could someone walk me through on how to do this? In fact, all I'd really need to know is: 1) what libraries do I need 2) a very small sample program that connects to a remote database, send a query, and get a result. I can figure out everything if I just have these two things.

I'm doing this in C, so I would prefer sample code in C. However C++ is fine as well, no problem.

Thanks a lot for any help!

[bigi]

By the way this only has to be a console-based program, so it doesn't need any GUI windows or anything fancy, the simplest sample code would be sufficient. Thanks a lot!

In fact, let me post you the sample code I've got for connecting to a local database using ODBC.


#include <Windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <stdio.h>

//
// function prototypes
//
int main();

//
// main
//
int main()
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN sqlrc;
char sql[1025];
char svalue[2049];
SQLINTEGER inum;
SQLSMALLINT cols;
int i;
char uid[24];
char pwd[24];
char dsn[24];

// connection strings
strcpy(uid, "uid");
strcpy(pwd, "pwd");
strcpy(dsn, "dsn");

// SQL strings
strcpy(sql, "select * from kemp");

// environment handle
sqlrc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (sqlrc != SQL_SUCCESS && sqlrc != SQL_SUCCESS_WITH_INFO) {
printf("SQLAllocHandle SQL_HANDLE_ENV Error\n");
return 9;
}

// environment variables settings
sqlrc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (sqlrc != SQL_SUCCESS && sqlrc != SQL_SUCCESS_WITH_INFO) {
printf("SQLSetEnvAttr Error\n");
return 9;
}

// connection handle
sqlrc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (sqlrc != SQL_SUCCESS && sqlrc != SQL_SUCCESS_WITH_INFO) {
printf("SQLAllocHandle SQL_HANDLE_DBC Error\n");
return 9;
}

// connect
sqlrc = SQLConnect(hdbc, (SQLCHAR*)dsn, SQL_NTS, (SQLCHAR*)uid, SQL_NTS, (SQLCHAR*)pwd, SQL_NTS);
if (sqlrc != SQL_SUCCESS && sqlrc != SQL_SUCCESS_WITH_INFO) {
printf("SQLConnect Error\n");
return 9;
}

// statement handle
sqlrc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (sqlrc != SQL_SUCCESS && sqlrc != SQL_SUCCESS_WITH_INFO) {
printf("SQLAllocHandle SQL_HANDLE_STMT Error\n");
return 9;
}

// statement prepare
sqlrc = SQLPrepare(hstmt, (SQLCHAR*)sql, SQL_NTS);
if (sqlrc != SQL_SUCCESS && sqlrc != SQL_SUCCESS_WITH_INFO) {
printf("SQLPrepare Error\n");
return 9;
}

// get result columns
sqlrc = SQLNumResultCols(hstmt, &cols);
if (sqlrc != SQL_SUCCESS && sqlrc != SQL_SUCCESS_WITH_INFO) {
printf("SQLNumResultCols Error\n");
return 9;
}

// execute
sqlrc = SQLExecDirect(hstmt, (SQLCHAR*)sql, SQL_NTS);

// display result in CSV format
while (TRUE) {
sqlrc = SQLFetch(hstmt);

if (sqlrc == SQL_SUCCESS || sqlrc == SQL_SUCCESS_WITH_INFO) {
for (i = 1; i < cols+1; i++) {
if (i > 1) {printf(",");}
strcpy(svalue, "");
SQLGetData(hstmt, i, SQL_C_CHAR, (SQLCHAR*)svalue, 2048, &inum);
printf(svalue);
}
printf("\n");
} else {
if (sqlrc == SQL_NO_DATA) {
printf("\n");
} else {
// otherwise error
printf("SQLFetch ERROR\n");
}
break;
}
}
}

[Jaycuse]

Hi, I never worked with ODBC yet but I was checking out info on the SQLConnect function.
I got the info on iAnywhere.com (http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/ulcpen9/00000329.htm)

and it says for the DSN that you need to give it a Connection String (well thats what I understood about it). And its in the connection string that you will add the ip.

www.ConnectionStrings.com (http://www.connectionstrings.com) is a good site that shows you how to format your connection string.

Like I said I never tested this, I might be totaly wrong but it might also help, and I do hope it helps. :P

--Edit--

crap I just relised I was checking out the info for UltraLite ODBC and not ODBC so it might not work