Deleting records while parsing query results (in Perl DBI)?

Hi, If I run a mysql query then work through the results of that query one at a time, deleting the record sometimes eg: while (my $href = $sth->fetchrow_hashref()) { if ($href->{'ExpiryDate'} eq '2003-02-22 00:00:00') { # Do something # then... $db->do("DELETE FROM MyTable WHERE ID

Re: Re: How to query an entire row?

At 13:53 24/02/03 +0100, you wrote: Hello. On Mon 2003-02-24 at 11:28:05 +, [EMAIL PROTECTED] wrote: > Hello, > > In a table like this: > > ID > Item1 char(100) > Item2 char(100) > . > . > ItemN char(100) > > What's the cleanest way to do this mysql query: > > SELECT * FROM MyTable WHERE AnyCo

How to query an entire row?

Hello, In a table like this: ID Item1 char(100) Item2 char(100) . . ItemN char(100) What's the cleanest way to do this mysql query: SELECT * FROM MyTable WHERE AnyColumn LIKE '%mysearch%' Only way I can think to do it is: SELECT * FROM MyTable WHERE (Item1 LIKE '%mysearch%' OR Item2 LIKE '%my

SET field manipulation

Hi, how can I update a set field such that all table rows get what they had in the set ORed with some other set value. I.E. If I have a field called SetField and rows: SetField A A,B A,B,C B etc How can I do what I'm trying to do with this mysql query? UPDATE MyTable SET SetField = (SetField

Query Help

Hi, How can I write a MySQL query to grab single lines of the form: ColumnNames: EnquiryID, Name, Address, Detail1, Detail2, Detail3, Detail4 From two joined tables: Tbl1: Enquiries -- ID Name Address Etc Tb12: Details ID EnquiryID Detail Etc -

Query Help

Hi, How can I write a MySQL query to grab single lines of the form: ColumnNames: EnquiryID, Name, Address, Detail1, Detail2, Detail3, Detail4 From two joined tables: Tbl1: Enquiries -- ID Name Address Etc Tb12: Details ID EnquiryID Detail Etc -

Perl DBI secret command?

Hi, just stumbled across the following piece of code: my $rowcount = $sth->rows(); And I checked it out compared to a count of the fetched rows from an SQL QUERY. AND It matched exactly! BUT, I can't find the command "rows()" in the DBI documentation. What's the story? Have I unearthed a cons

DBI Question?

Hi, if I call: my $record = $sth->fetchrow_hashref(); then: $sth->finish(); How valid is it for me to keep referring to my $record hash pointer? Especially if I use $sth in a subsequent transaction. Thanks, Jeff Snoxell Aetherweb Ltd http://www.aetherweb.co.uk NB. I know I aske

Help with SQL statement?

Hi, I've got a varchar field in a mysql table that contains 'N' numbers (each a maximum of 4 digits, not zero padded) separated by spaces when more than 1 number is present. eg. field content might be any of the following '' '123 4567 1234 45 3' '3' '3 4 6' I want to select only those records

Perl DBI $sth->fetchrow_hashref() persistence?

Hi, if I call: my $record = $sth->fetchrow_hashref(); then: $sth->finish(); How valid is it for me to keep referring to my $record hash pointer? Thanks, Jeff Snoxell Aetherweb Ltd http://www.aetherweb.co.uk [SPAM FILTER FODDER: MySQL,

More: What's returned from $sth->execute(); ?

Hi, I see your point re: >Some code to illustrate: > >printf "Query: %s\n", $query; >my $sth = $dbh->prepare ($query); >$sth->execute(); ># metadata information becomes available at this point ... >printf "NUM_OF_FIELDS: %d\n", $sth->{NUM_OF_FIELDS}; >print "Note: query has no result set\n" if $s

What's the point of SET fields?

Hi, I've setup a mysql table field which is a SET type, but it seems to be completely pointless. I can't see why I should use a SET type rather than a string for the following reasons: 1. I appear to be able to store whatever I want in there. MySQL doesn't enforce the rule that what I stick in

What's returned from $sth->execute(); ?

Hi, I've been using the return value of $sth->execute(); (in Perl DBI) to determine if I have a result set. I'm not using the value I get back but am assuming that if it's >=1 then I have some results. Is this a safe thing to do with MySQL? And The results do actually seem to be correct for the

When is learning chargeable to a client?

here do I draw the line? How can I identify a reasonable point at which it's fair to start logging some hours down to my client? Many thanks, Jeff Snoxell - Before posting, please check: http://www.mysql.com/manual.php

Using Perl DBI quote() method with NULL values?

Hello, I've got a mysql table with a lot of fields and I'm using a map statement to pass each of my values for a new record through the quote system so-as to have everything nicely wrapped up. Problem is that I can't find any way of writing a null value to my DB when the value has first passed

RE: Help with database Design

ients, # a comma separated list of ingredient ID's in a text field ) table ingredients( as above ) so then you can read in a list of ingredient id's for each recipe, then 'look up' the ingredients. Jeff Snoxell Aetherweb Ltd -

CREATE TABLE issue?

Hi again :) I found the following mysql stuff in the MySQL manual in a section explaining how to do relationships. CREATE TABLE persons ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(60) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE shirts ( id SMALLINT UNSIGNED NOT NULL

Restoring db from mysqldump output problem...

Hello, I've successfully backed up my database to JeffsDB.sql using mysqldump with the -opt option. When I try to restore it using: mysql -h myhost -u myusername -ppassword JeffsDB < JeffsDB.sql I get: ERROR 1065 at line 21: Query was empty Now, line 21 of the SQL file contains: /*!4 AL

Best Windows MySQL client?

What's the best MySQL client for windows? I've tried 5 or 10 shareware ones that seem ok but all seem a bit chasty (some more than others). Any recommendations? Thanks, Jeff - Before posting, please check: http://www.mysql

Re: Programmatic MySQL backup in Perl

Hello, I've successfully backed up my database to JeffsDB.sql using mysqldump with the -opt option. When I try to restore it using: mysql -h myhost -u myusername -ppassword JeffsDB < JeffsDB.sql I get: ERROR 1065 at line 21: Query was empty Now, line 21 of the SQL file contains: /*!4 AL

Programmatic MySQL backup in Perl

Hi, is there an easy way to programmatically backup MySQL database tables using Perl? My Perl code will be running on a machine remote to the MySQL server. Ta, Jeff - Before posting, please check: http://www.mysql.com/manua

Re: Accessing last_insert_id problem.

At 09:46 19/12/02 -0500, you wrote: Jeff Snoxell wrote: Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table Are you using InnoDB tables? You'll have to do something akin to ALTER TABLE my_table AUTO_INCREMENT=1 ... at least according to Paul ... :) No, I'm

re: Accessing last_insert_id problem.

I'm working with MySQL 3.23.36 so, according to "MySQL, Paul Dubois, New Riders" running the query: "DELETE FROM my_table_name" should reset the auto-increment value... but it doesnt'. What SQL do I use to reset the val. You can't believe anything that book says. The author didn't even incl

Re: Re: SELECT and UPDATE at the same time?

SELECT * FROM my_table WHERE Age > 50 AND UPDATE Status = "OLD" No. That's goofy anyway. Why wouldn't you just use a regular UPDATE query? UPDATE mytable Status = "OLD" WHERE Age > 50; Cos I want to do a fairly long-winded process on the records of those who are Age>50 and subsequently up

SELECT and UPDATE at the same time?

Hello again, I'm selecting a group of records from my database. I then loop through the selected records and do some work based on what I find. But what I also want to do as I interrogate each record is update some of its fields with new values... but won't that screw up the outer loop? I mean

re: Accessing last_insert_id problem.

Hi, I've got that sussed now and am happily using the aquired ref to subsequently play with the record etc. One other related problem and I recon I'm sorted How do I reset the auto-increment value? I'm working with MySQL 3.23.36 so, according to "MySQL, Paul Dubois, New Riders" running th

Accessing last_insert_id problem.

Hi, I'm adding records to a db using the Perl DBI. Subsequent to adding a record I need to know the value of the auto-incrementing 'Ref' field so that I can place a copy of the relavent details into a log file. I could query for the "LAST_INSERT_ID" but what if another process has added anothe

More locking issues

Hi, I'm new to MySQL. I see you all talking about locking databases etc... but isn't that the point of having a database server? So you don't have to worry about record locks? I'm designing the backend for a popular site where a user's visit will result in multiple read/write operations on a d

Inconsistent behavior when populating table?

Hi, I've written a Perl script to populate my MySQL table. It grabs values from my old database and uses the following for each to form a comma separated list of single quoted items... Each of the @values items is passed through: $value = $db->quote($original_value) first. $st = $db->prepare(

How to enter data in MySQLCC

Hi, I've installed MySQLCC as a client on Windows 2000, it connects via the net to a linux server running the mysql server and it's working ok to the point where I try to enter data into any cell of my tables other than one expecting a numerical value. I know MySQLCC is only in beta test mode b

Joined Tables Newbie Question

Hi, I've worked with a database system in the past that allows you to setup permanent joins between two tables in a database such that if you select record 1 say on a parent table and then add records to a child table, the child table automatically knows that this record is a sub-record of reco