Execute dot commands from a perl script

(1) By Trudge on 2024-08-04 17:14:27 [link] [source]

I'm trying to write a web-based library script in perl that can access 2 databases. I have successfully produced correct results in Terminal on a MacOS M1. But am getting errors when I try to use any dot commands in a script.

So far I've tried as an $sth prepare,execute,finish set.

Also using a 'system' command to execute sqlite .schema.

I'm not having any success with either method and am beginning to think it may not be possible.

Is this an exercise in futility? Any ideas how it can be done if possible?

my $schema=".schema"; sub GetSchema { $sth=$dbh->prepare(qq{"sqlite .read $schema"}); $sth->execute(); $sth->finish(); } GetSchema(); Throws this Perl error DBD::SQLite::db prepare failed: near ""sqlite .read .schema"": syntax error at /Users/trudge/httpd/cgi-bin/scratch/multi-db.pl line 84.

(2) By SeverKetor on 2024-08-04 17:20:14 in reply to 1 [link] [source]

Dot commands are just a helpful thing the SQLite3 CLI tool provides. They are not a part of the library, so trying to use them as a query will not work.

(3) By Trudge on 2024-08-04 17:24:47 in reply to 2 [link] [source]

I was afraid so. Thank you for the clarification. I've also tried executing a dot command using Perl's 'system' command with no success either.

(4) By SeverKetor on 2024-08-04 17:47:39 in reply to 3 [link] [source]

I don't know perl, but in python what works is os.system('sqlite3 example.db ".show"'). Might be enough of a hint to help

(5) By Aask (AAsk1902) on 2024-08-04 23:14:37 in reply to 3 [link] [source]

This Windows Command Prompt example might give you some ideas, perhaps.

(6) By Karl (kbilleter) on 2024-08-05 02:38:03 in reply to 1 [link] [source]

Something like this maybe?


   my $sth = $dbh->prepare('SELECT sql FROM sqlite_master WHERE sql NOT NULL');
   $sth->execute;
   
   while (my $schema = $sth->fetch) {
       print "$schema->[0]\n";
   }

(7.1) By Trudge on 2024-08-05 16:16:41 edited from 7.0 in reply to 6 [source]

I believe I've found a solution at https://stackoverflow.com/questions/61237549/opening-sqlite-3-shell-but-initialized-with-dot-commands.

In my Perl script I do: my @SysCmd; @SysCmd="/Users/trudge/bin/sqlite -cmd .schema /Users/trudge/MyDB/kb.db"; my $schema=(system(@SysCmd)); print qq{$schema };

This prints

Getting schema from the KB DB...

CREATE TABLE authors(id integer primary key autoincrement,author text,authorid integer); CREATE TABLE sqlite_sequence(name,seq); CREATE TABLE IF NOT EXISTS "formats" ( "id" INTEGER NOT NULL, "authorid" INTEGER NOT NULL, "titleid" INTEGER NOT NULL, "format" TEXT NOT NULL, PRIMARY KEY("id" AUTOINCREMENT) ); CREATE TABLE IF NOT EXISTS "keywords" ( "id" integer, "keyword" text COLLATE NOCASE, "authorid" integer, "titleid" integer, PRIMARY KEY("id" AUTOINCREMENT) ); CREATE TABLE IF NOT EXISTS "books" ( "id" integer, "titleid" integer, "authorid" integer, "title" text, "pubyear" date, "filename" text, "notes" text, "Birthdate" text, author text, PRIMARY KEY("id" AUTOINCREMENT) );

This is printing in a Perl here document, thus the <br/>

Hope this helps others who may have had a similar situation.