Can't write Data to DB when recieving csv file via Polling Port

, ,
1

Good Morning everyone,

I am facing a problem again with a webMethods JDBC adapter.
I am trying to extract values from a csv file, which i want to read using a polling port and save those values to my database.

I have created a test service where i enter the transport info, which usually comes from the polling port, manually and then call the services which is called by my polling port as well.

For my test service i save the file with the FilePolling Prefix in the work folder. This service is able to grab the file and extract the values and save them to the database.

When I save the file in the inbound folder of my Polling Port i can see, that the server gets the file and transfers it to the work folder and afterwards to the done folder. But for some reason i always get the following error message when the service is trying to write the extracted information to the database:

[260]Cannot insert the value NULL into column ‘EFFECTIVE_DATE’, table ‘NETZ_PDV.dbo.INP_WM_PDV_DV_PMES_IMPORT_PANDA’; column does not allow nulls. INSERT fails.
259 Cannot insert the value NULL into column ‘EFFECTIVE_DATE’, table ‘NETZ_PDV.dbo.INP_WM_PDV_DV_PMES_IMPORT_PANDA’; column does not allow nulls. INSERT fails."
[258][ADA.1.316] Cannot execute the SQL statement “insert into INP_WM_PDV_DV_PMES_IMPORT_PANDA (EFFECTIVE_DATE, DELIVERY_BASE, DISTRICT, PANDA_CREATION_TIME, PZ, ZB, ZBEZ, FILENAME) values (?,?,?,?,?,?,?,?)”. "
[257]2015-10-28 07:30:30 CET [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service dp.de.fulfillment.import_Panda.v1_0.load.adapterInternal.table_INP_WM_PDV_DV_PMES_IMPORT_PANDA:insert_ZustellnetzDatei.

It is the same file so i know, that the values, which are supposed to be written in the database are not null.

I hope someone can help me and that this is enough details

Thanks in advance

2

Hi,

trim the values first and then try inserting values to DB.

Regards,
Sreekanth

3

I tried that

I extract the values and match them to a document but it still doesn’t work

4

Hi,

looks like null values are being mapped to insert adapter service.

Please share the screenshot to explore more on this.

Regards,
Sreekanth

5

But why only when using the polling port?

What exactly would you need a screenshot from?

Mapping_Input_InsertZustellnetzInformationToDB.PNG
Mapping_Input_InsertZustellnetzInformationToDB.PNG771×286 14.9 KB

Mapping_Input_DB_Adapter.PNG
Mapping_Input_DB_Adapter.PNG882×262 12 KB

6

Hi,

Please try to run adapter service alone and check whether you are able to insert data or not.(this is to make sure no issue with adapter service)

as per my understanding, it could be data type issue.

when you try to fetch data from DB…you will get values in different datatype formats ( Ex: Int, Date,Vatchar…etc) and when you try to map them directly to string values in the document type…null values will be mapped.(ex:date to string or Int to String)

as your DB input fields are strings, make sure you convert all the values to string first and then it try to insert…it should work.

Regards,
Sreekanth

7

Also, try to pass Effective_date field with some constant value rather than mapping it from some doc.

Thanks,

8

My adapter works fine, i can insert information into the database.

I need to extract the values for effective_date field here

9

Maike – While retrieving you keep the datatype of this date field to Varchar, I hope there might be issue with your datatypes.

Thanks,

10

Hi Maike,

in this case sounds more as an issue while parsing the csv read from the file polling port.

Are you using a FlatFile dictionary, schema, doc type for it?

Please provide more details about the structure (columns) as well as the format/type of the data in these.

Make sure that these fields are read correctly from the file.

Regards,
Holger

11

Thanks everyone for your advice I found my problem and now it works fine.

I used the FF Dictonary while parsing the file from the polling port.

I extracted the wrong filename from the orignal filename filed in the transport info, which later lead to a wrong tokenized filename. From this filename i was not able to extract my values for the effective date field i.e…

I changed that and now i can insert the values to the database perfectly the way i want it.

Thanks again.

12

Hi Maike,

thanks for updating the thread with the resolution.

We are glad to hear, that some hints of us lead to the right place to look at.

Regards,
Holger

13

Thanks for the update back…

Glad to hear the issue is resolved… :smiley:

14

Maike,

I apologize if I misunderstood, but are you using getTransportInfo to get the file name and then using the file name to go retrieve the file contents from disk?

You do not have to do that. The file polling port will give you a handle to the file as an ffdata object. If you’re using the flat file adapter, and it sounds like you are, you can simply map that ffdata object to the convertToValues call. No need to call getTransportInfo or getFile.

See for yourself: add a call to savePipeline in the very beginning of your service, drop a file in your monitoring directory, and after the service executes, call restorePipeline. You should see the ffdata object I’m referring to.

Good luck,
Percio

15

I echo with Percio :smiley: