Re: C++, SQLCLI: Making a remote DB2 connection

On 21-Feb-2014 11:51 -0800, Kelly Beard wrote:

<<SNIP>> <ed: using the SQL CLI> create your db connection handle
then connect using:
sqlRet = SQLConnect(cv_hSqlDbc, databaseName, SQL_NTS, user,
SQL_NTS, password, SQL_NTS);

I'm setting a default library with my connection (I'm doing this
after the connect; doesn't seem to be a problem)
sqlRet = SQLSetConnectAttr(cv_hSqlDbc, SQL_ATTR_DBC_DEFAULT_LIB,
(SQLPOINTER)library.c_str(), (SQLINTEGER)library.length());

According to the docs, in order for this to work, you have to set
system naming off (do this step before the above SetConnectAttr()).
SQLINTEGER sysnaming = SQL_FALSE;
SQLPOINTER option;
option = &sysnaming;
sqlRet = SQLSetConnectOption(cv_hSqlDbc, SQL_ATTR_DBC_SYS_NAMING,
option);

This seems to work OK, if I have two connections and then 'ask' what
their default library is, the two report back the correct answers
(the default library is different for each connection).

char buf[100];
long olen;
SQLGetConnectAttr(local_db.getConnectionHandle(),
SQL_ATTR_DBC_DEFAULT_LIB, (SQLPOINTER)buf, 100, &olen);
cout<< "local lib="<< buf<< endl;
SQLGetConnectAttr(remote_db.getConnectionHandle(),
SQL_ATTR_DBC_DEFAULT_LIB, (SQLPOINTER)buf, 100, &olen);
cout<< "remote lib="<< buf<< endl;

local lib=BOBV
remote lib=DF99328

BUT (with IBM, C++, etc there always seems to be a 'but') - if I do
an unqualified SELECT on a couple of tables, one of the connections
is using the default database of the other connection. I'm trying to
figure this out

SQLSelectExecute(local_db, "SELECT * FROM DRIVERS_SNAPSHOT", rows);

SQLSelectExecute(remote_db, "SELECT * FROM UNITS_SNAPSHOT", rows);

In this instance, UNITS_SNAPSHOT is on a remote system and I've set
the default library for it to be DF99328, but the connection
information is trying to use BOBV.

SQLSelectExecute.cpp(67): sqlRet=-1 NativeError=-204 SqlState=42704
Message=UNITS_SNAPSHOT in BOBV type *FILE not found.

This connection should effectively be saying SELECT * FROM
DF99328.UNITS_SNAPSHOT

That's the only thing I need to figure out.

AFaIK the separate environments need to be running in /server mode/ instead of without server mode to avoid any conflicts.
<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/cli/rzadpsqlapd.htm>

AFaIK the SQL_ATTR_DBC_DEFAULT_LIB should be manifest as the CURRENT SCHEMA. I suggest using SELECT CURRENT_SCHEMA FROM SYSIBM.SYSDUMMY1 for each connection to examine the results... after changing that CLI connection attribute.

Seems one could just issue a SET CURRENT SCHEMA DF99328 to modify the attributes of the connection to establish the library DF99328 as the default library for unqualified table references, after the connection is already established, rather than trying to effect that modification of an existing connection using the SQLSetConnectAttr <http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/cli/rzadpfnsconx.htm>; i.e. instead of using the deprecated SQLSetConnectOption <http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/cli/rzadpfnscono.htm>. The same deprecation is noted in the corresponding "get" requests as well. A cursory glance as the given code seems to show them used interchangeably.