Automating Tasks with psBASIC and Databases
Pi Shack BASIC is a Great Tool for Data Based Automation
While psBASIC has no built-in support for accessing database servers (like MySQL) its abilities to communicate and control other programs and easily parse text table data makes it quite easy to launch tasks based on data in a database.
I wanted a CRON job that could look for specific new records and send an email when they were created. The mysql command line tool (MySQL or MariaDB) will produce TSV output when its piped into other programs. With the DELIMIT and OPEN "|mysql... psBASIC commands we can easily dump a SQL query into our program and operate on the data it contains. But, since automated tasks are by definition unattended tasks, we need to make sure we can login without the need of a user supplied password. I did this with a simple "~/my.cnf" file:
[client]
password=********
Of course Replace the asterisks with your password. If you do use this technique make sure you chmod 600 my.cnf to keep others from getting your password. This also works with MariaDB, which is what I'm actually using. With PostgreSQL it uses the user account that you are logged in as to authenticate you, so you may not need to do anything else. And, of course, SQLite normally doesn't have any passwords, you simply need read rights to the DB file. I have not tested this method with these other two DBs so there may be adjustments in order. In theory you could just as easily pull data from Redis or memcache, adapting your code to their semantics and data.
Running a query
After you have created your my.cnf accessing the results of a query is pretty straight forward:
280 DELIMIT #2,chr$(9)+chr$(0)
300 OPEN "i",#2,"|mysql -e """+q$+""" my_database"
310 ' empty queries return EOF right away
320 IF NOT eof(2) THEN
330 LINE INPUT #2,l$ : ' burn header
340 DO UNTIL eof(2)
350 INPUT #2,i,started$,who$,subject$
360 ' do something with data
370 LOOP
380 END IF
390 CLOSE #2
Ln280 sets up for TSV content: chr$(9) is TAB. You could just poke the [TAB] key between quotes, but its not particularly visible. So for my example I'll use the more visible chr$(9). The chr$(0) turns off quoting and by extension the escaping of quotes. Neither of those parsing features are part of the mysql client output. We don't want to accidentally scramble our data!
Ln300 OPENs the mysql client, executes a query and prepares to read the results. q$ contains the query. Make sure to replace the database name (my_database). I use quotes (") in my command because apostrophes (') are used as quotes in queries. If you are pulling queries from outside the psBASIC program or use quotes (") in your queries you'll have to provide some escaping (replace$())or risk errors and potential hackery. My query is embedded in the program and had no need of quotes (") so I took the path of least resistance.
Ln320 determines if we're receiving a result set. Ln330 reads and discards the header (field names) line. Ln340 is the usual read loop. Ln370-390 simply complete the loop and finish up.
Ln350 reads my data columns into variables that I can work with. Change this line to match your query. The number and data types of variables must be the same as your query produces. You can use string variables ($) for any non-numeric data type. Then do whatever you like with the data between ln350 and ln370.
NOTE: Line numbers are not necessary. They just made it easier to write about the code. You aren't limited to line numbers 351-369 to put your job in. You can leave the numbers off or use a different set. In my case I didn't use line numbers. They're too pesky. 
NOTE: DELIMIT is a professional edition feature. It requires a small purchase.
My situation and query
This website accepts support requests and loads them into a DB. I chose this path instead of sending emails directly, due to the spam factor. To prevent email addresses entered into the form from becoming potential targets I wanted new requests in the DB to send an email to me. This is a KISS approach to preventing spam abuse. My query only pulls the needed four fields from the table:
220 q$=q$+"select id,started,who,subject "
230 q$=q$+"from support_request "
240 q$=q$+"where progress='open' and id>"+str$(i)+" "
250 q$=q$+"order by id"
I broke this into several lines so its easier for me to read. 
The variable i is the last found record ID. It's kept in a state file and updated as new requests are found. More on that later.
Sending emails
Since I'm running this job from CRON, emails are automatic. Any output from my program is emailed to my user account on the server. You CRON's action and configuration may vary. An error result may be needed to trigger delivery. This is accomplished with END 1. psBASIC's END command takes an optional integer argument (0-255) that becomes the exit code of your program. The default is 0, meaning "success". The 1 would indicate an other than successful result.
You may also want your email to go to a different mail account than the one on the machine that is running the job. This may already be setup for you. But you may also want to use the MAILTO variable in your crontab. See man 5 crontab. If you aren't operating on your own machine talk with your SysOp (administrator / support staff).
You also may want or need to use the mail tools mail or sendmail. These are easily implemented by OPENing them for OUTPUT. But that is beyond the scope of this episode.
Due to my use of CRON to send the email my output code is simple:
360 PRINT dec$(i,"######");" | ";started$;" | ";right$(who$,24);TAB(56);" | ";subject$
This adds a line to the email that will be sent to me. CRON will accumulate all my output and send it once the program exits. So I'll get an email with the lines I PRINT.
NOTE: The CRON email will normally contain the stdout (normal PRINT output) and stderr (error output ie. PRINT #-1). The error output may come from your DB client, which will be helpful in letting you know things are borked.
Keeping a bookmark
120 statef$="~/.support_req"
130 ' If files is not found find$() returns "", which IF treats as "false"
140 IF find$(statef$) THEN
150 OPEN "i",#1,statef$
160 INPUT #1,i
170 CLOSE #1
180 END IF
...
410 '*** Update our location in the list ***
420 OPEN "o",#1,statef$
430 PRINT #1,i
440 CLOSE #1
The top part tests for the existence of my state file and reads in the last ID found into variable i. Before the program exits the second block of code is run to re-open the file and write out the new ID (i). The file name "~/.support_req" uses the tilde (~) short hand for your home directory. This is replaced with the environment variable $HOME, which is set by your login and CRON. (BA)SH and psBASIC use this shortcut notation.
Ln140 simply checks to see if the file exists. The variable i is automatically initialized to 0 by psBASIC so if the file doesn't exist, it works as expected. You could use an ON ERROR construct here... but that feels sloppy to me.
If you scroll back up or down you will see that I read data into i with the INPUT statement at ln350. If the query produces no results, then ln350 is never executed and i remains untouched. If data is found, then the order by id in the query will make sure the greatest value of i is the last value read, and therefor recorded back into the state file where it's picked back up by the next execution.
Putting it all together
#!/usr/bin/psb
100 DEFINT a-z
110 ' Name of state keeping file
120 statef$="~/.support_req"
130 ' If files is not found find$() returns "", which IF treats as "false"
140 IF find$(statef$) THEN
150 OPEN "i",#1,statef$
160 INPUT #1,i
170 CLOSE #1
180 END IF
190
200 '*** DB Query ***
210 ' NOTE: the query is broke out of OPEN for greater clarity
220 q$=q$+"select id,started,who,subject "
230 q$=q$+"from support_request "
240 q$=q$+"where progress='open' and id>"+str$(i)+" "
250 q$=q$+"order by id"
260
270 '*** Run query and spit out hits ***
280 DELIMIT #2,chr$(9)+chr$(0) : ' TSV - tab separator, no quotes
290 ' NOTE: this works because I have a ~/.my.cnf with my password
300 OPEN "i",#2,"|mysql -e """+q$+""" my_database"
310 ' empty queries return EOF right away
320 IF NOT eof(2) THEN
330 LINE INPUT #2,l$ : ' burn header
340 DO UNTIL eof(2)
350 INPUT #2,i,started$,who$,subject$
360 PRINT dec$(i,"######");" | ";started$;" | ";right$(who$,24);TAB(56);" | ";subject$
370 LOOP
380 END IF
390 CLOSE #2
400
410 '*** Update our location in the list ***
420 OPEN "o",#1,statef$
430 PRINT #1,i
440 CLOSE #1
The top line is #!/usr/bin/psb so that Linux knows to execute this script with /usr/bin/psb. This is the same syntax used by (BA)SH and Python scripts. psBASIC allows for this syntax. Then all I need to do is chmod +x ... my script and I don't have to specifically launch psb. In my crontab I put the full path name to my script as the command to execute. When new support requests are logged into the DB I get a plain text email with the list of new requests.
Thanks psBASIC!
Get your copy of Pi Shack BASIC here!
Read past episodes here
