Announcement

Collapse
No announcement yet.

Error in SqlSever with field name DATA

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

  • Error in SqlSever with field name DATA

    There is a problem generating applications where the database is SqlServer.
    In the specific case when a table contains a field named "DATA", the generated code contains an error because the sql field is considered as a string value and value added to each statement as "DATA".

    For example when I insert a record I get this result that generate a syntax error
    INSERT INTO TABLETEST (descriprion, info, "data") VALUES ('Test case', 'bla bla bla', '2013-04-24 00:00:00:000')
    This happens only with SqlServer and as a workaround I created a database replication in MySQL. When the application has been generated using the MySql connection then i change the connection and also works in SqlServer.

    I work with a database with more than 500 tables with frequent structure updates and this is really boring.

    A test case is easily accomplished. Just create a table in SqlServer with this structure and generate an application, grid or form it's the same

    CREATE TABLE [dbo]. [TABLETEST] (
    [id] [int] IDENTITY (1,1) NOT NULL,
    [descriprion] [nvarchar] (50) NULL,
    [info] [nvarchar] (50) NULL,
    [date] [datetime] NULL,
    * CONSTRAINT [PK_TABLETEST] PRIMARY KEY CLUSTERED
    (
    [id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF = ON ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]



    We are developing with version 7.0.009 but the probleme is present since version 6.0.039 (Ticket 18097-3030110837)

    I would be very grateful to anyone who wants to consider this case.

    Giorgio
    Giorgio Bravi
    Dolphin Software & Thinkware
    www.dolphin.it

  • #2
    It probabely is a bug, but I always advise developers not to use field names which could be interpreted as reserved word or which are duplicated over tables. It will prevent issues like these and ambiguity issues. Consider calling the fields like 'testdata' 'testdesc' 'testinfo'
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      Albert
      I know this may be a solution but the amount of database tables and the fact that it is shared with an ERP application does not enable this option.
      I expect the same behavior on each supported database and the fact that everything works well with MySQL makes it clear that it is a bug.
      The solution is very simple. It would be enough to change the representation of the field by enclosing it in brackets such as [DATA] instead of quotation marks "DATA".
      Quick and easy.

      Giorgio
      Giorgio Bravi
      Dolphin Software & Thinkware
      www.dolphin.it

      Comment


      • #4
        I don't disagree that it's a bug. But quoting reserved words is standard sql behaviour. [] is not. But it's not up to me to find solutions ;-)
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #5
          You know, I started using Sciptcase from version 6, but from a certain point has changed the behavior (6.0.39). Now I have to continue an important project with allocated resources and budget.
          You are right to say that the syntax is standard with quotes but in fact in Scriptcase does not work and returns an error.
          If I run the same statements in a T-Sql everything works with both the quotes and parentheses.
          INSERT INTO TABLETEST (descriprion, info, "DATA") VALUES ('Test case', 'bla bla bla', GETDATE())

          UPDATE TABLETEST SET "DATA" = GETDATE()

          SELECT "DATA" FROM TABLETEST
          We use FreeTDS for SqlServer connectivity under Linux and maybe the problem is located here. Do you know other options?

          Giorgio.
          Giorgio Bravi
          Dolphin Software & Thinkware
          www.dolphin.it

          Comment


          • #6
            I hope Bartho does. I don't run SC in all environments. If you have a support subscription I would try that one. I too am 'just a customer' of scriptcase. I run 6 and 7. And yes, sometimes changing behaviour can give unwanted effects. The way SC is handling updates is one of the flaws in my opinion as it changes your instance, but you cannot revert back to the old version. That should be solved in my point of view.
            Albert Drent
            aducom software netherlands
            scriptcase partner, reseller, support and (turn-key) development
            www.scriptcase.eu / www.scriptcase.nl

            Comment


            • #7
              Hello Giorgio,

              Issue reported to our bugs team. I'm going to ask them to check if the issue is related to a specific kind of connection (such as yours), or if it is a general bug (assuming it is a bug).

              Also is the issue happening to a field named "DATA" or "DATE"?

              In your create statement you changed from data to date: [date] [datetime] NULL,

              regards,
              Bernhard Bernsmann

              Comment


              • #8
                Hi Bernhard

                You're right, the problem only happens with fields called "DATA"

                Sorry for the mistake in CREATE statement
                Giorgio Bravi
                Dolphin Software & Thinkware
                www.dolphin.it

                Comment


                • #9
                  No sign of life?
                  Until now ( 7.00.0018 ) the problem still remain the same and we have to create a "mirror" database from SqlServer to MySql to work with columns called DATA.
                  It's tedious and penalizes the speed of development with Scriptcase, from my point of view one the best features.
                  Giorgio Bravi
                  Dolphin Software & Thinkware
                  www.dolphin.it

                  Comment


                  • #10
                    Originally posted by gbravi View Post
                    No sign of life?
                    Until now ( 7.00.0018 ) the problem still remain the same and we have to create a "mirror" database from SqlServer to MySql to work with columns called DATA.
                    It's tedious and penalizes the speed of development with Scriptcase, from my point of view one the best features.
                    Not sure, but could you try to use: data as mydata to alter the field name dynamically. I know, it's not a solution, but perhaps a workaround...
                    Albert Drent
                    aducom software netherlands
                    scriptcase partner, reseller, support and (turn-key) development
                    www.scriptcase.eu / www.scriptcase.nl

                    Comment


                    • #11
                      I really appreciate your suggestion Albert but this help only when you generate a Grid ( we are working in the same way ). Generating a Form you cannot interact with SQL code and the problem still remain.
                      Giorgio Bravi
                      Dolphin Software & Thinkware
                      www.dolphin.it

                      Comment


                      • #12
                        I think that using more standard Sql Server quotes like [DATA] instead of "DATA" in statements can easily resolve the problem but this is work for Scriptcase Team and they don't care ( it seems ).
                        Giorgio Bravi
                        Dolphin Software & Thinkware
                        www.dolphin.it

                        Comment


                        • #13
                          I know that support is not always what it should be, but in this case the database independencies should be kept in mind. What effect would it have on other databases...

                          Another option might be to create a view with different field names. I think that using reserved names like 'data' should be avoided but I know you have no option. But if you create a valid view over this table then it will work. Your other erp apps would not be affected.
                          Last edited by aducom; 07-24-2013, 03:33 PM.
                          Albert Drent
                          aducom software netherlands
                          scriptcase partner, reseller, support and (turn-key) development
                          www.scriptcase.eu / www.scriptcase.nl

                          Comment


                          • #14
                            Just to let you know that investigating around the problem i've found another workaround: set QUOTED IDENTIFIER ON on dberver to force double quotes "" instead of square bracket [] but sometime this create problems if you interface a database shared with other applications.

                            For SC staff: why not define a parameter at project/application level that allows the developer to choose an option or the other?
                            Giorgio Bravi
                            Dolphin Software & Thinkware
                            www.dolphin.it

                            Comment


                            • #15
                              Hello,

                              I will contact our bugs team regarding the issue.

                              regards,
                              Bernhard Bernsmann

                              Comment

                              Working...
                              X