Re: PostgreSQL 8.1.4 ODBC for Windows

[<me(at)alternize(dot)com>]

> 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