Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Urgent. ..... Can't call method "prepare" on an undefined value at line 69

 1  #!/usr/bin/perl
     2
     3  use Shell;
     4
     5
     6  #@final = mdbarray("SP500.mdb", "FocusList");
     7  @final = cat("<focuslist.array");
     8
     9  use DBI;
    10
    11  $dbh = DBI->connect('DBI:mysql:nirvana:localhost', 'root', '',
    12  { RaiseError => 1, AutoCommit => 1}); #EDIT USERNAME AND AUTH
    13  $dbh->do('DROP TABLE IF EXISTS FocusList;');
    14  $dbh->do('CREATE TABLE FocusList (
    15  SIGRUNDATE date default NULL,
    16  w_BTNL smallint(6) default NULL,
    17  Overlay varchar(255) default NULL,
    18  WATCHIT smallint(6) default NULL,
    19  d_FTNS smallint(6) default NULL,
    20  Source smallint(6) default NULL,
    21  Symbol varchar(25) NOT NULL,
    22  NOTES text,
    23  POS smallint(6) default NULL,
    24  w_BTNS smallint(6) default NULL,
    25  BTRUNDATE date default NULL,
    26  w_BTPROFPCT int(11) default NULL,
    27  w_FTHR int(11) default NULL,
    28  Tag int(1) default NULL,
    29  w_BTSTART date default NULL,
    30  d_SIG smallint(6) default NULL,
    31  w_FTAPR int(11) default NULL,
    32  d_SIGTYPE double default NULL,
    33  w_FTNL smallint(6) default NULL,
    34  GOODOPPORTUNITY int(1) default NULL,
    35  New int(1) default NULL,
    36  END date default NULL,
    37  d_BTAPR int(11) default NULL,
    38  d_BTPROFPCT int(11) default NULL,
    39  w_FTNS smallint(6) default NULL,
    40  PRICEFORMAT tinyint(3) unsigned default NULL,
    41  w_ADV smallint(6) default NULL,
    42  d_BTSTART date default NULL,
    43  w_ADVISORCUTOFF smallint(6) default NULL,
    44  d_BARS smallint(6) default NULL,
    45  FILTERTAG int(1) default NULL,
    46  UPDATED date default NULL,
    47  d_ADVISORCUTOFF smallint(6) default NULL,
    48  d_BTHR int(11) default NULL,
    49  TYPE smallint(6) default NULL,
    50  d_FTAPR int(11) default NULL,
    51  w_SIG smallint(6) default NULL,
    52  d_BTNL smallint(6) default NULL,
    53  w_FTPROFPCT int(11) default NULL,
    54  BTEND date default NULL,
    55  d_BTNS smallint(6) default NULL,
    56  w_BARS smallint(6) default NULL,
    57  d_FTHR int(11) default NULL,
    58  w_BTHR int(11) default NULL,
    59  w_SIGTYPE double default NULL,
    60  w_BTAPR int(11) default NULL,
    61  d_FTPROFPCT int(11) default NULL,
    62  d_ADV smallint(6) default NULL,
    63  d_FTNL smallint(6) default NULL,
    64  PRIMARY KEY (Symbol)
    65  ) TYPE=MyISAM;' );
    66
    67  print @final
    68
    69  foreach $final(@final){
    70  $sth->prepare('INSERT INTO FocusList VALUES ($final);');
    71  $sth->execute();
    72  }
    73  $sth->finish();
    74  $dbh->disconnect();
    75  print "done"

---------------------------
The focus.array file
---------------------------


