Variable Table Name in PostgreSQL

1

I am working with a system that connects to both a Firebird DB and a Postresql database.

I need a Postgres table to have a variable name, so in Full Pathname of the DCT I have placed a variable !GLO:Name_of_table and then I assign it the name that the table has in the engine (this is because there are several identical tables in structure but with names different, example, clients_01, clients_02, clients_03, etc…

If in the full Path name I put the name of one of them, for example, public.clientes_01, the table opens and works perfectly. But if I set the variable and assign the name later, it tells me that the file name is invalid.

If I do exactly the same with a Firebird DB table it works perfectly, it just doesn’t allow it with the Postgres one.

2

Greetings -

Not much to go on. You say it works in Firebird and not in PostgreSQL. And that it has a !GLO:Name_of_Table. Some screen captures of the file definitions for both the Firebird and PostgreSQL backends from the Clarion Side might help. Maybe the DDL definitions from the one that does not work correctly. Maybe some code.

If you are using the name public.clientes_01 in the PostgreSQL global name in Clarion what function are you getting the error message from?

Are you using the creation attribute in the Clarion file definition?

I will assume that “public” is the schema name in PostgreSQL.
I will assume that “clientes_01” is the table name in PostgreSQL.

Different SQL backends, they function differently. I will make a guess. If the name is spelled correctly in the global variable, check to see if the file exists in the PostgreSQL side. If you have not created the table in the correct schema, your error might be there.

Good Luck.
Regards,
Roberto Artigas

3

I’ve just tried and it all works fine for me.
Check and see if you glo:Name_of_Table is threaded

4

I dont know if this setting is ticked or not, but having it ticked could help.

C11, Global Properties tab, Actions button, File Control tab,

Defer Opening Files until accessed

Specifies when your application opens related files. Select Yes to delay opening the file until it is accessed. Delaying the open can improve performance when accessing only one of a series of related files. Select No to open the file immediately whenever a related file is opened. See File Manager Class – Lazy Open and Use File for more information.

Edit.

Posting your dct table definition would help as well.

5

Hi lleana,
to complete all has been replied, take care with Postgress as it is case sensitive. So for a table name, ‘tableName’ is different from ‘tablename’.
Kind regards,
Pierre-Jean

6

That depends upon the definition in postgres. If you define the object in lowercase without " around the definition then it’s case insensitive.
And I agree it could be a problem

7

and same for indexes, name of fields wich are case sensitive…

8

It should work. Needs to be a global variable. Should be a CSTRING, not a string.
You could try putting a message in the filemanager’s Open embed (under global embeds) to find out what the Name attribute is when it fails to open.

9

How can I get the External Name Attribute of the table? I have put a message that shows me the content of the variable !GLO:table_name and it shows it correctly, but I can’t really know how Clarion creates the connection string to it in the opening

10

image
image994×458 21.2 KB

OPTION 1: CODE

GLO:PEDMOBILE = 'Driver={{PostgreSQL ANSI};Server=localhost;Port=5432;Database=impreza_main;Uid=postgres;Pwd=postgres;'
glotabla = 'public.clients_admindb'

SHARE(WCLIPOS)

IF ERRORCODE()
   MESSAGE(ERROR())
ELSE
   MESSAGE('Conexión Exitosa!')
END

RESULT: “Invalid Filename”

OPTION 2: CODE:

GLO:PEDMOBILE = 'Driver={{PostgreSQL ANSI};Server=localhost;Port=5432;Database=impreza_main;Uid=postgres;Pwd=postgres;'

SHARE(WCLIPOS)

IF ERRORCODE()
   MESSAGE(ERROR())
ELSE
   MESSAGE('Conexión Exitosa!')
END

RESULT: “Conexion Exitosa”

11

WCLIPOS{PROP:Name} should give you the external name at the time you are trying to open it.

12

How have you defined glotabla in Clarion?

Also you should use Clarions driver trace to look at what is sent to the backend. That will answer a lot of questions for you.

14

I have tried string, cstring, thread, no thread… same result…
The Clarion trace only shows me OPEN(WCLIPOS) and the corresponding “could not open the file” error, but doesn’t show me how it communicates with the driver. How should I use it to show me that option?

15

Is there more than one type of postgres driver available to try?

Maybe it’s not a Clarion thing at all?

16

I was using version 9.6, now I have downloaded version 10, it is the latest version that allows me to work in 32 bits, but the problem continues…
An interesting fact is that placing the name of the table public.clients_admindb worked for me with an ANSI type connection, I tried with UNICODE and it does not allow me to open the table (name = empty)
I’m thinking it may be a problem with the “.” between public and the name of the table itself, I’m still looking for options…

1 Like
17

My test worked perfectly. I can post it if it would be helpful.

Only thing that threw me for a second: when the wizard created the app it added my test file to the application frame. I had my code for selecting the different tables pop up before the browse, so when the application started and tried to open the file in the application frame, at that point, of course, my global variable didn’t have a value and I got the Invalid Filename error. So I removed the file from the Frame and everything then worked as expected.

PROP:Name gives the name of the table you are trying to open, so you could put it in an error message.

My psqlODBC driver version is 13.2. Not sure why you think version 10 is the latest 32 bit ODBC

18

That’s not right.
https://www.postgresql.org/ftp/odbc/releases/REL-16_00_0005/
has 32 and 64 bit versions

19

usually you get more detailed information about what is being opened and the errors that occur.

20

I think you should expand this to be
MESSAGE('Error= ' & ErrorCode() & ' ' & Error() & ' ; FileError= ' & FileErrorcode() & ' ' & FileErrorCode())

That might also give a little more information

21

Well, I think I already found the problem although not the solution yet.

I isolated the table in a new DB and in a new app with only the connection to Postgres and a button to open it and everything works OK.

When I copy the same code to the app where the Firebird DB is coexisting, it doesn’t work. I use the connection to Firebird through File Manager 3.

The trace in the app that works ok correctly shows me the name of the table to open and opens it correctly:
01A74H(1) 21:28:56.271 OPEN(public.users:028FB88H) Time Taken:0.50 secs

The trace in the original app does not show the table name:
0F38H(3) 11:39:56.589 OPEN(:027C23E4H) Error: Invalid file name Time Taken:0.00 secs