Board index » jbuilder » error saving data back to database.

error saving data back to database.


2005-07-09 05:48:29 AM
jbuilder17
Hi there,
I received the following error when I tried to save changed value back to
database. I am using
Jbuilder X and build-in tableScrollPane1, jdbTable1, queryDataSet1 standard
stuffs.
com.borland.dx.sql.dataset.ResolutionException: An error occurred saving a
change to the "Ressim_hourly_view" table
table: ressim_hourly
sensor_id number(5) not null,
date_time date not null,
run_time date not null,
value number(12,3),
flag char(1),
queryDataSet1 contains the following SQL query.
select date_time, max(decode(sensor_id, 6, value, NULL)) as value1,
max(decode(sensor_id,76, value, NULL)) as value2,
max(decode(sensor_id, 20, value, NULL)) as value3
from ressim_hourly
where (sensor_id, run_time) in (select sensor_id, max(run_time)
from ressim_hourly
where sensor_id in (6, 76, 20)
group by sensor_id
)
group by date_time
This will produce the following output:
date_time value1 value2 value3
07-02-2005 01:00 1000 2000 3000
...
07-02-2005 23:00 1000 2000 3000
Thanks
Tom
 
 

Re:error saving data back to database.

Tom,
I take it that "value1", "value2", and "value3" are not fields in the
database.. Therefore you have to set them as "non-resolvable"
My first guess is that is{*word*154} up the resolution..
tom wrote:
Quote
Hi there,


I received the following error when I tried to save changed value back to
database. I am using
Jbuilder X and build-in tableScrollPane1, jdbTable1, queryDataSet1 standard
stuffs.

com.borland.dx.sql.dataset.ResolutionException: An error occurred saving a
change to the "Ressim_hourly_view" table


table: ressim_hourly

sensor_id number(5) not null,
date_time date not null,
run_time date not null,
value number(12,3),
flag char(1),

queryDataSet1 contains the following SQL query.

select date_time, max(decode(sensor_id, 6, value, NULL)) as value1,
max(decode(sensor_id,76, value, NULL)) as value2,
max(decode(sensor_id, 20, value, NULL)) as value3
from ressim_hourly
where (sensor_id, run_time) in (select sensor_id, max(run_time)
from ressim_hourly
where sensor_id in (6, 76, 20)
group by sensor_id
)
group by date_time


This will produce the following output:

date_time value1 value2 value3
07-02-2005 01:00 1000 2000 3000
...
07-02-2005 23:00 1000 2000 3000

Thanks

Tom



--
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
www.microps.com/mps/paperFAQ.html
====================================================
 

Re:error saving data back to database.

If I set the column to non-resolvable. I can't save any of the changes back
to the database at all.
Any cue as to why that's the case?
Tom
"tom" < XXXX@XXXXX.COM >wrote in message
Quote
Hi there,


I received the following error when I tried to save changed value back to
database. I am using
Jbuilder X and build-in tableScrollPane1, jdbTable1, queryDataSet1
standard stuffs.

com.borland.dx.sql.dataset.ResolutionException: An error occurred saving a
change to the "Ressim_hourly_view" table


table: ressim_hourly

sensor_id number(5) not null,
date_time date not null,
run_time date not null,
value number(12,3),
flag char(1),

queryDataSet1 contains the following SQL query.

select date_time, max(decode(sensor_id, 6, value, NULL)) as value1,
max(decode(sensor_id,76, value, NULL)) as value2,
max(decode(sensor_id, 20, value, NULL)) as value3
from ressim_hourly
where (sensor_id, run_time) in (select sensor_id, max(run_time)
from ressim_hourly
where sensor_id in (6, 76, 20)
group by sensor_id
)
group by date_time


This will produce the following output:

date_time value1 value2 value3
07-02-2005 01:00 1000 2000 3000
...
07-02-2005 23:00 1000 2000 3000

Thanks

Tom



 

{smallsort}

Re:error saving data back to database.

That makes sense..
What does not make sense is what you expect to "save" back from a query
that gets the Max() from those columns.
I honestly don't see what it is you are saving back and to what record
(primary key field) you are expecting it to be saved to...????
John..
tom wrote:
Quote
If I set the column to non-resolvable. I can't save any of the changes back
to the database at all.

Any cue as to why that's the case?

Tom


"tom" < XXXX@XXXXX.COM >wrote in message
news:42cef4b1$ XXXX@XXXXX.COM ...

