Forum OpenACS Development: porting a db

Posted by Ola Hansson on 10/08/01 10:37 PM

Which is the prefered way to port this non-SQL92 compliant "db_dml" within a transaction? (The QD didn't manage to extract the sql from this one).

...
	set sql "
update mp3_mp3s
set    deleted_p = 'f'
"

	foreach i_key [array names info_pairs] {
	    append sql ",
       $i_key = $info_pairs($i_key)"
	}
	append sql "
where  mp3_id = :mp3_id
"

	set sql2 "
update acs_objects
set    creation_user = :creation_user,
       creation_ip = :creation_ip,
       creation_date = sysdate,
       context_id = :package_id
where object_id = :mp3_id
"
...
...

db_transaction {
    db_dml undelete_mp3 $sql
    db_dml fix_creation $sql2
}
...

Thanks,

2: Response to porting a db_dml transaction (response to 1)

Posted by Don Baccus on 10/09/01 01:05 AM

The second one is easy - get rid of the set sql2 statement and move the SQL query into your generic .xql file (it's not db-specific so it only needs to appear in the generic queryfile). The first (undelete_mp3) isn't much more difficult. The static part, i.e. the update prefix, can go into your queryfile. Then build the dynamic part into a Tcl variable as is being done (you might want to choose a name like "extra_vars" instead of "sql" since it's s snippet that will be built, not the entire query). Reference the Tcl variable directly in the queryfile. The query, in other words, will look something like:

update mp3_mp3s
set deleted = 'f'
$extra_vars
where mp3_id = :mp3_id

The Query Dispatcher will substitute the Tcl variable before the driver is called with the query.

3: Response to porting a db_dml transaction (response to 1)

Posted by Ola Hansson on 10/09/01 01:46 PM

So, in the sql2 case above, are you saying that Oracle can handle "current_timestamp" in place of "sysdate"? (I was on my way to split *that* statement in a generic xql file and an -oracle.xql file).

Okay. Here's a piece I got stuck on although it's similar. I know how to port the psql block, but how do I treat the "extra_vars" when thy're both dynamic and db specific? db_map doesn't seem appropriate...

...
	set sql "
begin
   :1 := mp3.new(
      file_path => :client_filename,"

	foreach i_key [array names info_pairs] {
	    append sql "
      $i_key => $info_pairs($i_key),"
	}
	append sql "
      creation_user => :creation_user,
      creation_ip => :creation_ip,
      creation_date => sysdate,
      context_id => :package_id
);
end;
"
...
...
db_exec_plsql add_mp3 $sql
...

4: Response to porting a db_dml transaction (response to 1)

Posted by Dan Wickstrom on 10/09/01 02:48 PM

Create a list with the key values in the correct order to match the postgresql function call - oracle won't care about ordering since you're using named variables here.

        # foo, bar, and baz match key names in info_pairs
        set ordered_keys [list foo bar baz]
        foreach i_key $ordered_keys {
                  append sql "$i_key => $info_pairs($i_key),"
                  set $i_key $info_pairs($i_key)
        }

Then in your postgresql .xql file reference the variables in the correct order for the mp3__new function.

5: Response to porting a db_dml transaction (response to 1)

Posted by Don Baccus on 10/09/01 04:22 PM

Sorry, I missed the "creation_date" in the SQL2 portion, so that call needs to be put into both oracle and postgresql .xql files, sorry.

Forum OpenACS Development: porting a db_dml transaction