$VAR1 = {
          'SIGRUNDATE' => '2003-03-16 00:43:10',
          'w_BTNL' => '6',
          'Overlay' => undef,
          'WATCHIT' => '2',
          'd_FTNS' => '0',
          'Source' => '4',
          'Symbol' => 'ABI',
          'NOTES' => undef,
          'POS' => '0',
          'w_BTNS' => '5',
          'BTRUNDATE' => '2003-03-15 21:34:15',
          'w_BTPROFPCT' => '544',
          'w_FTHR' => '0',
          'Tag' => '0',
          'w_BTSTART' => '1999-05-12 07:00:00',
          'd_SIG' => '-8',
          'w_FTAPR' => '0',
          'd_SIGTYPE' => '1.0',
          'w_FTNL' => '0',
          'GOODOPPORTUNITY' => '0',
          'New' => '0',
          'END' => '2003-03-13 00:00:00',
          'd_BTAPR' => '120',
          'd_BTPROFPCT' => '105',
          'w_FTNS' => '0',
          'PRICEFORMAT' => '0',
          'w_ADV' => '74',
          'd_BTSTART' => '2002-03-19 15:30:00',
          'w_ADVISORCUTOFF' => '58',
          'd_BARS' => '32',
          'FILTERTAG' => '0',
          'UPDATED' => '2003-03-14 15:30:00',
          'd_ADVISORCUTOFF' => '87',
          'd_BTHR' => '83',
          'TYPE' => '1',
          'd_FTAPR' => '0',
          'w_SIG' => '-9',
          'd_BTNL' => '3',
          'w_FTPROFPCT' => '0',
          'BTEND' => '2003-03-14 15:30:00',
          'd_BTNS' => '3',
          'w_BARS' => '26',
          'd_FTHR' => '0',
          'w_BTHR' => '91',
          'w_SIGTYPE' => '-1.0',
          'w_BTAPR' => '155',
          'd_FTPROFPCT' => '0',
          'd_ADV' => '91',
          'd_FTNL' => '0'
        };
$VAR1 = {
          'SIGRUNDATE' => '2003-03-16 00:43:10',
          'w_BTNL' => '10',
          'Overlay' => undef,
          'WATCHIT' => '2',
          'd_FTNS' => '0',
          'Source' => '4',
          'Symbol' => 'ABK',
          'NOTES' => undef,
          'POS' => '0',
          'w_BTNS' => '13',
          'BTRUNDATE' => '2003-03-15 21:34:16',
          'w_BTPROFPCT' => '134',
          'w_FTHR' => '0',
          'Tag' => '0',
          'w_BTSTART' => '1998-06-03 07:00:00',
          'd_SIG' => '-9',
          'w_FTAPR' => '0',
          'd_SIGTYPE' => '-1.0',
          'w_FTNL' => '0',
          'GOODOPPORTUNITY' => '0',
          'New' => '0',
          'END' => '2003-03-13 00:00:00',
          'd_BTAPR' => '123',
          'd_BTPROFPCT' => '79',
          'w_FTNS' => '0',
          'PRICEFORMAT' => '0',
          'w_ADV' => '78',
          'd_BTSTART' => '2002-03-14 15:30:00',
          'w_ADVISORCUTOFF' => '70',
          'd_BARS' => '99',
          'FILTERTAG' => '0',
          'UPDATED' => '2003-03-14 15:30:00',
          'd_ADVISORCUTOFF' => '75',
          'd_BTHR' => '100',
          'TYPE' => '1',
          'd_FTAPR' => '0',
          'w_SIG' => '-8',
          'd_BTNL' => '2',
          'w_FTPROFPCT' => '0',
          'BTEND' => '2003-03-14 15:30:00',
          'd_BTNS' => '3',
          'w_BARS' => '3',
          'd_FTHR' => '0',
          'w_BTHR' => '70',
          'w_SIGTYPE' => '1.0',
          'w_BTAPR' => '29',
          'd_FTPROFPCT' => '0',
          'd_ADV' => '83',
          'd_FTNL' => '0'
        };

Can't call method "prepare" on an undefined value at ./nirvana2mysql.pl line 69

Good answer get the points otherwise I keep them myself.
0
sosolala
Asked:
sosolala
  • 8
  • 3
  • 3
  • +2
1 Solution
 
prady_21Commented:
Try this, it is working with my code
$dbh = Mysql->connect($Host, $Database, $User, $Passwd);
.
.
$sql_statement = " your statement";
.
.
..
$sth = $dbh->query($sql_statement);
if (!defined $sth) { warn($dbh->errmsg); }


all the best
0
 
biglugCommented:
Many many many problems here.

First: You're missing a semi-colon at the end of line 67.

Second: You're calling prepare on an undefined value. But you knew this.
When you're saying $sth->prepare(...) you're telling prepare to work with the $sth object .. only you don't have one. You'll have to make one first:
$sth = $dbh->prepare($statement);
And then you can $sth->execute(); Only that would be silly. If you're not using the bind parameters options available to $sth, then just use $dbh->do('INSERT ...');

Thirdly, if your information file is exactly what you're showing above, then it wont work. The file above is a dump from Data::Dumper. You need to slurp the whole thing and then parse it into useful information. The following will do that for you, its ugly, but it was quick to code:

