CreateDynaset seems to select twice, generating +2 sequence nos.??? - OO4O and Wizards(Archived)

I am using Oracle 8.1.6 and OO40 on NT4SP5
When I get the .nextval from a sequence using CreateDynaset, I get 2 added to the ID, and not 1. The Sequence cache is set to 0.
I use :
sSql = select table_seq.nextval Docid from dual
Set OD_date=Oradatabase.CreateDynaset(sSql,0)
I seem to remember something about altering the 'option' parameter.
Many thanks in advance 

I assume you have checked your sequence's 'Increment By' setting and it is 1?
Zane E 

I have the same problem and I've checked my Increment by parm, it is set to 1.
If I execute the same sql statement in TOAD or SQL*Plus I get the next value (i.e. it works). But through OO4O it increments by 2.
I use 8.1.7 on W2K. Different Oracle version and OS version, so it sounds like an OO4O problem.
Blaise

Yes, Your Options setting of 0 - ORADYN_DEFAULT may be the problem. I would use...
ORADYN_READONLY +
ORADYN_NO_AUTOBIND +
ORADYN_NOCACHE +
ORADYN_ORAMODE +
ORADYN_NO_REFETCH
Sounds like a refetch is calling it twice.
Zane E

Related

JDBC Timestamp in where-clause

Hi! I'm having a problem with Timestamp and JDBC drivers Oracle 9 and above (all works well when I use a Oracle 8i driver). I'm inserting a row into table DFIAction using a prepared statement (column timestamp is of type DATE): String id = "011131ddc7970001"; Timestamp timestamp = new Timestamp(System.currentTimeMillis()); int state = 0; PreparedStatement preparedStatement = pconnection.prepareStatement("insert into DFIAction (id, state, timestamp) values (?,?,?)"); preparedStatement.setString(1,id); preparedStatement.setInt(2,state); preparedStatement.setTimestamp(3, timestamp); int affected = preparedStatement.executeUpdate(); Then I do an update using the exact same values from the insert (the variable's values haven't changed): preparedStatement = pconnection.prepareStatement("update DFIAction set state=? where id=?" + " and state=?" + " and timestamp=?"); preparedStatement.setInt(1,10); preparedStatement.setString(2,id); preparedStatement.setInt(3,state); preparedStatement.setTimestamp(4, timestamp); affected = preparedStatement.executeUpdate(); But the row doesn't get updated. The value of the column state is still 0 and executeUpdate() returns 0 rows affected. When I leave out the timestamp column from the where-clause it works. (And when I use an Oracle 8i driver it works too.) When doing selects I noticed that the milliseconds of Timestamps get set to 0, but this also happens with the Oracle 8i driver. Is there an issue with loss of precision and if so, do I have to round off all my Timestamp values to 0 milliseconds before passing them to JDBC? Any help greatly appreciated.
user505479,
Handling of DATE data-type by Oracle JDBC driver changed between versions 8 and 9.
In version 10, Oracle realized the error of their ways and introduced the "oracle.jdbc.V8Compatible" property.
You can find more information in the JDBC Developer's Guide and Reference which is part of the documentation for Oracle 10g.
Good Luck,
Avi. 
Yes, that did the trick :) It seems to work with the 9.2.0.5 driver too. Thanks for your help.
Why anyone would want the new behaviour (however that is exactly defined), will probably remain a mystery to me. 
Hi, I'm having the same problem. When I use the timestamp object as date filter for the where-clause in java aplication, only when it has not milliseconds as 0 will work. I'm using the Oracle Database 10g Express Edition 10.2.0.1.0 and I made tests with driver s: classes12 for oracle 10.2.0.3, the ojdbc14 for 10.2.0.1.0, 10.2.0.3 and 9.2.0.8. 
Yes, that did the trick :) It seems to work with the
9.2.0.5 driver too. Thanks for your help.
Why anyone would want the new behaviour (however that
is exactly defined), will probably remain a mystery
to me.It was a mystery to us as well, so we got rid of it in 11g. The 11g drivers work (more) like the 8i drivers, very similar to what happens if you set V8Compatible=true. If for some reason you actually like the 9i, 10g default behavior, then you can set mapDateToTimestamp=false in 11g and get (mostly) the old behavior.
The whole TIMESTAMP issue is a confusing mess. The simple rule of thumb is to set V8Compatible=true if you are using 9i or 10g and do not worry about it otherwise. If that does not do what you want, then either you are trying to do something funky or you need to read more and run some experiments to get the driver to do what you want.
Douglas 
Hi dsurber,
Can you please explain - how to set value mapDateToTimestamp=false.
I am using JBOSS as my application server and in a file Jboss/bin/run.conf
I setting the value of mapDateToTimestamp=false which is not resolving the issue. Normally, in Jboss this file is the location where we set such values.
If this is can be set in a different place please do share your knowledge with me, about how to set this value and where to set this value.
Thanks in advance.
Nainar 
I have no idea how to set it in JBoss. It is an Oracle connection property. You can either set the system property oracle.jdbc.mapDateToTimestamp using -D (or some other method for system properties) or you can pass it in the Properties object to the getConnection method. How to get JBoss to do that I don't know.
Douglas 
Hi,
I got this resolved, by changing the DATE type of the corresponding column in the table to TIMESTAMP.
Regards,
Nainar

