headers
Dmitry Samokhin | 1 Aug 2007 11:56
Picon

Unable to update JOIN'ed recordset with PSQLODBC 08.02.0400 and ADO

In the following environment:
WinXP SP2 + ADO + MS OLE DB Provider for ODBC drivers + PSQLODBC ANSI 
08.02.0400

I'm unable to update a recordset of JOIN'ed tables. The method 
'rs("colname") = <new value>' started to fail with 08.02.0400, but it works 
fine with 08.02.0200. Something wrong with tables and columns metadata ADO 
gets from the driver, I suppose.
My test suite included. Please note only columns from table t1 are loaded 
into the recordset, JOIN is used just for sorting rows.

Test suite:

Server side:
------------

CREATE TABLE t1
(
  a integer NOT NULL,
  b integer,
  x character varying(255),
  CONSTRAINT pk_t1 PRIMARY KEY (a)
)
WITHOUT OIDS;

CREATE TABLE t2
(
  b integer NOT NULL,
  c integer,
  CONSTRAINT pk_t2 PRIMARY KEY (b)
(Continue reading)

Permalink | Reply | 3 comments |
headers
Paul Lambert | 3 Aug 2007 03:45
Picon

Strange ODBC behavior.

I had a server have a power outage on one of my PostgreSQL servers 
yesterday and am having a strange ODBC issue now - not sure if they are 
related occurances though.

I have a table called billing_code and one called models in my database. 
If I connect via psql to the database with my standard db user I can see 
both of these tables as well as the data contained within.

If I attempt to link these tables from MS Access, the billing_code one 
returns "The Microsoft Jet database engine could not find the object 
'public.billing_code'. Make sure the object exists and that you spell 
its name and the path name correctly" - this error despite the fact that 
I had to physically select the table from a list it presented me when I 
went into the linked table manager.

If I link the models table, it links fine, but when I attempt to open 
it, I just get #Deleted in every column of every row for about 54,000 
rows (the total number in the table)

The same problem happens with some of the other tables in the database - 
some will get the first problem, some will get the second problem, and 
some will link and load data without issue.

I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in 
case there was a problem with the driver getting corrupted but the 
problem persists.

Can anyone offer any thoughts or suggestions on what the problem might be?

Thanks in advance,
(Continue reading)

Permalink | Reply | 7 comments |
headers
Paul Lambert | 3 Aug 2007 10:38
Picon

Re: Strange ODBC behavior.

Paul Lambert wrote:
> I had a server have a power outage on one of my PostgreSQL servers 
> yesterday and am having a strange ODBC issue now - not sure if they are 
> related occurances though.
> 
> I have a table called billing_code and one called models in my database. 
> If I connect via psql to the database with my standard db user I can see 
> both of these tables as well as the data contained within.
> 
> If I attempt to link these tables from MS Access, the billing_code one 
> returns "The Microsoft Jet database engine could not find the object 
> 'public.billing_code'. Make sure the object exists and that you spell 
> its name and the path name correctly" - this error despite the fact that 
> I had to physically select the table from a list it presented me when I 
> went into the linked table manager.
> 
> If I link the models table, it links fine, but when I attempt to open 
> it, I just get #Deleted in every column of every row for about 54,000 
> rows (the total number in the table)
> 
> The same problem happens with some of the other tables in the database - 
> some will get the first problem, some will get the second problem, and 
> some will link and load data without issue.
> 
> I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in 
> case there was a problem with the driver getting corrupted but the 
> problem persists.
> 
> Can anyone offer any thoughts or suggestions on what the problem might be?
>
(Continue reading)

Permalink | Reply | 6 comments |
headers
Magnus Hagander | 3 Aug 2007 12:47

Re: patch win32.mak of libpq

On Fri, Jul 27, 2007 at 10:01:06PM +0200, Magnus Hagander wrote:
> Hiroshi Saito wrote:
> >>>> Ok. So there are actually two ways to go about it:
> >>>> 1) Discontinue support for MSVC6 and require MSVC8
> >>>>
> >>>> 2) Change it so that MSVC6 can still build libpq, just not with SSPI
> >>>> support. This can be done by conditionally enabling ENABLE_SSPI, so
> >>>> it's
> >>>> not that hard.
> >>>>
> >>>> The question is, if we go with option 2, is it something that anybody
> >>>> actually will *use*?
> >>>
> >>> I desire 1 as formal. However, It contains VC7.1 and VC8.
> >>> Moreover, libpq.dll can be used by the module of VC6.
> >>
> >> Is there any actual reason to keep VC7.1 support?
> > 
> > It is still used and has sufficient function. Then, Inoue-san is
> > developing in the environment.:-)
> > The project file of VC7.1 differs from VC8 a little. However, nmake.exe
> > absorbs it.
> > for the reasons, we are maintaining win32.mak. but, project file offers
> > the minimum function
> > in simple. MSDTC is a reason for being somewhat more complicated than
> > standard compile.
> 
> Ok. Just to be clear, do you need MSVC7.1 support, or do you need
> win32.mak/nmake support? I realize they both work here, but if we
> changed something else that needed MSVC8 but maintained the win32.mak
(Continue reading)

Permalink | Reply | 0 comments |
headers
Hiroshi Saito | 3 Aug 2007 13:22
Picon

Re: [ODBC] patch win32.mak of libpq

Hi Magnus.

I'm sorry lateness of a reaction..
Thanks!

P.S)
Inoue-san is busy and worsens condition.

Regards,
Hiroshi Saito

>> Ok. Just to be clear, do you need MSVC7.1 support, or do you need
>> win32.mak/nmake support? I realize they both work here, but if we
>> changed something else that needed MSVC8 but maintained the win32.mak
>> file, would that be enough?
> 
> Since I've had no further feedback, I've applied a patch that changes the
> list of supported MSVC versions to 7.1-8.0, but keeping the win32.mak
> files.
> 
> //Magnus

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Permalink | Reply | 0 comments |
headers
Andrei Kovalevski | 3 Aug 2007 15:47
Favicon

Re: Strange ODBC behavior.

Paul Lambert wrote:
> Paul Lambert wrote:
>> I had a server have a power outage on one of my PostgreSQL servers 
>> yesterday and am having a strange ODBC issue now - not sure if they 
>> are related occurances though.
>>
>> I have a table called billing_code and one called models in my 
>> database. If I connect via psql to the database with my standard db 
>> user I can see both of these tables as well as the data contained 
>> within.
>>
>> If I attempt to link these tables from MS Access, the billing_code 
>> one returns "The Microsoft Jet database engine could not find the 
>> object 'public.billing_code'. Make sure the object exists and that 
>> you spell its name and the path name correctly" - this error despite 
>> the fact that I had to physically select the table from a list it 
>> presented me when I went into the linked table manager.
>>
>> If I link the models table, it links fine, but when I attempt to open 
>> it, I just get #Deleted in every column of every row for about 54,000 
>> rows (the total number in the table)
>>
>> The same problem happens with some of the other tables in the 
>> database - some will get the first problem, some will get the second 
>> problem, and some will link and load data without issue.
>>
>> I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in 
>> case there was a problem with the driver getting corrupted but the 
>> problem persists.
>>
(Continue reading)

Permalink | Reply | 3 comments |
headers
Adrian Klaver | 3 Aug 2007 16:21
Picon

Re: Strange ODBC behavior.

On Friday 03 August 2007 1:38 am, Paul Lambert wrote:
> Paul Lambert wrote:
> > I had a server have a power outage on one of my PostgreSQL servers
> > yesterday and am having a strange ODBC issue now - not sure if they are
> > related occurances though.
> >
> > I have a table called billing_code and one called models in my database.
> > If I connect via psql to the database with my standard db user I can see
> > both of these tables as well as the data contained within.
> >
> > If I attempt to link these tables from MS Access, the billing_code one
> > returns "The Microsoft Jet database engine could not find the object
> > 'public.billing_code'. Make sure the object exists and that you spell
> > its name and the path name correctly" - this error despite the fact that
> > I had to physically select the table from a list it presented me when I
> > went into the linked table manager.
> >
> > If I link the models table, it links fine, but when I attempt to open
> > it, I just get #Deleted in every column of every row for about 54,000
> > rows (the total number in the table)
> >
> > The same problem happens with some of the other tables in the database -
> > some will get the first problem, some will get the second problem, and
> > some will link and load data without issue.
> >
> > I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in
> > case there was a problem with the driver getting corrupted but the
> > problem persists.
> >
> > Can anyone offer any thoughts or suggestions on what the problem might
(Continue reading)

Permalink | Reply | 0 comments |
headers
Paul Lambert | 6 Aug 2007 00:26
Picon

Re: Strange ODBC behavior.

Andrei Kovalevski wrote:

>> Any thoughts on this or is it more of an access issue?
> 1) Could you post here CREATE script for one of the tables with problem 
> you descrived?  #delete message appears when something happens to the 
> primary keys.
> 2) What is the version of your MS Access? PostgreSQL? Unicode or ANSI 
> driver version?
> 3) Please, have a look on the linked table structure as it's recognised 
> by MS Access. And post it here.
> 
> 
> Andrei.
> 
> 
> 

I believe I have found the problem.

The primary key fields are defined in PG as type text, but MS access is 
bringing them across as type "memo" - whatever that is.

It seems to be bringing most text type fields across like this.

I changed them to varchar on one of the tables I'm having a problem 
with, relinked and they are coming across with a type of text on the 
fields now and I can see the data without issue.

Strange... has anyone see that before?
(Continue reading)

Permalink | Reply | 2 comments |
headers
Adrian Klaver | 6 Aug 2007 01:41
Picon

Re: Strange ODBC behavior.

On Sunday 05 August 2007 3:26 pm, Paul Lambert wrote:
> Andrei Kovalevski wrote:
> >> Any thoughts on this or is it more of an access issue?
> >
> > 1) Could you post here CREATE script for one of the tables with problem
> > you descrived?  #delete message appears when something happens to the
> > primary keys.
> > 2) What is the version of your MS Access? PostgreSQL? Unicode or ANSI
> > driver version?
> > 3) Please, have a look on the linked table structure as it's recognised
> > by MS Access. And post it here.
> >
> >
> > Andrei.
>
> I believe I have found the problem.
>
> The primary key fields are defined in PG as type text, but MS access is
> bringing them across as type "memo" - whatever that is.
>
> It seems to be bringing most text type fields across like this.
>
> I changed them to varchar on one of the tables I'm having a problem
> with, relinked and they are coming across with a type of text on the
> fields now and I can see the data without issue.
>
> Strange... has anyone see that before?
Actually this has been noted before. Search Google or psql-odbc archives for 
references. There are some changes you can make to the ODBC settings to help. 
Unfortunately, I cannot remember them off the top of my head. I only bring
(Continue reading)

Permalink | Reply | 1 comment |
headers
Paul Lambert | 6 Aug 2007 01:54
Picon

Re: Strange ODBC behavior.

Adrian Klaver wrote:

> Actually this has been noted before. Search Google or psql-odbc archives for 
> references. There are some changes you can make to the ODBC settings to help. 
> Unfortunately, I cannot remember them off the top of my head. I only bring 
> this up because I saw your post on the general list about changing the type 
> of 600  text columns. You might find it easier to change the behavior of the 
> ODBC driver.

I should have thought about checking the ODBC settings before.

There is a setting called "Text as LongVarChar" - I've unticked that and 
now text fields are coming through as text.

Thanks for the pointers.

--

-- 
Paul Lambert
Database Administrator
AutoLedgers

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Permalink | Reply | 0 comments |

Gmane