Postgres and PB issue

Just starting out? Need help? Post your questions and find answers here.
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Postgres and PB issue

Post by captain_skank »

Has anybody else had any problems with interacting with postgres ?

Specificaly with the DatabaseColumnSize() command.

Works fine when using with MySQL - returns the field size as expected, but with postgres always returns 0.

Oh and i'm using PB 5.62

Cheers
User avatar
blueb
Addict
Addict
Posts: 1041
Joined: Sat Apr 26, 2003 2:15 pm
Location: Cuernavaca, Mexico

Re: Postgres and PB issue

Post by blueb »

Hmm.. just tested on 5.46 (non-unicode) and 5.62 (x86 and x64) and got the same results as you. :(

Test Code:

Code: Select all

UsePostgreSQLDatabase()

If OpenDatabase(0, "host=localhost port=5432", "postgres", "mypassword",#PB_Database_PostgreSQL)
     Debug "Connected to PostgreSQL"
Else
     Debug "Connection failed: "+ DatabaseError()
EndIf


If OpenWindow(100, 450, 200, 400, 400, "Window_0", #PB_Window_SystemMenu|#PB_Window_SizeGadget|#PB_Window_MinimizeGadget|#PB_Window_TitleBar)
     
     listicon_row_data.s = ""
     
     If DatabaseQuery(0, "SELECT * FROM customer")
          ; iterate through the columns and retrieve column data
          For loopcount = 0 To (DatabaseColumns(0) - 1)
               column_type.s
               Select DatabaseColumnType(0, loopcount)
                    Case #PB_Database_Long
                         column_type = "LONG"
                    Case #PB_Database_String
                         column_type = "STRING"
                    Case #PB_Database_Float
                         column_type = "FLOAT"
                    Case #PB_Database_Double
                         column_type = "DOUBLE"
                    Case #PB_Database_Quad
                         column_type = "QUAD"
                    Case #PB_Database_Blob
                         column_type = "BLOB"
               EndSelect
               
               column_size = DatabaseColumnSize(0, loopcount)
              
               
               ; now create / update listicon with column headings
               If loopcount = 0
                    table.i = ListIconGadget(#PB_Any, 10, 10, 375, 380, DatabaseColumnName(0,loopcount), 100, #PB_ListIcon_AlwaysShowSelection|#PB_ListIcon_GridLines|#PB_ListIcon_FullRowSelect)
                    Debug DatabaseColumnName(0,loopcount) + ", " + column_type + ", " + Str(column_size)
               Else
                    AddGadgetColumn(table, loopcount, DatabaseColumnName(0,loopcount), 100)
                    Debug DatabaseColumnName(0,loopcount) + ", " + column_type + ", " + Str(column_size)
                    
               EndIf
          Next
          
          ;populate the listicon
          While NextDatabaseRow(0)
               For loopcount = 0 To DatabaseColumns(0)
                    If loopcount = 0
                         listicon_row_data = GetDatabaseString(0, loopcount)
                    Else
                         listicon_row_data = listicon_row_data + Chr(10) + GetDatabaseString(0, loopcount)
                    EndIf
               Next
               AddGadgetItem(table, -1, listicon_row_data)
          Wend
     EndIf
     
EndIf

Repeat : Until WaitWindowEvent() = #PB_Event_CloseWindow
CloseDatabase(0)
End
- It was too lonely at the top.

System : PB 6.10 Beta 9 (x64) and Win Pro 11 (x64)
Hardware: AMD Ryzen 9 5900X w/64 gigs Ram, AMD RX 6950 XT Graphics w/16gigs Mem
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Postgres and PB issue

Post by captain_skank »

Thanks for that Blueb - least i know it's not just me :)

Damn another morning wasted :evil: means i'm gonna have to stick with ODBC and mysql now :evil: :evil: :evil:
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: Postgres and PB issue

Post by Marc56us »

Work fine for me (tested on a string colums)

PB 5.62 x64 - PostgreSQL 10.3 (Windows)
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Postgres and PB issue

Post by captain_skank »

Okay - now tested with PB 5.62 x32 and x64 with the same non-result.

Postgres version is 10.3 running localy on windows 10 at the mo.
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: Postgres and PB issue

Post by Marc56us »

Test this
(adapt host, port, database name, username, pass)

Code: Select all

EnableExplicit

UsePostgreSQLDatabase()

Enumeration 
    #DBcnx
EndEnumeration


If OpenDatabase(#DBcnx, "host=localhost port=5432 dbname=postgres", "postgres", "postgres")
    Debug ~"OK Connected.\n"
Else
    Debug "Connection failed: " + DatabaseError()
EndIf

; --- CREATE 
Define SQL$
SQL$ = "DROP TABLE IF EXISTS Test_Hello;" + 
       "CREATE TABLE Test_Hello (Hello_Row TEXT);" +
       "INSERT INTO Test_Hello (Hello_Row) VALUES ('Hello World');" + 
       "INSERT INTO Test_Hello (Hello_Row) VALUES ('It is a good day to code in PB');"  + 
       "INSERT INTO Test_Hello (Hello_Row) VALUES ('PB is the best product language! think simple, think PB');" 

If Not DatabaseQuery(#DBcnx, SQL$)
    Debug "** CREATE or INSERT KO :-( SQL: " + SQL$ + " (" + DatabaseError() + ")"
    End
EndIf


; --- SELECT
SQL$ = "SELECT * FROM Test_Hello";

If Not DatabaseQuery(#DBcnx, SQL$)
    Debug "** Query KO :-( SQL: " + SQL$ + " (" + DatabaseError() + ")"
    End
EndIf

Define i
While NextDatabaseRow(#DBcnx)
    i + 1
    Debug "#" + Str(i) + 
          " - (" + RSet(Str(DatabaseColumnSize(#DBcnx, 0)), 2, " ") + " chars) "  +
          GetDatabaseString(#DBcnx, 0)
Wend

CloseDatabase(#DBcnx)
Debug ~"\nReady."
End
Result (if OK)

Code: Select all

OK Connected.

#1 - (11 chars) Hello World
#2 - (30 chars) It is a good day to code in PB
#3 - (55 chars) PB is the best product language! think simple, think PB

Ready.
:wink:
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Postgres and PB issue

Post by captain_skank »

Thanks for that Mar56us.

Ran that - get the same results you listed -does that mean my db is incorrectly configured ?

Cheers
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Postgres and PB issue

Post by captain_skank »

Forget that that - id assumed it would return the size of the database field not the size of that actual data contained therin.

In my own mind this made sense cos all the other commands return general table data not actual field data.

Or again am i misunderstaing this ?

Thanks for all the help
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: Postgres and PB issue

Post by Marc56us »

Yes, the first sentence of the help is ambiguous, but the second clearly specifies the role of this function and what it counts.

"Return the size of the specified column in the #Database.
It is especially useful when the size of the column can change depending of the records, like a blob or string column.
"

:wink:
Post Reply