Solved

net access provider | IFS10 | How to get result string

2 years ago
30 December 2021
9 replies
691 views

Userlevel 1

T2S
Do Gooder
5 replies

Dear all,

In my WPF application I want to execute package (as below) to create new Transport Task (TT) in IFS.

Here’s code that I use:

queryStr = "DECLARE " & " "
queryStr = queryStr & " p0_ VARCHAR2(32000) := NULL; " & " "
queryStr = queryStr & " p1_ VARCHAR2(32000) := NULL; " & " "
queryStr = queryStr & " p2_ VARCHAR2(32000) := NULL; " & " "
queryStr = queryStr & " p3_ VARCHAR2(32000) := 'FIXED_DB'||chr(31)||'TRUE'||chr(30); " & " "
queryStr = queryStr & " p4_ VARCHAR2(32000) := 'DO'; " & " "
queryStr = queryStr & "BEGIN " & " "
queryStr = queryStr & "IFSAPP.Transport_Task_API.NEW__( p0_ , p1_ , p2_ , p3_ , p4_ ); " & " "
queryStr = queryStr & "commit; " & " "
queryStr = queryStr & "DBMS_OUTPUT.PUT_LINE(ifsapp.client_sys.get_item_value('TRANSPORT_TASK_ID', p3_)); " & " "
queryStr = queryStr & "END; " & " "

Any idea how to send output data (newly created TT id) using ifsapp.client_sys.get_item_value('TRANSPORT_TASK_ID', p3_) to .net variable?

Objective is to present this number on .net user form.

I want to avoid additional sql query to get TT id.

Many thanks,

icon

Best answer by T2S 4 January 2022, 11:41

View original

This topic has been closed for comments

9 replies

Userlevel 6

+12

Hello,

Bear in mind I know very little about Transport Tasks, and I find the way they are implemented a little odd (as I often do with certain IFS aspects. *smile*).

It looks like when a TT is created in IEE it creates an empty header record to start with, then detail lines are added to it. If a single line exists for a TT (a single part moving to and from locations), that part and location information are stored right on the TT header line. If multiple detail lines exist, then "..." starts to appear on the header line for fields such as part number and to/from location numbers.

I verified that just passing the single field "FIXED_DB" will successfully create the TT header line (as you probably already know). And the TT header line has no freeform field that you could try to use to place some sort of unique key to later retrieve the record. I tried adding a non-existent PART_NO and the procedure still succeeded in creating a TT header line, but PART_NO remained null.

(I am just thinking out loud here to wrap my head around data is structured.)

So, you want to add a new TT header record and get back the auto-incremented ID, and there is nothing on the record that you can use to make the record unique (other than the system-generated Transport_Task_Id). I see a few options here, but am not sure what will work best for you.

First, you could try to get output from the anonymous PL/SQL block, as outlined here: https://stackoverflow.com/questions/27613046/bind-in-and-out-parameters-to-anonymous-pl-sql. I am not sure if that will work, as anonymous blocks are not really meant to return output. You can explore that thread, though, and see if anything jumps out as useful.

Second, you could try just grabbing the highest Transport_Task_ID in the table after a successful insert. Since the ID auto-generates, the one you just created should have the highest number. I do not know if this is suitable for you, though, if for example you are going to have a LOT of TTs getting created at the same time.

Third, and similar the second option, you could try getting the last-used value of the Transport Task ID sequence right after you run the PL/SQL block to create the new row:

SELECT Last_Number - 1 AS Last_Used_ID
    FROM User_Sequences
    WHERE Sequence_Name = 'TRANSPORT_TASK_ID'

However, I am not sure how this will work across user sessions, so it could suffer from the same contention issue if a lot of folks are adding these rows at the same time.

Fourth and finally, you could write the SQL that is now an anonymous block as a packaged method in a custom API package under the IFSAPP schema. That would allow output parameters that I believe the access provider should let you to get back (I know output parameters work with standard ODBC calls from .NET, as that is what I leverage via the Oracle Instant Client [OIC] layer.)

