Using HANDLE to copy records from PROD to TEST

[radi8]

I am trying to create an app to copy records from PROD to TEST. According to the JDE documentation, the best method of doing this is to use a HANDLE for table IO and to associate 1 handle to the PROD environment and another to the TEST environment.

This is a very basic test to check connectivity. I am setting the F4201 handle to point to the PROD environment, and testing to see if an ORDER NUMBER (DOCO) exists as entered into a form control.

When I set this up, I am getting an null error. My code is as follows:
<font class="small">Code:</font><hr /><pre>
evt_FROM_TableHandleF4201
evt_FROM_Environment
evt_TO_Environment
evt_TO_TableHandleF4201
evt_OrderType
// this is an option button test
0001 If FC Sales Order is equal to "1"
// set up the FROM and TO environments as listed in P0094
0002 VA evt_FROM_Environment = "JPD811"
0003 VA evt_TO_Environment = "JPY811"
0004 VA evt_FROM_TableHandleF4201 = F4201.Open Handle
Invoice, etc.)
// this line generates the error... see error log below
0005 F4201(VA evt_FROM_TableHandleF4201).Select
FC Order Number = TK Document (Order No, Invoice, etc.)
0006 F4201(VA evt_FROM_TableHandleF4201).Fetch Next
VA evt_OrderType <- TK Order Type
0007 If SV File_IO_Status is equal to CO SUCCESS
0008 FC ->Test Results <- = "Order Exists in Production!"
0009 Set Edit Control Color(FC Order Number, <Red>)
0010 Set Control Error(FC Order Number, "NSIDOCO")
0011 Enable Control(FC Copy Data)
0012 Else
0013 FC ->Test Results <- = "Invalid Order Number!"
0014 Set Edit Control Color(FC Order Number, <Default>)
0015 Clear Control Error(FC Order Number)
0016 Disable Control(FC Copy Data)
0017 End If
0018 F4201(VA evt_FROM_TableHandleF4201).Close
0019 Else
0020 End If
</pre><hr />

The error in the log files is as follows:
<font class="small">Code:</font><hr /><pre>
P59COPY_W59COPYB, at ER Line = 0 If statement: evaluates TRUE.
26 Mar 2009 09:37:06,892 [APP ] RICK - [RUNTIME] P59COPY_W59COPYB, at ER Line = 1 Executed Assignment : VA[2] = JPD811{ LI[String] = JPD811 }
26 Mar 2009 09:37:06,892 [APP ] RICK - [RUNTIME] P59COPY_W59COPYB, at ER Line = 2 Executed Assignment : VA[3] = JPY811{ LI[String] = JPY811 }
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] SELECT * FROM SY811.F0093 WHERE (LLUSER = ? )
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] SQL statement parameter marker values are :
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] Param1 : RICK_Types.VARCHAR,
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] SELECT T0.RLFRROLE,T0.RLFUROLE1,T1.AUROLEDESC,T0.RLEXPIRDATE,T1.AUSEQNO,T1.AULNGP,T1.AUUSER FROM SY811.F95921 T0, SY811.F00926 T1 WHERE ((T0.RLTOROLE = ? AND EXISTS( SELECT T0.LLLL FROM (SY811.F0093 T0 INNER JOIN SY811.F00941 T1 ON T0.LLLL = T1.LMLL ) WHERE ((T1.LMENHV01 = ? AND T1.LMPATHCD IN ( ? ) AND T0.LLUSER = RLFRROLE)) ) AND (T1.AULNGP = ? OR T1.AULNGP = ? ) AND T0.RLEFFDATE <= ? AND (T0.RLEXPIRDATE = ? OR T0.RLEXPIRDATE > ? ))) AND (T0.RLFRROLE = T1.AUUSER) ORDER BY T1.AUSEQNO DESC
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] SQL statement parameter marker values are :
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] Param1 : RICK_Types.VARCHAR,
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] Param2 : 1_Types.VARCHAR,
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] Param3 : PY811_Types.VARCHAR,
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] Param4 : _Types.VARCHAR,
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] Param5 : _Types.VARCHAR,
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] Param6 : 109085_Types.INTEGER,
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] Param7 : 0_Types.INTEGER,
26 Mar 2009 09:37:06,939 [APP ] RICK - [JDBJ] Param8 : 109085_Types.INTEGER,
26 Mar 2009 09:37:07,033 [APP ] RICK - [RUNTIME] F4201.OPENHANDLE | TABLE | false | 0 | VA[1] | VA[2]
26 Mar 2009 09:37:07,033 [APP ] RICK - [RUNTIME] F4201.TABLESELECT | HANDLE | false | 1 | VA[1] | null
IN: DOCO | FC[32]
FILTER: DOCO | =
26 Mar 2009 09:37:07,033 [APP ] RICK - [RUNTIME] Exception occured while executing the TableIOStatement.execute(): | Form Name 59COPY_W59COPYB, at ER Line = 5, Table Name :F4201. This ER statement is ignored. The Exception message: java.lang.NullPointerException
</pre><hr />

I followed the documentation from Oracle (doc ID: 639024.1) but cannot determine what I am doing wrong. can anyone please help?

 

[JDEAT]

Hi,
I miss the part where are you opening the handle.
First you have to create a DD Element for the handle eg HF4201 and in your code you have to open the handle before you can do any table IO.
At the end of your code don't forget to close the handle!
Let me know if you need more Infos on that.
Regards

 

[JDE User11]

There are also some other methods you could use: I've used Access and ODBC connections to PROD and TEST to copy records. When that wasn't available I've used table conversions to copy data from Prod to Test.

I am interested to see how this method works.

Steven

 

[radi8]

Thanks for the quick reply's!

I am using the DD HF4201 for this test. The HF4201 is a JDE defined handle, so I did not think I would need to create a new one.

I was hoping to get this to work because there are times when I need to transfer specific records from PROD to TEST (SO, PO, and WO), and I wanted to learn this functionality within JDE. After all, it is only a pointer to a database table.

 

[JDEAT]

Hi,

again, i miss the open handle statement in your code:

something like...
VA evt_TableHandleF4201_HF4201 = F4201.Open Handle

 

[radi8]

[ QUOTE ]
Hi,

again, i miss the open handle statement in your code:

something like...
VA evt_TableHandleF4201_HF4201 = F4201.Open Handle

[/ QUOTE ]

line 0004

 

[dss01]

Just a guess...Does ur Jde user Id have access rights to PROD?
may be you can first try for DV to PY...