|
Post by carlgundel on Sept 24, 2007 17:10:32 GMT -5
Who would be interested in creating a Run BASIC app for interacting with SQLite?
-Carl
|
|
|
Post by StefanPendl on Sept 25, 2007 0:01:11 GMT -5
|
|
|
Post by carlgundel on Sept 25, 2007 6:33:42 GMT -5
No, I mean a program which will let you enter queries against any SQLite database and display the results. -Carl
|
|
|
Post by Jerry Muelver on Sept 25, 2007 6:53:56 GMT -5
That's an interesting problem, somewhat like "how to query Google", or even how to search this forum. Do you have in mind to parse any old question into SQL? Or provide a basic 15-word or so vocabulary (field names from the database?) to the user from which to build queries?
PLAYERS more than 100 HOME RUNS COUNTRY POPULATION greater than 50 million all STOCKSYMBOL PRICE 2007 greater than PRICE 2000
Or set of drop-down pick lists to build queries? Or interactive Q&A to narrow the query, like a troubleshooting tree?
Is the computer plugged in? Is the monitor turned on? Did you enter your username? ....
Animal? Vegetable? Mineral? Does it live on land? Does it have wings? Two legs, four, or more? ...
|
|
|
Post by David den Haring on Sept 25, 2007 7:25:10 GMT -5
|
|
|
Post by carlgundel on Sept 25, 2007 7:28:37 GMT -5
Yup. I'm not proposing anything too fancy. Just a field to enter the database name. Another field to type a SQL query and an execute button. The results appear rendered below. Any frills above this are just icing on the cake. -Carl
|
|
|
Post by StefanPendl on Sept 25, 2007 8:27:09 GMT -5
So it is more like the SQL Scratchpad created with LB, which can be found in the database section of the LB conforum.
|
|
|
Post by colinmac on Sept 25, 2007 10:37:50 GMT -5
Carl said :- "Yup. I'm not proposing anything too fancy. Just a field to enter the database name. Another field to type a SQL query and an execute button. The results appear rendered below. " Perhaps other information also - a menu of hosted databases to make an initial choice - a list of tables and fields in the selected database so the user may compose a query - perhaps also some specimen queries to help the user out? I assume we are looking here for something that is read only at this stage, rather than a database creation/management tool. This would avoid a lot of issues with locking and unlocking etc. Colin
|
|
|
Post by carlgundel on Sept 25, 2007 11:14:33 GMT -5
Carl said :- "Yup. I'm not proposing anything too fancy. Just a field to enter the database name. Another field to type a SQL query and an execute button. The results appear rendered below. " Perhaps other information also - a menu of hosted databases to make an initial choice - a list of tables and fields in the selected database so the user may compose a query - perhaps also some specimen queries to help the user out? I assume we are looking here for something that is read only at this stage, rather than a database creation/management tool. This would avoid a lot of issues with locking and unlocking etc. I am just proposing an idea for a project. There are no hard requirements. As for bells and whistles, feel free to go to town. -Carl
|
|
|
Post by colinmac on Sept 30, 2007 15:23:41 GMT -5
Here is some simple code for reading, and writing to, an sqlite database.
' sqlite monitor - run-basic script by Colin McMurchie
call setCSS
dim dbases$(2) dbases$(0) = "buggs.db" dbases$(1) = "wiki.db" ' dbases$(2) = "cdlist.sq3"
[choosedb] html "<h1> Choose a database to work with </h1>"
listbox #dbase, dbases$(),5 #dbase select(dbases$ )
link #go, "Select Database", [handleSelection] print link #exit ,"exit the programme?" ,[exit2] wait
[handleSelection] dbase$ = #dbase selection$() SQLITECONNECT #dbh, dbase$ on error goto [fileerror] sql$ = "select type , name from sqlite_master " gosub [sqlexecute] print gosub [printscreen] wait
end
[printscreen] cls gosub [printtable] gosub [printmenu] return
[printmenu] div menubox div sqlStatementBox textarea #sqlstatement, sql$ end div ' sqlStatementBox link #sqlstmnt, " process this statement", [warning] print div sqlErrorBox textarea #sqlerror, monerror$ end div ' sqlErrorBox print "Error Report " link #exit ,"exit this database?" ,[exitdb] #exit cssclass("menuItem") end div ' menubox return
[exitdb] #dbh DISCONNECT() ' - Disconnect from the database cls goto [choosedb]
[exit2] cls end
[sqlexecute] monerror$ = "No SQL error reported" #dbh EXECUTE(sql$)' - Execute the SQL query in expr$ on error goto [fileerror] #dbh caption( dbase$ ) #dbh TRCLASS("restable") #dbh TDCLASS("restable") return
[printtable] div restablebox render #dbh end div return
[warning] sql$ = #sqlstatement contents$() if upper$(word$(sql$,1)) = "SELECT" then goto [yes] div warningbox html "<h1> Warning ,h1>" html "<h2> You are about to overwrite the database. Do you wish to continue?</h2>" link #yes, "Yes" , [yes] print " " ; link #no, "No", [no] end div wait [yes] gosub [sqlexecute] [no] gosub [printscreen] wait
sub setCSS cssclass "textarea", " {background: #fff } " cssid #warningbox, "{ clear: left } " cssid #restablebox, "{ height: 400px ; width: 65%; overflow: scroll ; float: left; }" cssclass "caption", "{ background: #EEF }" cssclass "table.relative", "{ table-layout: fixed }" cssclass "table", "{ border: 8px ridge #CCF ; border-spacing: 3px ; float: left}" cssclass "tr", "{ background: #ccf }" cssid #menubox, " { width; 30% ; height: 400px ; border : 8px ; position: fixed ; right: 0px ; background: #ccf} " cssclass "table.restable", "{ width: 100%; border: 3px ridge #DDF ; border-spacing: 3px }" cssclass "tr.restable", "{ background: #8f8 }" cssid #sqlStatementBox, "{ height: 150px ; overflow: hidden } " cssid #sqlErrorBox, "{ height: 150px ; overflow: hidden } " end sub
[fileerror] monerror$=Err$ gosub [printscreen] wait
To should run if pasted into a new project. For convenience it reads the two databases used by Carl for beta three , so it is available to all testers without additional files. It is easy enough to add a further database, by including it in the rbp directory and adding its name to the listbox array.
There is one known problem. Using the query "select * from sqlite_master" generates an error on some databases but not on others. I will look at that further.
Colin
|
|