How to deal with case sensitive databases?

[djogopatrao]

Hi

I'm using Teiid 7.7 over JBoss 5.0.1, and Teiid Designer 7.8.0.

My postgresql database has some columns with capitals, and if I (directly connected to this database) fail to put double quotes around the column name (as in SELECT "CdPatient" From... ") it will issue an error.

The Teiid Plugin keeps removing the double quotes when I put them around the column names, so when I deploy the VDB, I can't run the query.

How can I solve this problem without having to mess with the database?

Thanks in advance!

dfcp

[shawkins]

> The Teiid Plugin keeps removing the double quotes when I put them around the column names, so when I deploy the VDB, I can't run the query.

In Teiid Designer column name metadata is split between the the runtime name (used by Teiid SQL) and a name in source property.  The latter is used to control the exact string used in the source query issued against the database.  If you put a quoted string in the name in source, then that is what you should see in the source query.  Ideally Teiid Designer will do this for you on import, but in 7.8 it may not be enabled as an option by default.

[djogopatrao]

Hi Steven!

Thanks for your answer!

I'm on a different setup now, to try and solve this issue. I created a VDB with one source database and imported one table.

When I'm importing from the JDBC source, there is no option to add quotes - in this particular case, all columns from X are uppercased.

I tried to add quotes manually by editing the column name (see below), but it still won't put quotes on the columns names when I make a select over this model.

Cheers,

[shawkins]

> I tried to add quotes manually by editing the column name (see below), but it still won't put quotes on the columns names when I make a select over this model.

You are trying to modify the Teiid name * - not the name in source. When you select a column look, at the associated properties editor you should see a "name in source" property.  It is likely set to be the same as the Teiid name.  You need to change it in the properties editor to be quoted.  Later versions of Designer should automatically quote the name in source on import.

* Older versions of Designer assume that it should remove enclosing quotes from the Teiid name (although that's not entirely necessary).

[djogopatrao]

Hello again

I did as you said, stil getting the error below (althought  it correctly quotes the table name)

SELECT * FROM atendime

Error Code:0 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:0 Message:mv_repl: Error Code:0 Message:'ERROR: column g_0.cd_atendimento does not exist

  Position: 8' error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.CD_ATENDIMENTO, g_0.CD_PACIENTE, g_0.CD_CONVENIO, g_0.CD_SERVICO, g_0.CD_CID, g_0.CD_PRESTADOR, g_0.DT_ATENDIMENTO, g_0.CD_ORI_ATE, g_0.CD_TIP_MAR FROM "public"."atendime" AS g_0]

[shawkins]

You'll want it to be the proper quoted name with whatever mixed case is in the database column names - "Cd_A...".  Quoting it as upper case won't match.

[djogopatrao]

Actually, in the database, the column name is in all caps. Look below:

mktshare=# select CD_PACIENTE FROM ATENDIME limit 1;

ERROR:  column "cd_paciente" does not exist

LINE 1: select CD_PACIENTE FROM ATENDIME limit 1;

               ^

mktshare=# select "CD_PACIENTE" FROM ATENDIME limit 1;

CD_PACIENTE

-------------

     6007066

(1 row)

So I need to put quotes to make postgresql understand that the column name is all uppercase.

[shawkins]

> So I need to put quotes to make postgresql understand that the column name is all uppercase.

I was just going by your initial example of SELECT "CdPatient".  So yes you do want a name in source that is a quoted caps then.

From the sample source sql with "SELECT g_0.CD_ATENDIMENTO, g_0.CD_PACIENTE, g_0.CD_CONVENIO, g_0.CD_SERVICO, g_0.CD_CID, g_0.CD_PRESTADOR, g_0.DT_ATENDIMENTO, g_0.CD_ORI_ATE, g_0.CD_TIP_MAR FROM "public"."atendime" AS g_0"

It would appear that the name in source setting is not in effect.  You may need to resynch/rebuild your vdb.

Steve

[djogopatrao]

YES! Synchronize! That (and quoting the uppercase column name) solved the issue.

for the record, that's where I needed to click: the first checkbox goes unchecked every time I change something. Before deploying, I need to click this box or else nothing will change.

Thanks a million, Steve!