Bulk lowercase external name for postgres

questions
,
1

Hi Everyone.

I am changing the database from mssql to postgres and now I need change all external names to lowercase. Also add an external name to all without.

I have many tables with a lot of fields and it will take me a bit of time do do everything. Is there an easy method? Is there a template or some code someone can share to help?

Clarion 11, FM3, and a few other templates from CapeSoft. ( if there is a flag/setting that already do this and I don’t know about it )

Thanks

2

Actually, No, You don’t.
Provided that the Postgres database table and columns are created lowercase it’s case insensitive to access.

ie
CREATE TABLE mytable
myfield INT
end

can be happily referenced any way you please MyTable, MYTABLE All work.

CREATE TABLE “MyTable” is a different story altogether.

I also just tested in pgadmin
CREATE TABLE MyTable in pgadmin will create the table as lowercase, you have to actually quote the text to make it case sensitive.

3

Okay, after looking at a test app I can see that is should not matter. I guess something else is going on. I will have to look and see what is going on/wrong.

4

You might get better advice if you told us what you have done so far and what the actual problem is?

5

The tokens MY_TABLE and A are examples of identifiers.

They identify names of tables, columns, or other database objects, depending on the command they are used in. Therefore they are sometimes simply called “names”. Key words and identifiers have the same lexical structure, meaning that one cannot know whether a token is an identifier or a key word without knowing the language

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable. The SQL standard will not define a key word that contains digits or starts or ends with an underscore, so identifiers of this form are safe against possible conflict with future extensions of the standard.

Key words and unquoted identifiers are case-insensitive. Therefore:

UPDATE MY_TABLE SET A = 5;

can equivalently be written as:

uPDaTE my_TabLE SeT a = 5;

A convention often used is to write key words in upper case and names in lower case, e.g.:

UPDATE my_table SET a = 5;

Key words and unquoted identifiers are case-insensitive. Therefore:

UPDATE MY_TABLE SET A = 5;

can equivalently be written as:

uPDaTE my_TabLE SeT a = 5;

A convention often used is to write key words in upper case and names in lower case, e.g.:

UPDATE my_table SET a = 5;

For everything else export the dictionary to a TXD file, and in a text editor like Notepad++ search for tables with existing table names and then search for tables with missing table names to fill in.

There’s 3 different ways a table name can be set.

First two stored in the Dct editor.

Stored in a variable (typically global) where the table name variable is prefixed with a $ in the Full Pathname dct editor field to tell the AppGen not to wrap the variable with single quotes.

The database and table name is simply written out in the Full Pathname field.

Last way is the database and table name is specified in program code using the NAME property (Prop:Name). This allows for swapping to identical format database servers where the server name changes like TestServ or ProductionServ but the app can not shut down and reload with new db server details.

6

My guess is that you are running into the quote problem, i.e. that you have

my_field short,name(‘“MY_FIELD”’)

because Clarion helpfully added an external name with quotes around it in case MSSQL didn’t understand my_field as a column name because of the underscore. The name has to be a string, so it has single quotes around the double quotes.

That would work in MSSQL, but in Postgres it won’t, because it makes the name case sensitive, and the column name is probably my_field, not MY_FIELD. The better solution (as Sean said) is to remove the quotes from the external name rather than make the external name lower case.

I think this would be fairly easy to do if you export the dictionary to text and edit the .dctx. Your problems will look like this:
ExternalName=""BP_NUMBER""

and you will need to remove the " bits. I think this is unlikely to fool up anything unless you have some external names that in fact do need quotes, like a field called ‘my field’ (with a space)

7

I did just try this with a dictionary that had a lot of quoted external names and it imported fine after a global search and replace (with nothing) of the quotes

8

You might want to take a look at Roberto Renz’s clarionDictionaryTasker on GitHub (GitHub - robertorenz/clarionDictionaryTasker: Clarion Dictionary Addin with Assistant, Tasker, Viewer, Organization. etc. · GitHub) . It’s a clarion addin designed for bulk dictionary manipulation, so things like updating external names across lots of tables/fields become much easier than doing it manually or via TXD editing.

Might be exactly what you need here.

Mark

9

This also might be a good time to consider the new PostgreSQL2 driver. Its not cheap but will save you a lot of time in the long run.