Solved

Oracle sequence number ODBC problem

Posted on 2002-05-16
4
483 Views
Last Modified: 2008-03-10
I'm trying to get the next sequence number from an Oracle sequence with:

SELECT WRV.WRV_SEQ_ADRESSEN.NEXTVAL FROM DUAL

This works fine with the Oracle ODBC drivers from Oracle, but not with those from microsoft. Miscrosoft gives an "value out of range" error (error 22003) for sequences with high numbers.

I checked the ODBC trace for both drivers and it appears that the Microsft versions see the NEXTVAL column as a SQL_C_SHORT (5 positions) where Oracle sees it as a SQL_C_DOUBLE (8 positions).

Unofrtunately the target machine has only Microsoft ODBC drivers so I'm stuck with those.

How do I tell the Microsoft ODBC driver for Oracle to use a larger datatayp for the NEXTVAL column?

How can I instruct
0
Comment
Question by:patrickl
  • 2
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 7013390
You can cast the value to varchar datatype:

SELECT to_char(WRV.WRV_SEQ_ADRESSEN.NEXTVAL) FROM DUAL

CHeers
0
 
LVL 5

Author Comment

by:patrickl
ID: 7013397
Cool, that works! I was trying something like that, but no luck. Great!

Do you have an idea why this goes wrong at all?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7013399
the ODBC drivers have different data type representations, and while the ORACLE driver knows the data types, the one of MS does some kind of approximation :-(
Glad I could help.
CHeers
0
 
LVL 5

Author Comment

by:patrickl
ID: 7013413
I guess ODBC is just a minefield. I always keep running into trouble with it. The oracle drivers never seem to install and the Microsoft ones don't always work.

But anyway, many thanks.

Just to be complete. I change the query to:

SELECT to_char(WRV.WRV_SEQ_ADRESSEN.NEXTVAL) AS NEXTVAL FROM DUAL

To make it equivalent to the one I had before. Works like a charm.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question