#no need to use shell etc .. that's a complete waste

open(FILE,'focuslist.array');
{local $/=undef; $file=<FILE>}
close(FILE);

$file=~s/^\s*\$VAR1 \= \{//; #remove the top
$file=~s/\};\s*$//; # and bottom
@parts = split(/\};\n\$VAR1 \= \{/,$file); #split the remainder

foreach $part(@parts) {
     my %hash = eval('{' . $part . '}'); # Eval it into a hash
     foreach $key(keys %hash) {
          $fields .= "$key, "; # create a list of fields
          $values .= (($hash{key}) ? ($hash{key}=~/\D/ ? $dbh->quote($hash{$key}) : $hash{$key}) : 'null');
 #quote strings, don't quotes numbers, null for undef
          $values .= ', '; # whack a comma in there
     }
     $fields =~ s/,\s+$//; # remove trailing commas
     $values =~ s/,\s+$//; # here too.
     $dbh->do("INSERT INTO FocusList ($fields) VALUES ($values)"); # do the insert
}
0
 
sosolalaAuthor Commented:
Yo biglug,
I have tried you code..

I think we are getting almost there
can you tell me what is your coders instinct
makes of this...
 
DBD::mysql::db do failed: Unknown column '$fields' in 'field list' at ./nirvana.pl line 83.
DBD::mysql::db do failed: Unknown column '$fields' in 'field list' at ./nirvana.pl line 83.

For your information I have posted the complete script

#!/usr/bin/perl

open(FILE,'focuslist.array');
{local $/=undef; $file=<FILE>}
close(FILE);

$file=~s/^\s*\$VAR1 \= \{//; #remove the top
$file=~s/\};\s*$//; # and bottom
@parts = split(/\};\n\$VAR1 \= \{/,$file); #split the remainder


use DBI;

$dbh = DBI->connect('DBI:mysql:nirvana:localhost', 'root', '',
{ RaiseError => 1, AutoCommit => 1}); #EDIT USERNAME AND AUTH
$dbh->do('DROP TABLE IF EXISTS FocusList;');
$dbh->do('CREATE TABLE FocusList (
SIGRUNDATE date default NULL,
w_BTNL smallint(6) default NULL,
Overlay varchar(255) default NULL,
WATCHIT smallint(6) default NULL,
d_FTNS smallint(6) default NULL,
Source smallint(6) default NULL,
Symbol varchar(25) NOT NULL,
NOTES text,
POS smallint(6) default NULL,
w_BTNS smallint(6) default NULL,
BTRUNDATE date default NULL,
w_BTPROFPCT int(11) default NULL,
w_FTHR int(11) default NULL,
Tag int(1) default NULL,
w_BTSTART date default NULL,
d_SIG smallint(6) default NULL,
w_FTAPR int(11) default NULL,
d_SIGTYPE double default NULL,
w_FTNL smallint(6) default NULL,
GOODOPPORTUNITY int(1) default NULL,
New int(1) default NULL,
END date default NULL,
d_BTAPR int(11) default NULL,
d_BTPROFPCT int(11) default NULL,
w_FTNS smallint(6) default NULL,
PRICEFORMAT tinyint(3) unsigned default NULL,
w_ADV smallint(6) default NULL,
d_BTSTART date default NULL,
w_ADVISORCUTOFF smallint(6) default NULL,
d_BARS smallint(6) default NULL,
FILTERTAG int(1) default NULL,
UPDATED date default NULL,
d_ADVISORCUTOFF smallint(6) default NULL,
d_BTHR int(11) default NULL,
TYPE smallint(6) default NULL,
d_FTAPR int(11) default NULL,
w_SIG smallint(6) default NULL,
d_BTNL smallint(6) default NULL,
w_FTPROFPCT int(11) default NULL,
BTEND date default NULL,
d_BTNS smallint(6) default NULL,
w_BARS smallint(6) default NULL,
d_FTHR int(11) default NULL,
w_BTHR int(11) default NULL,
w_SIGTYPE double default NULL,
w_BTAPR int(11) default NULL,
d_FTPROFPCT int(11) default NULL,
d_ADV smallint(6) default NULL,
d_FTNL smallint(6) default NULL,
PRIMARY KEY (Symbol)
) TYPE=MyISAM;' );

#print @final;

