I have almost mastered connection - ids to an MSSQL database.

The point of this post is to suggest a blog entry would be nice with practical examples of how they can be used because i struggled quite a bit to assemble what was required to make all this work. I ramble on about some matters that may be helpful to include if anyone did create a blog.

Clearly SQL databases in a single database work very easily but the moment Connection-ids are used then there is a whole level of mysterious management to understand.

I my case - one program needs to maintain about 200 databases with identical files.
The database required is chosen from a grid and:

  1. Existing files and data entry screens are closed.
  2. Files dropped with dfclose_all
  3. Logout
  4. Connection-id is deleted and recreated to new database with silent login
  5. files reopened.

That now seems to work ok.

Where i struggle is that despite a default connection id in the MSSQLDRV.int file
DB Builder and DB Explorer to not seem to acknowledge that default connection_ID

DB Explorer does have the facility to create a connection-id.
DB Builder does not.

I think it may be helpful if the help documentation was expanded to explain first where connection-id's are stored or if they are session only.I also think it would be helpful to expand the documentation surrounding Connection strings distinguishing when they are required to have the Database= section.

Lastly i encourage some more code in the classes to do things like.
DoesConnectionIdExist.
DoesDatabaseExist.
Seamlessly to points 1 to 5 above.
Create or Delete SQL database (Not tables)
Ability to run scripts

The later was interesting MSSQL script files appear to have a double byte format where the first is a character and the second is "00" exeamine one in a hex editor and you will see what i mean. I read a xxx.sql file by executing two read_block single characters thowing away the second and formatting a string until i see a "go" then process the line through esql.

In my case a developed an MSSQL database with tables in MS Management Studio. Created a script from the studio then Edited the script with %1 parameter for the database name %2 for the mdf file and %3 for the Ldf file then as i process the script apply sFormat to the line and it works very well to create a whole new database.

Lastly I would encourage terminology changes where in files in particular refer to Database when they really mean table.