Thanks Pete. What I do now is I have my own library of calls for opening a connection (which is really my own internal system of checking to make sure the parameters are in order and setting a global if successful) then for reconnecting, which is really making a connection in the LC sense, then idling, which is really disconnecting.
I have a database connection card for all my apps where a user can enter information like the database server url, the user name and password, and type of database etc. When opening a database enabled app, I go to that card with the screen locked, which attempts the connection and sets a global to true and another global with the database id. All "reconnect" calls in the future check for the connected global, then open a connection and attempt a basic query. If all is well, the function returns true and away we go. I don't leave connections open during user interaction. Old habit I learned when attempting to resolve some deadlocks in Foxpro due to bad programming habits. I basically get to the point where I have all the info from the user I need, then I open the connection, do my business and then close it. This has resulted in a very stable methodology for me. Others may think it overkill. Still, if something can go wrong... you know the old adage. I will have to poke around a bit to see what the manual may have to say on premature disconnects during transactions. Bob On Oct 9, 2012, at 3:28 PM, Peter Haworth wrote: > I'm assuming this is mySQL? > > There is a server setting that determines the inactivity timeout period. > It's either wait_timeout for a non-interactive client or > interactive_timeout for an interactive client. I assume that LC opens > mySQL dbs as an interactive client. According to the mySQL documentation, > if you get an error on an SQL command because the connection has timed out, > all you have to do is re-issue the command and the connection will be > re-established. > > However, I'm not sure how that plays with LC since it will likely flag an > error on the database ID that you pass to it and, as I recall, that's a > runtime error in the script, not an error code (don;t understand why that > is the case but that's another issue). If you can find out what the error > is, you could wrap all your db calls in try/catch, check for the error and > proceed accordingly. Alternatively, I guess you could keep track of the > interval since the last SQL command you issue and the one you are about to > send and if it's more than the timeout, re-establish the connection > yourself. > > On the issue of a network error, I'm not sure what would happen. If mySQL > is in the middle of a transaction, I'd like to think it would not execute > the COMMIT if the connection to the client had gone away, but that would > depend on when it becomes aware of the problem. I think you can be pretty > certain that you would not end up with parts of the transaction being > reflected in the database and others not. Either the COMMIT executes in > which case the complete transaction will be in the database, or it doesn't > and none of the transaction will be in the database. There's also the > question of how LC detects that the connection died in the middle of a > transaction and if/how it communicates that back to your script. > > Interesting topic! > > Pete > lcSQL Software <http://www.lcsql.com> > > > > On Tue, Oct 9, 2012 at 2:50 PM, Bob Sneidar <b...@twft.com> wrote: > >> I was getting disconnected from On-Rev within an hour of inactivity, and >> all my database calls failed when the connection was closed by the host. >> Also, certain network outages or problems can cause managed switches to >> reset, notably excessive broadcasts, and when this happens, the SQL >> connection is reset. Same result. I have experienced this first hand in our >> accounting application when someone had installed some mesh wireless in >> bridge mode, and we found out later that this cause a low level packet >> storm that was wreaking havoc with our managed switches. >> >> I'm not sure how you could rollback a transaction if the connection had >> been reset and the server did it automatically. I guess what I am asking is >> how do SQL servers deal with connection failures in the middle of a >> transaction. Does it rollback or commit? If commit, that would be bad if >> the transaction were not complete. If rollback, than attempting to restore >> the connection and continue with the transaction could be equally bad. >> >> Bob >> >> >> On Oct 9, 2012, at 12:44 PM, stephen barncard wrote: >> >>> well "COMMIT/ROLLBACK" should handle the possibility of data loss. >>> >>> As far as "connections" I've found that there is little time difference >>> between being 'always connected' and making a connection open and close >> per >>> transaction, unless one is hitting it repeatedly for a single result (as >> I >>> had to to for a certain database system a few years ago). A greater time >>> lag is introduced when getting the returned data than the time required >> to >>> connect. This is assuming that the DNS is cached; the first fetch will >> take >>> longer. >>> >>> On Tue, Oct 9, 2012 at 11:23 AM, Bob Sneidar <b...@twft.com> wrote: >>> >>>> I've been pondering what the ramifications to sql session disconnects >> are. >>>> I have seen in other "professionally developed" applications, like our >>>> accounting software used here, that if the user gets disconnected for >>>> anything longer than a few seconds, the software completely bails out >>>> through a series of errors that I have to abort to get the app to quit. >> Not >>>> very graceful. I want to make my software more robust. >>>> >>>> So I am wondering what happens when there is a transaction in effect, >> and >>>> there is an unexpected disconnect. Will reconnecting restore the >>>> transaction state or is it flushed after the sql timeout? If I can >>>> reconnect and the transaction is still in effect, well and good, but if >> I >>>> proceed as though the transaction is still in effect and it is not, bad >>>> things could conceivably happen. >>>> >>>> Is completely bailing out the best approach after all? >>>> >>>> Bob >>>> _______________________________________________ >>>> use-livecode mailing list >>>> use-livecode@lists.runrev.com >>>> Please visit this url to subscribe, unsubscribe and manage your >>>> subscription preferences: >>>> http://lists.runrev.com/mailman/listinfo/use-livecode >>>> >>> >>> >>> >>> -- >>> >>> >>> >>> Stephen Barncard >>> San Francisco Ca. USA >>> >>> more about sqb <http://www.google.com/profiles/sbarncar> >>> _______________________________________________ >>> use-livecode mailing list >>> use-livecode@lists.runrev.com >>> Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >>> http://lists.runrev.com/mailman/listinfo/use-livecode >> >> >> _______________________________________________ >> use-livecode mailing list >> use-livecode@lists.runrev.com >> Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >> http://lists.runrev.com/mailman/listinfo/use-livecode >> > _______________________________________________ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
