Incorrect INSERT INTO

RHS · May 7, 2020, 9:27pm

Hi,

for an INSERT INTO is SC generated the following sql string:

INSERT INTO dbo.KHKAnsprechpartner (Nummer, Mandant, Adresse, Ansprechpartner, Gruppe, Titel, Vorname, Nachname, “Position”, Abteilung, Anrede, Briefanrede, ZuHaendenText, Telefon, Telefax, Mobilfunk, TelefonPrivat, Autotelefon, EMail, Geburtsdatum, Memo, “Timestamp”, USER_eMailPrivat, Transferadresse, USER_Newsletter, USER_NewsletterVK, USER_Weihnachsmailing) VALUES (916, 1, 15, ‘Test’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘-43’, ‘’, ‘’, ‘’, ‘’, ‘’, null, ‘’, null, ‘’, ‘’, ‘’, ‘’, ‘’)

Look at the fields “Position” and “Timestamp” …

My form (editable grid) looks like

The sql command should contain only the fields that enabled at the columns insert and/or update …

nmjohnlasantos · May 7, 2020, 8:32pm

Hello,

I did not understand your question very well. I believe these are reserved words and Scriptcase doing treatment with double quotes.
What is the database you are using? Could you explain a bit more about this problem?

RHS · May 7, 2020, 8:32pm

SC generated the SQL script in my first post for a insert instruction (MS SQL database). “Position” and “Timestamp” are reserved words yes, but this fields are not checked for insert / update (see image) and SC should not use this fields for the INSERT INTO instruction.

I can’t change this INSERT INTO instruction in a form (editable grid) …

RHS · May 7, 2020, 8:32pm

It this corrected or registered as bug?

bartho · May 7, 2020, 8:32pm

Hello Reinhard,

I will verify the bug status with our team.

regards,
Bernhard Bernsmann

gbravi · May 7, 2020, 8:32pm

Very old problem… as a workaround you need to change a setting server side and choose to use “” as qualifier.
A SR ticket is still open for that. Sql Server use [] as standard qualifier…
Waiting for an update from SC

RHS · May 7, 2020, 8:32pm

The qualifier is my second problem … the first problem is …

The sql command should contain only the fields that enabled at the columns insert and/or update …

… look at the image in first post.

@bartho: thanks.

gbravi · May 7, 2020, 8:32pm

I see… sorry non idea about that

nmjohnlasantos · May 7, 2020, 8:32pm

Hello,

This is not a bug. The options displayed in the insert and update settings fields are for the user to choose whether the field should be displayed in insert mode and/or update mode in the generated application.
These options are not related to the SQL command.

Fields, even if they are not displayed in the application, are used in the SQL command. If you want any values to be entered, you can configure this on Scriptcase or the database itself.

Concerning the use of brackets to deal with reserved words in SQL Server with Scriptcase, we recommend that you always use double quotes because Scriptcase uses brackets for global variables and this can create conflicts.
If the double quotes does not work, try another driver in the connection settings.

RHS · May 7, 2020, 8:32pm

But that would be useful …

How? I have a form (editable grid) and can’t influence the sql command …

nmjohnlasantos · May 7, 2020, 9:27pm

[QUOTE=RHS;20526]But that would be useful …

How? I have a form (editable grid) and can’t influence the sql command …[/QUOTE]

Hello,

In the settings of fields there is also a setting to insert and update in the database.
Please, see picture for attachment.

RHS · May 7, 2020, 8:32pm

Yes, but what is now the difference between your image and my in my first post? :rolleyes: For me it’s still a bug …

nmjohnlasantos · May 7, 2020, 8:32pm

Hello,

This is not a bug. The options displayed in the insert and update settings fields are for the user to choose whether the field should be displayed in insert mode and/or update mode in the generated application.
These options are not related to the SQL command.

Fields, even if they are not displayed in the application, are used in the SQL command. If you want any values to be entered, you can configure this on Scriptcase or the database itself.

What is the value being inserted in your field? If empty value or null value, you can configure this in the settings of DB value (Insert). In his picture of the first post, it is not configured.

RHS · May 7, 2020, 8:32pm

Again slowly …:

The options displayed in the insert and update settings fields are for the user to choose whether the field should be displayed in insert mode and/or update mode in the generated application.
These options are not related to the SQL command.

Yes. You mean Form Settings / Fields / <fieldname> / Disabled Field … That’s only for display, that’s correct.

But I mean (and you in post #11) Form Settings / Edit Fields. And here I have no possibility to hide fields (in SQL commands). Try it …

Edit: remove the update flag, save => ok. remove the new flag, save => new and update are checked … => bug!

nmjohnlasantos · May 7, 2020, 8:33pm

Hello,

Forgive me if I expressed myself badly. When you create a form, Scriptcase interprets all fields in the table, therefore it is impossible not to use a particular field in the SQL command, only if the field does not exist in your table.

What I meant is that if you just do not want to display a specific field, you can hide it in the editing fields (like you even know it). However, he will continue to be called in the application’s SQL command. Still in the editing fields, you can set it to receive a specific, maybe empty or null value if desired.

Well, the SQL command is only displayed if the debug mode is on, or if any SQL error occurs in your application.

[QUOTE=RHS;20531]
Try it …

Edit: remove the update flag, save => ok. remove the new flag, save => new and update are checked … => bug![/QUOTE]

If you uncheck the options for insert and update, is to say to the field is not displayed. Move down the field, in the fields not displayed block, and then the two options are unchecked.

What happens is that if the field is set to be displayed, you can not uncheck both options. The same occurs on the contrary, if the field is set to not be displayed, you can not check any of the options.

Anyway, I’ll check with the developers to make this clearer in the configuration interface of the fields, but that this is not a bug.

RHS · May 7, 2020, 8:33pm

Yes, that’s the problem … however, it would be simple & elegant to solve:

If the update flag is checked in Form Settings / Edit Fields then include this field in the UPDATE SQL command. If the new flag is checked then include this field in the INSERT INTO SQL command. If no flag checked then include this field only in the SELECT SQL command.

+++

My problem is still unresolved: how can I remove certain fields from SQL command?

I have therefore developed even further and found the following: the problem of post #1, I have this on a production server (LAMP) with PHP 5.3 (app generated with SC 7.1.0013). I have this error not with PHP 5.4 on my development machine (WIN7, SC standard installation). The difference is: on my machine running the MS SQL server native client (DBMS Type = MSSQL Server NATIVE SRV in SC) and on the production machine i’am use DBMS Type = MSSQL Server.

I will set up a new server with PHP 5.4 and look again …