It is currently Tue Mar 09, 2021 8:32 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: DatabaseColumnType() Reporting Wrong Type in Postgres
PostPosted: Mon Dec 07, 2020 3:15 pm 
Offline
Enthusiast
Enthusiast

Joined: Thu May 21, 2009 6:56 pm
Posts: 600
Hi

I used the following code to log the column types of a Postgres table. As you can see from my results the column types returned by PB are almost all "Long" whereas most of the fields are actually strings. The "character varying" worked correctly but the "Char(n)" field did not report the correct column type. I am using PB ver 5.73 on Windows 2019 Server with PostgreSQL 12.3, compiled by Visual C++ build 1914, 64-bit. If I run the same query in pgAdmin4 I get the expected column types. I ran the same test on Window Pro 10 with PB 5.72 and Postgres 12.3 with the same results.

Code:
UsePostgreSQLDatabase()
If OpenDatabase(0,"host=127.0.0.1 port=5432 dbname=FusionPro","****","****",#PB_Database_PostgreSQL)
  If DatabaseQuery(0,~"select cltrn.* from test.cltrn where cltrn.\"id\"='UbZ2oEZQAAA9'")
    If NextDatabaseRow(0)
      For ln=0 To DatabaseColumns(0)
        Select DatabaseColumnType(0,ln)
          Case #PB_Database_Long
            Debug DatabaseColumnName(0,ln)+" Long"
          Case #PB_Database_String
            Debug DatabaseColumnName(0,ln)+" String"
          Case #PB_Database_Float
            Debug DatabaseColumnName(0,ln)+" Float"
          Case#PB_Database_Double
            Debug DatabaseColumnName(0,ln)+" Double"
          Case#PB_Database_Quad
            Debug DatabaseColumnName(0,ln)+" Quad"
          Case#PB_Database_Blob
            Debug DatabaseColumnName(0,ln)+" Blob"
        EndSelect
      Next
    EndIf
    FinishDatabaseQuery(0)
  EndIf
EndIf

Quote:
; Results for the above code copied from the Debug Window
;========================================================
; authby Long
; authno Long
; bulk Long
; cardno1 Long
; cardno2 Long
; cctype Long
; clperiod Long
; contract Long
; date Long
; dept Long
; desc Long
; disc Long
; errcode Long
; fueltype2 Long
; gstinprice Long
; hose Long
; host Long
; id Long
; managing Long
; manual Long
; meterreadv Long
; note String
; owner Long
; pid Long
; pid2 Long
; pmpno Long
; price Long
; price2 Long
; product Long
; qty Long
; seqno Long
; site Long
; status_e Long
; status_i Long
; status_r Long
; test Long
; thru Long
; time Long
; total Long
; total2 Long
; track2 String
; trnno Long
; vchno Long
; vehicle Long
; _chngd1 Long
; _updated Long
; _updatedby Long
; ladingno Long
; promotion Long
; qtyg Long
; qtym Long
; temp Long
; tid String
; Long
;=====================================================

; Structure of the table copied from pgAdmin4
;=====================================================
; CREATE TABLE test.cltrn
; (
; authby character(1) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; authno character(20) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; bulk smallint NOT NULL DEFAULT 0,
; cardno1 character(19) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; cardno2 character(19) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; cctype character(1) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; clperiod character(6) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; contract smallint NOT NULL DEFAULT 0,
; date character(8) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; dept character(2) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; "desc" character(9) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; disc numeric(6,4) NOT NULL DEFAULT 0,
; errcode character(6) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; fueltype2 character(2) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; gstinprice smallint NOT NULL DEFAULT 0,
; hose character(1) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; host smallint NOT NULL DEFAULT 0,
; id character(12) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; managing numeric(7,5) NOT NULL DEFAULT 0,
; manual smallint NOT NULL DEFAULT 0,
; meterreadv numeric(16,3) NOT NULL DEFAULT 0,
; note character varying(30) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
; owner character(12) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; pid character(12) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; pid2 character(12) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; pmpno character(2) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; price numeric(7,4) NOT NULL DEFAULT 0,
; price2 numeric(7,4) NOT NULL DEFAULT 0,
; product character(12) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; qty numeric(10,3) NOT NULL DEFAULT 0,
; seqno character(3) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; site character(12) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; status_e integer NOT NULL DEFAULT 0,
; status_i integer NOT NULL DEFAULT 0,
; status_r integer NOT NULL DEFAULT 0,
; test smallint NOT NULL DEFAULT 0,
; thru numeric(6,4) NOT NULL DEFAULT 0,
; "time" character(5) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; total numeric(10,2) NOT NULL DEFAULT 0,
; total2 numeric(10,2) NOT NULL DEFAULT 0,
; track2 character varying(40) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
; trnno character(6) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; vchno character(6) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; vehicle character(9) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; _chngd1 integer NOT NULL DEFAULT 0,
; _updated character(14) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; _updatedby character(10) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; ladingno character(6) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; promotion character(3) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
; qtyg numeric(10,3) NOT NULL DEFAULT 0,
; qtym numeric(10,3) NOT NULL DEFAULT 0,
; temp numeric(5,1) NOT NULL DEFAULT 0,
; tid character varying(20) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
; CONSTRAINT cltrn_pkey PRIMARY KEY (id),
; CONSTRAINT cltrn_duplicate UNIQUE (site, clperiod, trnno, product, qty)
; )
;
;=================================================================

_________________
Simon White
dCipher Computing

_________________
Simon White
dCipher Computing


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnType() Reporting Wrong Type in Postgres
PostPosted: Mon Dec 07, 2020 6:48 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Sep 22, 2010 1:17 pm
Posts: 382
Location: United Kingdom
Also confirmed with 5.73 and a linux hosted PostgreSQL 9.5 server.


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnType() Reporting Wrong Type in Postgres
PostPosted: Wed Dec 09, 2020 7:06 pm 
Offline
Enthusiast
Enthusiast

Joined: Thu May 21, 2009 6:56 pm
Posts: 600
Hi

Is there a workaround for this bug while I wait for a fix?

Thanks,
Simon

_________________
Simon White
dCipher Computing


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnType() Reporting Wrong Type in Postgres
PostPosted: Wed Dec 09, 2020 9:49 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Sep 22, 2010 1:17 pm
Posts: 382
Location: United Kingdom
Code:
SELECT data_type FROM information_schema.columns WHERE table_name = 'X' AND column_name = 'Y';

The return type is a string, so you will need to convert that to the values you actually want. If your server has multiple databases and/or ambiguous column/table names, you may need to differentiate on table_catalog and table_schema in the WHERE clause too.


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnType() Reporting Wrong Type in Postgres
PostPosted: Thu Jan 14, 2021 5:00 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Jul 15, 2020 7:10 am
Posts: 106
I have the same problem with MySQL and 5.73 Beta 3 (5.73 doesn't work with MySQL, see other post from me).


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 5 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye