Asp Forum - Invalid Table Name, need database schema - microsoft.public.dotnet.framework.odbcnet

Dan Elebash

11/17/2002 1:19:00 AM

I am connecting to a mumps database via odbc dsn which
works fine in access and msquery. I can see list of
tables and run a query in msquery using odbc dsn. In .net
odbc I can get information from certain tables but other
tables give me invalid table name, I have used both the
SQL generated by msquery and Access2000 to select info
from the tabe as my select statement for obdc.net. with
msquery giving me invalid schema and access giving me
invalid table. Table name that I am having trouble with
is TR_CHECK_REGISTER, Access lists select statment "SELECT
* FROM TR_CHECK_REGISTER" msquery lists select
statement "SELECT * FROM TR.CHECK_REGISTER". One has
period one has underbar. I have tried both select
statements and just CHECK_REGISTER in odbc.net without
success, I need to know how odbc.net see's the table
names, maybe it is truncating or translating the name
differently. What are my options? Thank you. I cannot
use OLEDB since my driver does not support this

6 Answers

Bob Grommes

11/17/2002 5:29:00 PM

You are on the right track here. Recently I had to interface with FileMaker
(yech) via ODBC and found that their (rather crude) ODBC driver simply
doesn't support table or field names with spaces in them ... you must use
underscores. This, even though you can enumerate those offending tables.
And if you change the table name to have no spaces, you can do a SELECT *
(no field list or WHERE clause) and get back usable records, no problem.
But as soon as you try to reference such fields explicitly in your query by
name, the driver can't understand what you're asking it for.

Fortunately I had access to the team that develops those apps and was able
to get them to change the table name and field names I needed access to.

Have you tried surrounding the troublesome table name in square brackets?
Some drivers honor those. Might be worth a try.

By any chance, is TR a database name on the mumps side? Perhaps
TR!CHECK_REGISTER would work. Again, just a stab in the dark, but a cheap
experiment.

--Bob

"dan elebash" <delebash@relico.com> wrote in message
news:40cd01c28dcf$0f07eec0$89f82ecf@TK2MSFTNGXA01...
> I am connecting to a mumps database via odbc dsn which
> works fine in access and msquery. I can see list of
> tables and run a query in msquery using odbc dsn. In .net
> odbc I can get information from certain tables but other
> tables give me invalid table name, I have used both the
> SQL generated by msquery and Access2000 to select info
> from the tabe as my select statement for obdc.net. with
> msquery giving me invalid schema and access giving me
> invalid table. Table name that I am having trouble with
> is TR_CHECK_REGISTER, Access lists select statment "SELECT
> * FROM TR_CHECK_REGISTER" msquery lists select
> statement "SELECT * FROM TR.CHECK_REGISTER". One has
> period one has underbar. I have tried both select
> statements and just CHECK_REGISTER in odbc.net without
> success, I need to know how odbc.net see's the table
> names, maybe it is truncating or translating the name
> differently. What are my options? Thank you. I cannot
> use OLEDB since my driver does not support this

(Yan Liu [MS])

11/18/2002 11:14:00 AM

Hi,

I didn't have mumps database at hand. Therefore, I did some test against
SQL server. I tried a table with several underscores contained and had no
problem retrieving the data. Since ODBC.NET serves as a middle tier who
talks to underlying ODBC driver, I do think this is more likely driver
related. Bob's input was also a strong proof. Here is another proof of this
issue: MySql ODBC driver 2.5 does not work with ODBC.NET provider and it
turns out that the driver has a bug in it.

ODBC.NET should pass the table name as is to the underlying ODBC driver. Is
there newsgroup for Mumps? If so, I think it is worth that we have a try.
Also, is it possible to check the table name from Mumps' side? Does Mumps
provide a tool such as SQL profiler so that you can check which SQL
statement is submitted to database server? We just lacks the resource of
troubleshooting this issue since it is very likely we need to look into the
third party driver for more information. Maybe this is a well-known issue
for Mumps support?

HTH.

Regards,
Yan Liu
This posting is provided "AS IS" with no warranties, and confers no rights.

Dan Elebash

11/18/2002 4:54:00 PM

Thanks for you suggestions, I have tried using [ _ and !
but still doesn't work, since it does work in access and
msquery the odbc.net must be translating the table names
differently, I can do select * from some tables TR_TAB_5
works so not sure if it is the underscore problem, is
there anyway to loop through the tables in the database
via odbc.net to show how odbc.net see the tables? Maybe
it is truncation name??

>-----Original Message-----
>Hi,
>
>I didn't have mumps database at hand. Therefore, I did
some test against
>SQL server. I tried a table with several underscores
contained and had no
>problem retrieving the data. Since ODBC.NET serves as a
middle tier who
>talks to underlying ODBC driver, I do think this is more
likely driver
>related. Bob's input was also a strong proof. Here is
another proof of this
>issue: MySql ODBC driver 2.5 does not work with ODBC.NET
provider and it
>turns out that the driver has a bug in it.
>
>ODBC.NET should pass the table name as is to the
underlying ODBC driver. Is
>there newsgroup for Mumps? If so, I think it is worth
that we have a try.
>Also, is it possible to check the table name from Mumps'
side? Does Mumps
>provide a tool such as SQL profiler so that you can check
which SQL
>statement is submitted to database server? We just lacks
the resource of
>troubleshooting this issue since it is very likely we
need to look into the
>third party driver for more information. Maybe this is a
well-known issue
>for Mumps support?
>
>HTH.
>
>Regards,
>Yan Liu
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>

(Yan Liu [MS])

11/19/2002 3:17:00 AM

Hi Dan,

Yes, we might be able to fix this issue if we were able to list all the
tables using ODBC.NET provider. Unfortunately I was not able to find out
such a class by examing the help of ODBC.NET. Even we have this class, it
could be inaccurate. The fact that none of the SQL queries generated by
Access or MSQuery works indicates this. We have two choices to troubleshoot
this issue:

1. From client side
2. From server side

So far what we have tried is all from client side which is basically some
black box testing. Should Mumps provide some tools such as SQL profiler or
some similar tracing unilities, we will be able to tell where the problem
lies in a timely manner, won't we? Say we have the tracing utility, what we
need is to compare the trace log of Access and ODBC.NET and then we will be
able to tell the difference and do something to work around this issue.
Isn't there a better newsgroup that we might request information about the
tracing support provided by Mumps DBMS? I don't have access to the source
code of ODBC.NET, neither do I have Mumps database at hand. Thus, checking
the root cause of this issue from client side is not efficient in my
opinion. What do you think of this?

Regards,
Yan Liu
This posting is provided "AS IS" with no warranties, and confers no rights.

Dan Elebash

11/21/2002 6:18:00 PM

Unfortunately I don't have many resources in mumps either.
I will check other news groups for tracing, thanks for the
suggestion. But no I don't think this is better posted in
another news group becuaes Access and MSQuery both work
successfully and both enumerate the Tables in the
database, then this is not a mumps issue, although we may
be able to troubleshoot it better with a mumps tracing
tool. Maybe for the next version of odbc.net there should
be a function for listing tables since it is already a
function of the odbc driver it seems like it would be to
difficult to add this. Thanks for your help.

>-----Original Message-----
>Hi Dan,
>
>Yes, we might be able to fix this issue if we were able
to list all the
>tables using ODBC.NET provider. Unfortunately I was not
able to find out
>such a class by examing the help of ODBC.NET. Even we
have this class, it
>could be inaccurate. The fact that none of the SQL
queries generated by
>Access or MSQuery works indicates this. We have two
choices to troubleshoot
>this issue:
>
>1. From client side
>2. From server side
>
>So far what we have tried is all from client side which
is basically some
>black box testing. Should Mumps provide some tools such
as SQL profiler or
>some similar tracing unilities, we will be able to tell
where the problem
>lies in a timely manner, won't we? Say we have the
tracing utility, what we
>need is to compare the trace log of Access and ODBC.NET
and then we will be
>able to tell the difference and do something to work
around this issue.
>Isn't there a better newsgroup that we might request
information about the
>tracing support provided by Mumps DBMS? I don't have
access to the source
>code of ODBC.NET, neither do I have Mumps database at
hand. Thus, checking
>the root cause of this issue from client side is not
efficient in my
>opinion. What do you think of this?
>
>Regards,
>Yan Liu
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>

(Yan Liu [MS])

11/22/2002 3:09:00 AM

Hi Dan,

Thanks for your feedbacks. By saying 'a better newsgroup', I was actually
talking about a newsgroup where you can get information about the tracing
support of Mumps. I agree with you that if ODBC.NET has the functionality
of enumerating database tables, the problem might have been resolved. As a
side note, I want you to know that you can send feedbacks about out
products to mswish@microsoft.com. Furthermore, I a little suspect this is a
table name truncation issue, is it possible to shorten the table name by
renaming the table in question?

Regards,
Yan Liu
This posting is provided "AS IS" with no warranties, and confers no rights.

microsoft.public.dotnet.framework.odbcnet

Invalid Table Name, need database schema

Dan Elebash

Bob Grommes

(Yan Liu [MS])

Dan Elebash

(Yan Liu [MS])

Dan Elebash

(Yan Liu [MS])

x Login to ForumsZone