Forum OpenACS Development: Re: Res: Re: I can't find the error in MyFirstPackage

Thanks Jim, I could find my last error. Now I have another:

Database operation "dml" failed (exception NSDB, "Query was not a DML or DDL command.")

SQL: insert into mfp_notesi(item_id, revision_id, title) values('3352', '3354', 'Silvia')
while executing
"ns_pg_bind dml nsdb0 { insert into mfp_notesi(item_id, revision_id, title) values(:item_id, :revision_id, :title) }"
("uplevel" body line 1)
invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql]"
("postgresql" arm line 2)
invoked from within
"switch $driverkey {
oracle {
return [uplevel $ulevel [list ns_ora $type $db $sql] $args]
}
..."
invoked from within
"db_exec dml $db $full_statement_name $sql"

MY CODE IS:

{
if { ! [info exists item_id] } {
set item_id[db_nextval acs_object_id_seq]
}

db_transaction {
db_exec_plsql note_insert {select content_item__new(:title,-100,:item_id,null,null,null,null,null,'content_item','mfp_note',:title,null,null,null,null)}

set revision_id [db_nextval acs_object_id_seq]

db_dml revision_add { insert into mfp_notesi(item_id, revision_id, title) values(:item_id, :revision_id, :title) }

db_exec_plsql make_live {
select content_item__set_live_revision(:revision_id)
}
}
return $item_id
}

Thanks before hand,

Silvia

Collapse
Posted by Jim Lynch on
mfp_notesi is this weird view that if you insert into it, it does all the rest of the stuff needed (make an acs_object, etc) to get the new item in there.

Unfortunately...

doing that insert makes the -result- appear to be a "select" statement because of what the view is an "alias" for.

If you enable the debugging output of the nspostgres driver in the pool definition, it should show you what symbol is being returned by the pq exec call; you should fnd it's claiming there is a rows result.

So if you change the db_dml to soemthing else, like db_exec_plsql maybe, or maybe db_0or1row or db_string, the insert (which stops looking like an insert at the point of return) will return good exit status again.

Collapse
Posted by Brian Fenton on
You can insert into a view? Is this a Postgres "feature"?
You could try inserting into the underlying base tables directly.

Brian

Collapse
Posted by Jim Lynch on
The act of inserting into this particular view fires a trigger. This is a feature of the openacs content repository: when you create a content type, it makes a view with a trigger like this.
Jim, thanks, I get to run the example.