Hi there,
is it possible to update data that originates from a "query" rather than a
"table" tag (in dbforms-config.xml) if that query does a JOIN of sorts? In
the bookstore example's dbforms-config.xml I see a query with a LEFT JOIN,
but I couldn't get anything like it to work with inserts/updates.
During my experiments, I got messages like "Wrong syntax at INNER keyword"
(I do an INNER JOIN) from my driver and I wondered what SQL statement
results from the query tag definition, especially during insert/update
operations. After a bit of code research I believe the FROM part of the
query tag will be used as "INSERT INTO" location. This won't work if the
FROM part includes JOINs. Here's an example:
<query name="query1"
from="database.table1 INNER JOIN database.table2 ON
database.table1.ID = database.table2.ID">
<field name="database.table1.ID" ... >
<field name="field_in_table1" ... >
<field name="field_in_table2" ... >
</query>
I believe the INSERT statement produced by DbForms looks like this:
INSERT INTO database.table1 INNER JOIN database.table2 ON database.table1.ID
= database.table2.ID VALUES ('ID_value', 'value_for_field_in_table1',
'value_for_field_in_table2');
This statement results in the same "Wrong syntax at INNER keyword" error. If
this is the case, one option to solve it might be to try and split the
JOINed query up into multiple INSERT/UPDATE statements. In the case above:
INSERT INTO database.table1 VALUES ('ID_value',
'value_for_field_in_table1');
INSERT INTO database.table2 VALUES ('ID_value',
'value_for_field_in_table2');
I got the feeling that it might be a bit more complex though.
Regards,
Sebastian
P.S.: Some tecspecs of my setup: dbforms 1.1.3, MS SQL Server 2000 and MS'
JDBC driver
-------------------------------------------------------
This SF.Net email sponsored by: Free pre-built ASP.NET sites including
Data Reports, E-commerce, Portals, and Forums are available now.
Download today and enter to win an XBOX or Visual Studio .NET.
http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
_______________________________________________
DbForms Mailing List
http://www.wap-force.net/dbforms
