Need help with onAfterinsert eventt. Insert records.

Applications Forms
#1

Hello all, I need to perform the following MySQL code (which works in MySQL) in form event onAfterInsert. It must insert multiple records into multiple tables. I cannot figure out the syntax for the event. Here is the MySQL code.

BEGIN;
INSERT INTO module1_1_v2 (item_title, comp_id, module)
VALUES
((SELECT item_title FROM module_item_titles WHERE module = ‘111’),
[comp_id],
(SELECT module FROM module_item_titles WHERE module = ‘111’)),
((SELECT item_title FROM module_item_titles WHERE module = ‘112’),
[comp_id],
(SELECT module FROM module_item_titles WHERE module = ‘112’)),
((SELECT item_title FROM module_item_titles WHERE module = ‘113’),
[comp_id],
(SELECT module FROM module_item_titles WHERE module = ‘113’));
INSERT INTO module1_2_v2 (item_title, comp_id, module)
VALUES
((SELECT item_title FROM module_item_titles WHERE module = ‘121’),
[comp_id],
(SELECT module FROM module_item_titles WHERE module = ‘121’)),
((SELECT item_title FROM module_item_titles WHERE module = ‘122’),
[comp_id],
(SELECT module FROM module_item_titles WHERE module = ‘122’)),
((SELECT item_title FROM module_item_titles WHERE module = ‘123’),
[comp_id],
(SELECT module FROM module_item_titles WHERE module = ‘123’)),
((SELECT item_title FROM module_item_titles WHERE module = ‘124’),
[comp_id],
(SELECT module FROM module_item_titles WHERE module = ‘124’)),
((SELECT item_title FROM module_item_titles WHERE module = ‘125’),
[comp_id],
(SELECT module FROM module_item_titles WHERE module = ‘125’)),
((SELECT item_title FROM module_item_titles WHERE module = ‘126’),
[comp_id],
(SELECT module FROM module_item_titles WHERE module = ‘126’));
COMMIT;

#2

Hi Mark, can you describe your problem? Afaik it’s business as useual: create a sql statement and execute it. It might be that you need a sc_committrans before as your current transaction might be corrupted (of your form). Do you get an error message?

#3

It gives me Syntax error at ‘INSERT INTO module1_1_v2 (item_ti’ and stops right there.

#4

Does this code work outside Scriptcase?

#5

Yes, works from MySql cli. Except of course where [comp_id] which is needed in the app to pass company variable. In MySql cli, I tested with static comp_id of ‘5’ and it works as intended. I just cannot get the beginning and ending code in to work with sc_sql_exec and I get that error.

#6

A Different way

This is what I am currently attempting.
Start Code:

$insert = “insert into module1_1_v2 (comp_id, item_title, module)
({comp_id}, (select item_title, module from module_item_titles where mod_table_id = ‘m11’))”;
)
sc_exec_sql($insert);
sc_commit_trans();

End Code:

But I get this error after the below statement.
This is what it is trying to insert.

(mysqlt): insert into module1_1_v2 (comp_id, item_title, module) (5 , (select item_title, module from module_item_titles where mod_table_id = ‘m11’))

This is the error.

1064: 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 '5 , (select item_title, module from module_item_titles where mod_table_id = ‘m11’ at line 2

It is pulling the new comp_id from the field, but it looks like sql is not closed or quoted correctly. Does anyone see the mistake in the code?

After successful, I will then add the other module tables inserts.

#7

Where is the VALUES?

#8

You referring to the VALUES comment in string or where are they actually coming from? I have tried

$insert = “insert into module1_1_v2 (comp_id, item_title, module)
VALUES
({comp_id}, (select item_title, module from module_item_titles where mod_table_id = ‘m11’))”;
)
sc_exec_sql($insert);
sc_commit_trans();

This does not work, {comp_id} is from form field, item_title and module is select from other table.

#9

Your {comp_id} has to be inside of the select.

$insert = “insert into module1_1_v2 (comp_id, item_title, module)
VALUES
(select {comp_id}, item_title, module from module_item_titles where mod_table_id = ‘m11’)”;

jsb

#10

Hi jsb,

I just tried that. No go. I get the following.

1064: 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 ‘select 5 , item_title from module_item_titles where mod_table_id = ‘m11’)’ at line 2

Please keep in mind {comp_id} is form field. I can run this all day long in MySql and pass the field value. But when I put it in ScriptCase event, it blows up.

MS

PS, I currently have it stripped down to just the field value and one select field from table for debugging purposes.

#11

Perhaps you can consider to put the text in a help-variable. Then putting the sql statement between double quotes you can let php evaluate the variable to a decent sql statement. If needed you need to single quote the alphanumeric vars.

" … myfield=’$helpfield’ …"