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:
- Existing files and data entry screens are closed.
- Files dropped with dfclose_all
- Logout
- Connection-id is deleted and recreated to new database with silent login
- 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.