foreach $part(@parts) {
    my %hash = eval('{' . $part . '}'); # Eval it into a hash
    foreach $key(keys %hash) {
         $fields .= "$key, "; # create a list of fields
         $values .= (($hash{key}) ? ($hash{key}=~/\D/ ?
$dbh->quote($hash{$key}) : $hash{$key}) : 'null');
#quote strings, don't quotes numbers, null for undef
         $values .= ', '; # whack a comma in there
    }
    $fields =~ s/,\s+$//; # remove trailing commas
    $values =~ s/,\s+$//; # here too.
    $dbh->do('INSERT INTO FocusList ($fields) VALUES ($values)'); # do theinsert
}


#for $VAR1 ( @final ) {
#        $sql = "INSERT INTO FocusList VALUES (";
#        for $Colname ( keys %$VAR1 ) {
#          $sql .= "$VAR1->{$Colname},";
#        }
#        chop ($sql);
#       $sql.=");";
#        $dbh->do($sql);
#    }
#
   $dbh->disconnect();
print "done"


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
biglugCommented:
Erm .. you get that error because you change my double quotes to single quotes:
$dbh->do('INSERT INTO FocusList ($fields) VALUES ($values)'); # do theinsert
should have been
$dbh->do("INSERT INTO FocusList ($fields) VALUES ($values)"); # do theinsert
0
 
biglugCommented:
Also, from a DBA perspective, just empty the table is you must. Don't drop it and re-create it!

$dbh->do('DELETE FROM FocusList');
0
 
sosolalaAuthor Commented:
changing the ' to " was a test of mine because the " " and '' didn;t work..
this is what I get with double quotes..
line 86 is the line with the insert statement

Allthough I have created the table according to the array
could the problem lay in that area?


DBD::mysql::db do failed: You have an error in your SQL syntax near '(0x85b7a5c)) VALUES (null)' at line 1 at ./nirvana.pl line 86.
DBD::mysql::db do failed: You have an error in your SQL syntax near '(0x85b7a5c)) VALUES (null)' at line 1 at ./nirvana.pl line 86.


#!/usr/bin/perl

open(FILE,'focuslist.array');
{local $/=undef; $file=<FILE>}
close(FILE);

$file=~s/^\s*\$VAR1 \= \{//; #remove the top
$file=~s/\};\s*$//; # and bottom
@parts = split(/\};\n\$VAR1 \= \{/,$file); #split the remainder


use DBI;

$dbh = DBI->connect('DBI:mysql:nirvana:localhost', 'root', '',
{ RaiseError => 1, AutoCommit => 1}); #EDIT USERNAME AND AUTH

$dbh->do('DELETE FROM FocusList');
$dbh->do('DROP TABLE IF EXISTS FocusList;');
$dbh->do('CREATE TABLE FocusList (
SIGRUNDATE date default NULL,
w_BTNL smallint(6) default NULL,
Overlay varchar(255) default NULL,
WATCHIT smallint(6) default NULL,
d_FTNS smallint(6) default NULL,
Source smallint(6) default NULL,
Symbol varchar(25) NOT NULL,
NOTES text,
POS smallint(6) default NULL,
w_BTNS smallint(6) default NULL,
BTRUNDATE date default NULL,
w_BTPROFPCT int(11) default NULL,
w_FTHR int(11) default NULL,
Tag int(1) default NULL,
w_BTSTART date default NULL,
d_SIG smallint(6) default NULL,
w_FTAPR int(11) default NULL,
d_SIGTYPE double default NULL,
w_FTNL smallint(6) default NULL,
GOODOPPORTUNITY int(1) default NULL,
New int(1) default NULL,
END date default NULL,
d_BTAPR int(11) default NULL,
d_BTPROFPCT int(11) default NULL,
w_FTNS smallint(6) default NULL,
PRICEFORMAT tinyint(3) unsigned default NULL,
w_ADV smallint(6) default NULL,
d_BTSTART date default NULL,
w_ADVISORCUTOFF smallint(6) default NULL,
d_BARS smallint(6) default NULL,
FILTERTAG int(1) default NULL,
UPDATED date default NULL,
d_ADVISORCUTOFF smallint(6) default NULL,
d_BTHR int(11) default NULL,
TYPE smallint(6) default NULL,
d_FTAPR int(11) default NULL,
w_SIG smallint(6) default NULL,
d_BTNL smallint(6) default NULL,
w_FTPROFPCT int(11) default NULL,
BTEND date default NULL,
d_BTNS smallint(6) default NULL,
w_BARS smallint(6) default NULL,
d_FTHR int(11) default NULL,
w_BTHR int(11) default NULL,
w_SIGTYPE double default NULL,
w_BTAPR int(11) default NULL,
d_FTPROFPCT int(11) default NULL,
d_ADV smallint(6) default NULL,
d_FTNL smallint(6) default NULL,
PRIMARY KEY (Symbol)
) TYPE=MyISAM;' );

