CubeSql Example

Databases
1

Anyone willing to share a basic CubeSql example to get me started with Xojo ?

Thanks in Advance!

Mike

2

Willing to also pay for working example. (create - tables - query etc).

Regards

Mike

3

Hi Michael,

are you looking for instructions on how to connect to cubeSQL or how to work with sqlite in general?

4

I know SQL in general, however, working with the CubeSql plugin with Xojo is not the same. I can start the server - create the Databases etc.; - But I want to know how to program with Xojo and connect to the CubeSQL Server to get things done.

Looking for any example to use the CubeSql Plugin using Xojo.
Some of things I would like to see the example would be:

  1. Connection to CubeSql Database - via code in Xojo.
  2. Query the Database - via code in Xojo.
  3. And anything else to get me started.

I’m a Xojo beginner - if that helps. I’m a C++ and VB.Net Programmer - so Xojo is new to me.

So currently - I’m stepping through many examples everyday to get the hang of things in Xojo. However, I still want to use my CubeSql License and see if this is something I can use it for more serious projects.

Thanks

Mike

5

The only difference for SubSQL over any other servers is that it will have its own syntax for setting up the connection
But HOW TO is very close to any other server example

6

Most things are just the same as they would be for any Xojo database object. There are a few significant differences with the prepared statement mechanism, though.

To connect to the database (in this case, “mDB” is a module-level property):

[code]mDB = New CubeSQLServer
mDB.Host = mDatabaseHost // supply the ip address or host name as a string
mDB.Port = mDatabasePort // an integer - 4430 by default
mDB.UserName = “myUserName”
mDB.Password = “myPassword”

if NOT mDB.Connect then
// handle the error here
else
// you’re good to go
end If[/code]

To do a SELECT:

if mDB <> NIL then dim myRS as recordset = mDB.SQLSelect(theSQLString) if mDB.error then // handle error; you can query mDB.ErrorCode and mDB.ErrorMessage else // use myRS end if else // mDB is NIL; you need to instantiate it as per above end if

To do an EXECUTE:

if mDB <> NIL then mDB.SQLExecute(theSQLString) if mDB.error then // handle the error // if you previously executed "BEGIN TRANSACTION", do "mDB.rollback" here else //success // if you previously executed "BEGIN TRANSACTION", do "mDB.COMMIT" here end if else // mDB is NIL; you need to instantiate it as per above end if

Here are the differences in the PreparedStatement mechanism:

• Instead of using two method calls for each parameter as other Xojo databases do (bindType and bind), the cubeSQL prepared statement (which is called “CubeSQLVM”) uses a single method to do both (such as CubeSQLVM.bindBlob(index, property). depending on the property type, there’s also bindInt, bindDouble, bindInt64, bind Null, bidText, bindZeroBlob)

• IMPORTANT - instead of the indexes being zero-based, they are one-based.

• ALSO IMPORTANT - Unlike working with other Xojo preparedStatements, you must set the CubeSQLVM to NIL and re-prepare it every time you use it (e.g., if you are calling it repeatedly in a loop)

Here is a code snippet using the prepared statement mechanism:

[code]dim myPS as CubeSQLVM
dim i,n as integer
dim myContainerIDs() as integer
dim myPublicationIDs() as integer
dim fkContainerID, fkPublicationID as integer
dim myErrorCode as integer
dim myErrorMsg as string

mDB.SQLExecute(modASG.kBEGIN_TRANSACTION)
for i = 0 to n
fkContainerID = myContainerIDs(i)
fkPublicationID = myPublicationIDs(i)
mySQLStr = “INSERT INTO tblPublicationContents(fkContainerID, fkPublicationID) VALUES(?,?);”

myPS = NIL  // I don't know why, but you must do this
myPS = mDB.VMPrepare(mySQLstr) // and "re-prepare" it
if mDB.Error then
mDB.Rollback
return
end if

myPS.BindInt(1, myContainerID)
myPS.BindInt(2, myPublicationID)

if mDB.Error then
mDB.Rollback
return
end if

myPS.VMExecute

if mDB.Error then
myErrorCode = mDB.ErrorCode
myErrorMsg = mDB.ErrorMessage
mDB.Rollback
return
else

endif

next
mDB.commit[/code]

Hopefully, this will be enough to get you going…

7

Thank You Peter,

That will definitely get me started and on the right track.

Regards
Mike

8

Peter, the best example I just found is:

https://github.com/cubesql/cubeSQLAdmin
The cubeSQLAdmin full source, created with Xojo and uses the CubeSQL Plugin.

Just passing it on - for other like me learning the process!

Regards
Mike

9

Ah, yes; that’s a good point. I’d forgotten that Marco recently made it open source - been meaning to look at it myself.

Thanks!

10

Peter,

Thanks Again! I took used your notes and everything worked flawlessly with the CubeSQL Plugin.

Regards