HELP !!! ODBC Error in MS ACCESS - ODBC

Hi !!
I have a big problem !
I am developing Applications in MS ACCESS 2K and I use an Oracle (Ver 8.1.7) Database.
I connect via ODBC (i just downloaded the newest Oracle ODBC Driver 8.01.74.00)
So, there's just one problem:
If in a Oracle Table there are decimal values, in MS ACCESS they are not converted as decimal values
e.g. : ORACLE TABLE VALUE : 12,43
ACCESS TABLE (ODBC): 1243 (without any comma)
In Oracle the column is setted as (number(5,2))
...in Access it's imported as decimal(5,2), but it doesn't show any decimal values
all other tables and columns are linked correct.
only the decimal values show such problems...
...i really don't know what to do anymore!!!
please help 

I use MS ODBC driver instead of Oracle ODBC driver with Access W2K and Oracle 8.1.7.
It work right 

Given your e-mail address, I suspect that you have a discrepency between your NLS settings and your Windows Internationalization settings. Windows probably wants the decimal separator to be a comma (,) while NLS probably wants the separator to be a period (.).
Justin

Related

sybase oracle heterogeneous connection

Hi all,
I created a connection from oracle to sybase using oracle heterogeneous connection. However I have a problem with numeric characters, I cannot get floating parts of numbers.
when I use create as syntax it maps to correct data types(with the correct precision and scale) but 12,49 comes like 12,00.
My Database is 10.2.0.3.0 32bit windows
Any help would be appreciated.. 
The problem was because of language settings. Setting NLS_LANG in the registery to correct setting solved the problem..

MS Access via ODBC to Oracle CLOB field Types show as Text Type in Access

When connecting to a migrated ACCESS database to the ORACLE database via ODBC the ORACLE CLOB fields show up in ACCESS as 255 char text fields.
Original ACCESS tables were Memo Type, they converted correctly to ORACLE CLOB type but the reverse doesn't appear to work ACCESS sees them as 255 char Text type
MS ACCESS version 2003 sp2
ORACLE version 8
Microsoft ODBC Driver 2.575.117.00
ORACLE driver 8.01.07.00
Is there an easy fix without modifing the ACCESS front end. 
Hi,
I'm unable to reproduce the behaviour you are seeing, as I currently don't have access to an Oracle 8 instance. I know that there is no such issue when working with a newer database instance e.g Oracle 10g, and a CLOB column in Oracle appears as a MEMO column in an imported table (via ODBC) in MS Access. Have you tried to change the TEXT column to a MEMO in the imported table? This change would only require updating the underlying table and not any front end objects. I've tried it on a few test cases, but again not connecting to an Oracle 8 instance, and the change works.
I would recommend trying to change the column type to MEMO. If this doesn't work, please let me know.
Regards,
Hilary 
Thanks,
I'm in the process of migrating a couple of the tables to 10 to see if it resolves the issue. Thanks again 
Hilary,
HELP, I converted to Oracle 10 and I get the same results. When I access the converted table in ORACLE connected via ODBC link from the Access Front End the CLOB data types with lengths upwards of 3000 characters show up as Text types length 255 and data truncates to 255 char.
All the information I can read states this is not supported
Can you give me an idea of how you are configured or the ODBC drivers you are using 
Hi,
If possible can you try doing the following:
1. Install an Oracle 10g ODBC driver on your machine, if you haven't done so already. For this test, I have used version 10.02.00.01 of the Oracle ODBC driver.
2. Set up a new DSN to your migrated Oracle database schema.
3. In MS Access add a New Table to your MDB file - it should be either an Import Table or a Link Table.
4. Set the file type to ODBC Databases, and browse to the Machine Data Source DSN that you set up in step 2. Select the table you wish to link to.
5. To avoid having to update your form object, make sure that the linked/imported table name matches the name of the original MS Access table e.g. Employees.
6. Run your form & you should be able to view the CLOB information successfully.
If you are still experiencing issues doing so, please outline all the steps you have carried out in your attempt to access & view the CLOB information.
I hope this helps.
Regards,
Hilary 
Where can we find the Oracle 10g ODBC Driver to install?

ODP.NET and database characterset

I'm a newbie in using .NET and ODP.NET and I've found a strange situation when I've tried to select varchar2 from Oracle using ODP.NET. That's it:
Environment:
Oracle Database 8.1.7.4.1. Oracle Client 9.2.0.4. ODP.NET 9.2.0.401. Visual Studio 2003.
Situation: Two databases - one with character set WE8ISO8859P1 (database 1), second with character set CL8MSWIN1251 (database 2).
When I'm testing client NLS setting in .NET (via OracleGlobalization.GetClientInfo) everything is OK. For example, let it be NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1.
When I'm selecting varchar2 data (cyrillic encoding) from database 1 under SQL-PLUS I'm getting correct cyrillic characters, when I'm selecting the same data from database 2 (with the same NLS_LANG) I'm getting '?????' instead of cyrillic characters.
But when I've tried the same operations under .NET app and the same NLS_LANG environment I've got the opposite results. The select results from database 1 show unreadable characters (not '?????'), but the select results from database 2 show correct cyrillic characters.
So, that's the question:
1. What is the relation between NLS_LANG setting and the results of selecting data from such two databases?
2. Is it my mistake? What I'm doing wrong? Or is it a bug of ODP.NET or Visual Studio?
Below is the C# source:
OracleString vchar;
OracleGlobalization ClientGlob = OracleGlobalization.GetClientInfo();
MessageBox.Show (ClientGlob.Language);
MessageBox.Show (ClientGlob.Territory);
MessageBox.Show (ClientGlob.ClientCharacterSet);
string sql = "select field from table where key = value";
OracleCommand cmd = new OracleCommand (sql, con);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
vchar = dr.GetOracleString(0);
MessageBox.Show (vchar.ToString());
Thanks fo any help. The decision has been found. I've solved the problem by using Encoding class of .NET framework. And the value of NLS_LANG environment variable isn't important to get correct characters from both databases. 
Hello, I am having the same problem with Greek characters. How did you fix your problem which is exactly the same with mine?
Thanks in advance 
Hello, Can you tell me how did manage to fix this problem because I have the same one.
Thanks in advance 
Hi,
99% of the time this issue is caused by folks trying to store characters in the database that the database characterset doesnt support, OR using a db that is the proper characterset but inserting them incorrectly resulting in invalid encoding.
so..
1) what characterset is your database? (select * from nls_database_parameters)
2) what character are you having a problem with?
3) how is it stored in the database? (select dump(columname,1016) from table where SomethingToMakeJustThatRowComeBack).
cheers,
Greg 
Hello Greg and thanks a lot for your help. First of all I must say that I am not an expert in oracle. What I am trying to do is to use VB.NET in order to select some data from our oracle database and export them in a TXT file. I am having problem with the Greek characters and microsoft tools (access or .net). In order to see these characters from all these tools I must use a specific font HELLASARIAL. So what I am trying to do is to convert the greek characters to another codepage which will be readed by almost all windows fonts. If I use a tool like PL/SQL DEVELOPER, and I export the results to a TXT file, I have no problem to see the greek characters.
Now the answers to your questions:
1. NLS_NCHAR_CHARACTERSET     WE8ISO8859P1
NLS_LANGUAGE     AMERICAN
NLS_CHARACTERSET     WE8ISO8859P1
NLS_COMP     BINARY
2 Greek Characters
3. 1     Typ=1 Len=25 CharacterSet=WE8ISO8859P1: ca,cf,d5,da,cc,d4,c6,c7,d3,20,cd,c9,ca,2e,20,26,20,d3,c9,c1,20,cf,2e,c5,2e
Now what I am using in order to retrieve data from oracle is the embedded oracle oledb driver which is comming with NET. I do not know if someone can help me on this problem.
Thanks in advance for your help. 
Hi,
WE8ISO8859P1 doesnt support Greek characters.. to see what it does support, check the following for example:
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/QB3AQ501/F.22?SHELF=&DT=19971201194621
A lot of times folks get confused because they think just because it happens to work with certain tools/drivers/etc, they have a valid setup. Thats not the case though, your database characterset needs to support the characters you're trying to store, or all bets are off.
Hope that helps,
Greg 
Well finally I found what was the problem. I do not know if I told you but when I am using some external tools of oracle I have no problem with the greek characters. This drive me to download another OLEDB driver of oracle from CoreLab and I test it and the problem with greek characters disappeared. Unfortunately, I tried to install the driver for .NET that oracle made it but the installation is stopping in the beginning with an error. So I will send an email to microsoft to its drivers because the problem is appearing not only to .NET but its other applications like MS Access.
Thanks a lot 
Here is the answer of the microsoft:
I have the information that you have a VB.Net 2005 application connected to an Oracle database 8.1.7.4 hosted on a UNIX server.
This database has the CharacterSet WE8ISO8859P1.
When retrieving Greek characters from this database in the application, you cannot see them.
Could you please send me a screenshot of these characters in the .Net application?
Are they displayed as gibberish, or as inverted questions marks (?)?
I already had similar cases with Hebrew characters hosted on an Oracle database.
These characters were displayed as questions marks on the client side.
This is due to the fact that System.Data.OracleClient is using the Server CharacterSet to display the characters.
If your Greek characters are not stored in the WE8ISO8859P1 characterset, then they won’t display correctly on the client-side.
This is different from OLEDB where you could interact on client side by modifying the NLS_LANG parameter in the registry HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0.
The client NLS_LANG and the server CharacterSet had to match in order to correctly display the data, and avoid SQL*NET conversion.
So there are two solutions to your case:
- The first one is to create a new database using the P8 characterset. The Oracle .Net managed provider will so be able to use it and display the characters correctly.
- The second one is to use the OLEDB.Net managed provider, and then use OLEDB for Oracle provider. OLEDB will take care of the client NLS_LANG registry parameter.
Would it be possible to test your application against an Oracle database with WE8ISO8859P8 characterset?
Would it be possible to test it with the OLEDB .Net managed provider, and after checking the NLS_LANG client registry parameter?

