Error in SqlSever with field name DATA

Scriptcase 7 Bugs
#1

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

[INDENT]INSERT INTO TABLETEST (descriprion, info, “data”) VALUES (‘Test case’, ‘bla bla bla’, ‘2013-04-24 00:00:00:000’)[/INDENT]

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

[I][INDENT]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][/INDENT][/I]

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

#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’

#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

#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 :wink:

#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.

[INDENT][I]INSERT INTO TABLETEST (descriprion, info, “DATA”) VALUES (‘Test case’, ‘bla bla bla’, GETDATE())

UPDATE TABLETEST SET “DATA” = GETDATE()

SELECT “DATA” FROM TABLETEST[/I][/INDENT]

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

Giorgio.

#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.

#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

#8

Hi Bernhard

You’re right, the problem only happens with fields called “DATA”

Sorry for the mistake in CREATE statement

#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.

#10

[QUOTE=gbravi;15949]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.[/QUOTE]

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…

#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.

#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 ).

#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.

#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?

#15

Hello,

I will contact our bugs team regarding the issue.

regards,
Bernhard Bernsmann

#16

Thank you Bernhard

#17

Hello,

This problem has been registered in the system to be fixed.