?
Solved

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

Posted on 2003-03-16
17
Medium Priority
?
251 Views
Last Modified: 2012-08-13
 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
Comment
Question by:sosolala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 3
  • +2
17 Comments
 
LVL 3

Expert Comment

by:prady_21
ID: 8148489
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
 
LVL 1

Expert Comment

by:biglug
ID: 8148583
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
 
LVL 1

Author Comment

by:sosolala
ID: 8149334
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
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.

 
LVL 1

Expert Comment

by:biglug
ID: 8149443
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
 
LVL 1

Expert Comment

by:biglug
ID: 8149449
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
 
LVL 1

Author Comment

by:sosolala
ID: 8151112
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
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8152272
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
 
LVL 1

Author Comment

by:sosolala
ID: 8152460
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
 
LVL 1

Author Comment

by:sosolala
ID: 8152512
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
 
LVL 1

Author Comment

by:sosolala
ID: 8152546
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
 
LVL 1

Author Comment

by:sosolala
ID: 8152575
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
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8153106
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
 
LVL 1

Author Comment

by:sosolala
ID: 8153321
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
 

Expert Comment

by:RotaredoM
ID: 8170686
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
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8177988
# 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
 
LVL 1

Author Comment

by:sosolala
ID: 8178597
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
 

Accepted Solution

by:
RotaredoM earned 0 total points
ID: 8186492
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

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question