ODBC Driver with Access problem

I have problems with fields of the number(9.3) datatype. in my linked access tables numbers like 1555.33 are shown as 155533.
I'm using the oracle odbc driver version 9.2.0.54 with a 9i (9.2.0.1.0) Database.
I've reading at an old post called "number(9.3) DataType in MS Access". Where I've found someone with the same problem as me. I've done everythink which is explained on that message, but it still doesn't work.
Is there anything else to do?
Thank you in advance! 
Hi Carles:
I use to have a similar problem with columns with decimels defined.
My solutions, the most quickly, was to create a new column defined just as number, with fixed length. And delete the other after passing the data into it.
I hope this works for you.
I guess this was a problem with the ODBC because use the ODBC means you would be restricted in some functions. 
I do this because my linked tables are actually just views, and I can't modify their structure.
Anyone knows another solution?
Just tell me something.
You linked views in MS Access from ORACLE thru ODBC.
How can you do that? And which version of MS Access are you using?
I ask you that because I can't see views with MS Access 97 thru ODBC.
Thanks a lot 
I'm using Access 2000. For me there isn't difference between tables & views trying to link them. May be you must grant access to your user to this views first.
See you,
Thanks a lot..... I didn't know I can see all my objects, tables and views.....
Thanks a lot.... again
Did you fixed your problem? 
No I'm still on the way, looking for a solution.
I hope someone may help me soon, because I need this info to do some reports before hollidays.
What version of the MDAC do you have installed?
Do you live in a region that uses the comma as a decimal separator?
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
I got MDAC 2.8, and yes I'm from Spain, we use comma as decimal separator.
Do you think this would be the problem? 
In the DSN configuration screen, there should be a pulldown for which national settings are to be used. Try all three options, but I believe Access wants you to choose the one that forces the decimal separator to be a period.
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
The following is extracted from Oralce's Metalink: (sorry lost the link)
"This is an NLS related issue. The ODBC driver uses the values derived from the NLS_LANG setting from the Oracle Home where the driver is installed.
The application actually uses the number setting from the NT Regional Settings.
When these two settings differ, then the decimal character will be cut off. This behaviour is also described in Bug:694323.
Example:
You have set your NLS_LANG in the ORACLE_HOME of your ODBC driver to GERMAN_GERMANY.WE8ISO8859P1. This will result in a value for the NLS_NUMERIC_CHARACTERS of ",." (the first part of the NLS_NUMERIC_CHARACTERS being the decimal character and the second part being the group separator).
So the "." is used by the Application and the "," is used by the Oracle ODBC driver.
As a result numeric values in your Oracle Database with an integer and a decimal part will thus be displayed by the application without the decimal character, i.e. a value of 10,734 will be displayed as 10734 a value of 0,123 will be displayed as 123.
To fix this, you will either have to correct the character used for the Decimal Symbol of your NT Regional settings to match the value of your Oracle NLS_LANG setting or vice versa. To change the value for NLS_LANG, go to your registry and change the NLS_LANG parameter that is situated under HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOMEnnn/NLS_LANG where HOMEnnn is the home where your Oracle ODBC driver is installed."
Hope this helps, resolved our issue with this problem. 
I believe that recent versions of the ODBC driver should fix this. In the DSN configuration screen, there is now an option to determine which setting the ODBC driver uses to identify the decimal separator.
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

Column truncated using hsodbc to AS400

Hi,
I appreciate if there any of can help me.
Is there limitation on hsodbc when retrieve or writing character more then 255 character from non Oracle via hsodbc ?
I'm using Oracle 8.1.6.3 to read and write record to AS400 using client access ODBC. I need to read from and write to a column of character 500 type. But without any error the result is truncated to 254 character. I use a view to split the 500char column into 3 smaller column and it succeed for read operation, but I cannot write to it.
I have try to read on write with the same ODBC using Oracle Forms without hsodbc with success. So I believe it is about hsodbc, no ODBC driver.
So is there any limitation, or any parameter to change on hsodbc ?
Thank you for any help
Frans.
Frans,
Yes there was a limitation in hsodbc v8.1.6. This has been fixed for v9.2 and a patch is available for v9.0.1.

Categories

Resources