Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2009
    Location
    Fujian, China
    Posts
    1

    Unanswered: Failed to connect to DB2 server via ODBC

    Hello, everyone
    I'm a University student from Fujian normal university.
    I'm now a beginner to database and recently I was learning something about the database and trying to use the database with ODBC.
    These codes works well with other databases like sqlite3, postgres and mssql2005ex.
    when I turn to db2, problem comes out.
    Probably it's me that don't know how db2's interface works.

    I have db2 9.5.0 Express installed in Windows 2003 in VirtualBox, the same environment when I tested my codes in mssql. My codes runs on Vista homebasic, the compiler is VC2008Ex. I installed the driver from DSDriver.zip (but during the installing a process called db2lswtchCLI.exe crashes) and setup the odbc like this,
    IBM DB2 ODBC DRIVER, dabasource name leiming, userid db2admin, pw 0019, DBName=leiming, Hostname=192.168.100.50, Port=50000
    the result is that SQLGetDiagRec tells me "SQL1013N Invaild database name or alia name "LEIMING"! SQLSTATE=42705" (translated from Chinese).

    In the system running in VirtualBox, I tried "db2 connect to leiming user db2admin using 0019", it works.

    this is my codes:
    Code:
    #define SQL_NOUNICODEMAP
    #include <windows.h>
    #include <sqlext.h>
    #include <iostream>
    using namespace std;
    
    int main(){
    	SQLHENV henv;
    	SQLHDBC hdbc;
    	SQLHSTMT hstmt;
    	SQLRETURN r;
    	SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    	SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    	SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    	r = SQLConnect(hdbc, (SQLCHAR*)"leiming", SQL_NTS, (SQLCHAR*)"db2admin", SQL_NTS, (SQLCHAR*)"0019", SQL_NTS);
    	if(r < 0){
    		SQLCHAR buf[600];
    		SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, 0, 0, buf, 600, 0);
    		cout << "Failed to connect to the ODBC data source!" << endl << buf << endl;
    		return 0;
    	}
    
    	SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    	SQLExecDirect(hstmt, (SQLCHAR*)"create table stu(name varchar(10), id char(12), math integer, english integer)", SQL_NTS);
    	SQLExecDirect(hstmt, (SQLCHAR*)"insert into stu values ('JJ', '123012007002', 80, 75)", SQL_NTS);
    	SQLExecDirect(hstmt, (SQLCHAR*)"insert into stu values ('GK', '123012007003', 90, 65)", SQL_NTS);
    	SQLExecDirect(hstmt, (SQLCHAR*)"insert into stu values ('LJ', '123012007004', 75, 80)", SQL_NTS);
    	SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    
    	SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    	SQLExecDirect(hstmt, (SQLCHAR*)"select count(*) from stu", SQL_NTS);
    	SQLFetch(hstmt);
    	long rcount;
    	SQLGetData(hstmt, 1, SQL_C_SLONG, &rcount, 4, NULL);
    	SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    
    	SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    	SQLExecDirect(hstmt, (SQLCHAR*)"select * from stu", SQL_NTS);
    	short ccount;
    	SQLNumResultCols(hstmt, &ccount);
    	cout << "Every recode has " << ccount << " fields" << endl;
    	cout << "There are " << rcount << " recodes in total" << endl;
    	int j;
    	while(SQL_SUCCEEDED(SQLFetch(hstmt))){
    		for(j = 1; j <= ccount; j++){
    			char r[1024];
    			SQLGetData(hstmt, j, SQL_C_CHAR, &r, 1024, NULL);
    			cout << r << "   ";
    		}
    		cout << endl;
    	}
    	SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    
    	SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    	SQLExecDirect(hstmt, (SQLCHAR*)"drop table stu", SQL_NTS);
    	SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    
    	SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    	SQLFreeHandle(SQL_HANDLE_ENV, henv);
    	return 0;
    }
    this is the result if working with postgres:
    Code:
    Every recode has 4 fields
    There are 3 recodes in total
    JJ   123012007002   80   75
    GK   123012007003   90   65
    LJ   123012007004   75   80
    Is db2 of great difference from other databases? How can I do?

    And another question,
    Shall I allocate a SQL_HANDLE_STMT each time I want to send a sql statement?

    Thanks.
    Last edited by LeiMing; 01-09-09 at 22:50.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •