Re: PostgreSQL 8.1.4 ODBC for Windows - Mailing list pgsql-odbc

> 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