Hi folks,

I am getting an error from the Input Mapping button that "Schema is not valid". The Output Mapping is good but I cannot figure out the Input Mapping?!

I have an SQL query with NO variables and the Input Schema and Output Schema buttons work fine (they return a schema). If I try to use a variable by replacing a value in my SQL query by a question mark the Output Schema fails with an error message. So for now I am not using variables.

I have gone through the documentation and it does not provide any insight on how to set the Input Schema properties.

Has anyone done this yet?

I am using the latest version of ED (v3.2.2) and the previous version does the same thing and this I suspect that it is a problem between the chair and keyboard but the documentation is not providing anything that can help me resolve this.

Thanks in advance,

Gabriel

On the Task property page, there is a question mark icon that you can click to get detail help information. I pasted the portion of Input and Output Schema here below and there are examples in it. If you still have issues please provide your SQL query.

Input schema (mandatory) – Click Retrieve to populate this field. It uses the SQL query field to generate the input schema. Click the Show checkbox to view the generated input schema. The input schema allows you to do Input Mapping.

If there is no variable in the SQL query, the properties field of the generated input schema is empty: {"title":"ReadDbTaskInputSchema","properties":{},"type":"object"}.

For an SQL statement containing variables (question marks), an input schema is created (by clicking Retrieve), based on the type of the columns that the variables represent. For example, if the SQL query is : select email from test.users where telephone = ? and name = ?, the generated schema would be: {"title":"ReadDbTaskInputSchema","properties":{"inputParam2":{"type":"string"},"inputParam1":{"type":"string"}},"type":"object"}, where inputParam1 represents the first question mark, and inputParam2 represents the second question mark. It is important for the user to map the correct parameter to the correct values during input mapping. Even if the inputParam numbers do not show in incremental numbering, use the numbers of inputParam to map with the question marks.

Output schema (mandatory) – Click Retrieve to populate this field. It is the schema of what you are expecting back from the database. It uses the SQL query field to generate the output schema. Click the Show checkbox to view the generated output schema. The output schema allows you to do Output Mapping. Because the result of a select statement can return multiple values, the output schema is a JSON array. For example, if the SQL statement is: select email from test.users where telephone = ? and name = ?, the generated schema would be: {"title":"ReadDbTaskOutputSchema","properties":{"resultSet":{"items":{"properties":{"email":{"type":"string"}},"type":"object"},"minItems":"1","uniqueItems":"true","type":"array"}},"type":"object"}.

Good morning Bill,

The Input and Output Schema works as expected as it does not use any variables right now.


The part that I cannot figure out is how to pass variables via the Input Mapping to the Read from DB Task!? Every time I click the Input Mapping button I get the error as shown the attachment.

I must be missing something really basic which may result in my banging my head on my desk but I fail to see what it is at this time.

Thanks,

Gabriel

Morning Gabriel. What is your SQL query? When there is no "?" in it you do not need to do input mapping and it is not available. If it contains "?", you need to "Click Retrieve to populate this field."(please see the Help info I pasted last time) to generate the Input Schema. Then you can click Input Mapping.

According to the example in the Help info, "if the SQL query is : select email from test.users where telephone = ? and name = ?, the generated schema would be: {"title":"ReadDbTaskInputSchema","properties":{"inputParam2":{"type":"string"},"inputParam1":{"type":"string"}},"type":"object"}, where inputParam1 represents the first question mark, and inputParam2 represents the second question mark. " Then you should be able to see these two variables on the Input Mapping page and draw the line to do the mapping to pass value to them and they will be used in the query in real time.

If there is still a problem please provide your SQL query, the generated input schema (click the Show button to see it) and Input mapping page.

Thanks Bill, now I understand the configuration flow.

Take a look at the first screen capture with the no variable SQL statement that works and retrieves successfully the schema.

When I add the question mark, the retrieve fails. I remember reading last night something and I just checked the Avaya Engagement Designer Reference and I forgot to set a customer parameter to for my JDBC Source:
Name: generateSimpleParameterMetadata; Value: true

I suspect that this is why the variable query is not working.

I am rebooting my Breeze server now that the change has been made...

I will post the result.

Gabriel

Right, you need generateSimpleParameterMetadata to generate the input schema.

Ta da + bang head on table for not doing the RTFM...

Thanks for the insight and patience!!

Gabriel

Good morning everyone,

So I have successfully completed my demo workflow that queries a MySQL database and changes the Called and Calling name & number prior to making the actual call. I spent yesterday' s afternoon trying to figure out whether ED can concatenate 2 variables together (firstName and lastName) for the purpose of using this new string to update the Called name display. I believe that ED cannot accomplish this.

When sitting down with my wife at the restaurant last night (because it was too late for me to to make supper because I was still hacking at this), it dawned on me that the SQL query could do that for me! One minute with her iPad confirmed this.

So I can use the following SQL query to accomplish this:
select employeeMobile, concat(firstName, ' ', lastName) from gd.maintenanceStaff where buildingId = ? limit 1;

When I tried to use this in the Read from DB task, the Output Mapping now complains that the Schema is not valid as per the attached and you can note that it complains specifically on the concat() function.

Do I conclude that ED only expects fields as the output of the SQL query??

Thoughts?

Gabriel

PS: Yes I know that I use use a field where both firstName and lastName are already concatenated together but I do not believe dB folks would do such a thing as it is a waste of space!

You can use ED data mapper in the Input/Output Mapping to do the string manipulation to create a new string, e.g. in your case, "firstName" + " " + "lastName".
so no need to use SQL. Apparently the Output Schema can be created properly with field names only.

Thanks Bill for confirming that data manipulation is possible on the Input/Output Mapping.

Keeping in mind that I am only retrieving 1 resultSet, my error yesterday was to use the wrong index (1 instead of 0). The following is now used on the Output Mapping:
ReadDbTaskOutputSchema["resultSet"][0]["firstName"] + " " + ReadDbTaskOutputSchema["resultSet"][0]["lastName"]

Now my caller see "firstName lastName" as retrieved from the SQL query!!

Regards,

Gabriel

Now onto creating ED tasks for Zang SMS via the new ED SDK issued this week.

Hi Bill,

i have one concern about Read From DB task, Can we call procedures in Read From DB task instead of table.

Thanks,

Hey can you reply to my question

thanks in advance

No, you cannot execute a procedure using the Read DB task.

Thanks for replying.
So how I can use the procedure in Engagement Designer?

Is there any way to access procedure in Engagement Designer

thanks

If you can expose SQL procedures via Web Services then you can use the Call Rest Service task in ED to execute them. Or, you may build your own task (dynamic task, using ED SDK) to execute SQL procedures.