Forum OpenACS Development: Clobs and db_dml

The query extractor moved clob inserts into the .xql. Since I will be using the text datatype in PostgreSQL and the clob datatype in Oracle, I should move these queries into the respective -postgresql.xql and -oracle.xql files. Am I correct?

Also, how does the db_dml procedure handle clobs in postgresql. For instance, in process-request.tcl:

db_dml survsimp_question_response_text_insert "
    insert into survsimp_question_responses
    (response_id, question_id, clob_answer)
    values (:response_id, :question_id, empty_clob())
    returning clob_answer into :1" -clobs [list $response_value]

If I am using postgresql, will db_dml ignore the clobs argument at the end of the db_dml command or do I need to rewrite the query in the .tcl file? For PostgreSQL, I changed the query to do a normal insert into a text column and put that query into the process-request- postgresql.xql file.

Thanks.

You need to leave the "-clobs [list $response_value]" in the .tcl file or the oracle version won't work.

For the oracle .xql file, just create a fullquery with:

    insert into survsimp_question_responses
         (response_id, question_id, clob_answer)
         values (:response_id, :question_id, empty_clob())
         returning clob_answer into :1

For postgresql, you need to change the clob_answer column to a text datatype, and create the following fullquery in the pg .xql file:

    insert into survsimp_question_responses
         (response_id, question_id, clob_answer)
         values (:response_id, :question_id, '$response_value')