Cubesql and json

In the cubesql documentation it says about json format communication but the example code is in php language. Can someone give an example code in xojo to connect to db and fetch some data.

Here’s a basic example:

[code]dim db as New CubeSQLServer
db.Host = “yourdatabasehost”
db.Port = 4430 //whatever port your cubesql listens on
db.UserName = “yourusername”
db.Password = “yourpassword”

if (db.Connect = false) then
MsgBox “Connection failed: " + db.ErrorMessage + " (” + Str(db.ErrorCode) + “)”
return
end if

//Once you are connected, you can query like this:

dim rs as RecordSet = db.sqlSelect(“your sql query here”)
//deal with results, etc.[/code]

No not this. This is the usual method using cubesql plugin. I need to know the way using rest api with json format.

considering they wrote on the blog

[quote]actually it is just a php class with about 100 lines of code[/quote] it sounds like this is specific to php

Wont that work using xojo code. I will check with cubesql aswell

[quote]In order to try to supports as much heterogeneous clients as possible cubeSQL fully
supports the JSON open standard protocol. JSON is a lightwave text based protocol and
is built-into any major language (like PHP, Ruby, LiveCode and so on). In this version only
JSON over TCP/IP is supported, next version will also support JSON over HTTP.
For a complete and working JSON implementation we strongly suggest you to take a
look at the cubeSQLServer.php class. [/quote]

Cubesql JSON protocol

http://documentation.xojo.com/topics/communication/internet/communicating_via_tcp/ip.html

http://documentation.xojo.com/topics/file_managment/reading_and_writing_data_in_json_format.html

https://github.com/cubesql/sdk/tree/master/PHP

The SQLite docs should apply to CubeSQL as well
https://www.sqlite.org/draft/json1.html

[quote=440034:@Dean Davidge]The SQLite docs should apply to CubeSQL as well
https://www.sqlite.org/draft/json1.html[/quote]

He is asking about the json communication with the CubeSQL server, not about an Extension of the SQLite

In the cubesql documentation it says about json communication over tcp / http. I tried the tcp method and was not able to connect because of invalid credential error. I believe its because of sha1 cripto issue in xojo. For the http method cubesql is not even accepting the communication. I have send a mail to cubesql and haven’t got a reply yet. Anyone can try this as cubesql is free for 2 users.

From:
https://www.sqlabs.com/download/cubesql/manuals/CubeSQL%20JSON%20protocol.pdf

I don’t know if the next version is out yet.

You can see that document was created in 2011. So i imagine after that several new versions were introduced.

I can’t find anything about 2011, this is what the PDF show on the first page:

I don’t know much about CubeSQL but I think the logo was updated recently and the new one is shown on that PDF.

Anyway, if JSON works now with HTTP, then you can use it (I guess). Good luck.

@Sunil Abraham , hey, how are you ?
Did you make any progress with this ? Did Marco from sqlabs ever reply to you?
I would be interested in helping you or seeing your code to connect to a cubesql server via http socket

Regards
Roman

@Roman Varas
Unfortunately i never got a reply from cubesql. Currently i am using the cubesql plugin from MBS and is working great. If you have any idea using http socket, please let me know.

The only idea I had which is a very “dirty” solution is to install a light webserver like nginx with php and use a php script to connect to cubesql.

I tried connecting with a http socket from xojo and looking at the cubesql log it did not seem to connect. It didn’t even show an error message. So I suspect i was doing something wrong in Xojo…

HTTP most likely isn’t available yet… But the TCP connection seems to work just fine.
Just have a look at the provided cubeSQL - PHP JSON SDK and cubeSQL JSON support Document.

Translate that to Xojo :wink:

  • make sure the order within the JSON is such as in the Documentation, starting with command (otherwise you’ll get a “unable to detect protocol” error)
  • make sure the binary representation matches the PHP behavior (e.g. Lowercase for Hex values)

I’ve quickly tried to connect using a TCPSocket - and it worked just fine.
Here you are - I have split the password part into statements per line, so it should be easier to see what to do there.

[code]
if TCPSocket1.IsConnected then

Dim iRandPool As Integer = 1928374655
Dim sUser As String = “admin”
Dim sPwd As String = “guess-what”

Dim j As New JSONItem

j.Value(“command”) = “CONNECT”
j.Value(“username”) = Lowercase(EncodeHex(Crypto.Hash(Str(iRandPool) + sUser, Crypto.Algorithm.SHA1), false))

Dim p As String = Crypto.Hash(sPwd, Crypto.Algorithm.SHA1)
p = Crypto.Hash(p, Crypto.Algorithm.SHA1)
p = EncodeBase64(p, 0)
p = Str(iRandPool) + p
p = Crypto.Hash(p, Crypto.Algorithm.SHA1)
p = EncodeHex(p, false)

j.Value(“password”) = Lowercase(p)
j.Value(“randpool”) = Str(iRandPool)

TCPSocket1.Write j.ToString
end if[/code]

Leaving the TCPSocket open, I can see the new, successfully established connection using cubeSQL Admin.

Just… why use a TCPSocket in Xojo, when you can use the CubeSQL Plugin?
Anyway - cubeSQL and JSON via TCPSocket seems to work, and it’s possible to do with Xojo.

I usually get an answer within reasonable time, yes.

Hey Jürg, thanks for taking the time to look into it…
Actually I spent the afternoon today trying to get this working, and I finally did it !.. just before I saw your reply.

For me it was mostly an exercise… I want to be able to use cubesql in other environments besides Xojo, and I was not sure how/if that was possible.

Did basically the same as you did… followed the PHP, and had to do some taes to make sure the sh1 results were correct. I do find the server much slower when using Json replies… but i think that’s understandable, as there the overhead of formatting the date…

Hi Jurg and Roman,

i am trying out the code above and wondering how to pass over the database name to use before doing a SELECT or EXECUTE command