Re: ESQL/C documentation nightmare

On Sun, May 4, 2008 at 11:29 AM, Jonathan Leffler <jleffler.iiug@gmail.com> wrote: > 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. Oh, my bad. I somehow grouped all database statements together in my mind and put them all of them in the list of backwards compatibility statements. The open nature of "such as" didn't help me much here. > > > 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). It's clearer to me now. CREATE DATABASE establishes an implicit connection to the database just created, no matter how you execute it. Previous experiences with other DBMS made me assume a different behavior. My bad. > > 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.] That's the subject of my previous message by the way. Is it expected?, is it a bug? I have just found (again, by experimentation) that such a problem doesn't appear when I previously connect to a server, without opening a database. ------------- >8 ------------- $ esql rename_and_connect.ec -o x && ./x11: connected to anydb? 0 12: connected to @server? 0 13: test renamed to test2? 0 15: connected to test2? 0 $ cat rename_and_connect.ec #include <stdio.h> #define psqlcode(msg) do \\ { \\ printf("%d: %s %d\\n", __LINE__, msg, SQLCODE); fflush(stdout); \\ } while(0) int main(void) { EXEC SQL connect to 'anydb'; psqlcode("connected to anydb?"); EXEC SQL connect to '@server'; psqlcode("connected to server?"); EXEC SQL execute immediate 'rename database test to test2'; psqlcode("test renamed to test2?"); EXEC SQL connect to 'test2'; psqlcode("connected to test2?"); } ------------- 8< ------------- > > > > 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. Indeed! Much better. > > 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