Thread: Help With ODBC Code

  1. #1
    Registered User
    Join Date
    Aug 2008
    Posts
    5

    Unhappy Help With ODBC Code

    I am trying to connect to a mysql database using the ODBC driver i set up on my PS as a system DSN. I have been struggling with this for 2 days now and i cant get pass "Establishing a Connection", so i would really appropriate any help. Here's the code:

    Code:
    #include <stdlib.h>
    #include <string.h>
    #include <stdio.h>
    #include <windows.h>
    #include <sql.h>
    #include <sqlext.h>
    #include <sys/types.h>
    
    //globals
    SQLHENV henv;//handle to connection environment
    SQLHDBC hdbc;//handle to odbc connection
    SQLHSTMT hstmt;//handle to sql statement
    
    //finction declaration
    void SQLDisconnectFunc();
    
    int main ( void ){
    	//var dec
    	SQLRETURN rs;//ODBC API return status
    	SQLCHAR *SqlConnStringIn;
    	SQLCHAR ConnStringOut[1024];
    	SQLSMALLINT ConnStringLength;
    	SQLSMALLINT columns; /* number of columns in result-set */
    
    	char dsn[20] = "diadus";
    	int lenDsn, i, row = 0;
    	
    	//var ini
    	lenDsn = strlen(dsn);
        SqlConnStringIn = (SQLCHAR*)malloc((lenDsn + 1)*sizeof(SQLCHAR));
    	
    	//load the driver manager and allocate 
    	//environment handle
    	if ((rs = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv)) != 0){
    		fprintf (stderr, "SQLAllocHandle(): Connection allocation: failure.\n");
    		return;
    	}//end if ((result = SQL
    	if ((rs = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0)) != 0){
    		fprintf (stderr, "SQLAllocHandle(): setting of odbc version: failure.\n");
    	    return;
    	}//end if 
    	
    	//allocate a connection handle with SQLAllocHandle 
    	//and connects to the data source with SQLConnect, SQLDriverConnect, or SQLBrowseConnect
    	if (( rs = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc)) != 0){
    		fprintf (stderr, "SQLAllocHandle(): allocation of odbc connection: failure.\n");
            SQLFreeHandle(SQL_HANDLE_ENV, henv);
            return;
        }//end if 
    	
    	//conversion from char* to SQLCHAR*    
    	for (i=0; i<lenDsn+1; ++i){ SqlConnStringIn[i]=dsn[i]; }//end for
    	rs = SQLDriverConnect(hdbc, NULL, SqlConnStringIn, lenDsn, ConnStringOut, sizeof(ConnStringOut), 
    						  &ConnStringLength, SQL_DRIVER_NOPROMPT);
    	
    	if(rs != SQL_SUCCESS && rs != SQL_SUCCESS_WITH_INFO){
    		SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1];
    		SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
    		SQLINTEGER  sqlcode;
    		SQLSMALLINT length;
            SQLError(henv, hdbc, NULL, sqlstate, &sqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length);
    		printf("SQLSTATE: &#37;s\n", sqlstate);
    		printf("Native Error Code: %ld\n", sqlcode);
            printf("%s \n", buffer);                                                          
            
    		//disconnect
    		//SQLDisconnectFunc();
           	system("PAUSE");
    		exit(-1);
            //return;
        }//end if
    
    	/* Allocate a statement handle */
    	SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    	/* Retrieve a list of tables */
    	SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
    	
    	/* How many columns are there */
    	SQLNumResultCols(hstmt, &columns);
    	
    	/* Loop through the rows in the result-set */
    	while (SQL_SUCCEEDED(rs = SQLFetch(hstmt))) {
    		SQLUSMALLINT i;
    
    		printf("Row %d\n", row++);
    		/* Loop through the columns */
    		for (i = 1; i <= columns; i++) {
    			SQLINTEGER indicator;
    			char buf[512];
    			/* retrieve column data as a string */
    			rs = SQLGetData(hstmt, i, SQL_C_CHAR,
    							 buf, sizeof(buf), &indicator);
    			if (SQL_SUCCEEDED(rs)) {
    				/* Handle null columns */
    				if (indicator == SQL_NULL_DATA) strcpy_s( buf, 1, " " );
    				printf("  Column %u : %s\n", i, buf);
    			}//emd if
    		}//end for
    	}//end while
    
    	//free helper variable
        free(SqlConnStringIn);
    	
    	system("PAUSE");
    	return (0);
    }//end main 
    
    
    /**
    * Disconnect ODBC connection
    */
    
    void SQLDisconnectFunc() {
    	SQLRETURN rs = SQLDisconnect(hdbc);
        
    	if (rs != SQL_SUCCESS && rs != SQL_SUCCESS_WITH_INFO){
    		SQLCHAR     buffer[SQL_MAX_MESSAGE_LENGTH + 1];
            SQLCHAR     sqlstate[SQL_SQLSTATE_SIZE + 1];
            SQLINTEGER  sqlcode;
            SQLSMALLINT length;
            
    		SQLError(henv, hdbc, hstmt, sqlstate, &sqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length);
    		printf("SQLSTATE: %s\n", sqlstate);
            printf("Native Error Code: %ld\n", sqlcode);
            printf("%s \n", buffer);             
    	}//enf if
    	
    	//free handle to odbc connection and handle to connection environment
        SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
        SQLFreeHandle (SQL_HANDLE_ENV, henv);
    }//end SQLDisconnect
    Last edited by zzman; 08-22-2008 at 02:30 PM.

  2. #2
    Registered User C_ntua's Avatar
    Join Date
    Jun 2008
    Posts
    1,853
    Don't know much about the subject, but in any case, what error do you get running this program? I assume it compiles normally eh? When you say you can't get pass the "Establishing a Connection" you mean you get a seqmentation fault there?

  3. #3
    Registered User
    Join Date
    Aug 2008
    Posts
    5
    SQLConnect is not returning the right value. I am not getting any compile errors.

    I am ready to pull my hair out, any help will be appreciated guys ...

  4. #4
    Registered User
    Join Date
    Jan 2007
    Location
    Euless, TX
    Posts
    144
    Not sure if this would be helpful, but try looking on this link:

    http://www.connectionstrings.com/?carrier=mysql

  5. #5
    Jack of many languages Dino's Avatar
    Join Date
    Nov 2007
    Location
    Chappell Hill, Texas
    Posts
    2,332
    Can you be any more vague? If you want detailed help, give detailed information about the problem.
    Mainframe assembler programmer by trade. C coder when I can.

  6. #6
    Registered User hk_mp5kpdw's Avatar
    Join Date
    Jan 2002
    Location
    Northern Virginia/Washington DC Metropolitan Area
    Posts
    3,817
    Aren't there error/diagnostic functions available? SQLError/SQLGetDiagField. These can return a message (amongst other things) describing the problem encountered rather than just an obscure numeric value.
    "Owners of dogs will have noticed that, if you provide them with food and water and shelter and affection, they will think you are god. Whereas owners of cats are compelled to realize that, if you provide them with food and water and shelter and affection, they draw the conclusion that they are gods."
    -Christopher Hitchens

  7. #7
    Banned master5001's Avatar
    Join Date
    Aug 2001
    Location
    Visalia, CA, USA
    Posts
    3,685
    You are trying to estabilish the correct kind of connection, right? I have found some issues with that in the past... But again, use the damn error functions! This is exactly what they are for!

  8. #8
    Registered User
    Join Date
    Aug 2008
    Posts
    5
    Did some error reporting, ive updated the code above to reflect the same, here's the error:

    SQLSTATE: IM002
    Native Error Code: 0
    [Microsoft][ODBC Driver Manager] Data source name not found and no default drive
    r specified

    Press any key to continue . . .

    Why is it not finding the data source name? I can connect using the same DSN from excel so its not that.

  9. #9
    Registered User
    Join Date
    Dec 2007
    Posts
    2,675
    Doesn't your connection string have to be (at least) "DSN=diadus"?

    EDIT: Actually, the bottom of this page shows the minimum requirements for calling the SQLDriverConnect function as you are doing.
    Last edited by rags_to_riches; 08-22-2008 at 02:56 PM.

  10. #10
    Registered User
    Join Date
    Aug 2008
    Posts
    5
    Figure it out, for anyone interested set the character encoding to "Use Multi-Byte Character Set" and make sure your DSN is setup as system.

    Thanks to all who responded.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Enforcing Machine Code Restrictions?
    By SMurf in forum Tech Board
    Replies: 21
    Last Post: 03-30-2009, 07:34 AM
  2. Values changing without reason?
    By subtled in forum C Programming
    Replies: 2
    Last Post: 04-19-2007, 10:20 AM
  3. Obfuscated Code Contest
    By Stack Overflow in forum Contests Board
    Replies: 51
    Last Post: 01-21-2005, 04:17 PM
  4. Interface Question
    By smog890 in forum C Programming
    Replies: 11
    Last Post: 06-03-2002, 05:06 PM
  5. Linking error using ODBC with C and BCC32
    By D@nnus in forum C++ Programming
    Replies: 0
    Last Post: 11-17-2001, 05:11 AM