Database and FlatFile

, ,
1

Hi to all,

I have a problem, I’m new to webMethods software.

I have a JDBC Adapter System (JAD) thad execute a dynamic sql, this statement return a different set of column:

“SELECT * FROM MY.DATA_V DV PIVOT ( MAX( “VALUE” ) FOR FIELD_NAME IN ( ${pivotFieldsNames} )”

Where ${pivotFieldsNames} have 3 value:

  1. “‘a’, ‘b’, ‘c’”
  2. “‘x’,‘y’,‘z’”
  3. “‘a’, ‘y’, ‘v’”

I want to write 3 different Flat Files with 3 different Flat File Schema. I have read this: http://www.idnxchange.com/blog/webmethods-step-by-step-database-to-flat-file.html
But does not work. :slight_smile:
My idea is to pass a FlatFileSchema programmatically.

It is possible?

Thanks
Marino

2

I believe you cannot create a FFSchema on the fly or programmatically…

Is your FF so dynamic and needs a proper format layout?? If not you can just create a comma seperated string and send it to down stream?

HTH,
RMG

3

Hi RMG, thanks for your reply,

My FF schema is not dynamic, I have 3 already ready fixed FF schema. I just want to set the '“ffschema” name programmatically.

Columns returned from sql SELECT must coincide with programmatically selected FF schema, I have tried the tutorial, but my output “string” is always empty.

Marino

4

If the string outputs empty I believe your FF schema is not working as expected.

What is your FF schema layout defined as can you elaborate and did you tested it properly with convertToString inputing the ffData and FFSchema fully qualified path?

HTH,
RMG

5

I have to tried with a SELECT and convertToString, but without a MAP and FFSchema Document Type that map SELECT result with FFSchema Document Type and convertToString, does not work.

Yes, I put the full qualified path.

In convertToString i dont have ffData but ffValue.

Marino

6

Do you see any errors and most likely the FFSchema that you created and the inputs mapped (delimiters,ffValues IDATA structure) not matching against schema.

Is the schema record definition as recordWithNoID?

HTH,
RMG

7

Both pub.flatFile:convertToString and pub.flatFile:convertToValues get ffSchame as input variable, so you can either set it fixed at development time or pass from a flow variable. They are also taking all the formatting data as (optional) input, so it would be possible to pass those dynamicall, although in most use cases i would advise against it.

Do you have specified a default record (resulting in recordWithNoId in the document structure)? If not, you need to be sure, that the records can be identified by the column you specified for identification to be properly mapped. If you do not have an identifying column, you need to have recordWithNoId and mapp it on the correct level of the document structure.
One way to debug is to parse a file you would expect by using convertToValues and look at the document the service created.

About the SQL: Even wit hdynamic SQL I would use fixed names for the return columns, not *. This way you can not be shure if you have the expected columns at the right place.

8

Yes I agree with Martin. :smiley: