[Gambas-user] PostgreSQL, how to create a new database

Gianluigi bagonergi at gmail.com
Mon Sep 2 18:31:48 CEST 2019


Hi guys,

I solved thanks to the suggestions of Berserker79 of the Italian forum [0].

I restored template1 thanks to this guide [1]

and I changed the connection string restoring the commented code.

  $hConn.Close()
  $hConn.Type = Lower(DBType)
  $hConn.Host = DBHost
  $hConn.Name = DBName
  $hConn.Login = UserName
  $hConn.Password = UserPassword
  $hConn.Port = "5432"

and now all is OK.

Regards
Gianluigi

[0] https://www.gambas-it.org/smf/index.php?topic=7253.msg46682#msg46682
[1] https://wiki.postgresql.org/wiki/Adventures_in_PostgreSQL,_Episode_1

Il giorno lun 2 set 2019 alle ore 15:22 Gianluigi <bagonergi at gmail.com> ha
scritto:

> Hi everyone,
>
> I was creating a postgresql test database to verify the steps.
> Unfortunately I must have done something wrong because at the final
> verification I get these results, which means that, if I understand
> correctly, instead of having populated the new "rubinettofelice" database
> with tables, I directly populated the template1.
>
> This is how much is returned to me from the terminal:
>
> =======================================================================
> ~$ psql -U postgres
> Password for user postgres:
> psql (11.5 (Ubuntu 11.5-0ubuntu0.19.04.1))
> Type "help" for help.
>
> postgres=# \l
>
>       Name       |   Owner   | Encoding |   Collate   |    Ctype    |
> Access privileges
>
> -----------------+-----------+----------+-------------+-------------+-----------------------
>  postgres        | postgres  | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 |
>  rubinettofelice | gianluigi | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 |
>  template0       | postgres  | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 |
> =c/postgres          +
>                  |           |          |             |             |
> postgres=CTc/postgres
>  template1       | postgres  | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 |
> =c/postgres          +
>                  |           |          |             |             |
> postgres=CTc/postgres
> (4 rows)
>
> postgres=# \du
>                                    List of roles
>  Role name |                         Attributes                         |
> Member of
>
> -----------+------------------------------------------------------------+-----------
>  gianluigi | Create role, Create DB                                     |
> {}
>  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS |
> {}
>
> postgres=# \c rubinettofelice
> You are now connected to database "rubinettofelice" as user "postgres".
> rubinettofelice=# \d
> Did not find any relations.
>  <<========================================SIGH!
>
> rubinettofelice=# \dn
>   List of schemas
>   Name  |  Owner
> --------+----------
>  public | postgres
> (1 row)
>
> rubinettofelice=# \c template1
> You are now connected to database "template1" as user "postgres".
> template1=# \d
>                  List of relations                     <<================
> ???
>  Schema |       Name        |   Type   |   Owner
> --------+-------------------+----------+-----------
>  public | fipart            | table    | gianluigi
>  public | tagent            | table    | gianluigi
>  public | tagent_idagen_seq | sequence | gianluigi
>  public | tfilms            | table    | gianluigi
>  public | tfilms_idfilm_seq | sequence | gianluigi
>  public | tst_fi            | table    | gianluigi
>  public | tstars            | table    | gianluigi
>  public | tstars_idstar_seq | sequence | gianluigi
> (8 rows)
> =======================================================================
>
> I created the database from the terminal with these steps.
>
> 1 - First I created the user:
>
> =======================================================================
> ~$ psql -U postgres -d template1
> Password for user postgres:
> psql sql (11.5 (Ubuntu 11.5-0ubuntu0.19.04.1))
> Type "help" for help.
>
> template1=# CREATE ROLE gianluigi WITH CREATEDB CREATEROLE LOGIN ENCRYPTED
> PASSWORD 'miapassword';
> CREATE ROLE
> =======================================================================
>
> 2 - Then I created the new "rubinettofelice" database:
>
> =======================================================================
> ~$ psql -U test -d template1
> Password for user test:
> psql sql (11.5 (Ubuntu 11.5-0ubuntu0.19.04.1))
> Type "help" for help.
>
> template1=> CREATE DATABASE rubinettofelice WITH OWNER=gianluigi TEMPLATE=
> template1 ENCODING='utf-8';
> CREATE DATABASE
> =======================================================================
>
> 3 - At this point he created a Gambas project with a module (MBase.module):
>
> =======================================================================
> ' Gambas module file
>
>
> Private $hConn As New Connection
>
> Public Sub OpenDB(DBType As String, DBHost As String, DBName As String,
> UserName As String, UserPassword As String)
>
>   $hConn.Close()
>   $hConn.Type = Lower(DBType)
>   $hConn.Host = DBHost
>   '$hConn.Name = ""
>   $hConn.Login = UserName
>   $hConn.Password = UserPassword
>   '$hConn.Port = "5432"
>
>   $hConn.Open()
>
>   If $hConn.Databases.Exist(DBName) Then
>     MakeTable()
>   Else
>     Error.Raise(("Database not found.\n\nAre you sure that the database
> exist?"))
>   Endif
>
>   $hConn.Close()
> Catch
>   Message.Error(Error.Text & "\n\n" & Error.Where)
>   FMain.Close()
>
> End
>
> Public Sub CloseDB()
>
>   $hConn.Close()
>
> End
>
> Private Sub MakeTable()
>
>   Dim hTable As Table
>
>   $hConn.Begin
>
>   If Not $hConn.Tables.Exist("tstars") Then
>     hTable = $hConn.Tables.Add("tstars")
>
>     hTable.Fields.Add("idstar", db.Serial)
>     hTable.Fields.Add("stname", db.String, 40)
>     hTable.Fields.Add("stsurn", db.String, 40)
>     hTable.Fields.Add("stheig", db.Integer)
>     hTable.Fields.Add("stweig", db.Integer)
>     hTable.Fields.Add("stbirt", db.Date)
>     hTable.Fields.Add("sttele", db.String, 13)
>     hTable.Fields.Add("idagen", db.integer)
>     hTable.Fields.Add("stimag", db.Blob)
>     hTable.PrimaryKey = ["idstar"]
>     hTable.Update
>
>   Endif
>
>   If Not $hConn.Tables.Exist("tagent") Then
>     hTable = $hConn.Tables.Add("tagent")
>
>     hTable.Fields.Add("idagen", db.Serial)
>     hTable.Fields.Add("agname", db.String, 40)
>     hTable.Fields.Add("agsurn", db.String, 40)
>     hTable.Fields.Add("agaddr", db.string, 80)
>     hTable.Fields.Add("agcity", db.string, 40)
>     hTable.Fields.Add("agstat", db.string, 40)
>     hTable.Fields.Add("agcoun", db.string, 40)
>     hTable.Fields.Add("agpost", db.string, 10)
>     hTable.Fields.Add("agtele", db.String, 13)
>     hTable.Fields.Add("agmail", db.String, 30)
>     hTable.Fields.Add("agwebs", db.String, 30)
>     hTable.PrimaryKey = ["idagen"]
>     hTable.Update
>   Endif
>
>   If Not $hConn.Tables.Exist("tfilms") Then
>     hTable = $hConn.Tables.Add("tfilms")
>
>     hTable.Fields.Add("idfilm", db.Serial)
>     hTable.Fields.Add("finame", db.String, 100)
>     hTable.Fields.Add("fiyear", db.Date)
>     hTable.Fields.Add("fiprod", db.string, 100)
>     hTable.Fields.Add("fiboxo", db.Float)
>     hTable.PrimaryKey = ["idfilm"]
>     hTable.Update
>   Endif
>
>   If Not $hConn.Tables.Exist("tst_fi") Then
>     hTable = $hConn.Tables.Add("tst_fi")
>
>     hTable.Fields.Add("idstar", db.Integer)
>     hTable.Fields.Add("idfilm", db.Integer)
>     hTable.Fields.Add("sfpart", db.String, 20, "ND")
>     'hTable.Indexes.Add("x_stfi", ["idstar", "idfilm"])
>     hTable.Update
>   Endif
>
>   If Not $hConn.Tables.Exist("fipart") Then
>     hTable = $hConn.Tables.Add("fipart")
>
>     hTable.Fields.Add("paname", db.String, 10)
>     hTable.Update
>   Endif
>
>   $hConn.Commit
> Catch
>   $hConn.Rollback
>   CloseDB()
>   Debug Error.Where, Error.Text, Error.Code
>
> End
> =======================================================================
>
> 4 - In FMain I open a connection window that creates something similar and
> starts the module code:
>
> MBase.OpenDB("postgresql", "localhost", "rubinettofelice", "gianluigi",
> "miapassword")
>
> Can anyone help me figure out where I made the mistakes?
>
> Regards
> Gianluigi
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.gambas-basic.org/pipermail/user/attachments/20190902/5cf5318a/attachment-0001.html>


More information about the User mailing list