rodinski has asked for the wisdom of the Perl Monks concerning the following question:

I would like to be able to use the DBI::Shell (or from the command line dbish) with sqlite AND have access to functions that I have writen in perl.

I'm not sure how to go about this. Do I:

1) write a perl program that connects to sqlite, registers a function using perl code:

dbi->func('mysqlfunc',-1,'my_perl_func','create_function');

[download]
Then somehow call up the interactive shell?

2) enter the shell using dbish at the command line then use the /perl command to create and register the new function from inside the shell.

I had just a bit of success with method one, I could hald the perl program and get the shell prompt but I couldn't see any output. Note I entered the dbish by using `dbish:SQLite:name_of_db`

Specifcally I would like to access to the sqrt function while having all the niceness of the dbi interactive shell.

Thank you.,
rodinski.

Replies are listed 'Best First'.
Re: DBI::Shell sqlite dbh->func(
by NetWallah (Canon) on Oct 13, 2013 at 02:12 UTC
    It looks like the third parameter to func() should be a subref, not the string 'my_perl_func'.

    I've been looking for a reason/excuse to try this, so, please post progress on this project.

                 When in doubt, mumble; when in trouble, delegate; when in charge, ponder. -- James H. Boren

[reply]
      This code below seems to work for me.

      If I try it without the single quotes within the func declaration, I get a strict subs error

      Now how to get DBI::Shell to interact with the user so they have access to this function? 

      #!/usr/bin/perl -w
# create a SQLite test database
use strict;
use DBI;

my $db = shift || 'no_name.splite';
my $dbh = DBI->connect("dbi:SQLite:$db", "","",
        {RaiseError => 1, 
         sqlite_see_if_its_a_number => 1, 
          PrintError=>0})
   or die "can't connect\n";

$dbh->func('sqrt_s_s', -1, 'sqrt_sum_of_sqrs', 'create_function'); #ne
+ed length function
sub sqrt_sum_of_sqrs {
    my $sum_sqrs = 0.0;
    map {$sum_sqrs += $_**2} @_;
    return sqrt($sum_sqrs);
}

my $sth;

my @rowa = $dbh->selectrow_array("select 3 as A,4 as B, sqrt_s_s(3,4) 
+as C");
print join ("\t",@rowa) ;

      [download]
[reply]
[d/l]

        If I try it without the single quotes within the func declaration, I get a strict subs error

        Something like this?

        #!/bin/env perl
# create a SQLite test database
use strict;
use warnings;
use DBI;

my $db = shift @ARGV || 'no_name.splite';

my $dbh = DBI->connect(
    "dbi:SQLite:$db", "", "",
    {
        RaiseError => 1,
        sqlite_see_if_its_a_number => 1,
        PrintError => 0
    }
);

$dbh->func('sqrt_s_s', -1, \&sqrt_sum_of_sqrs, 'create_function'); # n
+eed length function

my @rowa = $dbh->selectrow_array( "select 3 as A, 4 as B, sqrt_s_s( 3,
+ 4 ) as C" );

print join( "\t", @rowa ), "\n";

sub sqrt_sum_of_sqrs {
    my $sum_sqrs = 0.0;
    map { $sum_sqrs += $_ ** 2 } @_;
    return sqrt( $sum_sqrs );
}

        [download]

        Update: So now you need to research three things.

        1. How would you prompt a user for input?
        2. How to get the user input?
        3. How do you pass the user input into your function?

[reply]
[d/l]

Back to Seekers of Perl Wisdom