We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

SQL Error???

    • 32241
    • 1,495 Posts
    wendy Reply #1, 18 years, 2 months ago
    Ok guys, this is a weird error.

    Check this error message that I received from MODx.
    DELETE FROM modx_eshop_items WHERE ItemID = 4; DELETE FROM modx_eshop_items WHERE ItemParentID = 4;
« MODx Parse Error »
MODx encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; DELETE FROM modx_eshop_items WHERE ItemParentID = 4' at line 1 »
      SQL: DELETE FROM modx_eshop_items WHERE ItemID = 4; DELETE FROM modx_eshop_items WHERE ItemParentID = 4;
      [Copy SQL to ClipBoard]


    I run that exact sql code on my mysql server directly from phpMyAdmin or comman interface, and it works just fine, but in MODx, it keeps sending me that error message. Isn’t it weird?
      Wendy Novianto
      [font=Verdana]PT DJAMOER Technology Media
      [font=Verdana]Xituz Media
    • opengeek Reply #2, 18 years, 2 months ago
      What is the exact statement responsible for executing the SQL from MODx?
        Jason Coward
        Chief Architect @ MODX
        http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
        • 32241
        • 1,495 Posts
        wendy Reply #3, 18 years, 2 months ago
        Quote from: OpenGeek at Mar 02, 2006, 06:18 PM

        What is the exact statement responsible for executing the SQL from MODx?

        Thanks for the reply Jason, basically it’s just a plain $modx->db->query($sql);

        I’ll look into this more I guess.
          Wendy Novianto
          [font=Verdana]PT DJAMOER Technology Media
          [font=Verdana]Xituz Media
        • sottwell Reply #4, 18 years, 2 months ago
          Sort of off-topic, but that’s a bad idea anyway. You should make sure the first delete works before trying the second or you can end up with a database full of orphan records. That’s what MySQL developers refer to as "atomic operations", sort of a poor man’s transactions.

          http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html
            Studying MODX in the desert - http://sottwell.com
            Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
            Join the Slack Community - http://modx.org
            • 32241
            • 1,495 Posts
            wendy Reply #5, 18 years, 2 months ago
            Quote from: sottwell at Mar 03, 2006, 11:53 AM

            Sort of off-topic, but that’s a bad idea anyway. You should make sure the first delete works before trying the second or you can end up with a database full of orphan records. That’s what MySQL developers refer to as "atomic operations", sort of a poor man’s transactions.

            http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html

            Thanks for the advice, it’s true though. I’m using innodb a lot, and I have been having problem with nusupported transaction lock on myisam table. Actually I want to use table lock on myisam, but the problem is, I don’t have any idea on how to pass multiple mysql statement using one call. In my old programming language, I’m so used to constructing mysql statement and put them all in 1 sql transaction, and I have exception block to revert back/roll back the transaction if something goes wrong. I miss that ability in my current development using MODx. I haven’t dig deeper into php yet, but I believe there has to be a way to pass multiple sql statement together.

            Any advice or articles link on this will be awesome.

            Thanks
              Wendy Novianto
              [font=Verdana]PT DJAMOER Technology Media
              [font=Verdana]Xituz Media
              • 34162
              • 1 Posts
              Zombie Reply #6, 18 years, 2 months ago
              I don’t think it is possible to do multiple sql statement in php but I think if you are using mysql 5 then you can use store procedure in mysql and run the store procedure in php. So in effect you achieving the same thing in mysql space rather in php space.
                • 32241
                • 1,495 Posts
                wendy Reply #7, 18 years, 2 months ago
                Quote from: chanh at Mar 04, 2006, 05:59 AM

                I don’t think it is possible to do multiple sql statement in php but I think if you are using mysql 5 then you can use store procedure in mysql and run the store procedure in php. So in effect you achieving the same thing in mysql space rather in php space.

                I like that idea though, but I think I will need to settle down with whatever tools that I have right now. grin

                Thanks for all the response.
                  Wendy Novianto
                  [font=Verdana]PT DJAMOER Technology Media
                  [font=Verdana]Xituz Media