Database connection ids incrementing

[Friedrich Boettger]

Hi. I'm new to LiveCode but not to databases. (using Livecode 5.5.5 on a PC 64 bit, Intel I5, Win8, ODBC with MS Access 2013 and SQLite 3) In my experience, one attempts to reuse database ids to the extent possible, but the revOpenDatabase function seems to increment the id, both in my code and the user samples I've downloaded, including the sqlite sample that came with the development environment. There also seems to be a difference of opinion as to where to declare global variables, either outside or inside a command, or in a card or stack. I have a reasonable nube understanding of your messaging hierarchy, but I figure if I go by your sqlite sample, I should be on safe ground. The query part works fine but the adding and editing parts are looking for the global id, evidently, yielding a runreverr.
I've evolved several methods for closing the db connection, using the examples I could find, but they seem to be using a number other than the current db id, even though I think I should be catching it after the increment, since I'm passing the db id to the global variable after the RevOpenDatabase function (which is the one causing the increment).
As a user of VB and FoxPro, I've always declared globals once outside of a procedure and I don't understand the logic of declaring a global in a command, since to me that would remove the value you were presumably attempting to retain through your use of the "global".
What I'm asking is either for a failsafe db close routine (which I thought I had) or for a parameter on an open routine that would use previous db connection ids by default, or for something really obvious that I missed simply because I'm a nube. Thanks in advance for any enlightenment you might provide.

Examples from present code:
This line alone increments, as I assume it was designed to do by default (it does find and display the data source):
put revOpenDatabase("ODBC","cat", "CatData","","" ) into tDatabaseID

I've used infinite varieties of this, in and out of commands:
global sDatabaseID
put sDatabaseID into tDatabaseID (the local variable receiving the connection ID)

And I'm closing with various iterations of this:
command databaseClose
revCloseDatabase tDatabaseID
put empty into tDatabaseID
put empty into tDatabaseID
answer tDatabaseID
answer tDatabaseID
end databaseClose

[bangkok]

Yes the command

Code: Select all

put revOpenDatabase("ODBC","cat", "CatData","","" ) into tDatabaseID

will create an integer into tDatabaseID, if the connection is successful.

Declare tDatabaseID as a global, and you are good to go to use it into all your scripts.

And to close everything, here is a generic script .

Code: Select all

on closeDB
put revOpenDatabases() into myDatabases
   repeat for each item myBase in myDatabases
      revCloseDatabase myBase
   end repeat
end closeDB

[Klaus]

Hallo Friedrich,

the "database id" that you need in Livecode should better be called "connection ID"!
That's what it is, the ID of a database connection which Livecode manages internally.
So you cannot change this ID! And you don't need to

GLOBALS need to be declared in every handler or script where you use it!
Examples
1. SIngle handler script:

Code: Select all

on mouseup
  global gGlobalVar
  put 1213 into gGlobalVar
end mouseup

2. Multi handler script (stack or card script maybe), declare it at the top of the script and
every handler in the script can access it!

Code: Select all

global gGlobalVar

on openstack
   put 100 into gGLobalVar
end openstack

on any_custom_hanlder
  answser gGLobalVar
end any_custom_hanlder

on closestack
   put empty into gGLobalVar
end closestack

## etc...

Hope that helps!


Best

Klaus

[Friedrich Boettger]

Reply to my question just posted:

This is what Neil Roger at RunRev kindly emailed me just now (I was having trouble accessing this forum due to a login snafu so I emailed support earlier):

"In regards to the database connection ID, there is no direct way to reset the connection ID that is generated by LiveCode. A connection ID will only reset when the LiveCode IDE is closed or the standalone application that you are running is closed and re-opened.
One reason that the number increments is so that there in no chance of a conflict with an existing open database.
I hope this gives you some leads and I look forward to seeing you in the forums."

OK. So I guess my database close routines are working as expected (by more experienced Livecode programmers). I just thought that, if they were working, I'd see the previous db connection ids. So that means my frequent errors when inserting new records are due to some other misapprehension of mine. If someone could give me an answer as to where (and presumably how often) to declare globals, that might make my life less complicated. I'm guessing that in my willy-nilly attempts to relocate variables I am not getting the db connection id to my record editing routines.

[Friedrich Boettger]

Klaus

This completely squares with what I've observed so far. I think before I figured out that revOpenDatabase was going to increment no matter what I did, I was assuming the persistence of the variable because I always got a connection id based on an increment of the previous value and therefore assumed the variable still existed, being the container for the value. (Too bad M.C. Escher wasn't around to make a diagram of that last sentence).
I am going to go play with this before making any more assumptions.
Very kind of you to take your time and save me some (like maybe a chunk of a weekend better spent playing volleyball).

[Friedrich Boettger]

Yes the command

Code: Select all

put revOpenDatabase("ODBC","cat", "CatData","","" ) into tDatabaseID

will create an integer into tDatabaseID, if the connection is successful.

Declare tDatabaseID as a global, and you are good to go to use it into all your scripts.

And to close everything, here is a generic script .

Code: Select all

on closeDB
put revOpenDatabases() into myDatabases
   repeat for each item myBase in myDatabases
      revCloseDatabase myBase
   end repeat
end closeDB

Unfortunately, my previous reply was cruelly devoured by a forum timeout. Suffice it to say then that I found each of your comments very useful and am implementing them presently. The fact that I got such rapid and comprehensive replies from two obviously experienced programmers leads me to assume that this forum will be a valuable resource for me if I stick with LiveCode for awhile (if it doesn't time me out too often).