ESQL/C documentation nightmare

I just had a hard time trying to make sense from the ESQL/C online documentation about explicit and implicit connections. I wanted to create a database using ESQL/C while having multiple connections. I started reading this: http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.esqlc.doc/esqlc233.htm "Important: It is recommended that you use the CONNECT, DISCONNECT, and SET CONNECTION connection statements for new applications of Version 6.0 and later. For pre-6.0 versions, the SQL database statements (such as DATABASE, START DATABASE, and CLOSE DATABASE) remain valid for compatibility with earlier versions." Ok, I should stick with CONNECT, DISCONNECT and SET CONNECTION. If I want to create a database will use EXECUTE IMMEDIATE to avoid CREATE DATABASE. Then I run the following: EXEC SQL connect to 'test1'; EXEC SQL execute immediate 'create database test2'; and get: "-759 Cannot use database commands in an explicit database connection. If you use the CONNECT TO database@server syntax to connect to a database and server, you cannot select another database until you disconnect your current connection." Hmm, I'm not exactly using that syntax (I'm not specifying a dbserver) and was not expecting to select another database. By experimentation, I found that CREATE DATABASE and EXECUTE IMMEDIATE 'create database...' fail identically. Then I thought that the following paragraph should apply to both. http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.esqlc.doc/esqlc233.htm "Important: Use of the DATABASE, CREATE DATABASE, START DATABASE, CLOSE DATABASE, and DROP DATABASE statements is still valid with an explicit connection. However, in this context, refer only to databases that are local to the current connection in these statements; do not use the @server or //server syntax." I'm not specifying a server! Anyways, I keep experimenting and found that the following works: EXEC SQL CONNECT TO 'test1'; EXEC SQL CONNECT TO DEFAULT; /* or to '@dbserver' */ EXEC SQL EXECUTE IMMEDIATE 'create database test2'; EXEC SQL SET CONNECTION 'test1'; Ok, the trick is to connect to a server without opening a database before EXECUTE IMMEDIATE. I also found the following. http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.sqls.doc/sqls167.htm "After you create an explicit connection, you cannot use any database statement to create implicit connections until after you close the explicit connection." How come? I just did use a database statement!, after creating an explicit connection!, without disconnecting! I didn't expect to create an implicit connection though. I didn't want to make one. After more experimentation I got the following error. "-758 Cannot implicitly reconnect to the new server server_name. If you use the CONNECT TO statement to connect to a server, you cannot implicitly reconnect to another server through one of the DATABASE statements (DATABASE, START DATABASE, and so on). You must switch to it with the SET CONNECTION statement." Ah, now I understand. The online help was referring to implicitly connecting to a different dbserver by specifying one as part of the name of the database. Otherwise, I can use database statements and switch connections with SET CONNECTION. I don't have to disconnect. At the risk of being answered with a "yes, you are the only one, <insert your preferred insult here>" I'm asking: am I the only one that finds this documentation confusing? P.S. I still don't understand why do I need the trick of connecting with a server without opening a database to be able to create a database without losing my previous connections.

