CubeSQL Prepared Stmt

Databases
1

This code works fine when I use a desktop application. All I did was point it to the CubeSQL instance and it’s giving me a Nil for the Prepared Statement.

I’m using the USE DATABASE command. That was my first suspect. At any rate the CubeSQL seems to work with SQL commands just fine. I was doing some performance testing comparing Desktop(SQL only) to CubeSQL(SQL only) to CubeSQL(using ORM classes). For what it’s worth there’s a slight penalty using the CubeSQL. The desktop comes to .006 seconds while CubeSQL is .008. That’s for a simple query bringing back 2500 rows without an index.

Obviously I haven’t gotten the ORM to run but I expect a significant lag.

2

Prepared statements is one area in which cubeSQL requires a different approach than the Xojo mechanism.

Assuming that you have the cubeSQL plug-in installed, and mDB is an open CubeSQLServer instance, you need to do something like this (after having executed Use Database…):

[code]Dim mySQLstr as string = “SELECT * FROM REFERRALS WHERE ID=?”
Dim myPrepStatement As CubeSQLVM
Dim myRS As RecordSet

myPrepStatement = mDB.VMPrepare(mySQLstr)
myPrepStatement.BindInt(1, SomeIntegerValue)
myRS = myPrepStatement.VMSelect()[/code]

Note that unlike the Xojo preparedStatement, with cube SQL, the indexing of the properties in the bindxxx calls is one-based, not zero-based.

HTH

3

Thanks Peter that worked. And I found the bottleneck in my current application.

When it was small it ran fine with the ORM classes I had at the time. But it’s not scaling at all.

4

Usually thats the complaint about using ORM - Google for “orm slow performance”

5

I suspect these results are due to it being ORM and my own half-assery.

6

I can relate… It will be interesting to see if Bob Keeney or other users of his ActiveRecord classes chime in with their experiences vis-à-vis performance. If you’ve not checked them out, Joseph, it might be worth a look: .

You might also look at their commercial (though very reasonably priced) product ARGen which does the work of creating the Xojo classes by reading your database.

In the project we’re currently working on, we chose not to go with ORM, but we were impressed with the Keeney team’s work on these when we evaluated.

7

I have them and there was even a button I cut out called BKCube. I’m gluing it all together right now. My hope is that they are sufficient to meet my needs. I’d rather not go the SQL route. Objects are just so convenient to code and later to understand when I need to update.

8

The one big drawback is that the ActiveRecord object loads the entire recordset even if you’re really only interested in just 1 field. If you have a large table this will probably result in poor performance. Images and blobs will also be the cause of problems simply because it loads the data regardless of what’s used.

It can also be a memory hog. If you load 10,000 ActiveRecord objects it will put all 10,000 into memory after loading it from the database. Depending upon the data this might have awful performance. At that point you might want to rethink how you’re doing things. In Web Apps we’re really big into paging now so it’s not so bad. Desktop apps we haven’t had an issue but we might be adding paging to one real soon because of Windows thread performance issues).