I use SQlite more than native PB data structures, because it allows complex queries with little coding effort - I have a lot of mileage with SQL. A few years ago I started driving forms and listviews (report style) directly from SQL queries, adding the extra information inside column aliases, then preprocessing the adulterated SQL to extract the extra info, which gave the listbox column width, column title, and alignment, and located textboxes in the form, and so on. To cut a long story short, once conditionality is added to the extra information, whole applications can be coded in a single query - which has become a tree of queries. But the more I add to this framework, the more thousands of lines of code I have to maintain.
I wonder if there is an existing scripting language which might do this better?
QuoteI wonder if there is an existing scripting language which might do this better?
If you're feeling a little dangerous, you could always give ScriptBasic a try. (Window/Linux/Android)
import sqlite.bas
db = sqlite::open("sqlite3.db")
sqlite::execute(db,"create table demo (someval integer, sometxt text);")
sqlite::execute(db,"insert into demo values (123,'hello');")
sqlite::execute(db, "INSERT INTO demo VALUES (234, 'cruel');")
sqlite::execute(db, "INSERT INTO demo VALUES (345, 'world');")
stmt = sqlite::query(db,"SELECT * FROM demo")
while (sqlite::row(stmt) = sqlite::SQLITE3_ROW)
if sqlite::fetchhash(stmt,column) then
print column{"someval"},"\t-\t",column{"sometxt"},"\n"
end if
wend
sqlite::close(db)
jrs@laptop:~/sb/sb22/test$ scriba testsqlite3.sb
123 - hello
234 - cruel
345 - world
jrs@laptop:~/sb/sb22/test$
FYI The Sqlite3 ScriptBasic extension module has no dependencies. Very handy on the Android platform where SQLite is under the control of the Java VM.
John, a few quick questions, does Scriptbasic support Collections? Does it include an XML parsing library? Is there a single GUI component with is well supported on Windows and ~UX platforms which can be used successfully with it? Thanks, Chris
ScriptBasic supports two
fetch methods. The example I posted uses
fetchhash() which returns the row in an associative array with the column names you assigned on creation as the hash key. (single table only) FetchArray() on the other hand returns the resulting row in an element array as the query may have been with joins involving multiple tables.
QuoteDoes it include an XML parsing library?
No but I have written a XML parser in SB in about 10 lines of code. (see below) The
T (Tools) SB extension module has a function to turn an array (associative, element or a combination of both) into a XML string describing its structure. I haven't looked if there is an add-on library for SQLite that would support a XML interface. ScriptBasic also has an extension module for Mini-XML (http://www.allbasic.info/forum/index.php?topic=99.0) Armando wrote.
mxml.sb
IMPORT t.bas
xml = t::LoadString("shiporder.xml")
SPLITA xml BY ">" TO a
FOR x = 0 TO UBOUND(a)
IF LEFT(TRIM(a[x]),1) = "<" THEN GOTO IT
p = INSTR(a[x],"<")
PRINT MID(a[x],p+2) & " = " & LEFT(a[x],p-1),"\n"
IT:
NEXT
<?xml version="1.0" encoding="ISO-8859-1"?>
<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>
jrs@laptop:~/sb/sb22/test$ scriba mxml.sb
orderperson = John Smith
name = Ola Nordmann
address = Langgt 23
city = 4000 Stavanger
country = Norway
title = Empire Burlesque
note = Special Edition
quantity = 1
price = 10.90
title = Hide your heart
quantity = 1
price = 9.90
=
jrs@laptop:~/sb/sb22/test$
I use phpMyAdmin (http://www.phpmyadmin.net/home_page/index.php) for most of my GUI MySQL DB definition work. I work with Amazon EC2 (headless) servers and a HTTP GUI interface works out well. When in a console I use the Sqlite console utility.
Updatehttp://www.sqlitemanager.org/
Thanks for those. It looks as if ScriptBasic has the building blocks, but no convincing advantage, which is what would be needed to prise me away from PowerBASIC for current work. Also I don't think that doing the GUI in HTML would produce an acceptable result, viz, keystroke compatible with the current stuff, developed in MSDOS days.
The GUI which I am using (to write what looks and to some extent is coded like a character mode app) is PowerBASIC'c GRAPHIC one, a wrapper for GDI. I don't know how to migrate that to ~UX, except by rewriting the UI for a different graphics library, one which I can count on being available both on Windows and ~UX for the next ten years.
Chris--
OpenGL is the way to go.
You can work in 2D mode with it, and that would work on all OS.
Thanks Patrice, I shall give it a spin.
Quote from: ChrisIt looks as if ScriptBasic has the building blocks, but no convincing advantage, which is what would be needed to prise me away from PowerBASIC for current work.
Could you help me understand your comment better by posting an example of this ScriptBasic code in PowerBASIC?
import sqlite.bas
db = sqlite::open("sqlite3.db")
sqlite::execute(db,"create table demo (someval integer, sometxt text);")
sqlite::execute(db,"insert into demo values (123,'hello');")
sqlite::execute(db, "INSERT INTO demo VALUES (234, 'cruel');")
sqlite::execute(db, "INSERT INTO demo VALUES (345, 'world');")
stmt = sqlite::query(db,"SELECT * FROM demo")
while (sqlite::row(stmt) = sqlite::SQLITE3_ROW)
if sqlite::fetchhash(stmt,column) then
print column{"someval"},"\t-\t",column{"sometxt"},"\n"
end if
wend
sqlite::close(db)
Something similar:
' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Step.bas
' Contents: CSQLite class example
' Connects to a database and reads records.
' Copyright (c) 2012 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
' // Create an instance of the class
LOCAL pSQL AS ISQLite
pSQL = CLASS "CSQLite"
IF ISNOTHING(pSQL) THEN EXIT FUNCTION
' // Create a connection object
LOCAL pDbc AS ISQLiteConnection
pDbc = pSQL.Connection
IF ISNOTHING(pDbc) THEN EXIT FUNCTION
TRY
' // Delete our test database if it exists
IF ISFILE(EXE.PATH$ & "Test.sdb") THEN KILL EXE.PATH$ & "Test.sdb"
' // Create a new database
pDbc.OpenDatabase(EXE.PATH$ & "Test.sdb")
' // Create a table
pDbc.Exec("CREATE TABLE t (xyz text)")
' // Insert rows
pDbc.Exec("INSERT INTO t (xyz) VALUES ('fruit')")
pDbc.Exec("INSERT INTO t (xyz) VALUES ('fish')")
' ' // Prepare a query
LOCAL pStmt AS ISQLiteStatement
pStmt = pDbc.Prepare("SELECT * FROM t")
? "Column count:" & STR$(pStmt.ColumnCount)
' // Read the column names and values
LOCAL i AS LONG
DO
' // Fetch rows of the result set
IF pStmt.Step = %SQLITE_DONE THEN EXIT DO
' // Read the columns and values
FOR i = 0 TO pStmt.ColumnCount- 1
? pStmt.ColumnName(i)
' ? pStmt.ColumnText(i)
? pStmt.ColumnText("xyz")
NEXT
LOOP
CATCH
' // Display error information
? pSql.OleErrorInfo
END TRY
' // Cleanup
pStmt = NOTHING ' // Deletes the prepared statement
pDbc = NOTHING ' // Closes the database
pSQL = NOTHING
#IF %DEF(%PB_CC32)
WAITKEY$
#ENDIF
END FUNCTION
' ========================================================================================
Thanks José for the PB code example. I'm not sure how this solves Chris's original request of a cross platform SQLite scripting solution with a GUI admin facility. The SB code I posted runs untouched on Windows, Linux, OSX and Android. The GUI (browser based) SQLite administrative tool also runs on these platforms with no new learning curve. With Chrome and FireFox OS just around the corner, shying away from browser solutions could be fatal.
@Chris - Can you explain again how PB solved your original request?
Quote from: John Spikowski on August 25, 2013, 07:05:14 PM@Chris - Can you explain again how PB solved your original request?
Let's avoid any mistunderstanding here. What I'm looking for is a scripting language which extends SQL in the direction of the UI. I have a feeling that it doesn't exist, possibly because it would have to assume that the scripting engine and the GUI were aware of each other and could communicate. I don't know how that might be done other than by writing code, which is what I have done so far using PowerBASIC.
As to how I code it, I have put an early example using the technique in the public domain http://www.powerbasic.com/support/pbforums/showthread.php?t=44995. Here is a method from it which shows where I was three years ago:
'-------------------------------------------------------------------------
' set up listview columns from an array of pointers to asciz text
' parameters are
' 1) listview handle
' 2) pointer to asciz ptr array each member of which points to a column name asciz string
' NB this is the format in which SQLite results are returned
' 3) number of columns required
' 4) number of rows in the result set = number of items in the list view
'
' The SQL column names have been parasitized to include column formatting info.
' A separator character (defined as $mySQLseparator) is used to separate the
' column title itself from an alignment indicator and a column width in pixels.
' defaults are left alighnment and column width = title width.
' An example of syntax would be SELECT STUFF "Mystuff|L|200" FROM MYTABLE,
' resulting in a left-aligned column 200 pixels wide
' NB Windows rule is that the leftmost column must be left-aligned
method LVStart
local tLVC as LV_COLUMN
local tLVI as LV_ITEM
local s as string
local i, l as long
redim pSz(0 to ncols-1) as local asciz ptr at pResults
' Get ListView Handle
l = ListView_GetExtendedListViewStyle(hLV)
ListView_SetExtendedListViewStyle (hLV, l or %lvs_ex_gridlines or %lvs_ex_fullrowselect)
tLVC.mask = %LVCF_FMT or %LVCF_TEXT or %LVCF_SUBITEM or %LVCF_WIDTH
tLVC.fmt = %LVCFMT_LEFT
' Insert column headers
for i = 0 to ncols -1
s = @pSz(i)
if parsecount(s, $mySQLseparator) > 1 then
select case as const$ parse$(s, $mySQLseparator, 2)
case "L": tLVC.fmt = 0
case "R": tLVC.fmt = 1
case "C": tLVC.fmt = 2
end select
s = parse$(s,$mySQLseparator,1)
end if
tLVC.pszText = strptr(s)
tLVC.iOrder = i
l = ListView_InsertColumn(hLV, i , tLVC)
next
' set column widths
for i = 0 to ncols -1
s = @pSz(i)
if parsecount(s,$mySQLseparator) > 2 then
ListView_SetColumnWidth(hLV, i, val(trim$(parse$(s,$mySQLseparator,3))))
else
ListView_SetColumnWidth(hLV, i, %LVSCW_AUTOSIZE_USEHEADER)
end if
next
listview_setitemcountex(hLV, nrows+1, %LVSICF_NOINVALIDATEALL )
end methodCurrently I generate the code to drive a listview and associated form from a SQL query. By including additional metadata in the SQL, drilldowns and child forms can also be done by the generated application.
As to how would code your example in PB, my code would look very like Jose's, but my generated code uses the sqlite3_exec wrapper rather than prepare, step, etc. And, of course, I call my classes methods, not Jose's, but that is just an accident of history.
Having a well-defined and stable syntax, SQL provides opportunities for embellishment. The benefit to the coder is (IMO) that the embellished verion is much more weildy than either a separate, parallel structure for the extra information, or a container like XML for the two components.
QuoteWhat I'm looking for is a scripting language which extends SQL in the direction of the UI.
Did you check out the
IUP cross platform extension module for SB? Easy SQLite & GUI API sounds to me like what you were looking for.
FWIW SB translates it's scripts to C source if a standalone application is needed with the option of static linking extension modules.
You don't have to leave the comfort of your PB compiler if you embed SB to do the no brainer work. (something PBCC GUIless folks have to contend with)
PBCC has not DDT, but you can do GUI applications with it using straight SDK programming or using my CWindow class. In fact, I use PBCC more often than PBWin.
Very cool, I didn't know that.
Will the CWindows class work with PBCC 5?
No. That version does not support classes.
My chances of upgrading to 6 are nil. I can't even get support to give me a reason they approved then ban me and I didn't do anything wrong.
You're in a tough spot. If your includes and libraries only support 10/6, many are still on the fence if they should upgrade due to current circumstances.
Quote from: John Spikowski on August 25, 2013, 11:10:22 PM
QuoteWhat I'm looking for is a scripting language which extends SQL in the direction of the UI.
Did you check out the IUP cross platform extension module for SB? Easy SQLite & GUI API sounds to me like what you were looking for.
your wiki page for iup appears empty. Maybe I looked in the wrong place.
The IUP (http://www.tecgraf.puc-rio.br/iup/) GUI toolkit extension module is new for SB and the plan is to make it part of the ScriptBasic 2.2 release. Here are a few links to help you evaluate IUP and where I'm currently at with it. Charles (DLLC) has done some real magic with allowing IUP to run in a threaded environment with a shared callback handler to SB script functions. (IUP is not thread safe as it stands)
A customer master file maintenance screen I did with IUP in SB. (http://www.scriptbasic.org/forum/index.php/topic,279.0.html)
Early IUP / SB development (http://www.allbasic.info/forum/index.php?topic=184.0)
And the IUP extension module link you mentioned (http://www.scriptbasic.org/forum/index.php/topic,237.0.html)
I would be happy to assist getting you up to speed with SB/IUP/DLLC if this looks like something that will work for you.
John, Thanks for your prompt reply!
Your links were of interest and I shall certainly keep ScriptBasic in my sights.
Not sure about UIP. My UIs are very, er, retro-looking, and have to stay that way, although I have sneaked in a few features over the years - colour, for example. Very unlike the windows CC or modern widgety look. I draw them with PB Graphics, could have used a GDI class. Patrice suggests above to use OpenGL 2D, bit of a tank to shoot a rabbit, but it is available on both windows and ~UX.
On balance I think that I shall press on with PB for the 2014 version, then look again at alternative platforms and how to reach them. At least I know that will cover the Windows part of the estate until 2025, though by that time Windows may be the smallest slice!
Thanks for having a peek.
It's good to have options.
> You're in a tough spot.
No, I'm not. These includes use key features of the latest compilers, such classes, unicode and dead code removal.
There is no question the value of your offering. My point is you need a current version of PB to take advantage of them. Maybe PB needs to have a sale to get those on the fence to make a move.