SQLTools: stopping and restarting SQLTools

[Shawn Anderson]

Normally I init my database with SqlTools when I start my prog and shut it down when I'm done using sql_shutdown.

This time, I want to shut everything down and later in the same program, restart it.

Do I have to Authorize and Init SQL tools again or only re-connect to my database? I can't get either way to work as of now...

thanks!

[Bud Durland]

Once you've done SQL_Shutdown, i don't think you'll be able to reconnect. I suspect that the SQL_Authorize would stick; you'd just need to do SQL_Initialize again.

[Shawn Anderson]

here's how I'm doing it but it's not working on the re-open

Function openDatabase() As String  
	Local connectionString As String        
  
             If initDB=0 Then
	  ' authorize sql tools once only
	    SQL_Authorize &MyCode           
	    initDB=-1 ' global var- flag if already authorized
	End If

	SQL_Initialize 2,2,50,3,3,0,0,0  
                                                              
    connectionString="Driver={Microsoft Access Driver (*.mdb)};Dbq="+programDir+"\data\inspector.mdb;Uid=Admin;Pwd= ;"    
    
    sql_openDatabase(%inspectorDB,connectionString,%PROMPT_TYPE_NOPROMPT)    
    
    FUNCTION = SQL_ErrorQuickAll 

End Function

[Eric Pearson]

Shawn --

Sorry, but SQL Tools will not allow you to use SQL_Initialize more than once, so SQL_Shutdown is "permanent". From the SQL_Initialize docs:

If an attempt is made to re-initialize SQL Tools after it has been successfully initialized, ERROR_CANT_BE_DONE will be returned.

So once your program calls SQL_Shutdown, you're done using SQL Tools functions. Why do you want to? Maybe I can suggest an alternative.

-- Eric Pearson, Perfect Sync, Inc.

[Michael Mattias]

> From the SQL_Initialize docs: If an attempt is made to re-initialize SQL Tools ....

What if you load the library dynamically, initialize, use, shutdown, freelibrary, and start over?

Not that this is a good idea or a bad idea. Seems to me intitializing a library at start and shutting it down at the end makes perfect sense.

What is IMO a bad idea is maintaining an open connection to a database, but I know SQL Tools lets you connect and disconnect at will.


MCM

[Eric Pearson]

> What if you load the library dynamically, initialize,
> use, shutdown, freelibrary, and start over?

Sure, I suppose that would work. But using the GetProcAddress API to find each function, using CALL DWORD for every call to a SQL Tools function... seems like a lot of extra work for no real gain (as far as I know).

-- Eric

[Michael Mattias]

I was guessing that "Initialize" creates some GLOBAL or STATIC or THREADED control structures which are cleaned up (deallocated) on DLL_PROCESS_DETACH.

I guess I made a good guess.

MCM

[Shawn Anderson]

Here's what I'm doing:
I have a local app on a roaming field laptop that keeps a database.
Occasionally, that local computer will sync up it's data with an online SQL server.

My original plan opens the local db for the user's everyday work.
Then, when they want to sync, it opens the remote db and syncs up the data. I wanted then to close the remote db when I was done.

Anyway, I think my work around will be to open (by open I mean authorized and init and in sqltools) the remote db when they want to sync and it will just stay open until the program is closed and sql_shutdown is ran.
Otherwise, if they try to sync again, I'll have to test for connectivity before I can write to the db again (which I need to do anyway). I'll use a flag to decide if the remote db has already been init'd and only do it once.

Thanks!

[Eric Pearson]

To be clear, SQL_Authorize and SQL_Initialize do not make actual database connections. We use SQL_OpenDatabase for that, and you can use SQL_CloseDatabase as well. You can open and close database connections at will, as many times as you like. It's just the Auth/Init/Shutdown functions that can't be used repeatedly.

If you close a database connection with SQL_CloseDatabase (or SQL_CloseDB) then there is virtually no penalty for keeping SQL Tools active in a program. Other than a little memory, of course. No reason to call SQL_Shutdown until you are truly done.

-- Eric

[Shawn Anderson]

Yes, sorry my terminology is too loose.

OK, so I think the way to go is to go ahead and init the database whenever but to open it when I need to write to it and close it when I'm done.
That way I can troubleshoot if I have connectivity (ie: internet connection available) by whether or not the db opens. That is a better solution to what I was going to do (ping or similar).

[Michael Mattias]

>my terminology is too loose...

Here?

Say it ain't so, Joe!

[Eric Pearson]

Shawn, that sounds like a good plan.

Michael,

> DLL_PROCESS_DETACH

Actually, that can cause trouble with the ODBC subsystem, hence the need for an explicit call to a Shutdown function before the DLL is unloaded.

But overall you're right, SQL_Initialize sets up a bunch of arrays and other control structures, which are then destroyed by SQL_Shutdown. There are currently no provisions for re-dimming the arrays (etc.) because it's not necessary when the system is used as designed.

-- Eric

[Michael Mattias]

>because it's not necessary when the system is used as designed.

I wonder how much code has been written over the years to support users who wanted software to work "NOT as designed?"

[Shawn Anderson]

well at least I didn't use my normal technical terms of "thing-a-ma-jigger" or "wutch-ya-ma-call-it".

[Michael Mattias]

>my normal technical terms of "thing-a-ma-jigger" or "wutch-ya-ma-call-it

Those I might excuse, since it would be obvious you are at least trying to recall the correct teminology.

What I have a problem with is "This code crashes." I would like to think anyone who is permitted to compile programs and execute them would know the difference between "won't compile," "does not execute to completion" and "produces incorrect output."

MCM

[Shawn Anderson]

I will note that in my do-ma-flach-ee

[Michael Mattias]

For our friends in West Virginia:

"thingamajig", "watchamacallit" and all these other terms are synonyms for "thang."

If you'll just tell your cousins that ought to cover everyone in your state.