The last option is probably the most direct and controllable. But the third option could work as well if you can test it out in a multi-user, high-volume environment and be sure last-used sequence IDs aren't getting crossed.

Let us know what you figure out! Good luck, and Happy New Year!

Thanks,
Joe Kaufman

Userlevel 1

T2S
Author
Do Gooder
5 replies
2 years ago
3 January 2022

Hi Joe,

Thanks for your reply and detailed description.

I am aware that my initial message conatins only part of the code that creates TT header.

My key requirement was to get newly created TT id directly from attribute (p3_) out of the current execution.
I wanted to avoid running additional select query to fetch this number mainly due to the fact that max id can be generated by another user. I assumed that many users may work at the same time.
Once I have TT id I launch instruction that assignes lines to TT.

Nevertheless I approached this issue as described in IFS’ documentation.

Here is the link: https://docs.ifs.com/techdocs/Foundation1/050_development/040_core_server/010_access_providers_dev/020_ap_dotnet/050_examples.htm
1. Go to paragraph: PLSQL Examples
2. Click on “Commands” - this is where you can find sample code.

Many thanks,

Piotr

Userlevel 6

+12

Piotr,

If you want to avoid another query (and since getting the TT ID back that way is not reliable in a multi-user environment), that leaves the fourth option as the only one that will probably work for you. I do not know of a way to get an output variable back from an anonymous block of code.

That being said, I am not sure why you can’t call Transport_Task_API.New__() directly and get output back from that? The documentation you link to describes how to bind to the input and output parameters, and you shouldn’t really have to use an anonymous block at all. From the link you posted, a little deeper in are examples:

http://ifstechdocs10.azurewebsites.net/Foundation1/050_development/040_core_server/010_access_providers_dev/020_ap_dotnet/code_examples/plsqlcommands.cs.txt

Have you tried something like that, with a direct call to the TT API’s “New” method? The TRANSPORT_TASK_ID comes back in the fourth parameter after a successful insert. That is the only way I can see of you getting the ID back without needing to do an additional query…

Good luck!

Joe Kaufman

Userlevel 1

T2S
Author
Do Gooder
5 replies
2 years ago
3 January 2022

Joe,

Thank you for your effort and engagement.

I have this issue sorted, therefore I shared link to documentation with you.

Kind regards,

Piotr

Userlevel 6

+12

Piotr,

So you were able to get the bound variables working? I have been playing around and have code that works by using the “base method” scenario, as well as calling the New__ method directly. I am seeing the TT ID in the returned attr string.

I will post some sample code in a bit, but I have a problem with my code… Even though no errors are thrown and a new Transport_Task_Id is returned, no data is being written to IFS! Can you post your code that works so I can see what I am doing wrong? Is there some sort of COMMIT call I am missing? (Like I said, some code in a bit to provide examples for anyone else who might read this thread since Access Provider examples are fairly sparse...).

Thanks,

Joe Kaufman

Userlevel 6

+12

Here is example code to add a new Transport_Task using a straight method call with bound parameters. It gets back the attr string and pulls out the value of TRANSPORT_TASK_ID:

try
{
	// It all starts with a connection string. We connect to the IFS Application server, not directly to the Oracle database!
	string connString = "https://<server>:<port>/";
	string userId = <username>;
	string password = <password>;
	// Establish connection. Set CatchExceptions = false because we are handling any errors here with the try..catch.
	FndConnection conn = new FndConnection(connString, userId, password);
	conn.CatchExceptions = false;
	// Set up bound variables.
	FndBindVariable p0Info = new FndBindVariable(FndBindVariableDirection.Out, "INFO", new FndTextAttribute());
	FndBindVariable p1ObjId = new FndBindVariable(FndBindVariableDirection.Out, "OBJID", new FndTextAttribute());
	FndBindVariable p2ObjVersion = new FndBindVariable(FndBindVariableDirection.Out, "OBJVERSION", new FndTextAttribute());
	FndBindVariable p3Attr = new FndBindVariable(FndBindVariableDirection.InOut, "ATTR", new FndTextAttribute());
	FndBindVariable p4Action = new FndBindVariable(FndBindVariableDirection.In, "ACTION", new FndTextAttribute("DO"));
	// Set up command to execute with bound variables embedded in the method call to add a new Transport Task.
	string runCommand = "Transport_Task_API.New__(:INFO, :OBJID, :OBJVERSION, :ATTR, :ACTION)";
	FndPLSQLCommand cmdNewTransportTask = new FndPLSQLCommand(conn, runCommand);
	// Bind the variables to the command.
	cmdNewTransportTask.BindVariables.Add(p0Info);
	cmdNewTransportTask.BindVariables.Add(p1ObjId);
	cmdNewTransportTask.BindVariables.Add(p2ObjVersion);
	cmdNewTransportTask.BindVariables.Add(p3Attr);
	cmdNewTransportTask.BindVariables.Add(p4Action);
	// Execute the command.
	cmdNewTransportTask.ExecuteNonQuery();
	// Evaluate the bound output variable to get Transport_Task_Id.
	FndAttributeString attr = new FndAttributeString(p3Attr.Value.ToString());
	MessageBox.Show(attr.GetValue("TRANSPORT_TASK_ID"));
}
catch (FndException err)
{
	err.Show();
}

Everything works fine, but as I said further up on this thread, no new data is being added to IFS. The Transport_Task_Id continues to increment, but I assume that is because it is auto-generated based on a Sequence that always increases even if data is not saved. Is there a COMMIT I am missing or property that needs to be set?

Here is a more compact version of creating a new Transport_Task that leverages FndPLSQLBaseMethodCall:

try
{
	// It all starts with a connection string. We connect to the IFS Application server, not directly to the Oracle database!
	string connString = "https://<server>:<port>/";
	string userId = <username>;
	string password = <password>;
	// Establish connection. Set CatchExceptions = false because we are handling any errors here with the try..catch.
	FndConnection conn = new FndConnection(connString, userId, password);
	conn.CatchExceptions = false;
	// Use a "base method" call to add a Transport Task (based on example code).
	// FndDataRow must have OBJID and OBJVERSION defined since those values are returned by the base method call.
	FndDataRow newTransportTask = new FndDataRow("TRANSPORT_TASK");
	newTransportTask.Columns.Add("OBJID", FndAttributeType.Text);
	newTransportTask.Columns.Add("OBJVERSION", FndAttributeType.Text);
	// Also add TRANSPORT_TASK_ID so we can get that back.
	newTransportTask.Columns.Add("TRANSPORT_TASK_ID", FndAttributeType.Number);
	// We can now call the NEW method without anything else in the data row because IFS allows an "empty" TT header record.
	FndPLSQLBaseMethodCommand cmd = new FndPLSQLBaseMethodCommand(conn, FndBaseMethodType.New, "TRANSPORT_TASK_API", "New__", newTransportTask, FndBaseMethodAction.Do);
	cmd.ExecuteNonQuery();
	// Get value of TRANSPORT_TASK_ID for the row just added.
	FndNumberAttribute newTransportTaskId = (FndNumberAttribute)newTransportTask["TRANSPORT_TASK_ID"];
	int newId = Convert.ToInt32(newTransportTaskId.GetValue(-1));
	MessageBox.Show("New Transport Task ID: " + newId.ToString());
}
catch (FndException err)
{
	err.Show();
}

It works exactly the same as the code above it, and I get a new TT ID back, but, again, no data is actually being saved to IFS.

If anyone knows why the data would not be saving, please let me know! I would like to have a full understanding of how the Access Provider works to both read and write data so I can use it more in the future. The Access Provider is pretty cool, especially since it does not require an additional Oracle client layer to be installed (e.g. Oracle Instant Client).

Thanks,

Joe Kaufman

Userlevel 6

+12

OK, got the data to commit via an explicit command after the New__ call:

FndPLSQLCommand cmdCommit = new FndPLSQLCommand(conn, "COMMIT");
cmdCommit.ExecuteNonQuery();

I assume that commits all pending changes for the connection. I am just not used to having to do an explicit commit, as it is not required when I do my ODBC-based calls. Though, I do wrap those method calls in a BEGIN..END;, so perhaps there is some sort of implicit committing going on that I just don’t understand fully.

@T2S , I am still interested in seeing your working code so that I have learned all I can about the Access Provider in this scenario. This has been a great discussion, so thanks for posting!

Thanks,

Joe Kaufman

Userlevel 1

T2S
Author
Do Gooder
5 replies
2 years ago
4 January 2022
Answer

Here is the final solution. Crucial rows (also in red) are between commented lines.

Dim queryStr As String
Dim IFSconn As New Global.Ifs.Fnd.AccessProvider.FndConnection
Dim query As New Global.Ifs.Fnd.AccessProvider.PLSQL.FndPLSQLCommand
Dim rep_response As New Global.Ifs.Fnd.Data.FndDataTable
Dim dumy As New Global.Ifs.Fnd.Buffer.FndBuffer
Dim dumyRows As New Global.Ifs.Fnd.Data.FndDataRowCollection
Dim dumyRow As New Global.Ifs.Fnd.Data.FndDataRow

Dim TTid As String

'----------------------------------------------------------------------------------------------
Dim OUTPUT As New Global.Ifs.Fnd.Data.FndTextAttribute()
'----------------------------------------------------------------------------------------------

IFSconn.ConnectionString = (IfsConStr)
IFSconn.SetCredentials(userId_, psd_)
IFSconn.CatchExceptions = False

queryStr = "DECLARE "
queryStr = queryStr & " p0_ VARCHAR2(32000) := NULL; "
queryStr = queryStr & " p1_ VARCHAR2(32000) := NULL; "
queryStr = queryStr & " p2_ VARCHAR2(32000) := NULL; "
queryStr = queryStr & " p3_ VARCHAR2(32000) := 'FIXED_DB'||chr(31)||'TRUE'||chr(30); "
queryStr = queryStr & " p4_ VARCHAR2(32000) := 'DO'; "
queryStr = queryStr & "BEGIN "
queryStr = queryStr & "IFSAPP.Log_SYS.Init_Debug_Session_('pl'); "
queryStr = queryStr & "IFSAPP.Transport_Task_API.NEW__( p0_ , p1_ , p2_ , p3_ , p4_ ); "
queryStr = queryStr & "commit; "
'----------------------------------------------------------------------------------------------
queryStr = queryStr & ":OUTPUT := ifsapp.client_sys.get_item_value('TRANSPORT_TASK_ID', p3_); "
'----------------------------------------------------------------------------------------------
queryStr = queryStr & "END; "

'On Error Resume Next
query.Connection = IFSconn
query.CommandText = queryStr
'----------------------------------------------------------------------------------------------
query.BindVariables.Add(New Ifs.Fnd.AccessProvider.PLSQL.FndBindVariable(Ifs.Fnd.AccessProvider.PLSQL.FndBindVariableDirection.Out, "OUTPUT", OUTPUT))
'----------------------------------------------------------------------------------------------
query.ExecuteNonQuery()

TTid = query.BindVariables("OUTPUT").Value.ToString

Userlevel 6

+12

Piotr,

Thanks for posting the final solution! You should definitely mark it as the answer so others are all the more likely to find it. You got bound variables working with an anonymous PL/SQL block -- that’s fantastic! I had no idea it could work that way, embedded inside a block of code. This seems really powerful, and allows the COMMIT to be added more easily (as opposed to my methodology where a COMMIT needs to be explicitly called against the connection/session -- that still feels wonky to me).

Great work! Now we just need to get you coding in C#… (Just kidding -- VB.NET works just as well!)

I will definitely know what to search for on this forum the next time I need to run a block of code such as this!

Thanks,

Joe Kaufman

Did you find what you're looking for? If not:

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded