Talend community forum / Trouble mapping postgresql database column: xyz varchar(32)[] to tMap.

ayoder · 2009-10-29 20:20:56

Hello,

I have a problem with mapping a tMap module's output stream to a tPostgresqlOutput (or tPostgresqlOutputBulkExec) module. After assigning a repository schema from metadata I created from the table to the tPostgresqlOutput module, I began getting error messages whenever trying to perform a save, or an edit on the metadata. The tPostgresqlOutput is using a table schema as retrieved from the repository. An attached image shows a partial look of the schema. The error messages, listed briefely below, seemed to be indicating that hibernate was having an issue with the data types defined in the tables, and after removing the objects with the repository schema that had varchar(32)[] data types, the errors went away, and I could save.

QUESTIONS: Can a mapping be made between tMap and a PostgreSQL table with arrays of standard types as the column type? If so, what do I need to do to get the mapping to go smoothly.

Any help is appreciated. I know some Java, but have full-time Java developers available on staff, so detailed explanation would not be a total loss (i.e. I have interpreters available to help if it goes beyond my experience). New to Talend - using TIS3.0.4.r22547.

Image of schema used attached - noticed that the talend type was defined as object, and the db type was _VARCHAR with no length.

Portion of error messages received follows:

!ENTRY org.talend.libraries 0 0 2009-10-28 16:41:16.741
!MESSAGE 2009-10-28 16:41:16,740 ERROR org.hibernate.LazyInitializationException - failed to lazily initialize a collection of role: MetadataTable.columns, no session or session was closed
!STACK 0
org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: MetadataTable.columns, no session or session was closed
at org.hibernate.collection.AbstractPersistentCollection.throwLazyInitializationException(AbstractPersistentCollection.java:358)
at org.hibernate.collection.AbstractPersistentCollection.throwLazyInitializationExceptionIfNotConnected(AbstractPersistentCollection.java:350)
at org.hibernate.collection.AbstractPersistentCollection.readSize(AbstractPersistentCollection.java:97)
at org.hibernate.collection.PersistentList.size(PersistentList.java:91)
at org.eclipse.emf.common.util.DelegatingEList.delegateSize(DelegatingEList.java:233)
at org.eclipse.emf.teneo.mapping.elist.PersistableEList.delegateSize(PersistableEList.java:401)
at org.eclipse.emf.common.util.DelegatingEList.size(DelegatingEList.java:224)
at org.talend.core.model.metadata.builder.ConvertionHelper.convert(ConvertionHelper.java:84)
at org.talend.repository.ui.wizards.metadata.table.files.FilePositionalTableWizard.<init>(FilePositionalTableWizard.java:67)
at org.talend.repository.ui.actions.metadata.AbstractCreateTableAction.createFilePositionalTableWizard(AbstractCreateTableAction.java:222)
at org.talend.repository.ui.actions.metadata.CreateTableAction.run(CreateTableAction.java:111)
at org.talend.repository.ui.actions.RepositoryDoubleClickAction.run(RepositoryDoubleClickAction.java:90)
at org.talend.repository.ui.views.RepositoryView$7.doubleClick(RepositoryView.java:483)

...and...

Caused by: org.hibernate.exception.DataException: could not insert: [ColumnType]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:77)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:40)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2093)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2573)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:47)

Thanks.

--andy

Uploaded Images

shong · 2009-10-30 07:08:18

Hello
I can't reproduce your problem, can you export your job send it to me?

Best regards

shong

ayoder · 2009-11-04 19:50:40

Shong,

Thanks for the quick reply - I had removed the module that was causing the error, and when I re-inserted, I could not reproduce the error either. I did spend some time looking, but do not know what the issue was. However, I do have a related issue:

The problem I now face is that I have an input stream from a positional file which has three string fields that need to be loaded into varchar[] field in the database (i.e. legal_name_1, legal_name_2, legal_name_3 --> legal_name[0], legal_name[1], and legal_name[2]). No matter what I have tried, I get one of two kinds of errors between the incoming strings and the varchar[] (object in Talend). They are either:

Can't infer the SQL type to use for an instance of [Ljava.lang.String;. Use setObject()

or

Column "legal_name" is of type character varying[] but expression is of type character varying

Of the two errors, the first one is the one that I am currently getting, and seems to be "closest" to working as the error is actually reported in the PostgreSQL logs (the second one happens at run-time within Talend). I have an exported job, but dont' see a way to attach it to the post - how can I send it to you?

QUESTION: What am I doing wrong with the assignment of the strings into the array / what is the correct way to be doing this?

Since I don't see a way now, here is briefly what I am doing.

1) I have inserted a tJavaRow component into the flow, so that the last three components are: tJavaRow --> tMap --> tPostgresqlOutput.

The tJavaRow is doing the following code (among other things):

Code:

String[] temp_legalname = {input_row.legal_name_1,input_row.legal_name_2 ,input_row.legal_name_3};
globalMap.put("myLegalName", (Object)temp_legalname);

and the tMap is doing the following expression to assign into the object (Talend) / varchar[] (database) field:

Code:

(Object)globalMap.get("myLegalName")

I am attaching a new image of the job.

Thanks.

--Andy

Uploaded Images

Announcement

Unanswered posts

#1 2009-10-29 20:20:56

Trouble mapping postgresql database column: xyz varchar(32)[] to tMap.

#2 2009-10-30 07:08:18

Re: Trouble mapping postgresql database column: xyz varchar(32)[] to tMap.

#3 2009-11-04 19:50:40

Re: Trouble mapping postgresql database column: xyz varchar(32)[] to tMap.

Code:

Code:

Board footer