timestamp and hibernate

Hi, I'm trying to migrate my postgresql database to oracle express 10 on rhel 5. All seems ok. My application use tomcat and hibernate to access database. I have a problem between java timestamp and oracle timestamp. When I set hibernate in verbose mode, I can see the sql string generated. Here is an exemple:SQL> select * from etat_materiel where date_<='2008-06-19 11:17:55.433'; select * from etat_materiel where date_<='2008-06-19 11:17:55.433'                                         * ERROR at line 1: ORA-01843: not a valid monthApparently it's not the good format for sql timestamp, but I can't find where in hibernate I can specify another way of creating this sql string. Does anybody have been confronted to the same problem ? Regards. PS: I use - tomcat 6 - hibernate 3.2 - oracle express 10 on rhel5
This looks like a "Locale/Timezone" setting issue. In France the day comes before the month so in your case it would consider '19' as the month thus throwing an error.
Jean 
I have the same problem. It seems that the 'encoding' for the timestamp type is various and is defined as a system/database-wide property named NLS_TIMESTAMP_FORMAT. You can check for it here:
select * from sys.prop$ where 1=1;
what I have found out as well is that you can change the value of this property using ALTER SESSION:
alter session set NLS_TIMESTAMP_FORMAT = 'RRR-MM-DD HH:MI:SS.FF'
(this reflects your query string).
There is a way to use your SQL query string using TO_TIMESTAMP function:
SQL> select * from etat_materiel where date_<='2008-06-19 11:17:55.433';
select * from etat_materiel where date_<=to_timestamp('2008-06-19 11:17:55.433', 'RRRR-MM-DD HH:MI:SS.FF');
This should work for you.
My problem is somewhat different - I am using Hibernate Query Language (HQL) to get my Java objects from the database. I'm hitting the wall like you do because when I use similarly formatted 'WHERE' clause, HQL translates it directly to SQL. I am not sure if HQL will accept native Oracle SQL function 'TO_TIMESTAMP' - probably not... so I'm not clear what to do. It would be nice to be able to include the NLS_TIMESTAMP_FORMAT somewhere at the beginning of the hibernate session...

Problem with "=" operator in Select statement on Olite WinCE

We are having a weird problem with data downloaded
to an iPaq using Consolidator (mSync). On some
text (VARCHAR) columns when we try to locate unique
text strings using a query such as
SELECT * FROM myTable WHERE description = '11kv'
we get no result and yet the same query re-written
using the "LIKE" operator
SELECT * FROM myTable WHERE description LIKE '11kv'
works just fine.
We have tested for extraneous characters in the column
(there are none) and have tried building an index (no
difference). If we copy the synchronised table
using
CREATE TABLE myTable2 AS SELECT * FROM myTable
then the "=" operator works fine.
Has anyone else experienced this problem?
Cheers,
Allen. 
Further info:
An easier way to rectify the problem
is to either update all the affected
columns
UPDATE myTable SET myCol = myCol;
The '=' operator will then work fine.
Another (even less desirable) technique
is to wrap the column reference in the
WHERE statement of a SELECT in a
function such as upper(myCol)
SELECT * FROM myTable WHERE upper(myCol) = 'myValue';
Both of these methods would imply that
once the data in the VARCHAR columns has
passed through the Oracle Lite WinCE string
handler the resulting string is modified in
such a way that the data then becomes
recognisable.
Has no-one else experienced this???
Thanks,
Allen. 
Upgraded to latest patch and problem is resolved.
Thanks for the input. :-)
Allen. 
Sorry, but i applied the patch and i have the same problem in 9i Lite for Palm, when i using equal operator in select statement in columns with strings comparisions fail, the odbc driver get me the error 'S0001' EXECUTION OF CLAUSE WHERE FAILED' ERROR number 10.
This problem is a reported bug, but it persis after applied patch 5.0.1.1 for Oracle 9i Lite.
I believe that the solution to this problem is in the new release of Oracle 9iLite 5.0.2
that only God or Oracle's Internal Team know when is released.
Happyness....
CCRD.

Windows XP,  Visual Basic 5 and OO4O

