SQLExec Error

Guys,
I gave the below code with event FieldChange:
SQLExec("insert into ps_mf_master(folio_id,name,scheme_id_esp,no_of_uni ts,unit_rate) values(:1,:2,:3,:4,' ')", MF_PURCHASE.FOLIO_ID, MF_PURCHASE.INVESTOR_DETAIL, MF_PURCHASE.SCHEME_ID_ESP, MF_PURCHASE.NO_OF_UNITS);
And i am getting the below error when i save the page:
SQL error in Exec. (2,280)MF_PURCHASE.NO_OF_UNITS.FieldChange PCPC:263 Statement:1
During the execution of SQL, an error occured in the Exec Subroutine. The preceding message should have described the SQL being executed.
Help needed...

Hi,
The record that you have created has a keyword (NAME) as a column name. SQL will not allow an insert to happen with such a column.
Change the name of the column and build/alter the record and try again. Should work.
You can check your error logs in the appserver logs folder after you set a trace on your login page on "SQL statements".

Two things:
1) Do you really need to put a DML statement in the FieldChange? Can it be moved to SavePreChange? This would not have anything to do with the actual error you are encountering, but it's more of an overall design approach.
2) Can you use a record or SQL object instead?
With the record object, you can do:
&rec.GetField(Field.NAME).Value = MF_PURCHASE.INVESTOR_DETAIL;
If you try to do:
&rec.NAME.value = MF_PURCHASE.INVESTOR_DETAIL
you typically get a PeopleCode error because it's trying to do something with the name property of the record.

It seems strange that it is not giving you the actual error that occurred. I can think of two things, but I do not know the details of the record definition.
First of all, you do have the correct number of parameters. But normally, I would expect a field named UNIT_RATE to be numeric. You are inserting what looks like a space (or it might be a null). If the field is numeric, and you don't want to use a specific value, use a 0 instead (no quotes around it).
The other thing that I would question is the fact that you are inserting a new row every time NO_OF_UNITS is changed. Is NO_OF_UNITS a key on your table? If not, then you are most likely getting unique constraint errors. Even if it is a key, if the user changes it and then changes it back, you will be attempting to insert an identical row again. Doing an insert on FieldChange does not make a lot of sense, unless it is some kind of audit table, and the name and format does not look like it is. If the user enters the wrong value by accident, you will already have inserted the new row. It would make more sense to do something like this on SavePostChange.
I am confused by the answers that you cannot use NAME as a field name. PeopleSoft does use it as a field name, so I would assume that you should be able to do an insert into any delivered PeopleSoft table, even those that use that fieldname.

Not so much inappropriate, as you need to take more into consideration, Note PeopleBooks states:

Remember that SQLExec UPDATEs, INSERTs, and DELETEs go directly to the database server, not to the Component Processor (although SQLExec can look at data in the buffer using bind variables included in the SQL string). If a SQLExec assumes that the database has been updated based on changes made in the component, that SQLExec can be issued only in the SavePostChange event, because before SavePostChange none of the changes made to page data has actually been written back to the database.

So even SavePreChange may not be appropriate depending on the Insert. Safest place to Insert/Update into a table outside the Component Buffer is SavePostChange. Although sometimes you can get arround this with a DoSaveNow() just prior to the Insert statement.

I agree with Justin. If you are trying to issue DML statements from any event other than SavePostChange event and If the data you are modifying has to reflect in the current component buffer then you have to use it on SavePostChange otherwise you will get that "No matching buffer found Error .."

You can use DML statements from FieldChange/SavePreChange etc only if you want to modify the data which is not in context with current component buffer.

Even if you issue the DML statements in SavePostChange, care should be taken that the values are assigned appropriately through PeopleCode. Otherwise, the values in the component buffer and database would be different and If the user hits on save again then you will again get that "No matching buffer found error ....".

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.