#print @final;

foreach $part(@parts) {
    my %hash = eval('{' . $part . '}'); # Eval it into a hash
    foreach $key(keys %hash) {
         $fields .= "$key, "; # create a list of fields
         $values .= (($hash{key}) ? ($hash{key}=~/\D/ ?
$dbh->quote($hash{$key}) : $hash{$key}) : 'null');
#quote strings, don't quotes numbers, null for undef
         $values .= ', '; # whack a comma in there
    }
    $fields =~ s/,\s+$//; # remove trailing commas
    $values =~ s/,\s+$//; # here too.
    $dbh->do("INSERT INTO FocusList ($fields) VALUES ($values)"); # do theinsert
}

  $dbh->disconnect();
print "done"

0
 
ItatsumakiCommented:
Why are you putting in null for the undefs?  In DBI undef is the proper null-marker, and is appropriately moved & quoted by your DBD.

Also, the problem may be quoting, and might be clearer if you rewrote the query and printed out the SQL.  Do this:


my $sql = "INSERT INTO FocusList ($fields) VALUES ($values)";
print "$sql\n";
my $sth = $dbh->prepare($sql);
$sth->execute();

Then post here the error message and the SQL statement that is printed out.  It will help debugging immensely.  Also, what version of DBI and DBD::MySQL are these?  What OS?
0
 
sosolalaAuthor Commented:
Dear Itatsumaki,

The print of $sql =
INSERT INTO FocusList (HASH(0x85b7e90)) VALUES (null)

Why are you putting in null for the undefs?  In DBI undef is the proper null-marker, and is appropriately moved & quoted by your DBD.

What do you mean by this? (what should I change? to test)

I am using debian(linux as os) and the version of the dbi
is a package: libdbi-perl    1.32-1         The Perl5 Database Interface by Tim Bunce (where can I see the version of the dbi module?) (it is certainly not older then 3 months..when it was compiled from cpan)





0
 
sosolalaAuthor Commented:
I am willing to add 100 points extra for a complete working script..  what I can just copy and past. ....all data is is availeble in the question. :-)
0
 
sosolalaAuthor Commented:
I am willing to add 100 points extra for a complete working script..  what I can just copy and past. ....all data is is availeble in the question. :-)
0
 
sosolalaAuthor Commented:
sorry for the dubbel post...what I mean is 350 point for a complete working script..(ready copy and paste)

Gtz all,

ps I am getting a love/hate relation with perl...
0
 
ItatsumakiCommented:
Well, the problem is that you have badly formatted HTML, something to do with hashrefs instead of scalars, it looks like.  If you want I'll write this for you from scratch, but post:
a) what you start off with
b) what you want in as output

I'm not sure what I"m looking at right now, so I need a clear definition of starting material and end-goals (even if you just reword what is in the code in text).
0
 
sosolalaAuthor Commented:
I have an microsoft access database...

this mdb I would like to export into mysql so I can run
queries on it..  what you see above is my attemp of doing so.

I can access the mdb with various tools..

one of these tools creates an array as follow...


 
---------------------------
The focus.array file
---------------------------