We have a VB 5 application that uses OO4O (2.2.03.0.0) to access an Oracle 8i database.
We run the following code to create unique and consecutive sequence numbers:
sqlString = "SELECT SEQ_TBLCHEQUEREQ.NextVal as CINum FROM Sys.Dual"
Set OraDynaset = oraDatabase.DBCreateDynaset(sqlString, 0&)
CINumber = OraDynaset.Fields("CINum").Value
Running under an NT 4 Workstation client, there is no problem. Since we migrated to XP, the sequence numbers increment by twos. I suspect that the OraDynaSet is created twice somehow.
The sequence is set to increment by ones with no cache and works properly in SQL*Plus.
Any ideas as to what would cause this?
Thanks,
I'm not sure what's causing your problem, but I did want to ensure that you're aware that there's no way to guarantee a gap-free Oracle sequence. The only way to do this is through a hideous hack where you create a table, rather than a sequence, with one row in it, though this forces all your session to serialize.
One of many askTom links on the subject...
<http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4343369880986>
Justin 
I've run into a similar problem under Windows 2000. The solution I found is to change the "Set OraDynaset..." statement to the following:
"Set OraDynaset = oraDatabase.DBCreateDynaset(sqlString, 20&)"
The option '20&' at the end will cancel out the automatic refetch which is done by the Oracle Database object.

Oracle 9i, Sequences and ADO

ok, here goes...
Have an application that will require Oracle connectivity, it already has an abstracted data layer and currently works under MSSQL, Access, MySQL, etc..
The problem is as follows: The centralized code we use to add a record to any of the above mentionned DB platforms follows the same code base;
- Retrieve an ADO recordset containing the schema (columns) of the table you want to insert to, ensuring that it is blank ; for example: (this has been dumbed down to a ADO recordset for the sake of clairity and my own testing)
Set poCmpsRS = mo_GetRS("select id, CMPTNC_GRP_ID from cmptncs where id = -1")
then typically, an Addnew is called to create the new record:
poCmpsRS.AddNew
poCmpsRS.Fields("CMPTNC_GRP_ID").Value = 1
poCmpsRS.UpdateBatch
Now, on ALL OTHER DB platforms, the contents of .fields("ID") will contain the auto_increment value from access, mysql, mssql etc... but in Oracles case, as soon as updatebatch is called, the contents of .fields("ID") show "0" (zero), even though the contents of the database effectively show the triggered sequence value for the column.
This boils down to 2 questions:
Is there any way to get the last value issued by a sequence via an SQL call?
OR
Is there any way to get ADO to behave properly with Oracle with respect to retrieving the sequence value?
I'd HATE to have to modify our code to pre-fetch sequence values prior to issuing an Addnew to the DB, call it laziness; I don't wanna go there, MANY MANY tables. 
Anyone?
The only potential resolution I can see, is to pre-fetch the sequences number once the ADO addnew method is called, and do away with the trigger.
I have the exact same problem with an application
that works great with other DBMS's. I would appreciate
it if you let me know what/if you have found any kind
of solution. Thanks, Tony.
A couple of options I can think of:
1) You can get the last value of a sequence in Oracle by calling <<sequence name>>.currval
2) You can requery the ResultSet, which will update the values.
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC 
Paul,
I've exactly the same problem. Have you already found a solution? 
Hi all,
I also have this problem, any ideas?
"Jan" - I found a lot of your posts on the internet - I was hopeful your solution would work for me but it does not :( I copied you code almost entirely into my VB6 app, but no joy :( Can you or anyone else help?
I open the connection with the cursorlocation to adServer, and use adOpenKeyset with the recordset, but after the rs.update the id column is null!!!!
I am using an Oracle 8i database - could that be the problem?
Thanks! 
...Sorry, I should have added an URL to "Jans" post I was talking about:
http://www.talkaboutdatabases.com/group/comp.databases.oracle.server/messages/269136.html 
I'm afraid I can't help you.
I've tested my solution with oracle 9 & 10.
If it is not working with Oracle 8 you should take another approach.
Eg. making a stored function which does the insert and returns the new id. 
Ah well, it looks like a limitation of oracle 8i :(
Thanks anyway.... 
Hi skinnepa,
I work with vb 6.0 and VB .NET.
The solution I'm using for this problem is:
1st) Create a Sequence for the specific field (E.g.
CREATE SEQUENCE XXX.XXID_SEQ
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;)
2nd) Create a trigger associate to the specific table (in your case "cmptncs") that will be fired after the insert...Probably your problem is that ADO with the AddNew method firstly created the row. In any case the trigger fired after the insert should work. In the trigger (E.g.
CREATE OR REPLACE TRIGGER DLW_EVENT_MAIN_TRIGGER
BEFORE INSERT
ON DLW_EVENT_MAIN
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 0;
SELECT DSA.XXID_SEQ.NEXTVAL INTO tmpVar FROM dual;
:NEW.XXID := tmpVar;
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END DLW_EVENT_MAIN_TRIGGER;
) you can select the XXID_SEQ.NEXTVAL and place it into the new val...
Now it should works fine...

Categories

Resources