Announcement

Collapse
No announcement yet.

[SOLVED] SC7 & SC8: Update Statement wrong when field name is numeric

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • [SOLVED] SC7 & SC8: Update Statement wrong when field name is numeric

    Hi
    i have a table with tinyint(1) fields (Yes/No Checkboxes) where the field names are numbers like 2014, 2013, 2012.
    In this case, Scriptcase 8.00.0007 generates invalid SQL Statements (.....2014 = 0, 2013 = 0, 2012 = 0, 2011 = 0,....)

    This is not allowed. It schould be (`2014`=0, ...). Why you not put every fieldname in ``? Filed names with spaces like "This fieldname" you put allready in ``.

    Thank's for fix soon. Because it is a database other people use also, i not can change fieldnames.

    Best regards
    Steve

    UPDATE: Also tested in SC7, same problem.

  • #2
    If you go to the sql section can you apply the quotes to the fieldnames?
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      Aparently it is an adodb issue: http://bugs.mysql.com/bug.php?id=50180
      A hint on the fix may be here: https://github.com/ADOdb/ADOdb/blob/...-mysql.inc.php
      you would need to edit the file then, this one is not protected with zend guard. I havent tested it so you would need to do that yourself.

      Scriptcase is not to blame on this one I think...
      Last edited by rr; 09-12-2014, 05:00 AM.

      Comment


      • #4
        Who can i do?

        Hi Albert
        this sql generated from SC:
        Code:
        UPDATE User SET  2014 = 1, 2013 = 0, 2012 = 0, 2011 = 0 WHERE id = 919
        This not work.
        When i change to this:
        Code:
        UPDATE User SET  `2014` = 1, `2013` = 0, `2012` = 0, `2011` = 0 WHERE id = 919
        then it works. Who generates this SQL Code? SC or the adodb stuff? Who can i fix it?

        Best regards and thanks
        Steve

        @rr: I think the link you posted describes another problem, doesn't it?

        Comment


        • #5
          When you create a form then the fields are extracted from the table and put into the sql section. Did you correct the ' there? Or did you changed the generated code? If I understand rr correct then this problem is not easy solvable, so I would change the fields section myself. If there's a reserved word then sc puts quotes around them. The enhancement request could be to do the same for numeric fieldnames.

          But point is that identifiers need to start with a letter. So actually the ansi standard was not followed. So I consider this not as a bug. I understand that you can't change names so if it works for you to quote the fields in the sql section then I consider it solved. But you can always propose an enhancement request for quoting number fieldnames.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            Hi Albert
            i copied the sql Statement from the error message to phpmyadmin and put the quotes there.

            Where can i put the quotes in the sql section. I think in the grid i can, but in the form?

            Identifiers can be, when quoted, like that: see http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

            I not understand why the code who make the sql statement not always use quote... that's always a good idea. The code put the quotes as you said when for example a space is in the field name....

            Many thank's for your help. I'am happy with a workaround also. But i can not change the field names.... sorry.

            Best regards
            Steve

            Comment


            • #7
              Originally posted by stephanw View Post
              Hi Albert
              i copied the sql Statement from the error message to phpmyadmin and put the quotes there.

              Where can i put the quotes in the sql section. I think in the grid i can, but in the form?

              Identifiers can be, when quoted, like that: see http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

              I not understand why the code who make the sql statement not always use quote... that's always a good idea. The code put the quotes as you said when for example a space is in the field name....

              Many thank's for your help. I'am happy with a workaround also. But i can not change the field names.... sorry.

              Best regards
              Steve
              Otherwise, congratulations to the genious who used "years" as field names.
              /Giuseppe

              Professional Scriptcase Services
              Some Customers opinions

              Comment


              • #8
                Originally posted by Giu View Post
                Otherwise, congratulations to the genious who used "years" as field names.
                It's not the topic to talk about, how much sense this field names make. Fact is, the field names are allowed. But why sc not uses quotes to field names? And how can i do a workaround for that.

                I really thank's for serious solution... and yes, giu, i also not make field names like that. But someone did and the database is used by many "clients" and works with any other systems who access this db, like Access via ODBC and http://www.sqlmaestro.com for example... i think good for sc, if it supports any fieldnames who are allowed. Agree?

                Comment


                • #9
                  Originally posted by stephanw View Post
                  It's not the topic to talk about, how much sense this field names make. Fact is, the field names are allowed. But why sc not uses quotes to field names? And how can i do a workaround for that.

                  I really thank's for serious solution... and yes, giu, i also not make field names like that. But someone did and the database is used by many "clients" and works with any other systems who access this db, like Access via ODBC and http://www.sqlmaestro.com for example... i think good for sc, if it supports any fieldnames who are allowed. Agree?
                  I forgot to add on my topic, [OFFTOPIC]
                  /Giuseppe

                  Professional Scriptcase Services
                  Some Customers opinions

                  Comment


                  • #10
                    I was pretty convinced that you could change fieldnames, but this is only possible at grids. For now the only thing I can think of is to create a view where you use your fieldnames not starting with a number.
                    Albert Drent
                    aducom software netherlands
                    scriptcase partner, reseller, support and (turn-key) development
                    www.scriptcase.eu / www.scriptcase.nl

                    Comment


                    • #11
                      Originally posted by aducom View Post
                      I was pretty convinced that you could change fieldnames, but this is only possible at grids. For now the only thing I can think of is to create a view where you use your fieldnames not starting with a number.
                      Hi Albert
                      thank's for this idea. Works and does the trick for me!

                      But i hope, they will fix this soemtime, because i have to do many frontends for existing DB's for customers. And i wish that SC support every fieldname allowed.

                      Best regards
                      Steve

                      Comment


                      • #12
                        Closed as solved.
                        /Giuseppe

                        Professional Scriptcase Services
                        Some Customers opinions

                        Comment


                        • #13
                          Well, I stay with the idea that numeric fieldnames are not sql standard so I consider this as a bad design. I know that that doesn't help you a lot as you have to deal with it, but I think there are more important issues to fix currently ... But good that the issue is fixed.
                          Albert Drent
                          aducom software netherlands
                          scriptcase partner, reseller, support and (turn-key) development
                          www.scriptcase.eu / www.scriptcase.nl

                          Comment


                          • #14
                            I open again the thread, sorry, didn't noticed it was in Bugs forum.

                            Otherwise, Stephan, When you post a bug, best way is to report this bug too to bugs@scriptcase.net, and if you want add in your mail a link to the thread.
                            /Giuseppe

                            Professional Scriptcase Services
                            Some Customers opinions

                            Comment


                            • #15
                              Giu, why 'close' a thread? If you need to imho the best way is to close it as a moderator so that the thread cannot be replied upon. But I don't think that we want another standard answer like 'send to bugteam', 'thread closed' etc. ;-)
                              Albert Drent
                              aducom software netherlands
                              scriptcase partner, reseller, support and (turn-key) development
                              www.scriptcase.eu / www.scriptcase.nl

                              Comment

                              Working...
                              X