$VAR1 = {
         'SIGRUNDATE' => '2003-03-16 00:43:10',
         'w_BTNL' => '6',
         'Overlay' => undef,
         'WATCHIT' => '2',
         'd_FTNS' => '0',
         'Source' => '4',
         'Symbol' => 'ABI',
         'NOTES' => undef,
         'POS' => '0',
         'w_BTNS' => '5',
         'BTRUNDATE' => '2003-03-15 21:34:15',
         'w_BTPROFPCT' => '544',
         'w_FTHR' => '0',
         'Tag' => '0',
         'w_BTSTART' => '1999-05-12 07:00:00',
         'd_SIG' => '-8',
         'w_FTAPR' => '0',
         'd_SIGTYPE' => '1.0',
         'w_FTNL' => '0',
         'GOODOPPORTUNITY' => '0',
         'New' => '0',
         'END' => '2003-03-13 00:00:00',
         'd_BTAPR' => '120',
         'd_BTPROFPCT' => '105',
         'w_FTNS' => '0',
         'PRICEFORMAT' => '0',
         'w_ADV' => '74',
         'd_BTSTART' => '2002-03-19 15:30:00',
         'w_ADVISORCUTOFF' => '58',
         'd_BARS' => '32',
         'FILTERTAG' => '0',
         'UPDATED' => '2003-03-14 15:30:00',
         'd_ADVISORCUTOFF' => '87',
         'd_BTHR' => '83',
         'TYPE' => '1',
         'd_FTAPR' => '0',
         'w_SIG' => '-9',
         'd_BTNL' => '3',
         'w_FTPROFPCT' => '0',
         'BTEND' => '2003-03-14 15:30:00',
         'd_BTNS' => '3',
         'w_BARS' => '26',
         'd_FTHR' => '0',
         'w_BTHR' => '91',
         'w_SIGTYPE' => '-1.0',
         'w_BTAPR' => '155',
         'd_FTPROFPCT' => '0',
         'd_ADV' => '91',
         'd_FTNL' => '0'
       };
$VAR1 = {
         'SIGRUNDATE' => '2003-03-16 00:43:10',
         'w_BTNL' => '10',
         'Overlay' => undef,
         'WATCHIT' => '2',
         'd_FTNS' => '0',
         'Source' => '4',
         'Symbol' => 'ABK',
         'NOTES' => undef,
         'POS' => '0',
         'w_BTNS' => '13',
         'BTRUNDATE' => '2003-03-15 21:34:16',
         'w_BTPROFPCT' => '134',
         'w_FTHR' => '0',
         'Tag' => '0',
         'w_BTSTART' => '1998-06-03 07:00:00',
         'd_SIG' => '-9',
         'w_FTAPR' => '0',
         'd_SIGTYPE' => '-1.0',
         'w_FTNL' => '0',
         'GOODOPPORTUNITY' => '0',
         'New' => '0',
         'END' => '2003-03-13 00:00:00',
         'd_BTAPR' => '123',
         'd_BTPROFPCT' => '79',
         'w_FTNS' => '0',
         'PRICEFORMAT' => '0',
         'w_ADV' => '78',
         'd_BTSTART' => '2002-03-14 15:30:00',
         'w_ADVISORCUTOFF' => '70',
         'd_BARS' => '99',
         'FILTERTAG' => '0',
         'UPDATED' => '2003-03-14 15:30:00',
         'd_ADVISORCUTOFF' => '75',
         'd_BTHR' => '100',
         'TYPE' => '1',
         'd_FTAPR' => '0',
         'w_SIG' => '-8',
         'd_BTNL' => '2',
         'w_FTPROFPCT' => '0',
         'BTEND' => '2003-03-14 15:30:00',
         'd_BTNS' => '3',
         'w_BARS' => '3',
         'd_FTHR' => '0',
         'w_BTHR' => '70',
         'w_SIGTYPE' => '1.0',
         'w_BTAPR' => '29',
         'd_FTPROFPCT' => '0',
         'd_ADV' => '83',
         'd_FTNL' => '0'
       };

an other tools creates a array like this


/******************************************************************/
/* THIS IS AN AUTOMATICALLY GENERATED FILE.  DO NOT EDIT IT!!!!!! */
/******************************************************************/

#include <stdio.h>
#include "types.h"
#include "dump.h"