On Sat, May 3, 2008 at 10:22 PM, Gerardo Santana <gerardo.santana@gmail.com> wrote: > I just had a hard time trying to make sense from the ESQL/C online > documentation about explicit and implicit connections. Oh dear. > I wanted to create a database using ESQL/C while having multiple > connections. > > I started reading this: > http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.esqlc.doc/esqlc233.htm > > "Important: > It is recommended that you use the CONNECT, DISCONNECT, and SET > CONNECTION connection statements for new applications of Version 6.0 > and later. For pre-6.0 versions, the SQL database statements (such as > DATABASE, START DATABASE, and CLOSE DATABASE) remain valid for > compatibility with earlier versions." > > Ok, I should stick with CONNECT, DISCONNECT and SET CONNECTION. If I > want to create a database will use EXECUTE IMMEDIATE to avoid CREATE > DATABASE. Connecting to an existing database is different from creating a new one, but the basic advice here is OK, as far as it goes. > Then I run the following: > > EXEC SQL connect to 'test1'; > EXEC SQL execute immediate 'create database test2'; > > and get: > > "-759 Cannot use database commands in an explicit database > connection. > > If you use the CONNECT TO database@server syntax to connect to a database > and server, you cannot select another database until you disconnect your > current connection." The '@server' portion is optional and when omitted, the value of $INFORMIXSERVER is used instead. > Hmm, I'm not exactly using that syntax (I'm not specifying a dbserver) > and was not expecting to select another database. By experimentation, > I found that CREATE DATABASE and EXECUTE IMMEDIATE 'create > database...' fail identically. Yes. When you have connected (via CONNECT) to a database -- as distinct from a server -- then you cannot do any of the database operations (or, at least none of the database operations that select a different database; that means DATABASE, CREATE DATABASE, START DATABASE (for SE), ROLLFORWARD DATABASE (for SE again), or CLOSE DATABASE. You also cannot use DROP DATABASE. Oddly, you can use RENAME DATABASE (but not the current database, and when you do rename a database, you acquire some sort of - presumably shared - lock on the database). With SE, there isn't a server to connect to via '@server', so my comments should be interpreted as applying to IDS rather than SE. Black JL: sqlcmd -e 'create database aleph in dbspace' Black JL: sqlcmd -d stores SQL[2637]: drop database aleph; SQL -759: Cannot use database commands in an explicit database connection. SQLSTATE: IX000 at /dev/stdin:1 SQL[2638]: rename database aleph to beth; SQL[2639]: rename database stores to contrapunctus; SQL -359: Cannot drop or rename current database. SQLSTATE: IX000 at /dev/stdin:3 SQL[2641]: connect to '@black_17'; SQL[2642]: drop database beth; SQL -425: Database is currently opened by another user. ISAM -107: ISAM error: record is locked. SQLSTATE: IX000 at /dev/stdin:6 SQL[2643]: info connections; stores|stores||OnLine|logged|non-ANSI|with concurrent transactions|idle| @black_17|@black_17||OnLine|unlogged|non-ANSI|no concurrent transactions|current| SQL[2644]: set connection 'stores'; SQL[2645]: info tables where tabid = (select max(tabid) from systables where tabtype = 'T'); jleffler|audit_days|T|557 SQL[2647]: disconnect current; SQL[2648]: set connection '@black_17'; SQL[2649]: drop database beth; SQL[2650]: q; Black JL: [I think the lock held by the rename database statement is probably a buglet.] > Then I thought that the following paragraph should apply to both. > > http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.esqlc.doc/esqlc233.htm > > "Important: > Use of the DATABASE, CREATE DATABASE, START DATABASE, CLOSE DATABASE, > and DROP DATABASE statements is still valid with an explicit > connection. However, in this context, refer only to databases that are > local to the current connection in these statements; do not use the > @server or //server syntax." I think that information is plain misleading. It would be better written along the lines of: You can only use the DATABASE, CREATE DATABASE, (START DATABASE, ROLLFORWARD DATABASE), CLOSE DATABASE, and DROP DATABASE statements if either (a) no connection has yet been made to the server, or (b) the current connection was made directly to the server and not to a database using CONNECT TO '@server'. In case (a), an implicit connection to the server is established as if via the CONNECT TO '@server' notation. > I'm not specifying a server! Anyways, I keep experimenting and found > that the following works: > > EXEC SQL CONNECT TO 'test1'; > EXEC SQL CONNECT TO DEFAULT; /* or to '@dbserver' */ > EXEC SQL EXECUTE IMMEDIATE 'create database test2'; > EXEC SQL SET CONNECTION 'test1'; > > Ok, the trick is to connect to a server without opening a database > before EXECUTE IMMEDIATE. Yes - or do not have any connection established. > I also found the following. > > http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.sqls.doc/sqls167.htm > > "After you create an explicit connection, you cannot use any database > statement to create implicit connections until after you close the > explicit connection." > > How come? I just did use a database statement!, after creating an > explicit connection!, without disconnecting! I suppose that explanation could add "or unless you create a new DEFAULT connection or a new connection to '@server'", or words to that effect. I was not able to connect to IDS using the '//black_17' notation -- something I'd not tried recently (say, during this millennium). The point is, when a given connection is created explicitly by connecting to a specific database (as distinct from a server), you cannot do almost any operation that involves the keyword DATABASE -- RENAME DATABASE seems to be the exception. So, the statement above might be better amended to: "After you create an explicit connection to a database, you cannot use any database statement (other than RENAME DATABASE) with that as the current connection." (or 'using that connection' instead of 'with that ...'). > I didn't expect to create an implicit connection though. I didn't want > to make one. > > After more experimentation I got the following error. > > "-758 Cannot implicitly reconnect to the new server server_name. > > If you use the CONNECT TO statement to connect to a server, you cannot > implicitly reconnect to another server through one of the DATABASE > statements (DATABASE, START DATABASE, and so on). You must switch to > it with the SET CONNECTION statement." > > Ah, now I understand. The online help was referring to implicitly > connecting to a different dbserver by specifying one as part of the > name of the database. Otherwise, I can use database statements and > switch connections with