I am working on a skinny table implementation where I am placing
db_dmls in a foreach loop, but I get an error if the loop exceeds one
iteration. My test form has two elements, and the inserts
work if I:
a) have a value for the first one, and leave the second as an empty
string
b) have a value for the second, and leave the first as an empty string
It breaks when both elements have a value.
set sql_query "
select adt.adt,
adt.sqltype,
w.widget,
a.attribute_name,
a.attribute_id,
a.pretty_name,
a.presentation_options,
a.default_value,
a.min_n_values,
a.mapping_object_type_id
from es_skinny_adt adt,
es_form_widget w,
es_skinny_attribute a
where a.form_number = 1
and a.widget_id = w.widget_id
and a.adt_id = adt.adt_id
and a.object_type_id = $object_type_id
order by a.form_sort_order
"
db_foreach get_attributes $sql_query {
element create object $attribute_name
-label "$pretty_name"
-datatype text
-widget $widget
-options "$element_options"
$optional
if { [form is_request object] } {
element set_properties object $attribute_name -value
$default_value
}
if { [form is_valid object] &&
![string equal [set $attribute_name] ""]} {
# don't allow all null values
# init values
set int_value [db_null]
set real_value [db_null]
set bool_value [db_null]
set text_value [db_null]
set timestamp_value [db_null]
# set actual value
set ${sqltype}_value [element::get_value object $attribute_name]
lappend data_insert_sql_list "
insert into es_skinny_data (
object_id,
attribute_id,
int_value,
real_value,
bool_value,
text_value,
timestamp_value
) values (
:object_id,
:attribute_id,
:int_value,
:real_value,
:bool_value,
:text_value,
:timestamp_value
)"
}
}
if { [form is_valid object] } {
set object_insert_sql "
insert into es_skinny_object (
object_id,
object_type_id,
name,
package_id
) values (
:object_id,
:object_type_id,
:name,
:package_id
)"
db_transaction {
db_dml insert_object $object_insert_sql
### error occurs here ###
foreach data_insert_sql $data_insert_sql_list {
db_dml insert_data $data_insert_sql
}
}
template::forward .
}