const FocusList FocusList_array [] = {
{                               /*      0 */
                0,
        0,
        4,
        65527,
        65527,
        84,
        94,
        67,
        80,
        4,
        2,
        5,
        3,
        0,
        0,
        0,
        0,
        0,
        0,
        85,
        41,
        0,
        0,
        70,
        154,
        0,
        0,
        0,
        0,
        "",
        03/13/03 00:00:00,
        03/14/03 15:30:00,
        03/15/02 15:30:00,
        06/03/98 06:59:59,
        03/14/03 15:30:00,
        1,
        03/17/03 15:28:10,
        03/17/03 15:28:10,
        44,
        94,
        -1.000000,
        -1.000000,
        72,
        90,
        0,
        2,
        0,
        "AA",
        ""
},
{                               /*      1 */
                0,
        0,
        4,
        65528,
        65527,
        89,
        61,
        78,
        86,
        11,
        3,
        7,
        4,
        0,
        0,
        0,
        0,
        0,
        0,
        82,
        78,
        0,
        0,
        71,
        324,
        0,
        0,
        0,
        0,
        "",
        03/13/03 00:00:00,
        03/14/03 15:30:00,
        03/19/02 15:30:00,
        06/03/98 06:59:59,
        03/14/03 15:30:00,
        1,
        03/17/03 15:28:10,
        03/17/03 15:28:12,
        8,
        15,
        1.000000,
        -1.000000,
        80,
        60,
        0,
        2,
        0,
        "AAPL",
        ""
},



0
 
RotaredoMCommented:
Dear expert(s),

A request has been made to close this Q in CS:
http://www.experts-exchange.com/Community_Support/Q_20556632.html

Without a response in 72 hrs, a moderator will finalize this question by:
22 Mar 2003

Thank you

RotaredoM
CS Mod & EE
0
 
ItatsumakiCommented:
# Sorry, was out of the office.  You want this, I think.
# IT's mostly like biglug's.  If it does *NOT* work
# please post the output of the print statement so that
# I can debug properly.

open(FILE,'focuslist.array');
{local $/=undef; $file=<FILE>}
close(FILE);

$file=~s/^\s*\$VAR1 \= \{//; #remove the top
$file=~s/\};\s*$//; # and bottom
@parts = split(/\};\n\$VAR1 \= \{/,$file); #split the remainder

foreach $part(@parts) {
    my %hash = eval('{' . $part . '}'); # Eval it into a hash
    foreach $key(keys %hash) {
         $fields .= "$key, "; # create a list of fields
         $values .= (($hash{key}) ? ($hash{key}=~/\D/ ? $dbh->quote($hash{$key}) : $hash{$key}) : 'null');
#quote strings, don't quotes numbers, null for undef
         $values .= ', '; # whack a comma in there
    }
    $fields =~ s/,\s+$//; # remove trailing commas
    $values =~ s/,\s+$//; # here too.

    my $sql = "INSERT INTO FocusList ($fields) VALUES ($values)";

    print "$sql\n";

    my $sth = $dbh->prepare($sql);
    $sth->execute();

}
0
 
sosolalaAuthor Commented:
Dear Itatsumaki,

A friend of mine wrote the script below that works
btw this is what happen's if I execute you version.
 
./array2mysqldb2.pl
INSERT INTO FocusList (HASH(0x85c705c)) VALUES (null)
DBD::mysql::st execute failed: You have an error in your SQL syntax near '(0x85c
705c)) VALUES (null)' at line 1 at ./array2mysqldb2.pl line 97.
DBD::mysql::st execute failed: You have an error in your SQL syntax near '(0x85c
705c)) VALUES (null)' at line 1 at ./array2mysqldb2.pl line 97.

I have to extract about 40 tables from 10 mdb files and put
them all into a mysql db .. maybe you could help me make this job easier as you probly noticed there are many colloms. is there a way to make a more uniform script instade of 1 script per table.

points are almost yours...

gtz Sosolala

#!/usr/bin/perl


      use Shell;