>Hi there,
>
>
>I received the following error when I tried to save changed value back to
>database. I am using
>Jbuilder X and build-in tableScrollPane1, jdbTable1, queryDataSet1
>standard stuffs.
>
>com.borland.dx.sql.dataset.ResolutionException: An error occurred saving a
>change to the "Ressim_hourly_view" table
>
>
>table: ressim_hourly
>
>sensor_id number(5) not null,
>date_time date not null,
>run_time date not null,
>value number(12,3),
>flag char(1),
>
>queryDataSet1 contains the following SQL query.
>
>select date_time, max(decode(sensor_id, 6, value, NULL)) as value1,
>max(decode(sensor_id,76, value, NULL)) as value2,
>max(decode(sensor_id, 20, value, NULL)) as value3
>from ressim_hourly
>where (sensor_id, run_time) in (select sensor_id, max(run_time)
>from ressim_hourly
>where sensor_id in (6, 76, 20)
>group by sensor_id
>)
>group by date_time
>
>
>This will produce the following output:
>
>date_time value1 value2 value3
>07-02-2005 01:00 1000 2000 3000
>...
>07-02-2005 23:00 1000 2000 3000
>
>Thanks
>
>Tom
>
>
>



--
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
www.microps.com/mps/paperFAQ.html
====================================================
 

Re:error saving data back to database.

John,
Basically, I am trying to do a self-join on the database table
(ressim_hourly).
I would like to be able to save any changes that made to value1, value2 and
value3 back to the database table.
each one of the values is associated with a sensor_id key, date_time and
run_time ; therefore, each value should be
retrievable via lookup of sensor_id, date_time and run_time.
The max() from those columns were to retrieve the latest copy of data. If I
were to write a SQL query with out the max() function, it would return many
duplicate records.
table: ressim_hourly
sensor_id number(5) not null,
date_time date not null,
run_time date not null,
value number(12,3),
flag char(1));
raw data:
There are 24 entries per 24 hours daily.
sensor_id date_time run_time value flag
6 1/1/2005 1:00 1/1/2005 14:00 1000 Y
6 1/1/2005 2:00 1/1/2005 14:00 1000
6 1/1/2005 3:00 1/1/2005 14:00 1000
6 1/1/2005 4:00 1/1/2005 14:00 1000
..
6 1/1/2005 23:00 1/1/2005 15:00 1000 Y <-----this
value will be retrieved by the query
76 1/1/2005 1:00 1/1/2005 23:00 2000 Y
...
76 1/1/2005 23:00 1/2/2005 14:00 2000 Y <-----this
value will be retrieved by the query
20 1/1/2005 1:00 1/1/2005 12:00 3000 Y
....
20 1/1/2005 23:00 1/2/2005 14:00 3000 Y <-----this
value will be retrieved by the query
Parameterized query:
:sensor1 = 6
:sensor2 = 76
:sensor3 = 20
select sensor_id, date_time, max(decode(sensor_id, :sensor1, value,
NULL)) as value1,
max(decode(sensor_id,:sensor2, value, NULL)) as value2,
max(decode(sensor_id, sensor3, value, NULL)) as value3
from ressim_hourly
where (sensor_id, run_time) in (select sensor_id, max(run_time)
from ressim_hourly
where sensor_id in (:sensor1, :sensor2,
:sensor3)
group by sensor_id
)
group by date_time
Tom
"John Moore" < XXXX@XXXXX.COM >wrote in message
Quote
That makes sense..

What does not make sense is what you expect to "save" back from a query
that gets the Max() from those columns.

I honestly don't see what it is you are saving back and to what record
(primary key field) you are expecting it to be saved to...????

John..

tom wrote:
>If I set the column to non-resolvable. I can't save any of the changes
>back to the database at all.
>
>Any cue as to why that's the case?
>
>Tom
>
>
>"tom" < XXXX@XXXXX.COM >wrote in message
>news:42cef4b1$ XXXX@XXXXX.COM ...
>
>>Hi there,
>>
>>
>>I received the following error when I tried to save changed value back to
>>database. I am using
>>Jbuilder X and build-in tableScrollPane1, jdbTable1, queryDataSet1
>>standard stuffs.
>>
>>com.borland.dx.sql.dataset.ResolutionException: An error occurred saving
>>a change to the "Ressim_hourly_view" table
>>
>>
>>table: ressim_hourly
>>
>>sensor_id number(5) not null,
>>date_time date not null,
>>run_time date not null,
>>value number(12,3),
>>flag char(1),
>>
>>queryDataSet1 contains the following SQL query.
>>
>>select date_time, max(decode(sensor_id, 6, value, NULL)) as value1,
>>max(decode(sensor_id,76, value, NULL)) as value2,
>>max(decode(sensor_id, 20, value, NULL)) as value3
>>from ressim_hourly
>>where (sensor_id, run_time) in (select sensor_id, max(run_time)
>>from ressim_hourly
>>where sensor_id in (6, 76, 20)
>>group by sensor_id
>>)
>>group by date_time
>>
>>
>>This will produce the following output:
>>
>>date_time value1 value2 value3
>>07-02-2005 01:00 1000 2000 3000
>>...
>>07-02-2005 23:00 1000 2000 3000
>>
>>Thanks
>>
>>Tom
>>
>>
>>
>
>
>

--
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
www.microps.com/mps/paperFAQ.html
====================================================
 

