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.

-- 
Regards, Chuck
-- 
This is the Bare Metal Programming IBM i (AS/400 and iSeries) (C400-L) mailing list
To post a message email: C400-L-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/c400-l
or email: C400-L-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/c400-l.