Re: Starting to experiment with Scott Klement's JDBCR4 -- what's the DB2/400 equivalent to MySQL "USE"

Hi James,

The SQL statements need to be in the proper syntax for the database server you are communicating with. Thus a MySQL statement like "use MYDATABASE" will not work on a DB2 for i server. The syntax on DB2 for i would be something like "set schema MYDATABASE"

Though, working with DB2 for i, I'm more likely to use the libraries property and system naming convention so I can use *LIBL and friends.

You always have to code your statements for the target database.

For example, you could connect like this:

prop = JDBC_Properties();
JDBC_setProp(prop: 'user' : 'your-userid' );
JDBC_setProp(prop: 'password': 'your-password' );
JDBC_setProp(prop: 'prompt' : 'false');
JDBC_setProp(prop: 'errors' : 'full');
JDBC_setProp(prop: 'naming' : 'system');
JDBC_setProp(prop: 'libraries': 'FILELIB,QGPL,QTEMP,ANOTHERLIB');

conn = JDBC_ConnProp( 'com.ibm.as400.access.AS400JDBCDriver'
: 'jdbc:as400://localhost'
: prop );
JDBC_freeProp(prop);


So after that connects, your library list (just for the JDBC connection) will be FILELIB, QGPL, QTEMP and ANOTHERLIB. SQL statements will search that library list.

Or, if you want, you can use *LIBL as a placeholder for the default library list on the system, but you can add on to the list. (Kinda like ADDLIBLE). For example:

JDBC_setProp(prop: 'libraries': 'APPLIB,*LIBL');

In this case, you're setting the library list to APPLIB, followed by your default library list, because *LIBL represents the default, here.

Anyway, this is very different from MySQL. In MySQL there's no concept of library lists. The "Use Testsck" was saying that all subsequent SQL statements use that database ("library") until another USE statement is issued.



On 2/1/2013 5:04 PM, James H. H. Lampert wrote:

Scott, I hope you're feeling better; don't push yourself on my account.
I'm sure anybody familiar with both MySQL and DB2/400 can answer this,
whether they know JDBCR4 or not.

At any rate, it's like the subject line says.

I haven't really had a good opportunity to install MySQL someplace with
a fixed IP address, and so I'm running the first tests of JDBCR4
pointing right back to the local DB2/400 on the very same box.

I changed a copy of the JDBCTEST source member, so that it goes into the
local DB2/400 (talk about carrying coals to Newcastle!) instead of a
MySQL server. I successfully got rid of the parameters on the CREATE
DATABASE that DB2/400 didn't like, and it successfully created the
TESTSCK library, with all the extra baggage of an SQL collection.

Then it tried a USE statement. Which is perfectly fine in MySQL, but
DB2/400 didn't like it. And while a quick RTFM confirmed that DB2/400
doesn't have a USE statement, I haven't a clue what the equivalent would be.

--
JHHL