Re: How to create and use a large database in Octave?

[Bruce Moore]

--- Dirk Eddelbuettel <address@hidden> wrote:
> On Sun, Jan 12, 2003 at 07:53:32AM +0530, Ananda
> Murthy R S wrote:
> > Hello:
> > 
> > I am writing a program in Octave which needs to
> create and use a large 
> > database. For this I thought of using Gnumeric
> spreadsheet or MySQL. How 
> > to read data from these databases in Octave. Is
> there any better way of 
> > doing this?
> 
> You can connect to SQL databases by means of
> hand-crafted .m or .oct files.
> One relatively simple way is to, say, pass your sql
> query as a text string
> to an .m file which then calls, say, Perl to connect
> to a SQL backend.
> Instead of Perl you could pipe directly into tools
> like isql, psql, mysql, ...
> 
> I once described another way of going directly
> against Postgres; these pages
> are still at 
> 
>     http://dirk.eddelbuettel.com/code/octave-pg.html
> 
> One problem is that SQL has different datatypes than
> Octave has, so it isn't
> obvious how to map them, and how to store them
> inside Octave. If you're
> mostly after number, it shouldn't be a problem.
> 
> Dirk
> 
Here is a Perl script to read from an ODBC data
source..could easily be changed to any Perl DBI data
source.

Note that this does not work on 2.1.43.  It will work
on 2.1.39, but you will need to read all of the return
values as strings and convert them to integers.

Hope this helps.

----------------
#!perl
#############################################################################
#
#
#
#############################################################################
$err_level     = 0;
$err_code      = 0;
$random_sample = 20;
@column_name   = "";
@column_type   = "";
@column_length = "";
@column_scale  = "";

$integer_length   = 10;
$smallint_length  =  6;
$float_length     = 20;
$float_scale      =  5;
$date_length      = 10;
$time_length      = 16;
$timestamp_length = 26;

#
# Read the input arguments
#
$dbname           = $ARGV[0];
$dbuser           = $ARGV[1];
$dbpw             = $ARGV[2];
$sql_statement    = $ARGV[3];
$feedback_path = $ENV{"VWP_LOG"};
if ($feedback_path eq "")
{
 $feedback_path = "c:\\temp\\octave_perl_sql.log";
}


open(WMFILE, ">>$feedback_path") || error_proc(8,"$0:
Error openning $feedback_path: $!");
#
# Put the basic tags in the feedback file
#
#
# Start the message tag
#
print (WMFILE "<MSG>");
print (WMFILE "octave_perl_dictionary.pl\n");
print (WMFILE "   Arguments:\n");
print (WMFILE "              dbname               =
",$dbname,"\n");
print (WMFILE "              dbuser               =
",$dbuser,"\n");
print (WMFILE "              sql_statement        =
",$sql_statement,"\n");
print (WMFILE "              WM feedback          =
",$feedback_path,"\n");

print (STDERR "cuoftx_affinium_data_dictionary.pl\n");
print (STDERR "   Arguments:\n");
print (STDERR "              dbname               =
",$dbname,"\n");
print (STDERR "              dbuser               =
",$dbuser,"\n");
print (STDERR "              sql_statement        =
",$sql_statement,"\n");
print (STDERR "              WM feedback          =
",$feedback_path,"\n");

#
# Install the DB2 interface
#
use DBI;

use DBD::ODBC;


$dbh = DBI->connect("dbi:ODBC:$dbname", $dbuser,
$dbpw, {AutoCommit => 0}) or error_proc(8,"Error
connecting to $dbname using $dbuser:  $DBI::errstr");
$query = $sql_statement;
$dataresource_sth = $dbh->prepare($query) or
error_proc(8,"Error preparing dataresource_sth: 
$DBI::errstr");



#
# Initialize variables
#

$line_nbr          = 0;
$nbr_dataresources  = 0;
$dataresource_sth->execute() or error_proc(8,"Error
executing dataresource_sth:  $DBI::errstr");

while ((@columns) = $dataresource_sth->fetchrow_array
and $dbh->err() == 0 and $err_level == 0)  
  {
      $line_nbr++;
      print(STDOUT "@columns\n");
  } # while (<INFILE>)
#close(OUTFILE);
if ($dbh->err() != 0)
{
    print(WMFILE "Error building SQL SELECT statement
$DBI::errstr\n");
    print(STDERR "Error building SQL SELECT statement
$DBI::errstr\n");
}
$rows = $line_nbr;

$dbh->commit;
$dbh->disconnect;

# 
# Close the message tag
#
print(WMFILE "</MSG>\n");
#
# Write the comment tag
#
print(WMFILE "<RC>$err_level</RC>\n");
print(WMFILE "<ROWS>$rows</ROWS>\n");
($size) = (stat($extract_fn))[7];
print(WMFILE "<BYTES>$size</BYTES>\n");
print(WMFILE "<COMMENT>\n");
print(WMFILE "Log file:          $logfn\n");
print(WMFILE "WM Feedback file:  $feedback_path\n");
print(WMFILE "error level:       $err_level\n");
print(WMFILE " </COMMENT>\n");
print(WMFILE "<WARNING>0</WARNING>\n");
print(WMFILE "<SQLSTATE>0</SQLSTATE>\n");

print(STDERR "$rows processed\n");


#print("$sql_statement\n");

close(WMFILE);
exit;
################################################################################################################
#
# Subroutine to handle error messages and return codes
# 
################################################################################################################
sub error_proc
{   
    $err_code = $_[0];
    $err_msg  = $_[1];

    if ($err_code > $err_level)
    {
        $err_level = $err_code;
    }

    print(WMFILE "$err_msg \n");
    print("$err_msg\n");

    return;
}

Octave script.....
------------------------------------------
.
.
.
perl_sql = strcat("../programs/octave_sql_pipe.pl
",dbname," ",dbuser," ",dbpw);



command       = strcat(perl_sql," ","\" select
distinct pd_code_nm,replace(ltrim(rtrim(nm)),' ','_')
\
    from bdwnf.pd where pd_grp_id = 62 and pd_code_nm
\
    not in ('L99','L00') and pd_code_nm is not null  
\
    order by pd_code_nm\"");
pd_list_file = popen(command,"r");
while (feof(pd_list_file) == 0)
     [pd_id,description] = fscanf(pd_list_file,"%s
%s\n","C");

endwhile;




=====
Bruce Moore

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



-------------------------------------------------------------
Octave is freely available under the terms of the GNU GPL.

Octave's home on the web:  http://www.octave.org
How to fund new projects:  http://www.octave.org/funding.html
Subscription information:  http://www.octave.org/archive.html
-------------------------------------------------------------