Failed to connect to DB2 server via ODBC

LeiMing · #1 (**permalink**) 01-09-09, 21:35

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech