Automation Builder, Execute SQL Statement - returns 0 record

Software technical issues not related to any of the other categories

Moderators: Georges, milos, NiceLabel Support Team

Post Reply
oscarxyz
Newcomer
Posts: 2
Joined: Thu Jun 30, 2016 4:01 pm

Automation Builder, Execute SQL Statement - returns 0 record

Post by oscarxyz » Wed May 03, 2017 12:18 am

For Automation Builder, List of Actions - Action type = Execute SQL Statement:
I connect to an Oracle database.
The SQL code is

begin
select cf$_durability_days, cf$_gtin, cf$_lot_number, cf$_operator, cf$_part_number, cf$_part_description
into :durabilityDays, :gtin12, :lot, :userId, :partNo, :partDescription
from ifsapp.dpf_label_setup_clv
where cf$_site = '01' and cf$_line_id = '2';
end;

When I run the code in Actions (TEST SQL Statement) - The execute result is Successful (green font), but the result message is "Executing the SQL statement returned 0 records." This is a different message than "no matching records" as I have seen that also. This statement has a matching record in the view.

When I run the code in PL/SQL I successfully retrieve the single row of data.

Admittedly, I am not well versed in the format needed within NiceLabel / Automation yet, but the TEST button suggest the format is correct.

I am attempting to retrieve my label variables from this view. All variables are stored in this single record. And then save the results of the query into the label variables.

Can anyone suggest what I might be doing wrong, or a better way to retrieve the data store into the label variables?

Thanks
Luke

User avatar
Saso
NiceLabel
NiceLabel
Posts: 2933
Joined: Mon Sep 04, 2006 8:09 am
Contact:

Re: Automation Builder, Execute SQL Statement - returns 0 re

Post by Saso » Wed Aug 09, 2017 2:26 pm

Hi Luke,

I am not so familiar with the Oracle's SQL commands, but I think the problem is in this part:

Code: Select all

into :durabilityDays, :gtin12, :lot, :userId, :partNo, :partDescription
What if you remove this line?

The result of your SQL query cannot be mapped directly to trigger's variables.
The result is a CSV-formatted data that you have to parse with Automation's filter.

In your case, Execute SQL Statement action would return the data as:

Code: Select all

cf$_durability_days, cf$_gtin, cf$_lot_number, cf$_operator, cf$_part_number, cf$_part_description
value1, value2, value3, value4, value5, value6
The result might contain one or many records.

Then you have to configure a CSV filter that will take this result from Execute SQL Statement, parse it and map values to trigger variables (durabilityDasy, gtin12, etc.)
Saso Fleiser
Senior Technical Product Specialist

Post Reply