Programming Forums > Application Development > C++

bigi · Mar 8th, 2005, 3:16 PM

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 · Mar 8th, 2005, 3:19 PM

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.

(Toggle Plain Text)

#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;
        }
    }
}

#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;
        }
    }
}

Mar 8th, 2005, 3:16 PM	#1
bigi Newbie Join Date: Mar 2005 Posts: 3 Rep Power: 0	Using ODBC to connect to a remote database in a C program 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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 8th, 2005, 3:19 PM	#2
bigi Newbie Join Date: Mar 2005 Posts: 3 Rep Power: 0	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. (Toggle Plain Text) #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; } } } #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; } } }

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > C++

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