headers
Rob Richardson | 25 Apr 2012 16:05
Picon
Favicon

How can I keep an OdbcDataAdapter from using fully qualified tble names?

Hello!

How can I keep an OdbcDataAdapter from insisting on the fully qualified table 
name?

In VS 2008, I am trying to use the System.Data.Odbc namespace to work with a 
PostGres database.  I don't want to use npgsql because I want to work with DSNs 
so that I get the flexibility of changing databases without changing my 
program.  

When I use an OdbcCommandBuilder object to create the insert, update and delete 
commands for me, the table names are fully qualified.  For example, an insert 
command might begin with "insert into MyDatabase.public.MyTable...".  When I try 
to use that, I get an exception thrown that complains that cross-database 
references are not implemented (even though the connection I am using is to the 
MyDatabase database).  

So, I wrote a little function that converts a string containing 
"MyDatabase.public.MyTable" into "MyTable", and use that to replace the fully 
qualified name in the command text with the bare table name.  But when it comes 
to time to call the adapter's Update() method to add a new row into my table, 
the first thing the adapter does is to change the bare table name back to the 
full "MyDatabase.public.MyTable" name, and then I get the same exception.

Thank you very much.

RobR

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
(Continue reading)

Permalink | Reply |
headers
Barry Bell | 25 Apr 2012 16:11
Favicon

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

It maybe a dns issue, The below connection works because server "my-server"
Resolves in my land from "my-server" to "my-server.mycompany.com".

Driver={PostgreSQL ANSI};Server=my-server;Port=5432;Database=dbname;Uid=user;Pwd=password;BI=2;TextAsLongVarchar=1;UnknownSizes=2;UnknownsAsLongVarchar=1;UseServerSidePrepare=1;

Does this help?

Why not go DSN-less? (Put the connection string into an INI disturbed with the program?) 
Otherwise, you will goto every computer and Update the DSN whenever the server or db name is changed


Thanks
Barry Bell, IT Department 
Office: 954-429-3771 x267 Fax: 954-281-1464 email Barry_Bell <at> harte-hanks.com


-----Original Message-----
From: pgsql-odbc-owner <at> postgresql.org [mailto:pgsql-odbc-owner <at> postgresql.org] On Behalf Of Rob Richardson
Sent: Wednesday, April 25, 2012 10:06 AM
To: pgsql-odbc <at> postgresql.org
Subject: [ODBC] How can I keep an OdbcDataAdapter from using fully qualified tble names?

Hello!

How can I keep an OdbcDataAdapter from insisting on the fully qualified table name?


In VS 2008, I am trying to use the System.Data.Odbc namespace to work with a 
PostGres database.  I don't want to use npgsql because I want to work with DSNs 
so that I get the flexibility of changing databases without changing my
(Continue reading)

Permalink | Reply |
headers
Rob Richardson | 25 Apr 2012 16:25
Picon
Favicon

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

I want to stick with DSNs because we change databases frequently, mainly on our 
own systems.  Develop first using a test database, then do final testing on a 
copy of the customer's production database, then, if problems crop up, get an 
up-to-date copy of the customer's database and debug using that.  That's what 
DSNs are for.  They work.  (At least until the .Net world apparently decided it 
knows better.)

I think you're thinking of a different issue.  I'm not talking about a fully 
qualified name of a server, with "mycompany.com" appended to it.  I'm talking 
about a fully qualified table name, with the database name and schema prepended 
to it.

RobR

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Permalink | Reply |
headers
Barry Bell | 25 Apr 2012 16:35
Favicon

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

Your SQL"select * from DBNAME.TABLENAME"  
Instead of just " select * from TABLENAME"  ?

Does the user use a different DB Login then you do?
Try logging as the user into pgadmin and run the SQL.

If you have the same issues, the issue is with the user access rights (or under which right a view/table was created).


Thanks
Barry Bell, IT Department 
Office: 954-429-3771 x267 Fax: 954-281-1464 email Barry_Bell <at> harte-hanks.com


-----Original Message-----
From: pgsql-odbc-owner <at> postgresql.org [mailto:pgsql-odbc-owner <at> postgresql.org] On Behalf Of Rob Richardson
Sent: Wednesday, April 25, 2012 10:25 AM
To: pgsql-odbc <at> postgresql.org
Subject: Re: [ODBC] How can I keep an OdbcDataAdapter from using fully qualified tble names?

I want to stick with DSNs because we change databases frequently, mainly on our own systems.  Develop first
using a test database, then do final testing on a copy of the customer's production database, then, if
problems crop up, get an up-to-date copy of the customer's database and debug using that.  That's what DSNs
are for.  They work.  (At least until the .Net world apparently decided it knows better.)

I think you're thinking of a different issue.  I'm not talking about a fully qualified name of a server, with
"mycompany.com" appended to it.  I'm talking about a fully qualified table name, with the database name
and schema prepended to it.

RobR
(Continue reading)

Permalink | Reply |
headers
Rob Richardson | 25 Apr 2012 16:52
Picon
Favicon

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

The issue is that ADO.Net's OdbcDataAdapter object automatically expands the 
table name "MyTable" into "MyDatabase.public.MyTable", and PostgreSQL cannot 
handle that.

In PgAdmin, "insert into cycle (cycle) values ('Another cycle')" succeeds, but 
"insert into Anneal.public.cycle (cycle) values ('Another one')" fails with the 
cross-database references not implemented error.  The OdbcDataAdapter object 
automatically converts a query of the first form into a query of the second 
form, and I want to know how to stop it from doing that.

RobR

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Permalink | Reply |
headers
George Weaver | 25 Apr 2012 17:12
Picon
Favicon

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?


>----- Original Message ----- 
>From: Rob Richardson

>The issue is that ADO.Net's OdbcDataAdapter object automatically expands
>the table name "MyTable" into "MyDatabase.public.MyTable", and PostgreSQL 
>cannot
>handle that.

I think you'll find that it is not ADO.Net's OdbcDataAdapter that expands 
the table name, but
the CommandBuilder - I use ADO Net's OdbcDataAdapter extensively without 
problem,
but I write my own OdbcCommands.

George

>In PgAdmin, "insert into cycle (cycle) values ('Another cycle')" succeeds,
>but "insert into Anneal.public.cycle (cycle) values ('Another one')" fails 
>with
>the cross-database references not implemented error.  The OdbcDataAdapter 
>object
>automatically converts a query of the first form into a query of the second
>form, and I want to know how to stop it from doing that.

>RobR

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
(Continue reading)

Permalink | Reply |
headers
Nils Gösche | 25 Apr 2012 17:02
Picon
Gravatar

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

Rob Richardson wrote:

> In VS 2008, I am trying to use the System.Data.Odbc namespace to work

[...]

> When I use an OdbcCommandBuilder object to create the insert, update
> and delete
> commands for me, the table names are fully qualified.  For example, an
> insert
> command might begin with "insert into MyDatabase.public.MyTable...".
> When I try
> to use that, I get an exception thrown that complains that cross-
> database
> references are not implemented (even though the connection I am using
> is to the MyDatabase database).

I used to have the same problem and gave up on it a long time ago. I
just tried to reproduce it again and...failed!

I suspect it's because we have switched to VS 2010 in the meantime. Could
you perhaps check if your problem goes away, too, if you compile with VS
2010?

Thanks for bringing it up, anyway :-) :-) :-)

Regards,
--

-- 
Nils Gösche
"Don't ask for whom the <CTRL-G> tolls."
(Continue reading)

Permalink | Reply |

Gmane