Can Awk directly interact with some RDBMS engine (that is, within
'BEGIN{}')?

I haven't done a Google Search yet. Is looking for a direct answer. :)

On 01.03.2024 17:29, Mr. Man-wai Chang wrote:
>
> Can Awk directly interact with some RDBMS engine (that is, within
> 'BEGIN{}')?

It depends on the interface the RDBMS provides.

What you can do with GNU awk is, for example, to create a co-process
and send requests to that co-process and intercept its replies to
process them.

Janis

On 2/3/2024 1:25 am, Janis Papanagnou wrote:
>
> It depends on the interface the RDBMS provides.
>
> What you can do with GNU awk is, for example, to create a co-process
> and send requests to that co-process and intercept its replies to
> process them.
I suppose it's the only solution, unless you extend Awk's syntax to
SQLconnect some RDBMS. :)

On 03.03.2024 11:16, Mr. Man-wai Chang wrote:
> On 2/3/2024 1:25 am, Janis Papanagnou wrote:
>>
>> It depends on the interface the RDBMS provides.
>>
>> What you can do with GNU awk is, for example, to create a co-process
>> and send requests to that co-process and intercept its replies to
>> process them.
> I suppose it's the only solution, unless you extend Awk's syntax to
> SQLconnect some RDBMS. :)

Well, in the past there was xgawk that supported database handling.
This feature is now available in GNU Awk as part of its extension
library. You can handle PostgreSQL with it. But since I have never
used it myself you have to look up the details yourself. (Or maybe
there's someone around here who has experiences with the extension
or can answer any specific questions.)

Janis

On 3/1/24 9:29 AM, Mr. Man-wai Chang wrote:
>
> Can Awk directly interact with some RDBMS engine (that is, within
> 'BEGIN{}')?
>
> I haven't done a Google Search yet. Is looking for a direct answer. :)

I *think* most SQL type DBs have CLI interaction capabilities.

A SQLite3 example; 'csv' used for DB output mode since it's easy to run
split() on and several awks now have native CSV support:

--
$ sqlite3 -header -column site.db 'select * from site'
Station SSID CHAN
---------- ---------- ----------
1 CIA 9
2 FBI 11
3 NSA 1
4 DOD 10

$ cat site.awk
BEGIN {
DBfile = "./site.db"
CMD = "sqlite3 -csv " DBfile " \"" ARGV[1] "\""
while (CMD | getline == 1) arr[++cnt] = $0
close(CMD)
for (i=1; i<=cnt; i++) printf "arr[%d] = %s\n", i, arr[i]
print ""
}

$ nawk -f site.awk 'select * from site'
arr[1] = 1,CIA,9
arr[2] = 2,FBI,11
arr[3] = 3,NSA,1
arr[4] = 4,DOD,10

$ nawk -f site.awk 'select SSID from site'
arr[1] = CIA
arr[2] = FBI
arr[3] = NSA
arr[4] = DOD

--

This is just read-only of course; would need to use gawk for fancier things.

On 3/3/2024 11:28 pm, Janis Papanagnou wrote:
>
> Well, in the past there was xgawk that supported database handling.
> This feature is now available in GNU Awk as part of its extension
> library. You can handle PostgreSQL with it. But since I have never
> used it myself you have to look up the details yourself. (Or maybe
> there's someone around here who has experiences with the extension
> or can answer any specific questions.)

Thanks!

On 4/3/2024 6:20 am, Arti F. Idiot wrote:
> On 3/1/24 9:29 AM, Mr. Man-wai Chang wrote:
>>
>> Can Awk directly interact with some RDBMS engine (that is, within
>> 'BEGIN{}')?
>>
>> I haven't done a Google Search yet. Is looking for a direct answer. :)
>
> I *think* most SQL type DBs have CLI interaction capabilities.
>
> A SQLite3 example; 'csv' used for DB output mode since it's easy to run
> split() on and several awks now have native CSV support:
> ....

Thank you for putting up an example!

On 3/3/24 16:20, Arti F. Idiot wrote:
> BEGIN {
>   DBfile = "./site.db"
>   CMD = "sqlite3 -csv " DBfile " \"" ARGV[1] "\""
>   while (CMD | getline == 1) arr[++cnt] = $0
>   close(CMD)
>   for (i=1; i<=cnt; i++) printf "arr[%d] = %s\n", i, arr[i]
>   print ""
> }

Would someone please help me understand why you'd want to have the
sqlite3 (et al.) inside of awk (in the BEGIN{...}) verses having sqlite3
outside of awk and piping the output from sqlite3's STDOUT into awk's STDIN?

sqlite3 ... | awk ...

--
Grant. . . .

On 2024-03-04, Grant Taylor <gtaylor@tnetconsulting.net> wrote:
> On 3/3/24 16:20, Arti F. Idiot wrote:
>> BEGIN {
>>   DBfile = "./site.db"
>>   CMD = "sqlite3 -csv " DBfile " \"" ARGV[1] "\""
>>   while (CMD | getline == 1) arr[++cnt] = $0
>>   close(CMD)
>>   for (i=1; i<=cnt; i++) printf "arr[%d] = %s\n", i, arr[i]
>>   print ""
>> }
>
> Would someone please help me understand why you'd want to have the
> sqlite3 (et al.) inside of awk (in the BEGIN{...}) verses having sqlite3
> outside of awk and piping the output from sqlite3's STDOUT into awk's STDIN?

1. You could just have a #!/usr/bin/awk -f script instead of
a #!/bin/sh script that calls awk.

2. That script's standard input is available for other use. For
it could process the sqlite file to populate the array in the
above way, and then process standard input with the help of that
array.

--
TXR Programming Language: http://nongnu.org/txr
Cygnal: Cygwin Native Application Library: http://kylheku.com/cygnal
Mastodon: @Kazinator@mstdn.ca