Invalid Connection ID SQLite

Deploying to Windows? Utilizing VB Script execution? This is the place to ask Windows-specific questions.

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

SkinnyKenny
Posts: 28
Joined: Fri Jan 24, 2020 6:44 am

Invalid Connection ID SQLite

Post by SkinnyKenny » Thu Feb 06, 2020 2:08 am

Hello all,
I have a question regarding connecting to a SQLite database or rather connecting to two different databases from a single stack. So basically I have two datagrids in a stack on separate cards that I display data from two SQLite databases I have created. When I connect to one of them, everything is fine and I close the DB connection before moving to the next card. The issue is when I make a new connection to the second DB query the database, it gives me an invalid ID connection error. I assume it has something to do with the first connection being open first. I am using the basic syntax given in the LC lessons to connect to a SQLite DB. So overall, my question would be this....Do I need to create a completely different connection string with different variables if I need to connect to multiple databases? Here is my connection code...

Code: Select all

command databaseConnectDailyLog
   local tDatabasePath
   ## The database must be in a writeable location
   put specialFolderPath("0x0023") & "/dailyLogDB.sqlite" into tDatabasePath
   
   ## Open a connection to the database
   ## If the database does not already exist it will be created
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
   
   ## Store the database id so other handlers can access it
   setDatabaseID tDatabaseID
end databaseConnectDailyLog

SkinnyKenny
Posts: 28
Joined: Fri Jan 24, 2020 6:44 am

Re: Invalid Connection ID SQLite

Post by SkinnyKenny » Thu Feb 06, 2020 3:28 am

Some more clarification....If I open the first database, close it and go to the next card and access the second database, I can add records and retrieve them. It is only when I attempt to delete an entry from the SQLite DB does it give me a connection ID error. I'll show you my call to delete records below.

button "Delete Entry": execution error at line n/a (External handler execution error: revdberr,invalid connection id) near "revdberr,invalid connection id"

Code: Select all

global tDatabaseID
local tDate, tTime
on mouseUp
   put getDatabaseID() into tDatabaseID
   //databaseConnectMedAdmin
   answer tDatabaseID
   put the dgHilitedLines of group "adminLog" into theLine
   put the dgDataOfLine[theLine] of group "adminLog" into theDataA
   DGH_DeleteLines "adminLog"
   put theDataA["Date"] into tDate
   put theDataA["Time"] into tTime
   
   
   put "DELETE FROM medAdmin WHERE (t_Date = '"&tDate&"' and t_Time = '"&tTime&"')" into tSQL
   
   revExecuteSQL, tDatabaseID, tSQL
   
end mouseUp

Klaus
Posts: 12497
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Invalid Connection ID SQLite

Post by Klaus » Thu Feb 06, 2020 10:58 am

Hi Kenny,
Do I need to create a completely different connection string with different variables if I need to connect to multiple databases?
not really, unless you want to access both db files at the same time.

But please show us the actual handler that opens and closes the databases.
You do close one db before opening the other one, right?

Maybe some (local of global) variable is not being updated to the new db connection?
Or a conncetion cannot be established, I see you do no error checking!?

Code: Select all

...
## Like this:
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
if tDatabaseID is not a number then
   ## PROBLEM!
  answer "Error:" && tDatabaseID
  exit to top
