Mysql insert with different javascript objects in raw sql

💬 App Building
,
1

Hey folks,

I try to make an mysql insert into with data from and own defined data, see below.

INSERT INTO eddy_db.definition_keyword_pcb(
  {{ _.join(_.keys(form1.data), ', ') }},
  createdBy,
  createdAt
  )
values(
  {{ _.join(_.values(form1.data), ', '),
  {{ var1.value }},
  CURRENT_TIMESTAMP
  );

The query failes always with follwing message: "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 ''name_en, name_de, alternative_en, alternative_de, comment, dbComment, dbAssigne' at line 2".

I found this Issue: issue-link
But I ask me, why is this not possible in Raw SQL?

Thanks a lot!

2

With javascript it looks like:

{{ {..._.mapValues(form1.data, v => v === '' ? null : v), ...{"createdBy": var_user.value, "createdAt": new Date().toISOString().slice(0, 19).replace('T', ' ')} } }}

It looks very complex... for such a simple stuff...

3

This implemented with " GUI" looks like:

image
image902×1059 56.7 KB

I am serching for a RAW Mysql working code. Could anybody help?

4

Can you share an example of the data that you're trying to insert?

About your first message posted, you cannot dynamically mention columns in your SQL query, because your column_name will become a string value 'column_name' when prepared statements are ON in your database resource settings.

The 2nd approach should be working, where you are using GUI mode to add an object representing the new row. Can you show what the output of

{{ {..._.mapValues(......

looks like?

5

Hi @ali-sajjad-rizavi thank you for your message,

I am sorry. I did not mention: the second message with GUI approach works fine. But looks very complex.

For the GUI approach code:

{{ {..._.mapValues(global_drawerAddKeywordPcb_form_addKeywordPcb.data, v => v === '' ? null : v), ...{"createdById": global_var_appUser_userId.value, "createdAt": new Date().toISOString().slice(0, 19).replace('T', ' ')} } }}

The result object for the code above looks like

{
  "name_en": "Test",
  "name_de": "sdsd",
  "alternative_en": null,
  "alternative_de": null,
  "comment": null,
  "dbComment": null,
  "dbAssigneeId": null,
  "dbStatusId": 1,
  "createdById": 2,
  "createdAt": "2025-03-20 08:40:29"
}

The form global_drawerAddKeywordPcb_form_addKeywordPcb looks like:

image
image597×1090 40.9 KB

To your question:

About your first message posted, you cannot dynamically mention columns in your SQL query, because your column_name will become a string value 'column_name' when prepared statements are ON in your database resource settings.

Is there a way to remove the sting characters in mysql code with active prepared -statements? It seems to deacitivate prepared-statements will cause an high risk.

An update with raw mysql looks like following code works very fine:

UPDATE eddy_db.definition_keyword_pcb
SET
  {{ _.mapValues(global_drawerEditKeywordPcb_form_editKeywordPcb.data, v => v === '' ? null : v) }},
  updatedById = {{ global_var_appUser_userId.value }},
  updatedAt = CURRENT_TIMESTAMP
WHERE
  id = {{ keywordPage_formKeywordEdit_numberInput_id.value }};

I am very confused!

Greetings Georg

6

Ok, I did analyzed a little bit (I am a javascript newbie):

_.mapValues(global_drawerEditKeywordPcb_form_editKeywordPcb.data, v => v === '' ? null : v)

return following code:

{name_en: "multiplexer", name_de: "Multiplexer", alternative_en: "Mux", alternative_de: "Muxer", comment: null…}

The keys are without '-characters. This is why the update process works very fine.

In mysql there is no way to make an insert with key-value-pair. You have to sparate the value and the key in two lists.

To try a typical list, like:

_.map(_.keys(global_drawerAddKeywordPcb_form_addKeywordPcb.data))

returns ["name_en", "name_de", "alternative_en", "alternative_de", "comment", "dbComment", "dbAssigneeId", "dbStatusId"] and won't work. for the insert.

I found out there is a dataytpe Set. This could do the job like:

INSERT INTO eddy_db.definition_keyword_pcb(
  {{ new Set(_.map(_.keys(global_drawerAddKeywordPcb_form_addKeywordPcb.data), v => v === '' ? null : v)) }},
  createdById,
  createdAt
  )
values(
  {{ _.map(_.values(global_drawerAddKeywordPcb_form_addKeywordPcb.data), v => v === '' ? null : v) }},
  {{ global_var_appUser_userId.value }},
  CURRENT_TIMESTAMP
  );

But this won't work, too. The error message looks like: "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 ', createdById, createdAt ) values( 'dsfdf', 'sfddf', NULL, NULL, NULL, ' at line 2". It seems the Set is empty like {}.

If I place it in a tansformer I got the following message, I can't realize:

image
image1011×1294 57.5 KB

7

Hi @bauergeorg

Providing a set in the columns section will not work because we cannot use dynamic column values when prepared statements are ON in resource settings.

When you do something like

insert into table_name (
    {{ ["column1", "column2"].join(", ") }},
    column3,
    column4
)
values ('val1', 'val2', 'val3', 'val4');

Retool will convert it to

insert into table_name (
    'column1, column2, column3',
    column3,
    column4
)
values ('val1', 'val2', 'val3', 'val4');

And this is not valid SQL syntax.

Can you tell me why do you not want to use the GUI mode? Is it because it looks complex?

You can select GUI mode and specify the object as

_.mapValues(global_drawerEditKeywordPcb_form_editKeywordPcb.data, v => v === '' ? null : v)

And if it has the exact column names in keys, which also exist in the database, the insert should work seamlessly.

1 Like
8

Hi @ali-sajjad-rizavi,

with the current timestamp it looks very complex. I think I need for other tables mysql transactions. I have inserts and updates in 5 or more mysql tables. I don't know how I can realize it with GUI mode.

I think it should very simple to remove '-characters. But it seems: not...
One solution could be, to activate the same mysql connection wit and without prepared-statements. For the mysql update querys, I take the mysql connection with prepared-statements. For the mysql insert querys , I take the mysl connection without prepared-statements. If there is no other solution.

Thanks a lot!

9

@bauergeorg I don't recommend doing this but here's what you can do if you still want to use manual SQL mode. You can disable converting queries to prepared statements. This way, Retool will not make your substituted value into a string, and you wouldn't get ' characters by default.

image
image1092×1588 129 KB

1 Like