Flat file to JDBC inserts empty record

, ,
1

I’ve gone through the series of tutorials and was successful reading my flat file, and my JDBC exercise inserts a record. But when I try putting those two actions together in a service, all I get is an empty record inserted to the database.

What is it that I’m not doing to get the data into the database? Is it something in the pipeline? Could it be from spelling some column name differently?

I don’t entirely understand the pipeline or the many different places to name all the fields.

2

try to trace your service. add SavePipelineToFile to the flow services where you think it’s broken and try to step through it, see if the values in each of the fields contain the expected values.

3

Yes, thank you, Tong Wang. I would trace it except that I don’t know what’s supposed to be there.

Is the Dictionary the layout for the records in the file?

Is the Document Type the also the layout for the file, and the layout for the database INSERT is contained in the Adapter Service?

If that’s the case, then maybe I only need to figure out at what point to map one to the other.

I can’t find an example of a webMethods service that both reads from a file and inserts records to a database without it being a batch insert. I need to insert one at a time because my files are going to be very large files. I see how to repeat for each record but I don’t know how to map the file data to the database insert. That’s what’s missing. That’s why I’m successfully inserting a record but every column in my insert is NULL.

4

Hi Amy, here are the basic steps:

  1. Create a dictionary to define the record and field definitions used by the flat file.
  2. Create a flat file schema defining the flat file structure. This schema will reference the dictionary from step 1. The dictionary is not actually used in the service that will read the flat file and insert into the database.
  3. Create a JDBC adapter connection for the database into which you will be inserting.
  4. Create a JDBC adapter service referencing the adapter connection from step 3. It looks like you will be using an insertSQL.
  5. Create a flow service that reads the flat file and invokes pub.flatFile:convertToValues (based on the ffschema you created) to generate a ffValues document (this internal document will appear in your pipeline).
  6. Loop through the ffValues document from step 6 and invoke your JDBC adapter service, mapping the elements from ffValues to the corresponding adapter service ‘ServiceIn’ values.
5

Thanks, Mary.

My Flow Service contains these three things:

I have the pub.file:getFile.

I have pub.flatFile:convertToValues. The bytes in Pipeline In are mapped to ffData in Service In, and the file’s name is in ffSchema in Service In. And ffValues in Serfice Out is mapped to the Document Type that the Designer created from the Dictionary.

I have the TryRecordTypes.db:tryInsertContracters. That’s the name of my adapter. When I run this adapter by itself by right clicking and fill in some data, it does successfully insert the data.

Running the service does insert a record where it’s supposed to, but every column in that record is NULL.

The Results tab shows that the data from the file is successfully read in, but I don’t know what’s supposed to get my data from there to the record that’s inserted. Where does webMethods know to do that?

6

Hello Amy,

Are you doing head to head mapping or field to field mapping.

Please share the pipeline screenshot of adapter service mappings.

Regards,
Sreekanth

7

The convertToValues already put the data into my Document Type, so I didn’t think there was any more to map. Since reading your reply, I have been experimenting with mapping within the adapter step of my flow, but it’s having no effect.

I’m new at this. I don’t understand the mapping; I just did what the flat-file and JDBC tutorials said and they didn’t tell me WHY I was supposed to map as they were telling me.

What should be mapped in the adapter step?

I very much wish there were someplace to learn these things instead of cobbling together the information and trial and error!

If you are able to help me, I’d very much appreciate it!

8

Hi Amy,

Your service will map the data from the document created by convertToValues to the adapter service. Insert loop step right after convertToValues. Input array for loop step will be ffValues. Within the loop, insert an invoke step to invoke the TryRecordTypes.db:tryInsertContracters adapter service, mapping the fields from ffValues to the corresponding ‘Service In’ fields for the TryRecordTypes.db:tryInsertContracters adapter service.

You can run your service in debug mode to step through it.

-Mary

9

Thanks, Mary. I also asked someone here for help before I saw your message. They about told me the same thing, that I need to do the looping for it to work. I was going to save that for later, after I got it to write, but he told me it doesn’t work that way.

I will try the debug mode. Maybe that will make it easier to see what the data’s doing.

Thanks.