DBStatement Execute() and Fetch() behavior...

[jscheller]

I've got a method that looks basically like this...

Code:

Method Refresh()
Begin
    Locals=
        StringList  AllList;
    EndLocals;

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

    While (m_DBStmt.Fetch())

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

    WriteStrListFld( mFldID, AllList );  

EndMethod;

...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.

:confused:

[Dean Roddey]

OK. I'll look into that.

[Dean Roddey]

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?

[jscheller]

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

[Dean Roddey]

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.

[jscheller]

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...

[Dean Roddey]

I would not at all doubt that that was the problem.

[Mikla]

j,
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.

[jscheller]

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.

[jscheller]

[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...