You are not logged in.

Unanswered posts



Important! This site has been replaced. All content here is read-only. Please visit our brand-new community at https://community.talend.com/. We look forward to hearing from you there!



#1 2006-10-16 07:33:33

kvocat
Member
110 posts

kvocat said:

Postgresql database, schemas and case-sensitivity

I work with a Postgresql database. I can't use postgresql schemas. Talend dosen't recognize them. It works only with the public schema. I also have an other problem with the case-sensitivity : Talend dosen't recognize tables or fields which contain capital letters... For example, noStreet is unknown and nostreet is ok.

Will it be corrected in further versions ?

Thanks a lot.

Kim

Offline

#2 2006-10-16 10:31:01

cantoine
Talend Team


cantoine said:

Re: Postgresql database, schemas and case-sensitivity

Hello,

Have you define a Schema value for the Db PostgreSQL connection ?

Have you some problems in the repositories or to use it in the Job Designer ?

In the SQL properties of tDbInput (Job Designer) don't forget to specify your Schema as example : 'Select * from mySchema.myTable'

Regards

Last edited by cantoine (2006-10-16 10:34:27)

Offline

#3 2006-10-17 15:50:33

kvocat
Member
110 posts

kvocat said:

Re: Postgresql database, schemas and case-sensitivity

Hello !

It seems to be okay when you use double quote in the query for DBInput. You also can retreive metadata with upper and lower letters.

Thanks a lot

Kim

Offline

#4 2006-10-19 09:52:27

kvocat
Member
110 posts

kvocat said:

Re: Postgresql database, schemas and case-sensitivity

Hi !

I still have problems with postgresql.

I just want to insert data from a inputFile in a table on a Postgresql database. And, I can't.....

I have a Postgresql database called "testTalend", in which I have a schema called "myTalendSchema". In this schema, I have a table "Rue". The structure of the table :
  CREATE TABLE "myTalendSchema"."Rue" (
  "idLocalite" BIGINT NOT NULL,
  "idRuePoste" BIGINT NOT NULL,
  "libelle" VARCHAR(50) NOT NULL,
  "genreRue" SMALLINT DEFAULT 0 NOT NULL,
  "codePreposition" SMALLINT DEFAULT 0 NOT NULL,
  "npaAdressage" VARCHAR(20) NOT NULL
) ;

I can create a connection with a right click on the DbConnections. And then, the schema. All is okay. You will find images below. You can also find the property of the DbOutput.

But when I try to execute the insert, I have this error message :

.....
ThreadStat started...
Connecting to talendStudio on port 3334...connected.

DBD::Pg::st execute failed: ERROR:  column "idlocalite" of relation "Rue" does not exist
can't execute insert query
A thread exited while 2 threads were running.
Job writeTalend ended at 10:45 19/10/2006. [exit code=9]
......

Talend can read fields with different cases (idLocalite <=> idlocalite) ? I don't think that...

Thanks a lot for you help.

Kim

Offline

#5 2006-10-19 12:44:22

cantoine
Talend Team


cantoine said:

Re: Postgresql database, schemas and case-sensitivity

Hello,

I have created the same Table in our PostgreSQL Database, and i have the same error for your job.

Then i have testing in another SQLClient and it's the same result as you can see in attach file.

It's a PostgreSQL bug :

In PostgreSQL the Queries are case sensitive, and it appears that PostgreSQL automatically lower-case the column names in queries before running them - even though the columns have mixed-case names.

The solution is to enclose colum_name with " character as INSERT  INTO "mypostgres"."Rue"  ("idLocalite","idRuePoste","libelle","genreRue","codePreposition","npaAdressage")  VALUES (1,1,'tt',1,1,'uu');.

We are working on this solution, you can follow this features in our BugTracker.
Normally it will be include in our next Update Site(v1.0.1).

Best regards.

Last edited by cantoine (2006-10-19 12:48:47)

Offline

#6 2006-10-19 13:50:48

kvocat
Member
110 posts

kvocat said:

Re: Postgresql database, schemas and case-sensitivity

Hello !

When you do an insert query, you can put the ". But when you want to insert data from Talend to a postgres table, how to do ?

Best regards.

Kim

Offline

#7 2006-10-19 14:07:45

plegall
Talend Team


plegall said:

Re: Postgresql database, schemas and case-sensitivity

There is a bug with PostgreSQL queries, but we can workaround it in Talend, when the bug 205 will be resolved.

Offline

#8 2006-11-03 09:16:11

kvocat
Member
110 posts

kvocat said:

Re: Postgresql database, schemas and case-sensitivity

Hi !

We can't see views in the postgresql schema. How to reach a view on a database ? (I have the same problem with an odbc driver for AS400)

Thanks a lot

Kim

Offline

#9 2006-11-03 11:14:29

cantoine
Talend Team


cantoine said:

Re: Postgresql database, schemas and case-sensitivity

Hi,

I have created a feature in our Bug Tracker about to retrieve VIEWS as TABLE in the Db Connection wizard.

Regards.

Offline

Board footer

Talend Contributor Agreement - Talend Website Privacy Policy