sosolala
asked on
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:ni
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.
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:ni
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.
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
}
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
}
ASKER
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:ni
{ 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"
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:ni
{ 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"
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
$dbh->do('INSERT INTO FocusList ($fields) VALUES ($values)'); # do theinsert
should have been
$dbh->do("INSERT INTO FocusList ($fields) VALUES ($values)"); # do theinsert
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');
$dbh->do('DELETE FROM FocusList');
ASKER
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:ni
{ 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"
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:ni
{ 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"
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?
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?
ASKER
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)
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)
ASKER
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. :-)
ASKER
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. :-)
ASKER
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...
Gtz all,
ps I am getting a love/hate relation with perl...
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).
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).
ASKER
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",
""
},
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",
""
},
Dear expert(s),
A request has been made to close this Q in CS:
https://www.experts-exchange.com/questions/20556632/please-delete-and-refund-point.html
Without a response in 72 hrs, a moderator will finalize this question by:
22 Mar 2003
Thank you
RotaredoM
CS Mod & EE
A request has been made to close this Q in CS:
https://www.experts-exchange.com/questions/20556632/please-delete-and-refund-point.html
Without a response in 72 hrs, a moderator will finalize this question by:
22 Mar 2003
Thank you
RotaredoM
CS Mod & EE
# 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();
}
# 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();
}
ASKER
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/arrayfi
use DBI;
$dbh = DBI->connect('DBI:mysql:ni
{ 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{'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{'FILTERTAG'}\", \"$lst{'WATCHIT'}\", \"$lst{'PRICEFORMAT'}\",
\"$lst{'Symbol'}\", \"$lst{'Overlay'}\" )";
$dbh->do("$sql");
$i = 0;
}
}
$dbh->disconnect();
print "Complete\n";
exit;
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/arrayfi
use DBI;
$dbh = DBI->connect('DBI:mysql:ni
{ 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{'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{'FILTERTAG'}\", \"$lst{'WATCHIT'}\", \"$lst{'PRICEFORMAT'}\",
\"$lst{'Symbol'}\", \"$lst{'Overlay'}\" )";
$dbh->do("$sql");
$i = 0;
}
}
$dbh->disconnect();
print "Complete\n";
exit;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
$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