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?
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.
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.
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.
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)
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.
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.
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?
