After selecting a set of data with a JDBC select adapter, we are trying to perform another select based on the first set of data. One of the fields should be just a VARCHAR, saved as a String. But when we try to use it in the WHERE clause, no values are found.

Running in debug mode, the value looks appropriate. But when I click on it, the value shows four extra square/box characters. Another forum thread mentioned that the view may simply not be able to display the value correctly. But surely there is actually something extra in the field if the subsequent SQL Select is failing?

Could the issue be something with the way the value is selected or stored?

Hi Trayer,

can you test the select query in Oracle SQL Developer?

Maybe the additional is already in the Database.

can you provide version for IS, JDBC-Adapter and Oracle Database?

Regards,
Holger

webMethods 8.2 SP3
JDBC_6.5_Fix42
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Default Charset: UTF-8

The query works as expected when directly against the database - selecting from the source table gives me only ‘C200’, and providing ‘C200’ to the next query gives the proper output.

And even when I run the JDBC-Adapter by itself, providing the input of C200, it provides the correct results.

As a test, I created a flow service that contains only the two adapters (with a loop over the first adapter’s records). But it does the same thing as before, adding the four extra squares/boxes when viewing the value from Debug mode.

Also, when I add in a length function, it gives a value of 8 instead of 4. The field in the adapter has a column type of VARCHAR2(50). Other columns that are VARCHAR2(30) are also getting the extra 4 squares/boxes.

Hi Trayer,

which driver resp. version of driver are you using?

Additionally you can try to write the data to a file and open it with a text editor or hex editor to see what characters the boxes represent.

Eventually your tablespaces/database do not have the correct codepage.
See installation guide for the recommended settings.

Regards,
Holger

Trayer,

Please see the length in the first loop before passing to second loop. Please share the results.

Thanks,

Holger,

I’m not sure what driver you are asking about? What is the driver resp.?

I’ve never needed to write to a file, so I’m still working that out.

Thanks,
Trayer

MR as173d,

There is only one loop in my test right now.

The first select adapter gets a set of results. I loop over the results, looking first at the length of the field in question, then passing the value to the next adapter.

I can look at the result set before it goes into the loop, and the field has the extra characters as soon as it is populated.

IT’s weird if that extra chars are adding in the pipeline for those records…

Did you also check with your DBA just make sure data types are set correctly both sides.

Also I would say open a ticket with SAG support in parallell and jdbc adapter fix is 50 now and may be they can suggest alternative troubleshooting.

HTH,
RMG

Trayer:

How you are reading data, if possible can you share the query. Are you using addSuffix() function to add some extra characters if length of field is less than some length. If not, if your column value is some constant in length then you can use trim() to remove unnecessary characters. I believe there is no issue with Adapter. Kindly share the query you have written and a little data if possible so that we can also check and correct if needed.

Thanks,

Attached is a file with the Create Table SQL as well as some data ready to be inserted. There’s a lot of other fields there, but you asked for it.

My adapter selects all fields where RAW_MTL_STATUS = ‘N’ and RAW_MTL_INV_ID IS NOT NULL.

The Output Field Type for all fields is String, and the field we care about is MIX_ITEM_ID_ACTUAL (7th from the bottom). Value should be C200. Getting C200----, where dashes are boxes, in Debug mode. Running the adapter alone gives C200.

My DBA says the value should always be in that format and length, so the trim() is probably a good work-around for now. The same boxes-issue is happening for me in another select, and in that case I need to check the length to choose what to do next.
batch_records_example.txt (43.9 KB)

RMG,

What do you mean by checking if the data types are set correctly on both sides? They are both VARCHAR2. The source table’s column is 50 bytes, and the destination is 40 byes - but I wouldn’t say that’s the issue. The squares/boxes are there as soon as I select with the adapter.

An interesting tidbit is that, when I click on the value displayed in Debug mode, it will change. It removes the squares, so that when I try to copy and paste, all that it gives me is the C200. Yet, the length() function still says 8, not 4. It’s the same for my other select I just mentioned - the value there is 16 characters, but it gives a length of 20.

WmPublic - pub.string:trim works. Length goes from 8 to 4, value from C200---- to C200. (- = box)

I will use that to move forward with my current project, but I’ll probably also open the ticket with SAG support to see what they say.

And any other help is more than welcome. Thanks for everything so far!

OK sounds good and if trim works as expected and no other spl chars behind it’s a good work around from my perspective.

HTH,
RMG

Trayer,
I suggest, you please update the column which is possessing C200 and set it as ‘C200’ once again for a couple of records and check is the issue with data or something else. Good to hear that you have WA for time being. Keep updates posted.

Thanks,

Trayer,

I am referring to the ojdbc6.jar (6 stand for Java 6 here).

In side it there is a META-INF/MANIFEST.MF file. This can viewed/extracted when opening the jar with a ZIP program or with the jar command provided by the JDK.
In the first paragraph there is a line mentioning the driver version.

Can you tell us what AdapterService template did you use to create the Select Adapter?
Can you share the screenshots of your adapter services?

Regards,
Holger