Re: Importing and Executing Raw SQL: Problems with Semicolons
At the moment, there is no reason beyond the fact I hadn't thought to do it. I ideally would like be able to read a straight SQL-dump, so I guess that would work, though I'd have to parse through the SQL first to construct the arrays. But those are the kind of philosophical changes that I'm looking for, so thanks for your advice! Casey On Jan 24, 3:58 pm, Chris Hartjes [EMAIL PROTECTED] wrote: On Jan 24, 2008 2:48 PM, daphonz [EMAIL PROTECTED] wrote: But when I try to use it to insert a line like: INSERT INTO `categories` (`id`,`parent_id`,`slug`,`name`,`teaser`,`description`,`model`,`options`,`ordering`,`lft`,`rght`,`enabled`,`type`,`level`,`created`,`modified`) VALUES ('2','0','file-repository','File Repository','pnbsp;/ p',NULL,'Document','a:1:{i:0;s: 1:\0\;}','1','3','4','true','nested',NULL,'2007-11-01 17:33:17','2007-11-01 17:33:17'); Is there any reason you're not building up an array containing that information and using $this-Model-save($dataArray) ? -- Chris Hartjes Internet Loudmouth Motto for 2008: Moving from herding elephants to handling snakes... @TheKeyBoard:http://www.littlehart.net/atthekeyboard --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Importing and Executing Raw SQL: Problems with Semicolons
Hi Folks, I'm trying to create a self-install routine for my web app, part of which requires it to create multiple tables with some pre-set content. I use a system similar to that mentioned by Cakebaker, (http:// cakebaker.42dh.com/2007/04/16/writing-an-installer-for-your-cakephp- application/), which, for the most part, works fine. The problem arises when I'm trying to load the SQL data into the database. The tables will load just fine, but there are content rows that contain serialized information. When it comes to executing those lines of code, (using the rawQuery routine, I get an MySQL syntax error. Here's what I'm currently doing: $statements = file_get_contents($fileNameOfSQLDump,'FILE_TEXT'); $db-rawQuery($statements); I'm reasonably sure that the problem comes in on the first line of content that includes a serialized data string. Also, I've used this same SQL data to import through other programs such as PHPMyAdmin, so I know it's not a fundamental problem with the data itself. I've tried using the Sanitize class to escape the text, but that escapes everything, and nothing is executed. Any guidance in this area would be appreciated. Either in alternate methods to load in the SQL data dump, or basic philosophical changes in how I approach this problem. Thanks! Casey --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Importing and Executing Raw SQL: Problems with Semicolons
On Jan 24, 2008 11:14 AM, daphonz [EMAIL PROTECTED] wrote: Hi Folks, I'm trying to create a self-install routine for my web app, part of which requires it to create multiple tables with some pre-set content. I use a system similar to that mentioned by Cakebaker, (http:// cakebaker.42dh.com/2007/04/16/writing-an-installer-for-your-cakephp- application/), which, for the most part, works fine. The problem arises when I'm trying to load the SQL data into the database. The tables will load just fine, but there are content rows that contain serialized information. When it comes to executing those lines of code, (using the rawQuery routine, I get an MySQL syntax error. Here's what I'm currently doing: $statements = file_get_contents($fileNameOfSQLDump,'FILE_TEXT'); $db-rawQuery($statements); I'm reasonably sure that the problem comes in on the first line of content that includes a serialized data string. Also, I've used this same SQL data to import through other programs such as PHPMyAdmin, so I know it's not a fundamental problem with the data itself. Can you execute the same statements using LOAD DATA LOCAL INFILE? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Importing and Executing Raw SQL: Problems with Semicolons
I may not understand the LOAD DATA LOCAL INFILE fully, but doesn't that require special security access? I'm trying to get this system to work off of a generic apache server out-of-the-box. On Jan 24, 12:26 pm, b logica [EMAIL PROTECTED] wrote: On Jan 24, 2008 11:14 AM, daphonz [EMAIL PROTECTED] wrote: Hi Folks, I'm trying to create a self-install routine for my web app, part of which requires it to create multiple tables with some pre-set content. I use a system similar to that mentioned by Cakebaker, (http:// cakebaker.42dh.com/2007/04/16/writing-an-installer-for-your-cakephp- application/), which, for the most part, works fine. The problem arises when I'm trying to load the SQL data into the database. The tables will load just fine, but there are content rows that contain serialized information. When it comes to executing those lines of code, (using the rawQuery routine, I get an MySQL syntax error. Here's what I'm currently doing: $statements = file_get_contents($fileNameOfSQLDump,'FILE_TEXT'); $db-rawQuery($statements); I'm reasonably sure that the problem comes in on the first line of content that includes a serialized data string. Also, I've used this same SQL data to import through other programs such as PHPMyAdmin, so I know it's not a fundamental problem with the data itself. Can you execute the same statements using LOAD DATA LOCAL INFILE? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Importing and Executing Raw SQL: Problems with Semicolons
On Jan 24, 2008 1:46 PM, daphonz [EMAIL PROTECTED] wrote: I may not understand the LOAD DATA LOCAL INFILE fully, but doesn't that require special security access? If you have shell access to the database you can use this command to load the data into a table. One needs to have the FILE privilege unless LOCAL is used. See here for all the dirty details: http://dev.mysql.com/doc/refman/5.0/en/load-data.html Alternatively (because i'm not entirely sure what you have in your file--raw data or SQL statements) you can connect to mysql from a shell and use this syntax: \. /path/to/your/file I'm trying to get this system to work off of a generic apache server out-of-the-box. I realise that. What i mean is, can the statements in the file be run against a database without errors when doing so directly (ie. without PHP)? If you can't get shell access, can you load the contents of the file using PHPMyAdmin? The point is to ensure that your SQL syntax is ok to begin with. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Importing and Executing Raw SQL: Problems with Semicolons
Oh, sorry, I misunderstood your initial reply. So to properly answer you: yes. The SQL syntax works when directly loaded through the command line or PHPMyAdmin. When I remove any INSERT data that contains serialized content, my initial system works just fine, and the SQL file is successfully loaded into the MySQL server. But when I try to use it to insert a line like: INSERT INTO `categories` (`id`,`parent_id`,`slug`,`name`,`teaser`,`description`,`model`,`options`,`ordering`,`lft`,`rght`,`enabled`,`type`,`level`,`created`,`modified`) VALUES ('2','0','file-repository','File Repository','pnbsp;/ p',NULL,'Document','a:1:{i:0;s: 1:\0\;}','1','3','4','true','nested',NULL,'2007-11-01 17:33:17','2007-11-01 17:33:17'); And run it with query(), rawQuery(), or execute(), the system fails. I'm pretty sure it's related to those additional semi-colons in the serialized content. Thanks for your continued assistance. -Casey On Jan 24, 2:32 pm, b logica [EMAIL PROTECTED] wrote: On Jan 24, 2008 1:46 PM, daphonz [EMAIL PROTECTED] wrote: I may not understand the LOAD DATA LOCAL INFILE fully, but doesn't that require special security access? If you have shell access to the database you can use this command to load the data into a table. One needs to have the FILE privilege unless LOCAL is used. See here for all the dirty details: http://dev.mysql.com/doc/refman/5.0/en/load-data.html Alternatively (because i'm not entirely sure what you have in your file--raw data or SQL statements) you can connect to mysql from a shell and use this syntax: \. /path/to/your/file I'm trying to get this system to work off of a generic apache server out-of-the-box. I realise that. What i mean is, can the statements in the file be run against a database without errors when doing so directly (ie. without PHP)? If you can't get shell access, can you load the contents of the file using PHPMyAdmin? The point is to ensure that your SQL syntax is ok to begin with. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Importing and Executing Raw SQL: Problems with Semicolons
On Jan 24, 2008 2:48 PM, daphonz [EMAIL PROTECTED] wrote: But when I try to use it to insert a line like: INSERT INTO `categories` (`id`,`parent_id`,`slug`,`name`,`teaser`,`description`,`model`,`options`,`ordering`,`lft`,`rght`,`enabled`,`type`,`level`,`created`,`modified`) VALUES ('2','0','file-repository','File Repository','pnbsp;/ p',NULL,'Document','a:1:{i:0;s: 1:\0\;}','1','3','4','true','nested',NULL,'2007-11-01 17:33:17','2007-11-01 17:33:17'); Is there any reason you're not building up an array containing that information and using $this-Model-save($dataArray) ? -- Chris Hartjes Internet Loudmouth Motto for 2008: Moving from herding elephants to handling snakes... @TheKeyBoard: http://www.littlehart.net/atthekeyboard --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---