Cloud Form Function Database

3

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 enter image description here

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.

Ray Troy

Posted 2016-03-23T13:27:10.123

Reputation: 33

Do you have a free Wolfram Development Platform account? Perhaps SQL commands like SQLSelect are disabled for such an account. The pricing page mentions database connectivity at the 100/month Producer price point. Wolfram support might be able to tell you if SQLSelect starts working at the 20/month Explorer price point. If you included the SQLite setup code in your question, it would be easier for others to test this.

– Christopher Haydock – 2016-03-23T14:49:56.480

@ChristopherHaydock here is some code to create a database so you can test. It will be empty but then calls should just return {}. Needs["DatabaseLink`"]; conn = OpenSQLConnection[ JDBC["SQLite", ToFileName[Directory[], "example"]]] – Ray Troy – 2016-03-23T15:24:08.057

At the Explorer price point of the Wolfram Development Platform CloudDeploy[Delayed[Needs["DatabaseLink\[RawBackquote]"]; SQLTables[OpenSQLConnection[JDBC["SQLite", FileNameJoin[{$CloudRootDirectory, "EmptySQLiteDatabase"}]]]]], FileNameJoin[{$CloudRootDirectory, "EmptySQLiteQuery"}]] does not give the expected empty list, but just displays unexecuted code like that you've posted above. I've emailed Wolfram Support asking if this code will execute at the Produce price point. – Christopher Haydock – 2016-03-23T20:05:15.403

Answers

1

The Wolfram Development Platform Producer plan features database connectivity, which would allow access to a SQLite database in the Wolfram Cloud. For example, this query should return an empty list (Free, Explorer, or Developer plans return unexecuted code):

CloudEvaluate[
   Needs["DatabaseLink`"]; 
   SQLTables[
      OpenSQLConnection[
         JDBC["SQLite", FileNameJoin[{$CloudRootDirectory, "EmptySQLiteDatabase"}]] ] ],
   Hold ]

The other alternative, which will even work for the Wolfram Development Platform Free plan, is to access your database through a RESTful API.

Christopher Haydock

Posted 2016-03-23T13:27:10.123

Reputation: 1 205