SQLite from BASIC

Has anyone done anything with SQLite from BASIC? I’d like to run queries and do minor updates on a SQLite database from BASIC, and perhaps build a little WIMP around the commands eventually.

I know of Richard Windley’s ports at https://www.reallysmall.co.uk/Pages/categories/riscos/database/sqlite.php but I’d like to use it from BASIC (and really wish it was installed by standard on RISC OS).

From BASIC I guess the easiest, but also very inefficient, way would be OSCLI commands to pass queries to the SQLite command line tool. Other than that, I would have thought you’d have to resort to something much more complicated: either to write some SWIs to call the library functions and then invoke those SWIs from BASIC, or call the library functions from the BASIC assembler. Maybe someone else will know of a better option.

Is there a way to get answers back from queries sent using OSCLI? If there is, then I would like to use that for another small project.

It’s getting even messier, but you could try running

OSCLI("Spool MyPath.To.MyDataFile")

OSCLI("Spool")

As well as OSCLI, there’s also the SWI OS_CLI and OS_ChangeRedirection to redirect OS_CLI’s output (or input) with a file handle and OS_FSControl 11 if you need to specify a filing system for the file handle, and OS_FSControl 19 to restore it.

https://www.riscosopen.org/wiki/documentation/show/OS_CLI
https://www.riscosopen.org/wiki/documentation/show/OS_ChangeRedirection
https://www.riscosopen.org/wiki/documentation/show/OS_FSControl%2011
https://www.riscosopen.org/wiki/documentation/show/OS_FSControl%2019

It would be a lot nicer if there was a way to just read directly what appears in standard output without writing to a file but I don’t know how you’d go about that in RISC OS.

Just run the command specifying either

C-style redirection: OSCLI\(“command > $.Temp.foo”) or

OS-style: OSCLI\(“command { > $.Temp.foo }”)

Is there a way to get answers back from queries sent using OSCLI?

While Andy’s *Spool method works (but be warned, sometimes Spool can begin a file with a bunch of weird control characters), you can do this on a command by command basis.

For everything:

• OSCLI("your_command { > filename }")

However there is a slightly different method available for C programs. To capture normal output to the screen, you can use one of:

• OSCLI("your_command >filename")
• OSCLI("your_command 1>filename"}

Note that for C-style, there is no space between the greater than and the filename, and there are no curly braces.
Note also that if anything goes wrong, it probably won’t be recorded in the file. That’s because in C, there’s a distinct difference between stdout (where character output is usually sent to) and stderr (where error messages are supposed to be sent to).
To capture error output, you’ll want “2>” instead:

• OSCLI("your_command 2>filename"}
  (this is frequently used for capturing diagnostics from crashed programs – you’ll spot this in NetSurf’s !Run file, for example)

You can combine these:

• OSCLI("your_command 1>filename 2>otherfilename")

Note, though, that they cannot both point to the same filename, or you’ll get the C library returning the error “Cannot open XXX for I/O redirection”.

There is no interactivity. Commands complete, file is closed, you can read it.

If you want interactivity, you’ll need to be running in the Desktop and set up a TaskWindow and interact with that. It’s quite a bit more complicated.

if there was a way to just read directly what appears in standard output without writing to a file but I don’t know how you’d go about that in RISC OS.

I would say “cheat” and point you at PipeFS, but this appears to only hold/buffer 256 bytes at a time, meaning most output will stall awaiting the data being read. It works between two TaskWindows, one program can output data that the other can see, but a program started using OSCLI woul hang everything up (as OSCLI won’t return as the buffer will be full).

Probably sticking a temporary file in !Scrap is the simplest bet.

Note, by the way, that OS redirected output (*Spool and { > filename }) write their newlines to the file as CRLF.

Edit: I see Stuart wrote a much shorter reply. ;-) It wasn’t there when I began writing this!

this appears to only hold/buffer 256 bytes at a time

It’s a shame PipeFS can’t dynamically allocate space for what wants to be written (within reason!).

If you’re running in the desktop you could use a TaskWindow to do the work and capture the output. I’ve even written a module to help with that, which I need to do a proper release of :)

which I need to do a proper release of

Where is it? Where is it?
Where is it? Where is it?
Where is it? Where is it?
Where is it? Where is it?
Where is it? Where is it?
Where is it? Where is it?
:)

I’ve shoved it on the interwebs here – http://cmj.orgfree.com/TaskRunner/index.html

Thanks Chris. I’ll need to make some tests on real hardware, as the demo simply block RPCEmu.

Ah, yes I forgot to say – you need run the demo in a TaskWindow, otherwise it will hang waiting for the task to run, which it won’t do because the system is waiting for it to run….