I’m back in REALbasic after about 6/7 years. My old program has this in it:
dim sql As String
sql = “SELECT * FROM Entry ORDER BY DayNumber”
dim rs as recordSet
rs = app.AccountDataDB.sqlSelect( sql )
I’m getting rs as NIL. Could this because XOJO’s SQL has changed?
Tom Bisbee - in the hills of VT
SQL hasnt changed. Have you tried to use the same SQL in a SQL/Database editor program to verify that it works in other tools?
Scott is right. Also, it is not Xojo SQL, but SQL for your given database engine. Each has their own little changes, although all engines have at least a standard on your said SQL above.
You should get in the habit of always checking for a db error. For example, the code above should be written as:
Dim rs As RecordSet = App.AccountDataDB.SQLSelect("SELECT * FROM Entry ORDER BY DayNumber")
If App.AccountDB.Error Then
MsgBox "Eror querying the database: " + App.AccountDB.ErrorMessage
Return
End IfOr something similar, in your own style of course.
Oh, and then also you can create helper methods once you have a db established in your app and a known error handler. What I do a lot of times is:
Dim rs As RecordSet = QueryDbStuff()
If CheckDbError("Querying the DB for stuff") Then
// There was an error, the error has been logged and displayed to the user (if necessary), now do something about it
End IfI include a textual message stating what I was doing. Once your app grows and you have SQL all over the place, an error message such as “Value too large to insert into CHAR(1)” isn’t too helpful.
Depending on which version of Xojo you use REALSQLdatabase is now SQLitedatabase
Please check errors. If recordset is nil, you always have an error!
One good thing of my MBS SQL Plugin is that you can switch it to exception mode. Than all errors cause exceptions and you will see them 
Thanks for the replies. I wonder if I must change my “REALSQLDataBase” s to
"SQLiteDataBase"s?
(I wrote my software back in about 2003, and I think there have been many changes since then. Right now, I’m trying to upgrade my program to Mac OS 10.5.8)
It would help if you actually say what version and OS you are using.
REALSQLDatabase vs. SQLIteDatabase may be a change you have to make in the future, but it has nothing to do with your current error. Your current error is due to an invalid query.
Did you check the DB error state as I suggested? That will more than likely tell you everything you need to know to fix the problem.
IDE
Supported
OS X Lion 10.7.x or later
At least 2 GB RAM
Compiled Applications
Compiled applications (Carbon or Cocoa) run on these versions of OS X:
OS X Lion 10.7.x
OS X Mountain Lion 10.8.x
OS X Mavericks 10.9.x
Perhaps this is the root of your problem (still add the error checks as suggested above)… But if you are attempting to compile for 10.5.8 and the minimum supported version is 10.7.x then that might be an issue. Also contributing is the fact that SQLite is a few versions ahead of what was there for 10.5
To Marcus Winter: My OS is OS X Version 10.5.8
My Xojo is Real Studio 2012 v2
To Jeremy: “Did you check the DB error state as I suggested?” How do I check the DB error state? Do you mean things like checking for NIL?
While I have the floor, I have further mysteries:
Why doesnt the Finders Find facility find my .rsd files? Yet files called Account1.rsd and OwnsMgrsAccts.rsd are listed in HD: Users: User1: Library: Application Support: Holdins Holdings TB !!
In my 2003 program, I was able to produce an icon for the program. This no longer works. How do I make an icon for a program nowadays?
Tom, please see my first response, it contains a code example how to do that exact thing.
Jeremy,
Yes, I have always checked for DB errors - have 26 “If blahblahDB.Error then” in my program. This is frustrating. Did you see my other two questions? (below)
Why doesnt the Finders Find facility find my .rsd files? Yet files called Account1.rsd and OwnsMgrsAccts.rsd are listed in HD: Users: User1: Library: Application Support: Holdins Holdings TB !!
In my 2003 program, I was able to produce an icon for the program. This no longer works. How do I make an icon for a program nowadays?
[quote=85262:@Tom Bisbee]Jeremy,
Yes, I have always checked for DB errors - have 26 “If blahblahDB.Error then” in my program. This is frustrating. Did you see my other two questions? (below)[/quote]
So what is the error it is displaying right after the query that returns Nil? Can you do a .schema for us on the db table with the sqlite3 command line program? Did you try to execute that exact query using the sqlite3 command line tool?
I do not know the answer to your other two questions, so I can not address them.
Jeremy-
Here is Sub Open() in my program:
App.AccountDataDB = New REALSQLDatabase
App.Changed = False
dim g as FolderItem
dim fname as string
fname = “Account” + str(App.AcctNo) + “.rsd”
g = GetFolderItem(app.fpath + fname)
App.AccountDataDB.databaseFile = GetFolderItem(app.fpath + fname)
if App.AccountDataDB.databaseFile.exists = true then // Connect to the database
if App.AccountDataDB.Connect() = false then
app.DisplayDatabaseError( false ) // error connecting to the database
return
end if
else
return
end
dim sql As String
sql = “SELECT * FROM Entry ORDER BY DayNumber”
dim rs as recordSet
rs = app.AccountDataDB.sqlSelect( sql )
if rs = nil then MsgBox “AcctRegisterW - Open rs is nil !!!”
rs.close
When I change it to SQLite instead of REALSQL - I think I’ve had similar bugs.
You lose me with the sentence below,
“Can you do a .schema for us on the db table with the sqlite3 command line program? Did you try to execute that exact query using the sqlite3 command line tool?”
I am ignorant of (a) .schema (b) db table © sqlite3 (d) sqlite3 command line program (e) sqlite3 command line tool . Good grief! Is there some textbook etc that I should be reading?
Thanks for your good efforts in helping me.
Tom Biz
Tom, you did not add the code I suggested that would tell you the error. If you are checking if rs is nil, but you are not checking the database error flag, nor the database error message. That is where the gold is.
My original suggestion was:
[code]Dim rs As RecordSet = App.AccountDataDB.SQLSelect(“SELECT * FROM Entry ORDER BY DayNumber”)
If App.AccountDB.Error Then
MsgBox "Eror querying the database: " + App.AccountDB.ErrorMessage
Return
End If
[/code]
Notice line #2 and #3. You are checking if rs is nil, that prevents a nil exception but gives you no database error information, of which you need to determine why rs is nil.
Very good info from you. I’ll get back to it this afternoon (if it is raining!).
Best, t biz
[quote=85262:@Tom Bisbee]1. Why doesnt the Finders Find facility find my .rsd files? Yet files called Account1.rsd and OwnsMgrsAccts.rsd are listed in HD: Users: User1: Library: Application Support: Holdins Holdings TB !!
Thanks Mr. S.
Your reply prompts two further questions.
He meant “then in”. The Inspector is the panel on the right in the IDE that shares space with the Library and displays the properties of whatever you have selected. When you click on App in the IDE, one of the properties will be Icon.
That said, this may be more of an OS X issue and less of a Xojo issue. Hard to say at this point.