Forum OpenACS Q&A: Too stupid to work this out for myself!

I am trying to write an additional page in the ecommerce module to display two full size photographs of a product if the user clicks on the thumbnail in product.tcl.

So I used ec_linked_thumbnail_if_it_exists as a template and wrote ec_linked_photo_if_it_exists to be used in a template which will display the obverse and reverse images of the product if the user clicks on the thumbnail in product.tcl. I copied product.tcl, modified it as I thought was required, saved it as me_product.tcl and modified the thumbnail href in product.tcl to point to the new .tcl page.

The result was that clicking on the thumbnail caused an error seemingly thrown by db_0or1row. I examined the tables manually and only one row would be returned so the error was caused by something else.

So to try to tie it down I modified the href link of the product in category-browse to point to the new page. The error still occurred.

So then I simply renamed product.tcl itself to me_product.tcl, modified the href link in category-browse to point to me_product.tcl and tried again. Same error.

So I now know that the name of a tcl file is critical but I do not know how to register a name change or create a new tcl file. Please can someone help me with this or point me to some suitable docs. (I have scoured the existing docs and read the developer guide but am still floundering).

Many Thanks
Richard

Bart, OK, here is the logfile output for the error. The requested URL taken from the address bar when the error was thrown was:

https://63.246.3.10:8446/purchase/me_product?product_id=5963

me_product.tcl is an exact copy of product.tcl with the same ownership and permissions. The product_id 5963 is valid and should return one row from the query that is throwing the error.

Excerpt from the logfile:

[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: Querying '
            select 1
            where 't' = acs_permission__permission_p('5892', '2612', 'read');'
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: dbinit: sql(localhost::militariaexchange): '
            select 1
            where 't' = acs_permission__permission_p('5892', '2612', 'read')
        '
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: ns_getform using encoding iso8859-1 for charset iso-8859-1
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: Querying '
    select *
    from ec_products p, ec_custom_product_field_values v
    where p.product_id = '5963'
    and p.product_id = v.product_id(+);'
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Error: Ns_PgExec: result status: 7 message: ERROR:  parser: parse error at or near "("

[06/Mar/2003:09:36:10][8458.24580][-conn3-] Error: dbinit: error(localhost::militariaexchange,ERROR:  parser: parse error at or near "("
): '
    select *
    from ec_products p, ec_custom_product_field_values v
    where p.product_id = '5963'
    and p.product_id = v.product_id(+)'
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: RP (37.281 ms): error in rp_handler: serving GET /purchase/me_product?product_id=5963
	ad_url "/purchase/me_product" maps to file "/web/militariaexchange/packages/ecommerce/www/me_product.tcl"
errmsg is Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: Querying '
            select 1
            where 't' = acs_permission__permission_p('5892', '2612', 'admin');'
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: dbinit: sql(localhost::militariaexchange): '
            select 1
            where 't' = acs_permission__permission_p('5892', '2612', 'admin')
        '
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: Querying '
      select apm_parameter_values.attr_value
      from apm_parameters, apm_parameter_values
      where apm_parameter_values.package_id = '177'
      and apm_parameter_values.parameter_id = apm_parameters.parameter_id
      and apm_parameters.parameter_name = 'AutomaticErrorReportingP';'
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: dbinit: sql(localhost::militariaexchange): '
      select apm_parameter_values.attr_value
      from apm_parameters, apm_parameter_values
      where apm_parameter_values.package_id = '177'
      and apm_parameter_values.parameter_id = apm_parameters.parameter_id
      and apm_parameters.parameter_name = 'AutomaticErrorReportingP'
    '
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: Querying '
      select apm_parameter_values.attr_value
      from apm_parameters, apm_parameter_values
      where apm_parameter_values.package_id = '177'
      and apm_parameter_values.parameter_id = apm_parameters.parameter_id
      and apm_parameters.parameter_name = 'EnabledP';'
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Notice: dbinit: sql(localhost::militariaexchange): '
      select apm_parameter_values.attr_value
      from apm_parameters, apm_parameter_values
      where apm_parameter_values.package_id = '177'
      and apm_parameter_values.parameter_id = apm_parameters.parameter_id
      and apm_parameters.parameter_name = 'EnabledP'
    '
[06/Mar/2003:09:36:10][8458.24580][-conn3-] Error: GET http://63.246.3.10:8446/purchase/me_product?product_id=5963
referred by "https://63.246.3.10:8446/purchase/category-browse?category_id=1"
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind 0or1row nsdb0 {
    select *
    from ec_products p, ec_custom_product_field_values v
    where p.product_id = :product_id
    and p.produc..."
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql"
    invoked from within
"db_exec 0or1row $db $full_statement_name $sql"
    invoked from within
"set selection [db_exec 0or1row $db $full_statement_name $sql]"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle db {
	set selection [db_exec 0or1row $db $full_statement_name $sql]
    }"
    (procedure "db_0or1row" line 22)
    invoked from within
"db_0or1row get_ec_product_info "
    select *
    from ec_products p, ec_custom_product_field_values v
    where p.product_id = :product_id
    and p...."
    ("uplevel" body line 1)
    (procedure "code::tcl::/web/militariaexchange/packages/ecommerce/www/me_..." line 1)
    invoked from within
"code::tcl::$__adp_stub"
    invoked from within
"if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init tcl $__adp_stub
..."
    ("uplevel" body line 3)
    invoked from within
"uplevel {

    if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init t..."
    (procedure "adp_prepare" line 2)
    invoked from within
"adp_prepare "
    (procedure "template::adp_parse" line 30)
    invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
    (procedure "adp_parse_ad_conn_file" line 7)
    invoked from within
"$handler"
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
	$handler
      } ad_script_abort val {
	# do nothing
      }"
    invoked from within
"rp_serve_concrete_file [ad_conn file]"
    (procedure "rp_serve_abstract_file" line 60)
    invoked from within
"rp_serve_abstract_file "$root/$path""
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
	rp_serve_abstract_file "$root/$path"
	set tcl_url2file([ad_conn url]) [ad_conn file]
	set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."

Thank you very much.
Richard

Hi Richard,

You should also copy the product*.xql files to corresponding me_product*.xql files.

Your new version of product.tcl could not find the proper queries and, as such, was executing the default query within the db_0or1row.  This query is Oracle-specific and I assume you're using Postgres given the ns_pg call in your log snippet.

This is one reason why queries should be separated out into *.xql files and not included within the db_* calls.

Hope this helps...

Robert, Of course - obvious once pointed out! I had just got as far as working out that the postgres doesn't like the (+) inner join syntax but had no idea why the error was not thrown normally. Many thanks. R.

because in postgres they are not bind variables, they are
really just quoted and interpolated.  There isn't really any
way (short of writing a sql parser and looking at the backend)
to know what the actual datatype is.