recordset search question
Hi, I'm sure this must've been done before, so if someone can point me at a discussion or assist me in some other way I'd appreciate it. If I'm browsing a paged list of invoices say in numerical order and I then want to reposition the list on a certain client, I can do a second query to the get the record id, but how would I easily reposition the browse listing back in date order on the record found in the second query? Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help creating relation
I could be wrong but I think your problem is the unique index. -- João Cândido de Souza Neto robert rottermann rob...@redcor.ch escreveu na mensagem news:4dd967a8.5040...@redcor.ch... Hi there, I would like to create a table that optionally links to an other table. The field company_id in the following table should be either Null or link to the table tblCompany (`id` ). How do I do that. The way I it now, I can not enter records where the field company_id is null. Thanks robert drop table IF EXISTS tblAgentCompanyMapper; CREATE TABLE IF NOT EXISTS `energie_2`.`tblAgentCompanyMapper` ( `siaid` INT COMMENT 'id assigned in the excelsheet by SIA' , `company_id` INT NULL DEFAULT NULL UNIQUE COMMENT 'id automatically used by energiecluster adresses' , `name` VARCHAR(128) NOT NULL , `url` VARCHAR(256) NULL , INDEX `fk_tblAgentCompanyMapper_tblCompany1` (`company_id` ASC) , PRIMARY KEY (`siaid`) , CONSTRAINT `fk_tblAgentCompanyMapper_tblCompany1` FOREIGN KEY (`company_id` ) REFERENCES `energie_2`.`tblCompany` (`id` ) ON DELETE SET NULL ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Deleting of records older than a specific date time
Hi Everyone I have posted this question quite a while back and noticed now that I haven't gotten an answer as yet and this is still on my unresolved list. We have 2 tables which we want to archive data from. We need to be able to delete all data from the 2 tables that is older than the start of yesterday. The process of determining the rows manually is easy enough, however we would like to cut out manual intervention and automate this process. I have played around with trying to create a stored routine / function which is then passed the date as a parameter and then to use this to determine the rows and delete them. - This created 2 problems for me though -- initially I struggled with the date format because it does not want to accept the date format. -- when at last it seemed to accept the date format , it caused the script to not exit upon completion. I am sure there must be a better way to do this than using functions and linux scripts to automate the process, however my experience is failing me in this respect as I am fairly new to the dba scene. I would really appreciate it if someone can assist me in this regard please. Regards Machiel PS someone already suggested me using MySQL scheduled events , however I was not able to figure out how to use it for this functionality though.
Re: Deleting of records older than a specific date time
A simple stored procedure run by the event scheduler at predetermined times will do this quite easily. Can you post the output of SHOW CREATE TABLE for your two tables? John On 23 May 2011 13:15, Machiel Richards machi...@rdc.co.za wrote: Hi Everyone I have posted this question quite a while back and noticed now that I haven't gotten an answer as yet and this is still on my unresolved list. We have 2 tables which we want to archive data from. We need to be able to delete all data from the 2 tables that is older than the start of yesterday. The process of determining the rows manually is easy enough, however we would like to cut out manual intervention and automate this process. I have played around with trying to create a stored routine / function which is then passed the date as a parameter and then to use this to determine the rows and delete them. - This created 2 problems for me though -- initially I struggled with the date format because it does not want to accept the date format. -- when at last it seemed to accept the date format , it caused the script to not exit upon completion. I am sure there must be a better way to do this than using functions and linux scripts to automate the process, however my experience is failing me in this respect as I am fairly new to the dba scene. I would really appreciate it if someone can assist me in this regard please. Regards Machiel PS someone already suggested me using MySQL scheduled events , however I was not able to figure out how to use it for this functionality though. -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Deleting of records older than a specific date time
Quoting Machiel Richards machi...@rdc.co.za: We need to be able to delete all data from the 2 tables that is older than the start of yesterday. Hi, I use this ver simple script to purge data from a syslog DB: #!/usr/local/bin/bash /usr/local/bin/mysql -u syslog -pmypasswd -e 'DELETE FROM SystemEvents WHERE ReceivedAt date_add(current_date, interval -60 day)' Syslog maybe that is useful for you...? cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
FW: MySQL shell...with parameters
Here’s an email from a good friend of mine that may be of interest. It is done in Ruby. From: Brad Robel-Forrest [mailto:b...@gigglewax.com] Sent: Sunday, May 15, 2011 7:01 PM To: Daevid Vincent Subject: MySQL shell...with parameters Remember a long while back I was bitching about the need for parameterized MySQL when working at the prompt? Well, it took me down a road and I've fulfilled that need and many more. If you're interested, I've opened a project on a shell that allows you to write all manner of SQL queries with variable interpolation and even generated query for programmatic searching through your database. Plus, it has the ability to do all of this remotely over an SSH connection without needing to have a mysql binary installed either locally or remotely. Main project site: https://github.com/bradrf/rsql Installing on Windows: https://github.com/bradrf/rsql/wiki/Install-on-Windows Tutorial: https://github.com/bradrf/rsql/raw/master/example.rsqlrc I've been using it a ton for analyzing our database at work for the past several months. It's a rare day that goes by where I don't fire this up. -brad