end if
...
And what will specialfolderpath("("0x0023") resolve to? 8)


Best

Klaus

SkinnyKenny
Posts: 28
Joined: Fri Jan 24, 2020 6:44 am

Re: Invalid Connection ID SQLite

Post by SkinnyKenny » Thu Feb 06, 2020 11:23 am

Ok, here is the entire code I use in the stack script in order to handle the commands:

*So this is how I connect. The "0x0023" is the shared user folder for multiple users in the Window's environment.

Code: Select all

on databaseConnectMedAdmin
   local tDatabasePath
   ## The database must be in a writeable location
   put specialFolderPath("0x0023") & "/medAdminDB1.sqlite" into tDatabasePath
   
   ## Open a connection to the database
   ## If the database does not already exist it will be created
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
   
   ## Store the database id so other handlers can access it
   setDatabaseID tDatabaseID
end databaseConnectMedAdmin

*Create my table

on databaseCreateMedTable
   ## Add a contact_details table to the database
   put getDatabaseID() into tDatabaseID
   put "CREATE TABLE medAdmin (t_Date varchar, t_Time int, supervisor varchar, callNumber int, patientName varchar, medication varchar, adminAmount varchar, wastedAmount int, paramedic varchar, hospital varchar, oldTag int, newTag int, Comments varchar)" into tSQL
   revExecuteSQL tDatabaseID, tSQL
   end databaseCreateMedTable
   
   *Insert my data into the table. I have local global variables defined so I can use them in my card objects (buttons).
   
   on databaseInsertMedLog
   global tHospitals
   ## Insert names and email addresses into the database
   put getDatabaseID() into tDatabaseID
   put "INSERT into medAdmin VALUES ('"&tDate&"', '"&tTime&"', '"&tSup&"', '"&tCallNumber&"', '"&tPatientName&"', '"&tMedication&"', '"&tAdminAmount&"', '"&tWastedAmount&"', '"&tParamedic&"', '"&tHospitals&"', '"&tOldTag&"', '"&tNewTag&"','"&vComments&"');" into tSQL
   
   revExecuteSQL tDatabaseID, tSQL
end databaseInsertMedLog

command setDatabaseID pDatabaseID
	put pDatabaseID into sDatabaseID
end setDatabaseID
function getDatabaseID
   return sDatabaseID
end getDatabaseID

*Close the DB....I have no idea how to error check this...Iv'e been reading up on it
on closeDatabaseMedAdmin
   if getDatabaseID() is not empty then
      revCloseDatabase sDatabaseID
      put empty into sDatabaseID
      put empty into tDatabaseID
   else
     
   end if
end closeDatabaseMedAdmin

Klaus
Posts: 12497
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Invalid Connection ID SQLite

Post by Klaus » Thu Feb 06, 2020 11:32 am

I see the handler "closeDatabaseMedAdmin" but not if and where you call it?
And all I see is ONE database that you open?

SkinnyKenny
Posts: 28
Joined: Fri Jan 24, 2020 6:44 am

Re: Invalid Connection ID SQLite

Post by SkinnyKenny » Thu Feb 06, 2020 12:13 pm

Sorry Klaus...I'm just not being descriptive enough I guess. What I do is one card has a datagrid that uses the "databaseConnectDailyLog" command to open the "dailyLogDB". The second card has a datagrid that uses the "databaseConnectMedAdmin" command to connect and add data to that particular DB. Switching between cards, I use the "on closeCard" handler to close each database as I move between cards. I have no problem adding data or updating data for each database, it's just when I attempt to delete an entry from the "medAdminDB" that it gives me the error.

(Open and closes the DB when I switch between cards. I just call the handlers in the stack script I defined above.)

Code: Select all

on closeCard
   DGH_Search_Clear "adminLog"
   closeDatabaseMedAdmin
end closeCard

Code: Select all

on openCard
   databaseConnectMedAdmin
end openCard
(This is the second DB that I use. The commands are in the stack script along with my other DB connection script)

Code: Select all

command databaseConnectDailyLog
   local tDatabasePath
   ## The database must be in a writeable location
   put specialFolderPath("0x0023") & "/dailyLogDB.sqlite" into tDatabasePath
   
   ## Open a connection to the database
   ## If the database does not already exist it will be created
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
   
   ## Store the database id so other handlers can access it
   setDatabaseID tDatabaseID
end databaseConnectDailyLog

THIS IS MY SECOND DB CONNECTION:
on databaseConnectMedAdmin
   local tDatabasePath
   ## The database must be in a writeable location
   put specialFolderPath("0x0023") & "/medAdminDB1.sqlite" into tDatabasePath
   
   ## Open a connection to the database
   ## If the database does not already exist it will be created
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
   
   ## Store the database id so other handlers can access it
   setDatabaseID tDatabaseID
end databaseConnectMedAdmin

HOW I CLOSE DailyLog DB

on closeDatabaseDailyLog
//answer sDatabaseID
   if getDatabaseID() is not empty then
      revCloseDatabase sDatabaseID
      put empty into sDatabaseID
      put empty into tDatabaseID
   else
   end if
end closeDatabaseDailyLog

HOW I CLOSE medAdminDB (Basically same thing)
on closeDatabaseMedAdmin
   if getDatabaseID() is not empty then
      revCloseDatabase sDatabaseID
      put empty into sDatabaseID
      put empty into tDatabaseID
   else
     
   end if
end closeDatabaseMedAdmin
I am prob making this way to complicated. I am sorry. If this is getting to strange, I'll understand if you can't follow my messed up coding and explanations.

Klaus
Posts: 12497
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Invalid Connection ID SQLite

Post by Klaus » Thu Feb 06, 2020 12:18 pm

To avoid all these possible inconveniences, I would heavily recommend to use
two different variables for the different DB connections!
-> tDatabaseID_Med
-> tDatabaseID_Log

Or open the db when neccessary and close immediately after fetching data!
Opening a local SQLite db files take about 1 or 2 millisecs, so no actual overhead here.
I have been doing this for years without problems.
The "0x0023" is the shared user folder for multiple users in the Window's environment.
Ah, I see, you can even save some typing by using: specialfolderpath(35)
That is the same folder.
For the current user only use: specialfolderpath(26)

SkinnyKenny
Posts: 28
Joined: Fri Jan 24, 2020 6:44 am

Re: Invalid Connection ID SQLite

Post by SkinnyKenny » Thu Feb 06, 2020 12:21 pm

Thank you! I will give that a try and report back.

SkinnyKenny
Posts: 28
Joined: Fri Jan 24, 2020 6:44 am

Re: Invalid Connection ID SQLite

Post by SkinnyKenny » Thu Feb 06, 2020 1:06 pm

So what's strange is this....I changed all the functions and commands with new variables. Just to check if when I connect to the DB it has the same connection number, I put "answer tDatabaseID_med" in the connection command and "answer tDatabaseID_med" when its called in my "Delete" button to see if they match. They do. So when I call "getDatabaseID_med()" in my delete button it matches the initial database connection ID. It still says invalid connection ID. I assume if I want to use "revExecuteSQL, tDatabaseID_med, tSQL" the tDatabaseID_med connection has to match the initial connection ID.

Code: Select all

on databaseConnectMedAdmin
   local tDatabasePath_med
   ## The database must be in a writeable location
   put specialFolderPath("desktop") & "/medAdminDB1.sqlite" into tDatabasePath_med
   
   ## Open a connection to the database
   ## If the database does not already exist it will be created
   put revOpenDatabase("sqlite", tDatabasePath_med, , , , ) into tDatabaseID_med
   answer tDatabaseID_med
   ## Store the database id so other handlers can access it
   setDatabaseID_med tDatabaseID_med
end databaseConnectMedAdmin

Code: Select all

   put getDatabaseID_med() into tDatabaseID_med
  answer tDatabaseID_med
   put the dgHilitedLines of group "adminLog" into theLine
   put the dgDataOfLine[theLine] of group "adminLog" into theDataA
   DGH_DeleteLines "adminLog"
   put theDataA["Date"] into tDate
   put theDataA["Time"] into tTime
   put "DELETE FROM medAdmin WHERE (t_Date = '"&tDate&"' and t_Time = '"&tTime&"')" into tSQL
   
   revExecuteSQL, tDatabaseID_med, tSQL

Code: Select all

command setDatabaseID_med pDatabaseID
	put pDatabaseID into sDatabaseID_med
 end setDatabaseID_med

function getDatabaseID_med
   return sDatabaseID_med
end getDatabaseID_med

Klaus
Posts: 12497
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Invalid Connection ID SQLite

Post by Klaus » Thu Feb 06, 2020 1:13 pm

Very strange!? :shock:
Unless you close and reopen a db the connection ID should be the same as "the initial connection ID"!

No idea what's going wrong on your side, but maybe opening and closing the db file as mentioned in
my last posting is the way to avoid this!?

SkinnyKenny
Posts: 28
Joined: Fri Jan 24, 2020 6:44 am

Re: Invalid Connection ID SQLite

Post by SkinnyKenny » Thu Feb 06, 2020 1:43 pm

I'm going to fool with it after some rest. Iv'e been up all night looking at this and can't take anymore. Maybe I have the command for closing the DB incorrect. Honestly, there really isn't a lot of info in the lessons on how to properly close a SQLite DB. There is one on closing a MYSQL, but it doesn't make much sense to me. I assume it's as easy as:

Code: Select all

on closeDB
put getDatabaseID_med() into tDatabaseID_med
if tDatabaseID_med is empty then
pass closeDB
else
revCloseDatabase tDatabaseID_med
end CloseDB
[code]

Klaus
Posts: 12497
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Invalid Connection ID SQLite

Post by Klaus » Thu Feb 06, 2020 2:00 pm

Hi Kenny,

yes, that's it:

Code: Select all

on closeDB
  put getDatabaseID_med() into tDatabaseID_med
  if tDatabaseID_med <> empty then
     revCloseDatabase tDatabaseID_med
  end if
end CloseDB
Since you will surely not have another CLOSEDB handler in your stack, no need to -> pass closedb.

The only problem that could arise is to provide an invalid conncetion ID!
To be on the safe side, you could TRY to close the DB:

Code: Select all

on closeDB
  put getDatabaseID_med() into tDatabaseID_med
  if tDatabaseID_med <> empty then
     try
       revCloseDatabase tDatabaseID_med
     end try
  end if
end CloseDB
This will well TRY to close the db file but will NOT throw an error if you provide an invalid db connection ID,
unless you script so like:

Code: Select all

on closeDB
  put getDatabaseID_med() into tDatabaseID_med
  if tDatabaseID_med <> empty then
     try
       revCloseDatabase tDatabaseID_med
       
       ## LC will put any eventual errors into this variable (whatever its name is!)
     catch the_error
       ## Do this in case of error
       answer "An error has occurred:" && the_error
     end try
  end if
end CloseDB
Best

Klaus

SkinnyKenny
Posts: 28
Joined: Fri Jan 24, 2020 6:44 am

Re: Invalid Connection ID SQLite

Post by SkinnyKenny » Mon Feb 10, 2020 8:52 am

So Klaus,
I am curious about the closeDB function you helped me with. Just to be sure I wasn't getting a previous connection mixed in while trying to disconnect, I created a new stack and copied the tutorial code for connecting to a SQLite DB. I am having no issues connecting or inserting info into the newly created DB but when I attempt to disconnect I keep getting this error:

634,0,0,revdberr,invalid connection id
573,43,1,revCloseDatabase

I am unsure what the numbers represent. Any ideas?

Klaus
Posts: 12497
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Invalid Connection ID SQLite

Post by Klaus » Mon Feb 10, 2020 8:42 pm

Hi Kenny,
634,0,0,revdberr,invalid connection id
634 means there was an error in an external, revDB is here the external
573,43,1,revCloseDatabase
573 means "Handler not found", that that completely puzzles me!?
Handler not found? With "revclosedatabase"? Clueless! :shock:


Best

Klaus

SkinnyKenny
Posts: 28
Joined: Fri Jan 24, 2020 6:44 am

Re: Invalid Connection ID SQLite

Post by SkinnyKenny » Mon Feb 10, 2020 10:17 pm

Yeah, I have not been able to do anything further with my 2 DB's because it continually throws this error. I am thinking of two options....1 start a completely different stack and set my DB's there then attach it to my mainstack or 2 uninstall LC and run a fresh installation. Otherwise, I am stuck. Thank you for the information though, at least I don't feel like it is something that I have done in my code. I'll update this thread.

Post Reply

Return to “Windows”