# CubeSql Example

**URL:** <https://forum.xojo.com/t/cubesql-example/24049>\
**Category:** Databases\
**Created:** [May 4, 2015, 10:18pm UTC](https://forum.xojo.com/t/cubesql-example/24049 "2015-05-04T22:18:36Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Michael\_Meeks](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/c6cbf5/32.png) [@Michael\_Meeks](https://forum.xojo.com/u/Michael_Meeks)\
**Post date:** [May 4, 2015, 10:18pm UTC](https://forum.xojo.com/t/cubesql-example/24049/1 "2015-05-04T22:18:36Z")

</div>

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

Thanks in Advance!

Mike

---

<div class="post-metadata">

**Author:** ![Michael\_Meeks](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/c6cbf5/32.png) [@Michael\_Meeks](https://forum.xojo.com/u/Michael_Meeks)\
**Post date:** [May 4, 2015, 11:30pm UTC](https://forum.xojo.com/t/cubesql-example/24049/2 "2015-05-04T23:30:32Z")

</div>

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

Regards

Mike

---

<div class="post-metadata">

**Author:** ![Rich\_H](https://forum.xojo.com/user_avatar/forum.xojo.com/rich_h/32/18188_2.png) [@Rich\_H](https://forum.xojo.com/u/Rich_H)\
**Post date:** [May 4, 2015, 11:31pm UTC](https://forum.xojo.com/t/cubesql-example/24049/3 "2015-05-04T23:31:30Z")

</div>

Hi Michael,

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

---

<div class="post-metadata">

**Author:** ![Michael\_Meeks](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/c6cbf5/32.png) [@Michael\_Meeks](https://forum.xojo.com/u/Michael_Meeks)\
**Post date:** [May 5, 2015, 1:29am UTC](https://forum.xojo.com/t/cubesql-example/24049/4 "2015-05-05T01:29:56Z")

</div>

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](http://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

---

<div class="post-metadata">

**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\
**Post date:** [May 5, 2015, 2:16am UTC](https://forum.xojo.com/t/cubesql-example/24049/5 "2015-05-05T02:16:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter\_Truskier](https://forum.xojo.com/user_avatar/forum.xojo.com/peter_truskier/32/243_2.png) [@Peter\_Truskier](https://forum.xojo.com/u/Peter_Truskier)\
**Post date:** [May 5, 2015, 2:55am UTC](https://forum.xojo.com/t/cubesql-example/24049/6 "2015-05-05T02:55:22Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![Michael\_Meeks](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/c6cbf5/32.png) [@Michael\_Meeks](https://forum.xojo.com/u/Michael_Meeks)\
**Post date:** [May 5, 2015, 5:29am UTC](https://forum.xojo.com/t/cubesql-example/24049/7 "2015-05-05T05:29:13Z")

</div>

Thank You Peter,

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

Regards  
Mike

---

<div class="post-metadata">

**Author:** ![Michael\_Meeks](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/c6cbf5/32.png) [@Michael\_Meeks](https://forum.xojo.com/u/Michael_Meeks)\
**Post date:** [May 5, 2015, 4:15pm UTC](https://forum.xojo.com/t/cubesql-example/24049/8 "2015-05-05T16:15:26Z")

</div>

Peter, the best example I just found is:

[https://github.com/cubesql/cubeSQLAdmin](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

---

<div class="post-metadata">

**Author:** ![Peter\_Truskier](https://forum.xojo.com/user_avatar/forum.xojo.com/peter_truskier/32/243_2.png) [@Peter\_Truskier](https://forum.xojo.com/u/Peter_Truskier)\
**Post date:** [May 5, 2015, 4:21pm UTC](https://forum.xojo.com/t/cubesql-example/24049/9 "2015-05-05T16:21:23Z")

</div>

> [@184934:@Michael Meeks](#):
>
> The cubeSQLAdmin full source

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!

---

<div class="post-metadata">

**Author:** ![Michael\_Meeks](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/c6cbf5/32.png) [@Michael\_Meeks](https://forum.xojo.com/u/Michael_Meeks)\
**Post date:** [May 7, 2015, 5:25am UTC](https://forum.xojo.com/t/cubesql-example/24049/10 "2015-05-07T05:25:06Z")

</div>

Peter,

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

Regards

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 5:44pm UTC](https://forum.xojo.com/t/cubesql-example/24049/11 "2020-10-29T17:44:40Z")

</div>