#      @final = mdbarray("SP500.mdb", "FocusList");
      @final = cat("<./arrayfiles/arrayfile.SP500");

      use DBI;

      $dbh = DBI->connect('DBI:mysql:nirvana:localhost', 'root', '',
      { RaiseError => 1, AutoCommit => 1}); #EDIT USERNAME AND AUTH
      $dbh->do('DROP TABLE IF EXISTS FocusList;');
      $dbh->do('CREATE TABLE FocusList (
      Tag int(1) default NULL,
      New int(1) default NULL,
      Source smallint(6) default NULL,
      d_SIG smallint(6) default NULL,
      w_SIG smallint(6) default NULL,
      d_ADV smallint(6) default NULL,
      w_ADV smallint(6) default NULL,
      d_BTHR int(11) default NULL,
      w_BTHR int(11) default NULL,
      d_BTNL smallint(6) default NULL,
      w_BTNL smallint(6) default NULL,
      d_BTNS smallint(6) default NULL,
      w_BTNS smallint(6) default NULL,
      d_FTHR int(11) default NULL,
      w_FTHR int(11) default NULL,
      d_FTNL smallint(6) default NULL,
      w_FTNL smallint(6) default NULL,
      d_FTNS smallint(6) default NULL,
      w_FTNS smallint(6) default NULL,
      d_BTAPR int(11) default NULL,
      w_BTAPR int(11) default NULL,
      d_FTAPR int(11) default NULL,
      w_FTAPR int(11) default NULL,
      d_BTPROFPCT int(11) default NULL,
      w_BTPROFPCT int(11) default NULL,
      d_FTPROFPCT int(11) default NULL,
      w_FTPROFPCT int(11) default NULL,
      GOODOPPORTUNITY int(1) default NULL,
      POS smallint(6) default NULL,
      NOTES text,
      END date default NULL,
      UPDATED date default NULL,
      d_BTSTART date default NULL,
      w_BTSTART date default NULL,
      BTEND date default NULL,
      TYPE smallint(6) default NULL,
      BTRUNDATE date default NULL,
      SIGRUNDATE date default NULL,
      d_BARS smallint(6) default NULL,
      w_BARS smallint(6) default NULL,
      d_SIGTYPE double default NULL,
      w_SIGTYPE double default NULL,
      d_ADVISORCUTOFF smallint(6) default NULL,
      w_ADVISORCUTOFF smallint(6) default NULL,
      FILTERTAG int(1) default NULL,
      WATCHIT smallint(6) default NULL,
      PRICEFORMAT tinyint(3) unsigned default NULL,
      Symbol varchar(25) NOT NULL,
      Overlay varchar(255) default NULL,
      PRIMARY KEY (Symbol)
      ) TYPE=MyISAM;' );


my %lst =();

$i = 0;
for $row ( @final ) {
($var1, $var2, $var3, $var4, $var5) = split/'/, $row, 5;
if ($var4 eq ""){
$var4 = "undef";
}
$lst{"$var2"} = $var4;

$i++;

if($i>51){

$sql = "INSERT INTO FocusList VALUES ( \"$lst{'Tag'}\", \"$lst{'New'}\",
\"$lst{'Source'}\", \"$lst{'d_SIG'}\", \"$lst{'w_SIG'}\",
\"$lst{'d_ADV'}\", \"$lst{'w_ADV'}\", \"$lst{'d_BTHR'}\",
\"$lst{'w_BTHR'}\", \"$lst{'d_BTNL'}\", \"$lst{'w_BTNL'}\",
\"$lst{'d_BTNS'}\", \"$lst{'w_BTNS'}\", \"$lst{'d_FTHR'}\",
\"$lst{'w_FTHR'}\", \"$lst{'d_FTNL'}\", \"$lst{'w_FTNL'}\",
\"$lst{'d_FTNS'}\", \"$lst{'w_FTNS'}\", \"$lst{'d_BTAPR'}\",
\"$lst{'w_BTAPR'}\", \"$lst{'d_FTAPR'}\", \"$lst{'w_FTAPR'}\",
\"$lst{'d_BTPROFPCT'}\", \"$lst{'w_BTPROFPCT'}\",
\"$lst{'d_FTPROFPCT'}\", \"$lst{'w_FTPROFPCT'}\",
\"$lst{'GOODOPPORTUNITY'}\", \"$lst{'POS'}\", \"$lst{'NOTES'}\",
\"$lst{'END'}\", \"$lst{'UPDATED'}\", \"$lst{'d_BTSTART'}\",
\"$lst{'w_BTSTART'}\", \"$lst{'BTEND'}\", \"$lst{'TYPE'}\",
\"$lst{'BTRUNDATE'}\", \"$lst{'SIGRUNDATE'}\", \"$lst{'d_BARS'}\",
\"$lst{'w_BARS'}\", \"$lst{'d_SIGTYPE'}\", \"$lst{'w_SIGTYPE'}\",
\"$lst{'d_ADVISORCUTOFF'}\", \"$lst{'w_ADVISORCUTOFF'}\",
\"$lst{'FILTERTAG'}\", \"$lst{'WATCHIT'}\", \"$lst{'PRICEFORMAT'}\",
\"$lst{'Symbol'}\", \"$lst{'Overlay'}\" )";

$dbh->do("$sql");


$i = 0;
}

}

      $dbh->disconnect();
      print "Complete\n";
      exit;

0
 
RotaredoMCommented:
Dear all,

No objections to close this Q has made.

Since this question has valuable information that could help others, I refunded the points & PADed the question.

Thank you,

RotaredoM
CS Mod & EE
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now