Forum OpenACS Development: Tcl Help...ASAP Please!!!!!!

Hi, Note below the code and below that the error message...Can n ebody kindly help me out ASAP....have tried everything possible....now need your help...

Thanks in advance, DR

ad_page_contract {

} {
    commercial_details_id
}


set title "Add Delivery Details"
set html ""
set db [ns_db gethandle]


        set ctc_display [database_to_tcl_string_or_null $db "select ctc from commercial_details where commercial_details_id=$commercial_details_id and  ctc_approval='yes'"]

        if {$ctc_display == ""} {
        append html "
                    <table cellpadding=5 cellspacing=5 border=1 width=100%>
                    <tr><th colspan=2 align=center><strong><font face=verdana font size=1>Warning : CTC Value not approved</font></strong></td></tr>
                    </table>
 "
        }


set selection  [ns_db select $db "select  data_transfer_id,data_transfer from data_transfer"]
set data_transfer_val ""
while {[ns_db getrow $db $selection] } {
        set_variables_after_query
        append data_transfer_val  "<option value=$data_transfer_id>$data_transfer</option>"
}

set selection  [ns_db select $db "select hardware_assets_id,name from hardware_assets "]
set hardware_assets ""
while {[ns_db getrow $db $selection] } {
        set_variables_after_query
        append hardware_assets  "<option value=$hardware_assets_id>$name</option>"
}

set count  [database_to_tcl_string_or_null $db "select count(*) from reports"]
set i 1
while {$i <= $count } {
    set selection  [ns_db select $db "select reports_id,name from  reports "]
    set report ""
    while {[ns_db getrow $db $selection] } {
            set_variables_after_query
            append report  "<option value=$reports_id>$name</option>"
}
    if {$i == 1} {
    append reports "<tr>
                    <td><font face=verdana font size=1>List of Reports</font></td>
                    <td><select name=list_of_reports$i>$report</select></td>
                    <td><font face=verdana font size=1>Reports periodicity</font></td>
                    <td><select name=periodicity$i>
                    <option>Online</option>
                    <option>Fort nightly</option>
                    <option>Monthly</option>
                </select></td>
            </tr>"
    } else {
        append reports "<tr><td>&nbsp</td><td><select name=list_of_reports$i>$report</select></td><td>&nbsp</td>
                        <td><select name=periodicity$i>
                                <option>Online</option>
                                <option>Fort nightly</option>
                                <option>Monthly</option>
                        </select></td>
                        </tr>"
    }
    incr i
}

set so_number [db_string topic_id "select so_number_sequence.nextval from dual"]
set selection  [ns_db select $db "select  data_transfer_id,data_transfer from data_transfer"]
set data_transfer_val ""
while {[ns_db getrow $db $selection] } {
        set_variables_after_query
        append data_transfer_val  "<option value=$data_transfer_id>$data_transfer</option>"
}


set customer_id [database_to_tcl_string_or_null $db "select customer_id from commercial_details where commercial_details_id = $commercial_details_id" ]

set address_id [database_to_tcl_string_or_null $db "select address_id from commercial_details where commercial_details_id = $commercial_details_id" ]

ns_log notice "AAAAAAAAA $address_id"

set selection [ns_db select $db "select address_book_id,first_names ||','||line1 ||','||city as address from address_book where address_book_id=$address_id"]

set count 0
while { [ns_db getrow $db $selection] } {
        set_variables_after_query
        append customer "<option value=$address_book_id>$address</option>"
    incr count
}

if {$count < 1} {
    append customer "<option value=na>No customer Contact</option>"
}

append html "
<form method=post action=delivery-add-2>
<input type=hidden name=commercial_details_id value=$commercial_details_id>
<table cellpadding=5 cellspacing=5 border=1 width=100%>

    <tr bgcolor=#bbccff><th colspan=4>Adding delivery details</th></tr>

    <tr><td>So Number.</td><td><input type=hidden name=so_number value=$so_number>$so_number</td><td>So Receipt Date</td><td>[philg_dateentrywidget_default_to_today so_receipt_date ]</td></tr>

    <tr><td>SSTF Number</td><td><input type=text name=sstf_ref></td><td>Rack number</td><td><select name=rack_num><option>1</option><option>2</option><option>3</option></select> </td></tr>

    <tr><td>Effective delivery start date</td><td>[philg_dateentrywidget_default_to_today effective_delivery_start_date ]</td><td>Date of sending notification to customer</td><td>[philg_dateentrywidget_default_to_today notif_to_cust_post_date ]</td></tr>

        <tr><td>Delivery Details</td><td><textarea name=add_delivery_details rows=2></textarea></td><td>Reminder for renewal</td><td> [philg_dateentrywidget_default_to_today remider_for_renewal] </td></tr>

    <tr><td>SLA terms description</td><td><textarea name=sla_terms_desc rows=2></textarea></td><td>Primary customer Contact</td><td><select name=primary_cust_contact>$customer</select></td></tr>

@                                                     <tr><td>Data transfer</td><td><select name=data_transfer_pm>$data_transfer_val</select></td><td>Disk space allocated</td><td><input type=text name=disk_space_alloc_web></td></tr>

        <tr><td>Disk space for mail</td><td><input type=text name=disk_space_alloc_mail></td><td>Database allocated</td><td><select name=db_allocated>
                <option>oracle</option>
                <option>ms-sql</option>
                <option>db2</option>
                <option>postgre-sql</option>
        </select></td></tr>

    <tr><td>Physical space allocated</td><td><input type=text name=physical_space_alloc></td><td>Assets associated</td><td><select name=hw_assets_associated>$hardware_assets</select></td></tr>

    <tr><td>IP number allocated</td><td><input type=text name=ip_num_allocated></td><td>Services end date </td><td> [philg_dateentrywidget_default_to_today service_end_date]</td></tr>


    <tr><td>Backup policy</td><td><textarea name=backup_policy rows=3 cols=20></textarea></td>
    <td>Services Delivered</td><td><textarea name=services_delivered rows=3 cols=20></textarea></td></tr>

$reports

    <tr><td>Ports Open</td><td><input type=text name=ports_open></td><td>Internal IP</td><td><input type=text name=internal_ip></td></tr>

        <tr><td colspan=4 align=center><input type=submit value=Submit></td></tr>
</form>
</table>
"

append page_content "
[nsp_header $title]
<br>
<br>
[ad_scope_context_bar_ws_or_index [list "/nspapp/" "Nsp App"] [list "/nspapp/delivery" "Add delivery details"] $title]

<hr>
<p>&nbsp<p>

        $html

[ad_footer]
"

doc_return 200 text/html $page_content

ERROR:-

Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_db select $db "select address_book_id,first_names ||','||line1 ||','||city as address from address_book where address_book_id=$address_id""
    invoked from within
"set selection [ns_db select $db "select address_book_id,first_names ||','||line1 ||','||city as address from address_book where address_book_id=$addre..."
    (file "/web/nspapp/www/nspapp/delivery/delivery-add.tcl" line 88)
    invoked from within
"source [ad_conn file]"
    (procedure "rp_handle_tcl_request" line 2)
    invoked from within
"rp_handle_tcl_request"
    invoked from within
"catch $handler error"
    invoked from within
"rp_abstract_url_server"

also, make sure debug is set to true in your nsd config.tcl file - that way your error log will include the sql query exactly as it is passed to the database.

Also, although this isn't your problem, you probably should use bind variables in your queries - ie:

where address_book_id = :address_id

that way you are automagically protected against 'sql in variable' hacks etc.

Hi, This is the error log from the statement:-

"set selection [ns_db select $db "select address_book_id,first_names ||','|| line1 ||','|| ....." up to the end of that very  statement !!

Note below: -

[10/Jun/2003:09:30:44][11041.286725][-conn273-] Error: ora8.c:1500:ora_exec: 

error in `OCIStmtExec
ute ()': ORA-01745: invalid host/bind variable name

SQL: select first_names ||','||line1 ||','||city as address from address_book where 

address_book_i
d= !>>>!
[10/Jun/2003:09:30:44][11041.286725][-conn273-] Error: dbinit: 

error(,ora8.c:1500:ora_exec: error
in `OCIStmtExecute ()': ORA-01745: invalid host/bind variable name

SQL: select first_names ||','||line1 ||','||city as address from address_book where 

address_book_i
d= !>>>!): 'select first_names ||','||line1 ||','||city as address from address_book where 

address
_book_id=:'
[10/Jun/2003:09:30:44][11041.286725][-conn273-] Error: Database operation 

"select" failed (exception NSDB, "Query was not a statement returning rows.")

    while executing
"ns_db select $db "select first_names ||','||line1 ||','||city as address from 

address_book where
address_book_id=:$address_id""
    invoked from within
"set selection [ns_db select $db "select first_names ||','||line1 ||','||city as address 

from address_book where address_book_id=:$address_id"]"
    (file "/web/nspapp/www/nspapp/delivery/delivery-add.tcl" line 88)
    invoked from within
"source [ad_conn file]"
    (procedure "rp_handle_tcl_request" line 2)
    invoked from within
"rp_handle_tcl_request"
    invoked from within
"catch $handler error"
    invoked from within
"rp_abstract_url_server"

Thanks in advance, divya

Please do really read the db-api as Jarkko suggested - you will then notice that bind variables look like that:

:my_var

and not:

:$my_var

Hi,

I rectified that....It gave me an error for db0or1row...I havent used that as the command.....
I need to use db0or1row right??

Divya

This is again the incorrect error message. Please do us all a favor and practice on a simple page. Please try to use the correct api for OpenACS applications. I'm sure no one here, even the experts, started on such a complicated page before understanding how the various parts worked, and how to debug the basics. We all assume you have a properly loaded data model, but right now that might not be a good thing to assume.

Please tell us, before moving on:

• What version of AOLserver are you using?
• What version of OpenACS are your using?
• What documentation are you using?
• Do you have any pages that are working?
• Does the query you are trying work from psql?

ASAP? ok, that sounds like what a boss says to an employee,,, post all the code and make us pour thru it to determine all possible problems? There's a name for that,,, it's called an "expensive job description" :) Normally, my consulting rates are $150/hour, and Divya, what you'd primarilly be getting is information about problem solving, which is my forte (see this introduction to templating, raw mode for a somewhat recent example of my work). However, given I don't know the toolkit completely, I'll dock myself $25/hour. Now, one and only one question remains, Divya: What is your billing address? -Jim, who has seen behavior such as yours before, and saw you coming from a mile away :)