Login or Register to Ask a Question and Join Our Community


Programming

derefencing issue with perl and mysql

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming derefencing issue with perl and mysql
# 1  
Old 04-20-2009
derefencing issue with perl and mysql
I've tried every type of dereferencing I can think of but I still can't get to array data.

Code:
#!/usr/bin/perl 

use DBI;
use DBD::mysql;
use DBI qw(:sql_types);

$hostname = "localhost";
$database = "c2";
$username = "user";
$password = "password";

$dbh = DBI->connect("dbi:mysql:dbname=$database;host=$hostname", "$username", "$password", {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      ) || die "Database connection not made: $DBI::errstr";

$sth = $dbh->prepare("SELECT server_id FROM server WHERE server_name =?"); 
$ins = $dbh->prepare("INSERT INTO server_details (server_details_id, server_id, apt_source, cleaner_conf, crons, date, df, dmidecode, ethtool, fstab, group, hosts_allow, hosts_deny, hosts, ifconfig, j2_serverconf, last, lsmod, lspci, mdstat, mount_nfs, netstat, network, proc_scsi, pw, raidtab, rpm_list, sudoers, sysctl, tr114, uname, date_inserted) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");


$dir = "/usr/local/c2/serverfiles";
opendir(sf, $dir) or die "can't open $dir: $!";
while (defined($serv_dir = readdir(sf))) {
    @data_load = "";
    next if $serv_dir =~ /^\.\.?$/;    # skip . and ..
    $sth->execute($serv_dir) or die $sth->errstr;
    $sid = $sth->fetchrow_array;
        #logic to make new server entries if sid = null
    print "$serv_dir has a server id of: $sid\n"; 
        @xml = glob("$dir/$serv_dir/*.xml");
        @data_load[0] = "\t";
        @data_load[1] = "$sid";
        $inc = 2;
        foreach $xml_file (@xml) {
            #print "$xml_file";
            open(XMLFILE, "$xml_file" ) or die "can't open $xml_file: $!"; 
            while(<XMLFILE>) {
            @data_load[$inc] .= $_;
            }
            $inc++;
        }
        #foreach $x (@data_load) {
        #    print "$x\n";
        #}

$ins->bind_param(1,$data_load[0],SQL_VARCHAR); # server_details_id, primary key
$ins->bind_param(2,$data_load[1],SQL_INTEGER); # server_id foreign key
$ins->bind_param(3,$data_load[2],SQL_TEXT); # Line 49, the problem.
$ins->bind_param(4,$data_load[3],SQL_TEXT); # cleaner_conf
$ins->bind_param(5,$data_load[4],SQL_TEXT); # crons
$ins->bind_param(6,$data_load[5],SQL_TEXT); # date
$ins->bind_param(7,$data_load[6],SQL_TEXT); # df
$ins->bind_param(8,$data_load[7],SQL_TEXT); # dmidecode
$ins->bind_param(9,$data_load[8],SQL_TEXT); # ethtool
$ins->bind_param(10,$data_load[9],SQL_TEXT); # fstab
$ins->bind_param(11,$data_load[10],SQL_TEXT); # group
$ins->bind_param(12,$data_load[11],SQL_TEXT); # hosts_allow
$ins->bind_param(13,$data_load[12],SQL_TEXT); # hosts_deny
$ins->bind_param(14,$data_load[13],SQL_TEXT); # hosts
$ins->bind_param(15,$data_load[14],SQL_TEXT); # ifconfig
$ins->bind_param(16,$data_load[15],SQL_TEXT); # j2_serverconf
$ins->bind_param(17,$data_load[16],SQL_TEXT); # last
$ins->bind_param(18,$data_load[17],SQL_TEXT); # lsmod
$ins->bind_param(19,$data_load[18],SQL_TEXT); # lspci
$ins->bind_param(20,$data_load[19],SQL_TEXT); # mdstat
$ins->bind_param(21,$data_load[20],SQL_TEXT); # mount_nfs
$ins->bind_param(22,$data_load[21],SQL_TEXT); # netstat
$ins->bind_param(23,$data_load[22],SQL_TEXT); # network
$ins->bind_param(24,$data_load[23],SQL_TEXT); # proc_scsi
$ins->bind_param(25,$data_load[24],SQL_TEXT); # pw
$ins->bind_param(26,$data_load[25],SQL_TEXT); # raidtab
$ins->bind_param(27,$data_load[26],SQL_TEXT); # rpm_list
$ins->bind_param(28,$data_load[27],SQL_TEXT); # sudoers
$ins->bind_param(29,$data_load[28],SQL_TEXT); # sysctl
$ins->bind_param(30,$data_load[29],SQL_TEXT); # tr114
$ins->bind_param(31,$data_load[30],SQL_TEXT); # uname
$ins->bind_param(32,$data_load[31],SQL_DATETIME); # timestamp for record creation

$ins->execute() or warn $ins->errstr();
}
closedir(sf);

The error is:

Code:
DBI::st=HASH(0x863151c)->bind_param(...): attribute parameter 'SQL_TEXT' is not a hash ref at ./c2etl.pl line 49, <XMLFILE> line 1791.

I tried to call my array like @$data_load. I tried $$data_load[2], ${$data_load}[2], $data_load->[2].
# 2  
Old 04-21-2009
If you look at the source of DBI.pm, you will notice that there is no SQL_TEXT, so it is undefined and DBI expects either an integer or a hash reference for that parameter.

Are you sure bind_param() is strictly needed? I have been using placeholder params with DBI without using bind_param() at all ...
# 3  
Old 04-21-2009
Yes, the SQL_* stuff was superflous. Also, I was using a reserved word as a column name and had autocommit = 0.

Here is the working code:

Code:
#!/usr/bin/perl 

#use DBI;
use DBD::mysql;
use DBI qw(:sql_types);

$hostname = "localhost";
$database = "c2";
$username = "collect";
$password = "password";

$dbh = DBI->connect("dbi:mysql:dbname=$database;host=$hostname", "$username", "$password", {
                          RaiseError => 1,
                          AutoCommit => 1
                        }
                      ) || die "Database connection not made: $DBI::errstr";

$sth = $dbh->prepare("SELECT server_id FROM server WHERE server_name =?"); 
$ins = $dbh->prepare("INSERT INTO server_details (server_id, apt_source, cleaner_conf, crons, date, df, dmidecode, ethtool, fstab, groups, hosts, hosts_allow, hosts_deny, ifconfig, j2_serverconf, last, lsmod, lspci, mdstat, mount_nfs, netstat, network, proc_scsi, pw, raidtab, rpm_list, sudoers, sysctl, tr114, uname) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");


$dir = "/usr/local/c2/serverfiles";
opendir(sf, $dir) or die "can't open $dir: $!";
while (defined($serv_dir = readdir(sf))) {
        @data_load = "";
        next if $serv_dir =~ /^\.\.?$/; # skip . and ..
        $sth->execute($serv_dir) or die $sth->errstr;
        $sid = $sth->fetchrow_array;
                #logic to make new server entries if sid = null
        print "$serv_dir has a server id of: $sid\n"; 
                @xml = glob("$dir/$serv_dir/*.xml");
                @data_load[0] = "$sid";
                $inc = 1;
                foreach $xml_file (@xml) {
                         open(XMLFILE, "$xml_file" ) or die "can't open $xml_file: $!"; 
                        while(<XMLFILE>) {
            @data_load[$inc] .= $_;
            }
            $inc++;


$ins->bind_param(1,$data_load[0]); # server_id foreign key
$ins->bind_param(2,$data_load[1]); #apt_source
$ins->bind_param(3,$$data_load[2]); # cleaner_conf
$ins->bind_param(4,$data_load[3]); # crons
$ins->bind_param(5,$data_load[4]); # date
$ins->bind_param(6,$data_load[5]); # df
$ins->bind_param(7,$data_load[6]); # dmidecode
$ins->bind_param(8,$data_load[7]); # ethtool
$ins->bind_param(9,$data_load[8]); # fstab
$ins->bind_param(10,$data_load[9]); # group
$ins->bind_param(11,$data_load[10]); # hosts_allow
$ins->bind_param(12,$data_load[11]); # hosts_deny
$ins->bind_param(13,$data_load[12]); # hosts
$ins->bind_param(14,$data_load[13]); # ifconfig
$ins->bind_param(15,$data_load[14]); # j2_serverconf
$ins->bind_param(16,$data_load[15]); # last
$ins->bind_param(17,$data_load[16]); # lsmod
$ins->bind_param(18,$data_load[17]); # lspci
$ins->bind_param(19,$data_load[18]); # mdstat
$ins->bind_param(20,$data_load[19]); # mount_nfs
$ins->bind_param(21,$data_load[20]); # netstat
$ins->bind_param(22,$data_load[21]); # network
$ins->bind_param(23,$data_load[22]); # proc_scsi
$ins->bind_param(24,$data_load[23]); # pw
$ins->bind_param(25,$data_load[24]); # raidtab
$ins->bind_param(26,$data_load[25]); # rpm_list
$ins->bind_param(27,$data_load[26]); # sudoers
$ins->bind_param(28,$data_load[27]); # sysctl
$ins->bind_param(29,$data_load[28]); # tr114
$ins->bind_param(30,$data_load[29]); # uname
#$ins->bind_param(31,$data_load[30],SQL_DATETIME); # timestamp
$ins->execute() || die "FAIL: $DBI::errstr";
}
closedir(sf);

# 4  
Old 04-21-2009
Quote:
Originally Posted by cbkihong
Are you sure bind_param() is strictly needed? I have been using placeholder params with DBI without using bind_param() at all ...
I am not sure they are needed. How do you do this without the bind_params?
# 5  
Old 04-22-2009
DBI - Database independent interface for Perl - search.cpan.org

Look at the second syntax of execute() with bind params. That's what I customarily use.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

syntax issue mysql in bash script

I'm running mysql in a bash script mysql <<EOF query EOF one query is like this: UPDATE $dbname.$prefix"config" SET value = $var WHERE "$prefix"config.name = 'table colname'; with variable but it's giving an error i'm not sure what to put for "$prefix"config.name the table... (3 Replies)
Discussion started by: vanessafan99
3 Replies

2. Shell Programming and Scripting

syntax issue with quotes in mysql command for a bash script

i'm trying to write a bash script that executes a mysql statement mysql -sN -e INSERT INTO "$database"."$tableprefix"users (var1, var2,var3) VALUES (123, '1','') i don't know where to put the quotes it doesnt work with this one: ` it seems i can only put double quotes around the... (0 Replies)
Discussion started by: vanessafan99
0 Replies

3. Programming

LEFT JOIN issue in Mysql

I have a data table as follows: mysql> select * from validations where source = "a03"; +------------+-------+--------+ | date | price | source | +------------+-------+--------+ | 2001-01-03 | 80 | a03 | | 2001-01-04 | 82 | a03 | | 2001-01-05 | 84 | a03 | | 2001-01-06... (2 Replies)
Discussion started by: figaro
2 Replies

4. Programming

Help with mySQL database by perl script

Hello; I was trying to set up a mysql database using following script, but never went through. The code seems fine without any syntax error as I tested it: perl -c Brapa0101-db.pl Brapa0101-db.pl syntax OKHowever, whenever I run it, an error message was tossed out: DBD::mysql::st execute... (7 Replies)
Discussion started by: yifangt
7 Replies

5. Linux

mysql server start issue

Hi, I have installed mysql in linux box as mysql-5.0.77-4.el5_6.6.. I would like to start the server. But there is no mysql file in this location /etc/init.d/ Please advice how to start the server now !! Thnaks, Mani (5 Replies)
Discussion started by: Mani_apr08
5 Replies

6. Web Development

Strange Mysql issue

Hi all, I recently changed the name of my hostname from 'abc123' to 'abc456' (as an example). I then added a user in my mysql database with the new host and removed references to the old users and hostname. The strange thing is though, a process using the database still uses the old... (1 Reply)
Discussion started by: muay_tb
1 Replies

7. Shell Programming and Scripting

perl+CGI+mysql !!!!!!!

hi expert, I am totally new to perl CGI coding. And stop by below issue: 1> i have a script names conn.pl, which can connect to mysql and get the information of table user(id,name) 2> i copied above code into one CGI web page named user.cgi 3> when i view user.cgi in web browser, it toldme... (3 Replies)
Discussion started by: summer_cherry
3 Replies

8. UNIX and Linux Applications

mysql query browser issue

Hi, I'm new to mysql, I've installed MYSQL on our unix server and started MYSQL on this server. I've now downloaded MYSQL Query Browser, when I try to connect to MYSQL server, I get the following error "could not connect to the specified instance" - MySQL Error Number 1130. From the... (3 Replies)
Discussion started by: venhart
3 Replies

9. UNIX for Dummies Questions & Answers

PHP-MySQL: POST issue

I am moving from a shared hosting environment to a dedicated box. The migration also involves a mySQL database managed using a web-based PHP interface. I can read the contents of the db (connection is good) but when I try to administer, the form variables aren't going from step 1 (add) to step 2... (0 Replies)
Discussion started by: adrious
0 Replies

10. UNIX for Dummies Questions & Answers

mysql issue

I have a lot of files getting this on a new install of phpmysql 4.3.4 any ideas what would be causing this... Fatal error: Call to undefined function: mysql_connect() in /home/httpd/vhosts/ucandevelopments.com/httpdocs/includes/functions/database.php on line 19 (4 Replies)
Discussion started by: Blackrose
4 Replies
Login or Register to Ask a Question