[Gambas-user] Problem with Postgresql

[rolf]

Hi Patrick

> >Hi Ron
> >
> >>>- CODE:
> >>>  query1 = "alter table _" & TextBox.Text & " add column id serial not
> >>>null"   res1 = hDB.Exec(quer<1)
> >>>
> >>>if a primary key should be set with a code like:
> >>>    query1 = "ALTER TABLE ONLY _" & TextBox.Text &
> >>>                     " ADD CONSTRAINT X" & TextBox.Text &
> >>>                     "_pkey PRIMARY KEY (id)"
> >>>   res1 = hDB.Exec(query1)
> >>
> >>What are those underscores?
> >
> >They are used, because the tablename is a number (of a bank account). If I
> > use pure numbered tablenames they always must be double quoted with mouch
> > more trouble.
> >
> >>In example on postgres site: 'ALTER TABLE table_name ADD'
> >
> >All the comands work in the psql-shell without any error! I printed out
> > the commands in a file and passed this file as input to psql and again
> > all commands will execute as expected without any error.
> >
> >>Leave those underscore away or do you have really a table starting with
> >> '_' ?
> >>
> >>" ADD CONSTRAINT X" & TextBox.Text & "_pkey PRIMARY KEY (id)"
> >>This looks as 'CONSTRAINT Xmyname_pkey PRIMARY' are you sure this
> >> correct?
> >>
> >>Maybe also case sensitive can be a problem.
> >
> >Postgres is case sensitive in table, column and database names, not in sql
> >commands.
> >
> >I found a discussion on the postgres mailing list about this problem -
> > with version 8.1.1 - I use 8.1.4 - but in spanish, which I don't
> > understand.
> >
> >Because all the trouble making sql commands work in the psql-shell I am
> > not shure, if gambas is linked against the right libraries or uses the
> > right function calls. I recompiled gambas (using 1.9.31 because the
> > documentation is still usable) and will test it.
> >
> >Many thanks for your help.
> >Rolf
> >
>
> Wouldn't it be better to use one and only one table
> for all the bank accounts,
> and add the account number to the identifiant of that table?

I did it this way in a previous version, but it seems more clear to do it in 
the new way and it is more easy to handle the data entries.

The problem is not the name of the table but how to add an autoincrement field 
as a primary key from within gambas.

I solved the problem by generating a file with the SQL statements and a shell 
script which send the file to the database and execute this shell skript with 
a "SHELL" command from within gambas.

> Cordialement

Thank you for your help
Rolf