Forum OpenACS Development: Problem with the Oracle database

Request notifications

Collapse
Posted by Rodrigo C on
Hello everyone, I'am dealing with a problem with the database and I'am realy giving up to it.
I have this two tables:
create table rooms (
      room_id    integer primary key,
      capacity  integer,
      room_name    varchar(100) not null,
      comments        varchar(200)
);

create table reserve (
      reserve_id    integer primary key,
      room_id      not null references rooms,
      user_id      not null references users,
      start_time    timestamp(0),
      end_time      timestamp(0),
      people        integer
);

In a tcl script if I do somethin like this:
db_foreach foo "select * from rooms" {.....
everything is fine, but if I try some thing like:
db_foreach foo "select * from reserve" {...
I get an error like this:
Database operation "getrow" failed (exception 1406, "nsoracle.c:2055:ora_get_row: error in `OCIStmtFetch ()': ORA-01406: fetched column value was truncated

I apreciate if someone can help me. ThX

Collapse
Posted by Andrew Piskorski on
What versions of AOLserver and nsoracle are you using?
Collapse
Posted by Rodrigo C on
OK I'am not shure how to request that information, when I enter in sqlplus it displays: Oracle9i Enterprise Edition Release 9.2.0.1.0
Now, the aolserver version I don't now how to chek out that; I belive it is something like 3.3 but not shure.
OpenAcs Version 5.0.4 Final.
Collapse
Posted by Rodrigo C on
Here is an other problem, im doing the problem sets of the education section in OpenACS. A query like this runs well:

db_multirow reporte cosilla "select rm.room_name as room_name, trunc(sum(to_date(to_char(rv.end_time,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI') -
to_date(to_char(rv.start_time,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI')),4) as hours_reserved,
trunc(avg(rv.people),4) as people_avg  from reserve rv, rooms rm where rm.room_id=rv.room_id group by room_name"

I now it is a bit heavy, but it is the only way I found to work with timestamps ;)

But in the .adp I need the room_id, so I add in the query:
....select rm.room_id ,rm.room_name as .....

But it is imposible to get the room_id in the multirow, it throws this:
nsoracle.c:3904:ora_tcl_command: error in `OCIStmtExecute ()': ORA-00918: column ambiguously defined

SQL: select !>>>!room_id,rm.room_name as room_name,  trunc(sum(to_date(to_char(rv.end_time,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI') -  to_date(to_char(rv.start_time,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI')),4) as hours_reserved,  trunc(avg(rv.people),4) as people_avg  from reserve rv, rooms rm where rm.room_id=rv.room_id group by room_name
    while executing
"ns_ora select nsdb0 {select ...

Collapse
Posted by Ola Hansson on
The "!>>>!" in the error message usually points directly to the actual problem.

Maybe try "select rm.room_id,rm.room_name as room_name, ..."?

Collapse
Posted by Sebastiano Pilla on
Just a guess, but... You have timestamps (a 9i-feature) columns in your table, but the nsoracle driver doesn't know how to handle them.

What happens if you change the timestamps columns to dates? I don't know if you really need the millisecond-precision that the timestamp datatype has, perhaps the second-precision of the date datatype is enough for you to solve your problem.

Collapse
Posted by Samer Abukhait on
and also
group by rm.room_id, rm.room_name
Collapse
Posted by Rodrigo C on
The second problem was solved with the group by rm.room_id,rm.room_name, thanks Samer; but the original problem is not solved, I get it in a diferent query, look:

Query:
db_multirow reporte foo "select rm.room_name as room_name, pa.email, rv.start_time, rv.end_time, rv.people from reserve rv, rooms rm, parties pa where rv.room_id=$room_id and rm.room_id=rv.room_id and pa.party_id=rv.user_id"

The error:
Database operation "getrow" failed (exception 1406, "nsoracle.c:2055:ora_get_row: error in `OCIStmtFetch ()': ORA-01406: fetched column value was truncated

SQL: [nil]")
    while executing
"ns_db getrow $db $selection"
    invoked from within
"db_getrow $db $selection"
    ("while" body line 4)
    invoked from within
"while { 1 } {

            if { $more_rows_p } {
                set more_rows_p [db_getrow $db $selection]
            } else {
                break..."
    ("uplevel" body line 16)

"Thanks in for your help"
Rodrigo C.

Collapse
Posted by Brian Fenton on
Rodrigo, Sebastiano told you the problem and the answer. You have to change your start_time and end_time TIMESTAMP columns to DATEs. Try it and let us know what happens.

Brian