questions about bind_param and mysql trace data included
below I have a snippet from a trace file read out for a section of code below. The
problem seems to be with '$sth-bind_param (2,$rh_row-{prop_str_addr});' The first
Bind works fine however the next one in the line listed produces a null entry per the
trace file. I have verified that the data is correct in '$rh_row-{prop_str_addr}' and
that it is quoted but for some reason myql isn't recieving the data any help would be
appreciated.
- dbd_st_execute 0 rows
- execute= '0E0' at db_load_1-2.pl line 128
- bind_param for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c 1
''310-673-5515'')
- bind_param= 1 at db_load_1-2.pl line 122
- execute for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c)
- dbd_st_execute for 08104e48
Binding parameters: UPDATE own_info
SET own_phone_home = '\'310-673-5515\''
WHERE own_str_addr = NULL
###CODE SNIPPETTE
$dbh = connect_try(**,**);
foreach $k (keys (%{$ar_info-[1]})){ # retrieves a generic set of fields and uses
them to assign values for each row.
if ($table eq prop_info){ # checks which table is being used and assigns the
correct SQL statement
$sth = $dbh-prepare (UPDATE prop_info
SET $k = ?
WHERE prop_str_addr = ?) or
err_trap(failed to prepare statement\n);
}elsif ($table eq own_info){
$sth = $dbh-prepare (UPDATE own_info
SET $k = ?
WHERE own_str_addr = ?) or
err_trap(failed to prepare statement\n);
}
$sth-trace(2, ./trace_data.txt);
foreach $rh_row (@$ar_info) { # iterates through the list of rows and assigns
the correct value to the field
print ::$k=$rh_row-{$k}; # this is an internal check to verify what
values are being inserted
$sth-bind_param (1,$rh_row-{$k});
if ($table eq prop_str_addr) {
$sth-bind_param (2,$rh_row-{prop_str_addr});
}elsif ($table eq own_str_addr) {
$sth-bind_param (2,$rh_row-{own_str_addr});
}
$sth-execute() or
err_trap(failed to execute statement\n);
}
print \n===\n;
}
$sth-finish();
$dbh-disconnect or
err_trap(failed to disconnect statement\n);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data dump using perl with mysql won't update
before I begin sorry for the cross post but i'm desperate
The routine in question is below. It's pretty straight forward its a series of loops
which basically matches the row then dumps data stored in hash ref into the DB. for
some reason the script runs with no errors (ran with strict) I can verify that the
data is being iterated, but when I check the Db no data is updated, the int fields
have been updated to 0 as opposed to NULL. is anyone seeing somethign I'm not.
sub write_db_2 {
my ($ar_info,$table) = @_;
my ($dbh,$sth,$k,$rh_row);
$dbh = connect_try(rowan,5340brig);
foreach $k (keys (%{$ar_info-[1]})){
if ($table eq prop_info){
$sth = $dbh-prepare (UPDATE prop_info
SET $k = ?
WHERE prop_str_addr = ?;) or
err_trap(failed to prepare statement\n);
}elsif ($table eq own_info){
$sth = $dbh-prepare (UPDATE own_info
SET $k = ?
WHERE own_str_addr = ?;) or
err_trap(failed to prepare statement\n);
}
foreach $rh_row (@$ar_info) {
print ::$k=$rh_row-{$k};
$sth-bind_param (1,$rh_row-{$k});
if ($table eq prop_str_addr) {
$sth-bind_param (2,$rh_row-{prop_str_addr});
}elsif ($table eq own_str_addr) {
$sth-bind_param (2,$rh_row-{own_str_addr});
}
$sth-execute() or
err_trap(failed to execute statement\n);
}
print \n===\n;
}
$sth-finish();
$dbh-disconnect or
err_trap(failed to disconnect statement\n);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data dump using perl with mysql won't update
If you want them set to NULL using DBI, you have to bind undef to the
proper parameter. Is that what you're doing, or are you binding
something else, such as 0 or the empty string?
No the initial values in the DB are set to null, with the exception of one field. I'm
trying to assign the correct values to each field as I iterate through the rows.
However after runnign the script the values either remain at null or 0 in the caase of
an intiger. I'm reposting a clearer version of the script which actually has remarks
sub write_db_2 { #updates specified table in a dump fasion using a hash table
my ($ar_info,$table) = @_;
my ($dbh,$sth,$k,$rh_row);
$dbh = connect_try(***,**);
foreach $k (keys (%{$ar_info-[1]})){ # retrieves a generic set of fields and uses
them to assign values for each row.
if ($table eq prop_info){ # checks which table is being used and assigns the
correct SQL statement
$sth = $dbh-prepare (UPDATE prop_info
SET $k = ?
WHERE prop_str_addr = ?;) or
err_trap(failed to prepare statement\n);
}elsif ($table eq own_info){
$sth = $dbh-prepare (UPDATE own_info
SET $k = ?
WHERE own_str_addr = ?;) or
err_trap(failed to prepare statement\n);
}
foreach $rh_row (@$ar_info) { # iterates through the list of rows and assigns
the correct value to the field
print ::$k=$rh_row-{$k}; # this is an internal check to verify what
values are being inserted
$sth-bind_param (1,$rh_row-{$k});
if ($table eq prop_str_addr) {
$sth-bind_param (2,$rh_row-{prop_str_addr});
}elsif ($table eq own_str_addr) {
$sth-bind_param (2,$rh_row-{own_str_addr});
}
$sth-execute() or
err_trap(failed to execute statement\n);
}
print \n===\n;
}
$sth-finish();
$dbh-disconnect or
err_trap(failed to disconnect statement\n);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best way to query multiple searchs --was bind_param and mysql
I must admit that I do not understand what you really have in mind witha search for multiple strings. If you are referring to specific MySQL features, could you please be so kind and provide a pointer to the relevant docs? maybe if I explain what i'm trying to do. I have a hash that has 4 strings for which I woudl like to search. Typically I would just do a SELECT * FROM table WHERE $1 OR $2 OR $3. However I wish to set up a ruitine that will automatically search for any given number of variables. reading the DBI book fromO'Rielly it pretty much says running a query for each variable via a loop then adding the result to an array is not efficient. I'm told bind_param is a better way this should allow me to Select each string via a loop. Then do one single fetch_all for all the results. However perhaps I m misunderstanding the use of bind. what would be the best way to approach this problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: questions about bind_param and mysql
Should work, as long as your parameters are all strings. With numbers you currently need to specify the type (for example DBI::INTEGER or similar, written from memory) as a third parameter. do I need to specify the DBD module in addition to DBI ? After reading the module info on CPAN it seemed to offer two options one just specifying DBI then anothe where you encoded the connection info using DBD then used DBI-connect to connect. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: questions about bind_param and mysql
I'm attempting a search for multiple strings using the bind_param option. My
understanding is the server will flag each string then after each string has been
selected and exicuted I can then do a fethall_array. However the the results only
include the last string searched. any ideas. an example query woudl be like [qw/%7th%
%8th%/] only values for %8th% are returned. help
+snippet+++
### extract search pattern, and values and seperate into veriables
my @search_pat = @_;
my $pat = $search_pat[0];
shift (@search_pat);
my $svalue;
my $dbh = connect_try(rowan,5340brig);
my $sql = SELECT str_no_addr, str_name_addr, cit_addr FROM
s3a_inglewood_project_info WHERE str_name_addr LIKE ?;;
## select rows in table based on search strings - only works with or
my $sth = $dbh-prepare ($sql) or err_trap(failed to prepare statement\n);
foreach $svalue (@search_pat){
$sth-bind_param( 1, $svalue);
$sth-execute or err_trap(failed to execute statement\n);
}
my $array_ref = $sth-fetchall_arrayref();
# place field names on top
unshift @$array_ref, [ 'id no.', 'street no.', 'street name', 'city' ];
# place search values in with everything to make sure i'm getting ligit values
unshift (@$array_ref, @search_pat);
$dbh-disconnect or err_trap(failed to disconnect at get_date statement\n);
gen_table ($array_ref);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
questions about bind_param and mysql
I have a snippet of code below. I'm trying to use teh bind_param option to select
multiple rows using a foreach loop. after doign some reading I realized this is
supprted through DBD are there any special measures that I need to take to make
bind_param work.
## select rows in table based on search strings - only works with or
foreach $search(@search_pat){
my $sth = $dbh-prepare (SELECT str_no_addr, str_name_addr, cit_addr
FROM s3a_inglewood_project_info
WHERE * LIKE ?;) or err_trap(failed to prepare
statement\n);
$sth-bind_param(1,$search);
## exicute and fetch selected rows
$sth-execute or
err_trap(failed to execute statement\n);
}
my $array_ref = $sth-fetchall_arrayref();
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unable to access mysql as anyone besideslinux user= root
whenever I try to connect to mysql logged on as anyone besides root I get teh error below. This isnt' making sence to me. The ps-ef output and the permissions info is also included. mysql 2524 2500 0 15:41 pts/100:00:00 /usr/sbin/mysqld-max --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/li mysql 2526 2524 0 15:41 pts/100:00:00 /usr/sbin/mysqld-max --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/li mysql 2527 2526 0 15:41 pts/100:00:00 /usr/sbin/mysqld-max --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/li mysql 2528 2526 0 15:41 pts/100:00:00 /usr/sbin/mysqld-max --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/li root 2568 2100 0 15:53 pts/100:00:00 ps -ef inglewood:/var/lib/mysql # l total 395 drwx--5 mysqldaemon616 Jan 24 15:41 ./ drwxr-xr-x 26 root root 672 Jan 21 03:06 ../ -rw-rw1 mysqldaemon 4884 Jan 21 03:06 inglewood-bin.001 -rw-rw1 mysqldaemon 347233 Jan 24 08:28 inglewood-bin.002 -rw-rw1 mysqldaemon 86 Jan 24 11:30 inglewood-bin.003 -rw-rw1 mysqldaemon 73 Jan 24 11:30 inglewood-bin.004 -rw-rw1 mysqldaemon 86 Jan 24 11:38 inglewood-bin.005 -rw-rw1 mysqldaemon414 Jan 24 15:37 inglewood-bin.006 -rw-rw1 mysqldaemon 86 Jan 24 15:40 inglewood-bin.007 -rw-rw1 mysqldaemon 73 Jan 24 15:41 inglewood-bin.008 -rw-rw1 mysqldaemon160 Jan 24 15:41 inglewood-bin.index -rw-rw1 mysqldaemon 4 Jan 24 15:41 inglewood.pid drwx--2 mysqldaemon528 Jan 21 03:06 mysql/ srwxrwxrwx1 mysqldaemon 0 Jan 24 15:41 mysql.sock= -rw-r--r--1 mysqldaemon 3831 Jan 24 15:41 mysqld.log -rw-r--r--1 mysqldaemon635 Jan 24 08:28 mysqld.log-20040122 drwx--2 mysqldaemon 48 Jan 24 14:42 studio_3/ drwx--2 mysqldaemon 2976 Jan 21 06:44 test/ [EMAIL PROTECTED]:~/perl mysqladmin -u root -p version Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! [EMAIL PROTECTED]:~/perl
unable to update root password or connect.
This is a new install of mysql 3.23 on a SuSE8.0 Box. I installd everythign via suse rpms. I ran mysql_install_db when I to run the update root password I get the following error. I'm logged into the server via ssh as root while exicuting the commands. any ideas. inglewood:/etc # mysqladmin -u root -h inglewood -p password '**' Enter password: mysqladmin: connect to server at 'inglewood' failed error: 'Host 'inglewood.studio3arc.com' is not allowed to connect to this MySQL server' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unable to start mysql new install
I am new so forgive me if i'm asking a stupid question. I did a reinstall (rpm) of my mysql 3.23 running on SuSE8.0 when I tried to run msql after running msql_install_db I get the error below. Just to give some bacground info. After getting connection errors regarding permissions (see previous e-mail) I deleted the mysql folder under /var/lib/mysql per the documentation. any help ? 040120 10:44:39 mysqld started 040120 10:44:39 Can't start server : Bind on unix socket: Permission denied 040120 10:44:39 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 040120 10:44:39 Aborting 040120 10:44:39 /usr/sbin/mysqld-max: Shutdown Complete 040120 10:44:39 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
