Обсуждение: PostgreSQL 8.1.4 ODBC for Windows

Hi list.

When will the PostgreSQL 8.1.4 ODBC Drivers be released (with support for the new security patch just released) ? Thanks.

Hi list.

When will the PostgreSQL 8.1.4 ODBC Drivers be released (with support for the new security patch just released) ? Thanks.

Mon, May 29, 2006 at 10:47:24AM +0200, me@alternize.com wrote:
> Re: [ODBC] PostgreSQL 8.1.4 ODBC for Windows8.02.0002 is totaly
> broken and thus unusable here - recordsets with TEXT fields are
> scrambled up.

Is this a bug report? Could you describe it better? How can we reproduce
the problem. Your's mylog output is welcome.
Feel free to use Bug tracker at pgfoundry.org.

Regards,

Luf

sorry, wasn't able to set up a properly test case as we noticed this problem
in our productive system (dev system not yet upgraded) while applying the
pgsql 8.1.x upgrades. i've been reporting this before, tho:

http://archives.postgresql.org/pgsql-odbc/2006-03/msg00232.php

i might try to set up a test case after my holidays. so far as a workaround,
we had to downgrade the driver to the beforementioned version after each
pgsql release. all newer pgodbc releases found in the postgresql.org
download section are nonfunctional for us.

- thomas


----- Original Message -----
From: "Ludek Finstrle" <luf@pzkagis.cz>
To: <me@alternize.com>
Cc: <pgsql-odbc@postgresql.org>
Sent: Monday, May 29, 2006 11:29 AM
Subject: Re: [ODBC] PostgreSQL 8.1.4 ODBC for Windows


> Mon, May 29, 2006 at 10:47:24AM +0200, me@alternize.com wrote:
>> Re: [ODBC] PostgreSQL 8.1.4 ODBC for Windows8.02.0002 is totaly
>> broken and thus unusable here - recordsets with TEXT fields are
>> scrambled up.
>
> Is this a bug report? Could you describe it better? How can we reproduce
> the problem. Your's mylog output is welcome.
> Feel free to use Bug tracker at pgfoundry.org.
>
> Regards,
>
> Luf
>

> sorry, wasn't able to set up a properly test case as we noticed this
> problem in our productive system (dev system not yet upgraded) while
> applying the pgsql 8.1.x upgrades. i've been reporting this before, tho:
>
> http://archives.postgresql.org/pgsql-odbc/2006-03/msg00232.php

I'm sorry but it's information less too. There is only information
about problem with no description.

I see no information about enconding (server, client),
example of select, code to retrieve the data (are you using
VB, VC, ... ADO, OLE DB, ...), ...
I'm unable to find information how is the output deformated.

> i might try to set up a test case after my holidays. so far as a

It would be nice. We're looking forward.

Thanks,

Luf

> I see no information about enconding (server, client),

UNICODE db, UNICODE encoding, unicode driver version

> example of select, code to retrieve the data (are you using
> VB, VC, ... ADO, OLE DB, ...), ...

------------------------------------

Tables:

CREATE TABLE "forum"."posts" (
  "p_id" SERIAL,
  "p_t_id" INTEGER,
  "p_first" BOOLEAN DEFAULT false NOT NULL,
  "p_user" INTEGER DEFAULT 0 NOT NULL,
  "p_msg_formated" TEXT DEFAULT ''::text NOT NULL,
  "p_msg_raw" TEXT DEFAULT ''::text NOT NULL,
  "p_date_add" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
  "p_date" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
  "p_status_deleted" SMALLINT DEFAULT 0 NOT NULL,
  "idxfti" "public"."tsvector",
  "p_msg_clean" TEXT DEFAULT ''::text,
  CONSTRAINT "posts_pkey" PRIMARY KEY("p_id"),
  CONSTRAINT "posts_fk" FOREIGN KEY ("p_t_id")
    REFERENCES "forum"."threads"("t_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "forum"."threads" (
  "t_id" SERIAL,
  "t_b_id" INTEGER,
  "t_status_locked" BOOLEAN DEFAULT false NOT NULL,
  "t_status_deleted" BOOLEAN DEFAULT false NOT NULL,
  "t_status_sticky" BOOLEAN DEFAULT false NOT NULL,
  "t_name" VARCHAR(150) DEFAULT ''::character varying NOT NULL,
  "t_first_post" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
  "t_first_user" INTEGER DEFAULT 0 NOT NULL,
  "t_last_post" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
  "t_last_user" INTEGER,
  "t_stats_posts" INTEGER DEFAULT 0 NOT NULL,
  "t_stats_views" INTEGER DEFAULT 0 NOT NULL,
  "t_special_type" SMALLINT DEFAULT 0 NOT NULL,
  "t_special_id" INTEGER DEFAULT 0 NOT NULL,
  CONSTRAINT "threads_pkey" PRIMARY KEY("t_id"),
  CONSTRAINT "threads_fk" FOREIGN KEY ("t_b_id")
    REFERENCES "forum"."boards"("b_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

(and several indizes / fks)

------------------------------------
ASP:

  Set DB2_Conn = CreateObject("ADODB.Connection")
  DB2_Conn.CommandTimeout = 90
  DB2_Conn.open "Driver={PostgreSQL

UNICODE};Server=localhost;Port=5432;Database=somedb;UID=db_user;pwd=somepassword;TrueIsMinus1=1;BoolsAsChar=0;TextAsLongVarchar=0"
   Set rsRecords = Server.CreateObject("ADODB.Recordset")

  sSQL = "SELECT threads.*, p_id, p_first, p_date, p_status_deleted,
p_msg_formated FROM forum.posts JOIN forum.threads ON posts.p_t_id =
threads.t_id WHERE t_id = " & lTID & " ORDER BY p_first DESC, p_date_add,
p_id"

   rsRecords.Open sSQL, DB2_Conn, 0 ' adOpenForwardOnly
-----------------------------------

> I'm unable to find information how is the output deformated.

p_msg_formated is a TEXT. if its removed, the result is fine. if its
included, the fields are messed up. ie if you try to read "threads.t_date"
(for example: response.write rsRecords("t_date")) you might receive the
content of another field in the same record. this happens to almost any
field in the resultset...

this is just one example query. the problem happens on all queries involving
TEXT fields...

- thomas

> >I see no information about enconding (server, client),
>
> UNICODE db, UNICODE encoding, unicode driver version

I have:

UNICODE db, 08.02.0004 which is unicode driver

I modify tables (a little bit):

> CREATE TABLE "forum"."posts" (
>  "p_id" SERIAL,
>  "p_t_id" INTEGER,
>  "p_first" BOOLEAN DEFAULT false NOT NULL,
>  "p_user" INTEGER DEFAULT 0 NOT NULL,
>  "p_msg_formated" TEXT DEFAULT ''::text NOT NULL,
>  "p_msg_raw" TEXT DEFAULT ''::text NOT NULL,
>  "p_date_add" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
>  "p_date" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
>  "p_status_deleted" SMALLINT DEFAULT 0 NOT NULL,
>  "idxfti" "public"."tsvector",
>  "p_msg_clean" TEXT DEFAULT ''::text,
>  CONSTRAINT "posts_pkey" PRIMARY KEY("p_id"),
>  CONSTRAINT "posts_fk" FOREIGN KEY ("p_t_id")
>    REFERENCES "forum"."threads"("t_id")
>    ON DELETE CASCADE
>    ON UPDATE NO ACTION
>    NOT DEFERRABLE
> ) WITHOUT OIDS;
>
> CREATE TABLE "forum"."threads" (
>  "t_id" SERIAL,
>  "t_b_id" INTEGER,
>  "t_status_locked" BOOLEAN DEFAULT false NOT NULL,
>  "t_status_deleted" BOOLEAN DEFAULT false NOT NULL,
>  "t_status_sticky" BOOLEAN DEFAULT false NOT NULL,
>  "t_name" VARCHAR(150) DEFAULT ''::character varying NOT NULL,
>  "t_first_post" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
>  "t_first_user" INTEGER DEFAULT 0 NOT NULL,
>  "t_last_post" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
>  "t_last_user" INTEGER,
>  "t_stats_posts" INTEGER DEFAULT 0 NOT NULL,
>  "t_stats_views" INTEGER DEFAULT 0 NOT NULL,
>  "t_special_type" SMALLINT DEFAULT 0 NOT NULL,
>  "t_special_id" INTEGER DEFAULT 0 NOT NULL,
>  CONSTRAINT "threads_pkey" PRIMARY KEY("t_id"),

I remove this until end of definition becouse you don't specify the
boards table.

>  CONSTRAINT "threads_fk" FOREIGN KEY ("t_b_id")
>    REFERENCES "forum"."boards"("b_id")
>    ON DELETE CASCADE
>    ON UPDATE NO ACTION
>    NOT DEFERRABLE
> ) WITHOUT OIDS;
>
> (and several indizes / fks)

VBS:
Option Explicit

Const adLockReadOnly = 1
Const adOpenForwardOnly = 0

Dim DB2_Conn
Dim rsRecords
Dim sSQL
Dim Text

Set DB2_Conn = CreateObject("ADODB.Connection")
DB2_Conn.CommandTimeout = 90
DB2_Conn.ConnectionString =
"Driver={PostgreSQL};Server=localhost;Port=5432;Database=test;UID=pgsql;pwd=postgresql;TrueIsMinus1=1;BoolsAsChar=0;TextAsLongVarchar=0"
DB2_Conn.Open

' Commented out or not commented out
'sSQL = "SET CLIENT_ENCODING TO ""UTF-8"""
'rsRecords.Open sSQL, DB2_Conn

Set rsRecords = CreateObject("ADODB.Recordset")

sSQL = "SELECT threads.*, p_id, p_first, p_date, p_status_deleted,p_msg_formated" & vbNewLine & _
       "FROM posts JOIN threads ON posts.p_t_id = threads.t_id" & vbNewLine & _
       "WHERE t_id = 1 ORDER BY p_first DESC, p_date_add, p_id"

rsRecords.Open sSQL, DB2_Conn, adOpenForwardOnly

Do Until rsRecords.EOF
  Text = Text & "Date: " & rsRecords("p_date") & "; Msg: " & rsRecords("p_msg_formated") & vbNewLine
  rsRecords.MoveNext
Loop

MsgBox Text

Set DB2_Conn = Nothing
Set rsRecords = Nothing

I'm unable to reproduce the error :-( Are you sure you use 08.02.0002 or
later psqlodbc driver? There is no ANSI x UNICODE version of the driver.

Could you try this VBS? Or could you specify one or two rows which
leads to error? Maybe length of text data could help ...

Regards,

Luf