Cloud FormFunction Database

Questions
,
1

I am trying to build a web inventory application using a SQLite database hosted in the cloud home directory. I have a database in the home directory and I can query that database while in the code editor but when I publish to a webform the code does not work.

Needs["DatabaseLink`"];
dbFilePath="/wolframcloud/userfiles/********";
conn=OpenSQLConnection[JDBC["SQLite",dbFilePath]]

SQLSelect[conn, "key"]

works file when I am in the notebook. I then go to deploy it to the cloud..

CloudDeploy[FormFunction["Table" -> {"key"-> "key","eltesting"-> "eltesting"},SQLSelect[conn,#Table]&]]

It looks great

ScreenShot2016-03-23at9.18.39AM.png
ScreenShot2016-03-23at9.18.39AM.png1680×404 11.3 KB

Then when I press submit I get response:

DatabaseLink`SQLSelect[DatabaseLink`SQLConnection[DatabaseLink`JDBC["SQLite", "******"], JLink`Objects`vm1`JavaObject10700769468612609, 6, "Catalog" -> Automatic, "Description" -> None, "Location" -> None, "Name" -> None, "Password" -> None, "Properties" -> {}, "ReadOnly" -> Automatic, "RelativePath" -> False, "TransactionIsolationLevel" -> Automatic, "UseConnectionPool" -> Automatic, "Username" -> None, "Version" -> None], "key"]

I have replaced my file path with ****** for security. If local database storage is not supported what is the best way for my application to store, update and retrieve data. DataDrop is not the correct solution as I need this data to last more than 30 days.

2

Hello Ray,
You need to reload Needs["DatabaseLink"] and reopen the connection conn before launching a SQLSelect. Btw use full context to make the DatabaseLinkSQLSelect call. The reason for this is that every time you launch a Form a new cloudkernel is used (check kernel sessionid).

good luck.

3

If you do CloudEvaluate[something, Hold] you get back the expression after it was evaluated on the cloud.

do it with your connection / db select in order to debug what is going on.
database connection in the cloud is coming, but in the mean time CloudExpression is a powerful keyvalue storage that is backed by redis.
If a key value store is enough for your application, CloudExpression is what you should use.

4

Thank you both for your help.

@Pieter, I’m not sure what you mean by full context. Do you mean, DatabaseLink`SQLSelect?

@Riccardo, I don’t think a key value store will work as I am looking for more database functionality. If you can give me a quick example how this would work I can try it.

Here is my updated code which still does not work.

selctTest[table_]:=
Module[{dbFilePath,conn2},

Needs["DatabaseLink`"];
dbFilePath="/wolframcloud/userfiles/*****";
conn2=DatabaseLink`OpenSQLConnection[JDBC["SQLite",dbFilePath]];

DatabaseLink`SQLSelect[conn2,table]
]

CloudDeploy[FormFunction["Table" -> {"key"-> "key","eltesting"-> "eltesting"},selctTest[#Table]&]]

Now I am getting this response:

SQLSelect[OpenSQLConnection[Global`JDBC[“SQLite”, “/wolframcloud/userfiles/****”]], “key”]

So it seems that database link is not running each time the form is called. How do I make Needs[“DatabaseLink`”] run each time?

5

Sorry, just saw I can reply directly to you. Please see below.

6

Sorry, just saw I can reply directly to you. Please see below.

7

Ray,
Yep this is what I meant. It should work. But I think because of the new

$SessionID

as a result of the FormFunction your are not allowed to setup the jdbc ip-connection. The following produces a Failed message.

CloudDeploy@FormFunction["f" -> "String", (Needs["DatabaseLink`"];OpenSQLConnection["demo"])&]

Would be nice if I could generate a warning message…