Execute SQL

[warreniii]

I'm trying to use the Execute SQL command to update or insert records into an Oracle database table named TEMP_INSERT and am having trouble referencing the FMP fields from the layout. My fp5 file is named FETC-Supervisors.fp5 and the fields are Q1, Q2, and Q3...

I've tried variations on '" & Q1 & "' and "' & Q1 & '" and even without quotes but no go... I've been trying to get this working now for what seems like forever and I simply cannot find the answer...

It works fine if I hard code a value in the SQL stmt...

but it needs to take the value of Q1, Q2 and Q3 from the FMP record the user happens to be on.

Can someone tell me how to qualify the FMP name in the sql stmt if that's what I need?

thanks in advance....

[Genx]

Can you paste the full calc?

[warreniii]

Below I pasted all of the calcs I've tried separated by dashed lines. The first calc is the working version with hard coded values. The remaining calcs are versions attempting to reference a fm field.

Working with hard coded values

----------------------------------------------

INSERT INTO TE_FORM8 VALUES(32408,23,'26-SEP-07','This','Is','A','Test','To','See',7,'I','Can','Insert','Data','test','Fall','St1')

----------------------------------------------

Non working calcs

----------------------------------------------

INSERT INTO TEST_IMPORT(TEST1,TEST2,TEST3) VALUES ('" & FETC-Form8 Apex Export Text::Q1 & "','" & FETC-Form8 Apex Export Text::Q2 & "','" & FETC-Form8 Apex Export Text::Q3 & "')

-----------------------------------------------

-----------------------------------------------

"Insert into TEST_IMPORT Values (Q1, Q2, Q3)"

-----------------------------------------------

-----------------------------------------------

INSERT INTO `TEST_IMPORT` ( `TEST1`) VALUES ("' & FETC-Form8 Apex Export Text::Q1 & '");

-----------------------------------------------

[DannyDK]

The first non-working calc appears that it should work assuming you have your table and field names correct in "TEST_IMPORT(TEST1,TEST2,T EST3)". Though I noticed a space after the T in T EST3.

What is the result of this? What is the error message you're getting, if any? What happens when you run this exact statement with hard coded values replacing each of the " & FIELDREF & " statements? i.e. INSERT INTO TEST_IMPORT(TEST1,TEST2,TEST3) VALUES ('testvalue1','testvalue2','testvalue3')

There shouldn't be any need to qualify the FMP name. You are really just passing text values in a SQL statement through ODBC. Oracle has no idea that it's coming from a FMP database.