ODBC access (closing connections)

[Sarah Reichelt]

>> Check out the docs for the revDataFromQuery function which I think
>> will do what you want in a lot less code.
>>
>> I think your script can be condensed to:
>>
>> put revOpenDatabase("ODBC","testdb",,,) into myDB
>> put "SELECT Item Id, Item Title from Item" into tSQL
>> revDataFromQuery(tab,return,myDB,tSQL) into fld "Item List"
>>
>
> First of all, when I need database tips, I search the list for "Sarah
> Reichelt" because well... SHE'S AWESOME!  I have been using the above 3
> lines very successfully in Rev 3.0 for accessing a Microsoft SQL database
> via ODBC. I remember having to keep up with a connection id in the past,
> and closing the database when done. Is that still required when using the
> above method to retrieve a record? What happens if connections are left
> open?


I always store the connection ID in a global or a custom property as I
generally want to make one than one query after opening a database.
Then when my app is closing, or I am finished with the database, I use
that connection ID to close the database.

Alternatively, you can just close all connections when the app is
quitting, using something like this:
  put revOpenDatabases() into connectList
  repeat for each item c in connectList
    revCloseDatabase c
  end repeat

I'm not sure what happens if you quit without closing any connections.
The database library might close them automatically, or the server
might have to time you out, but I think it is better to close the
connections yourself when you no longer need them.

Cheers,
Sarah