Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
DBStatement Execute() and Fetch() behavior...
I've got a method that looks basically like this...

Method Refresh()
        StringList  AllList;

    m_DBStmt.Execute("SELECT Description FROM Table");

    While (m_DBStmt.Fetch())

        AllList.Append( m_DBStmt.GetStrCol( 0 ) );  

    WriteStrListFld( mFldID, AllList );  


...and it exhibits two strange behaviors.

First, if the result set is empty, the call to Fetch() raises an exception instead of just returning false.

Second, if the result set is one row, Fetch() will succeed twice and I get two values in the generated StringList.

OK. I'll look into that.
Dean Roddey
Explorans limites defectum
Well the bailing out error one was pretty straightforward. A cut-n-paste from the connect class to the statement class of some code left me calling SQLDisconnect on the statement handle, which is bad. It's supposed to get a connection handle.

That could have been causing the other problems you are seeing, but I'm not sure and I cannot replicate your fetch problem. I'm not sure if that's because of the other thing being fixed or what.

If I do an empty table, I get zero rows and fetch returns false. If I do one with a one row result, I get one row.

What exception did you get? Was it a CML exception or a C++ one?
Dean Roddey
Explorans limites defectum
Okay, on the one row reporting two rows issue, I think that was me. I went to drop all the rows from the table and it said "2 rows affected", and looking at it I had an insert expression in the query window that I may have inadvertently run in conjunction with (and after) the select I using to check the table.

Duplicating the first problem seems a little sporadic... I reproduced it again this morning with an empty table, and (sorry) the exception is actually getting raised at the Execute() call...

Class: MEng.System.RunTime.DBStatement
Line: 98
Error: DBStmtErrors.ExecErr
Error Text: Direct statment exuection failed for statement Reminders
If it's failing there, then I think that's really an underlying error being returned from whatever ODBC engine/driver you are using.

I've increased the amount of info that's available in the CML exception to help diagnose these sorts of problems.
Dean Roddey
Explorans limites defectum
It may be, but the query's pretty basic and it runs in SQL Management Studio fine if I copy and paste, so it'd have to be something whacked in the ODBC driver itself... I'll try it with 2.0.3 and keep my fingers crossed that the other fix you made was possibly just leaving the statement in a disconnected state at times when it was hitting that call...
I would not at all doubt that that was the problem.
Dean Roddey
Explorans limites defectum
Are you working on a DB driver? If so, I am writing one as well. Right now I can do a Select, Update, Insert & Delete on a DB though it only works on one column at a time. The result is stored to a string field (first result in a resultset) and a stringlist field. I also write the number of records read from the select statement to a field. The driver also continuously updates the last select statement to maintain current data in case the underlying data is changed in the DB (through any means).

Be glad to share what I have so far. I still have plenty of work to do on it (error checking, formating [struggling to get the GetDateCol column converted to a string], etc.). But as a proof of concept it works pretty well.
I'm trying to put together a sort of reminder system. Basically, I wanted something to keep track of the regular maintenance things that need to happen around here that just sorta get done at random times, but really SHOULD happen on a more rigorous schedule (checking the water softener for salt, changing air filters, setting the irrigation system for summer versus winter, etc.). I plan on putting some sort of indicator on the root template just to tell me there's something I need to do, and that'll cue you to pop open an overlay that shows you everything and lets you reset reminders for future dates.

I appreciate the offer of help. I'm almost there, but I seem to have tripped over some of the newer bits in the ODBC wrappers. I think the driver's all in place, it's just cleaning up the templates a little and getting some of the things back in place once I get 2.0.3 installed (hopefully tomorrow night...). If it goes worse then I'd planned, I'll let you know... :-)

I'm documenting stuff pretty thoroughly and I'm hoping this'll be useful to others as well. I'll post the driver and templates along with instructions for setting up the database so that'll hopefully be reasonably turn-key. If you want a peek earlier, I can email a copy but it's still a little bit in progress yet.
Mikla Wrote:[struggling to get the GetDateCol column converted to a string]

Dean just made a fix in 2.0.3 to correct issues with binding date fields... That got me here as well... You may want to try things in the current build...

Possibly Related Threads…
Thread Author Replies Views Last Post
  Odd LogicSvr behavior gReatAutomation 2 1,109 08-14-2019, 12:40 PM
Last Post: Dean Roddey
  Odd behavior of Admin interface Mark Stega 1 1,852 10-22-2017, 09:12 AM
Last Post: Dean Roddey
  Field Boolean Image behavior pinballmark 6 2,962 12-28-2015, 04:45 PM
Last Post: pinballmark
  Execute a macro from another macro? dlmorgan999 6 2,603 08-10-2015, 07:53 AM
Last Post: dlmorgan999
  New Exception Behavior 4.2.16? wuench 1 1,434 11-19-2012, 12:52 PM
Last Post: Dean Roddey
  Elk 5.2.8/M1XEP 1.3.28 firmware curious behavior Ishmael 3 2,406 12-06-2011, 11:00 PM
Last Post: Ishmael
  Execute actions on CQCStartup? SamVimes2 3 1,875 06-23-2009, 09:00 PM
Last Post: Dean Roddey
  Retrieving Date/Time fields with DBStatement jrlewis 9 2,544 05-30-2008, 10:37 AM
Last Post: jrlewis
  Unusual behavior & RDP rm1759 9 3,816 11-17-2007, 07:32 AM
Last Post: wuench
  DBStatement errors when field is empty jrlewis 5 2,372 09-04-2007, 10:39 PM
Last Post: Dean Roddey

Forum Jump:

Users browsing this thread: 1 Guest(s)