Re:error saving data back to database.

Well, your SQL ends up with a table structure as..
sensor_id, date_time, value1, value2, value3
...and you are expecting it to be saved back to a table structure as
sensor_id, date_time run_time value flag
Do you see the problem...??? (hint, names and fields don't match..)
John...
tom wrote:
Quote
John,

Basically, I am trying to do a self-join on the database table
(ressim_hourly).
I would like to be able to save any changes that made to value1, value2 and
value3 back to the database table.
each one of the values is associated with a sensor_id key, date_time and
run_time ; therefore, each value should be
retrievable via lookup of sensor_id, date_time and run_time.

The max() from those columns were to retrieve the latest copy of data. If I
were to write a SQL query with out the max() function, it would return many
duplicate records.

table: ressim_hourly

sensor_id number(5) not null,
date_time date not null,
run_time date not null,
value number(12,3),
flag char(1));

raw data:
There are 24 entries per 24 hours daily.

sensor_id date_time run_time value flag
6 1/1/2005 1:00 1/1/2005 14:00 1000 Y
6 1/1/2005 2:00 1/1/2005 14:00 1000
6 1/1/2005 3:00 1/1/2005 14:00 1000
6 1/1/2005 4:00 1/1/2005 14:00 1000
..
6 1/1/2005 23:00 1/1/2005 15:00 1000 Y <-----this
value will be retrieved by the query
76 1/1/2005 1:00 1/1/2005 23:00 2000 Y
...
76 1/1/2005 23:00 1/2/2005 14:00 2000 Y <-----this
value will be retrieved by the query
20 1/1/2005 1:00 1/1/2005 12:00 3000 Y
....
20 1/1/2005 23:00 1/2/2005 14:00 3000 Y <-----this
value will be retrieved by the query



Parameterized query:
:sensor1 = 6
:sensor2 = 76
:sensor3 = 20

select sensor_id, date_time, max(decode(sensor_id, :sensor1, value,
NULL)) as value1,
max(decode(sensor_id,:sensor2, value, NULL)) as value2,
max(decode(sensor_id, sensor3, value, NULL)) as value3
from ressim_hourly
where (sensor_id, run_time) in (select sensor_id, max(run_time)
from ressim_hourly
where sensor_id in (:sensor1, :sensor2,
:sensor3)
group by sensor_id
)
group by date_time


Tom




"John Moore" < XXXX@XXXXX.COM >wrote in message
news:42e27d12$ XXXX@XXXXX.COM ...

>That makes sense..
>
>What does not make sense is what you expect to "save" back from a query
>that gets the Max() from those columns.
>
>I honestly don't see what it is you are saving back and to what record
>(primary key field) you are expecting it to be saved to...????
>
>John..
>
>tom wrote:
>
>>If I set the column to non-resolvable. I can't save any of the changes
>>back to the database at all.
>>
>>Any cue as to why that's the case?
>>
>>Tom
>>
>>
>>"tom" < XXXX@XXXXX.COM >wrote in message
>>news:42cef4b1$ XXXX@XXXXX.COM ...
>>
>>
>>>Hi there,
>>>
>>>
>>>I received the following error when I tried to save changed value back to
>>>database. I am using
>>>Jbuilder X and build-in tableScrollPane1, jdbTable1, queryDataSet1
>>>standard stuffs.
>>>
>>>com.borland.dx.sql.dataset.ResolutionException: An error occurred saving
>>>a change to the "Ressim_hourly_view" table
>>>
>>>
>>>table: ressim_hourly
>>>
>>>sensor_id number(5) not null,
>>>date_time date not null,
>>>run_time date not null,
>>>value number(12,3),
>>>flag char(1),
>>>
>>>queryDataSet1 contains the following SQL query.
>>>
>>>select date_time, max(decode(sensor_id, 6, value, NULL)) as value1,
>>>max(decode(sensor_id,76, value, NULL)) as value2,
>>>max(decode(sensor_id, 20, value, NULL)) as value3
>>
>>>from ressim_hourly
>>
>>>where (sensor_id, run_time) in (select sensor_id, max(run_time)
>>>from ressim_hourly
>>>where sensor_id in (6, 76, 20)
>>>group by sensor_id
>>>)
>>>group by date_time
>>>
>>>
>>>This will produce the following output:
>>>
>>>date_time value1 value2 value3
>>>07-02-2005 01:00 1000 2000 3000
>>>...
>>>07-02-2005 23:00 1000 2000 3000
>>>
>>>Thanks
>>>
>>>Tom
>>>
>>>
>>>
>>
>>
>>
>--
>=============================================
>TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
>Post all questions and replies to this newsgroup ONLY
>For papers on DataExpress, Applets, JSP, and Web Development go to:
>www.microps.com/mps/paperFAQ.html
>====================================================



--
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
www.microps.com/mps/paperFAQ.html
====================================================