Question & Answer
Question
Is it possible to use parameter markers when using IBM i Access Client Solutions data transfer?
Answer
Parameter markers can be used in the Where clause of a data transfer request. When used, the transfer will prompt the user to enter values to replace the parameter marker in the query.
Following is an example of using a parameter marker with IBM i Access Client Solutions data transfer:
The initial screen is filled in like you would normally do when running a regular data transfer:
Then click on Data Options:
The initial screen is filled in like you would normally do when running a regular data transfer:
Then click on Data Options:
Click on the Details button at the bottom and select the columns you want returned in your transfer:
Select the Where tab and double click on the field you want to search on:
Double-click an operator in the "Test" column to determine how this column will be compared and what it will be compared to. In this example I am using the equality operator and selecting the question mark, the indicator for a parameter marker.
The "Where clause:" field is automatically updated to show that the query will return rows where the value in the CUSTOMER_STATE column matches the value entered by the user:
More criteria could be added to this query, linking the individual predicates with AND or OR and grouping them with parenthesis. Press the "OK" button in the "Download Request Details" window and the "Change Data Options" window to return to the main data transfer window so that we can test the transfer. When you press the "OK" button on the "Change Data Options" window, the client prompts to you enter a meaningful label to prompt the user when the transfer runs.
I know where this is going so I'm going to add the following prompt text:
I set the comparison to equality for a character field. That means that it will be case sensitive and because it is a character value, the user has to put quotes around the value in order for it to work. (Not really but we will get to that shortly). Clicking the OK button in the label prompt takes us back to the main data transfer window. I'll press "Start Transfer" and run it. I'm prompted to enter a value. Because I don't read instructions, I'll enter the abbreviation for my fellow loon lovers in Minnesota:
And here is what I get for not following instructions:
So I'll follow 1/2 of those instruction and try it again:
Well that gets me further:
Yikes this is hard! I have to follow all the instructions? I thought this was like a Chinese take out menu pick one from column 1... OK I'll try again:
Well that's more like it:
And we are all thinking, "Uff-da cancha make it easier doncha know?", "YOU BETCHA!"
One way to make that work a little better is to use the LIKE comparator instead of equality:
That's a little better, but probably not for the reasons you might have guessed. The prompt for the LIKE comparator doesn't have a drop down list, you have to enter the parameter marker and it ends up between a pair of quotes. We could do the same thing with our equality comparator and even remove the case sensitivity by using the SQL UPPER function. You can't point and click your way through it, you have to type this in. While I'm add it, I'll add the prompt text without being prompted for it:
With the Where clause defined like this, even I have a hard time messing it up. I don't have to enter quotes and I can type the state abbreviation in any case I want and it will work.
I also performed the UPPER function on the CUSTOMER_STATE value so it will work even if someone entered the state in something other than upper-case letters.
Note that the prompt text and the parameter marker are all enclosed in the quotes, that is important, if you don't do it that way, you will get errors.
You can also take advantage of parameter markers in queries that are processed as 'Native SQL' (the only way you can join more than 2 files correctly with data transfer). Here's an example:
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z000000cwMLAAY","label":"Data Access-\u003EData Transfer"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
Data Access->Data Transfer
Software version:
All Versions
Document number:
646081
Modified date:
31 May 2024
UID
nas8N1020588
Manage My Notification Subscriptions