We are almost done with implementing Midretail, I have been extremely busy coding and testing. The SQL works beautifully and is absolutely amazing. Learning embedded SQL and RPG Free has been exciting and challenging. I would have never been able to pull this off without the help of Google and all the wonderful people posting hints, tricks and code examples. I hope to find time to pay it forward and post more snip-its of code I created for interfacing the systems.
Special thanks to -
www.scottklement.com/
www.itjungle.com
www.iprodeveloper.com
www.code400.com
www.rpg-xml.com
www.bmeyers.net
www.ilerpgprogramming.com
www.winautomation.com
and many more....
I am starting to look forward to my new position as EDI Lead/Developer at a large retailer here in Jacksonville. I am hopeful for a long term relationship with challenging opportunities to work with different hardware / software platforms in a fast paced environment.
~Richard
A handful of patience is worth more than a bushel of brains. ~Dutch Proverb
Showing posts with label WDSC. Show all posts
Showing posts with label WDSC. Show all posts
Thursday, June 7, 2012
Thursday, April 26, 2012
Extended contract and new task coming
Great day, contract extended for another 12 weeks. I have been really busy finishing up my current project which I am a week behind schedule. I hope to make up some time this weekend. The heavy coding is completed just need to tie up the loose ends and automate.
I am using Winautomation on the Windows server to watch for data in folders and initiate jobs on the iSeries based on responses from the jobs executed on the Windows server.
The next project sounds exciting, creating a data warehouse on the iSeries. I have never had the chance to work on putting together a data warehouse but understand the concepts. I may take a closer look at what Rodin has to offer.
Any other suggestions will be greatly appreciated.
Have your best day,
Richard
Try not to become a man of success, but rather try to become a man of value. ~Albert Einstein
I am using Winautomation on the Windows server to watch for data in folders and initiate jobs on the iSeries based on responses from the jobs executed on the Windows server.
The next project sounds exciting, creating a data warehouse on the iSeries. I have never had the chance to work on putting together a data warehouse but understand the concepts. I may take a closer look at what Rodin has to offer.
Any other suggestions will be greatly appreciated.
Have your best day,
Richard
Try not to become a man of success, but rather try to become a man of value. ~Albert Einstein
Tuesday, April 24, 2012
RPGLE SQL0511 for Update error.
Just when I think I have a handle on RPGLE Embedded SQL, SQL reaches out and slaps me silly.
I created a daily sales retrieval program using RPGLE Free and Embedded SQL which writes XML data to the /QNTC file system (windows share). I wanted to update the status and date field after I write out the XML. I thought a simple to do, I have already done something similar with no problem. Then.....
Explanation:
The result table of the SELECT or VALUES statement cannot be updated.
On the database manager, the result table is read-only if the cursor is based on a VALUES statement or the SELECT statement contains any of the following:
The statement cannot be processed.
User response:
Do not perform updates on the result table as specified.
Federated system users: isolate the problem to the data source failing the request (see the problem determination guide for procedures to follow to identify the failing data source). If a data source is failing the request, examine the restrictions for that data source to determine the cause of the problem and its solution. If the restriction exists on a data source, see the SQL reference manual for that data source to determine why the object is not updatable.
sqlcode: -511
sqlstate: 42829
Oh me oh my. I don’t have any more time to spend on this and decided to skip the update of the status and data field at this time. The program is called remotely from a batch file that will also run a step that processes the XML into the Midretail allocation program. I will capture the result of the update to Midretail in the batch program and if successful will send another remote command to update the status and date of the records processed.
Exec SQL declare mainCursor cursor
for Select stat, vstr, vdte, sum(vqty), vcls, vven, +
vsty, vclr, vstp, vdtp
from MidSalesPF
where vdte = :yesterday
group by stat, vstr, vdte, vcls, vven, vsty, vclr, vstp, vdtp
order by vstr, vdte, vdte, vcls, vven, vsty, vclr, vstp;
Exec SQL open mainCursor; // SQL open cursor
Exec SQL fetch next from mainCursor // Load data Structure
into :mainds;
exsr ClrHdr; // Clear or create XML header record
dow sqlstt = '00000' ; // Continue if no SQL error [DO Loop]
exsr Load_SSColSR; // Write detail XML records to IFS
Exec SQL fetch next from mainCursor // Get next record
into :mainds;
enddo ;
Exec SQL close mainCursor; // Close SQL file
I created a daily sales retrieval program using RPGLE Free and Embedded SQL which writes XML data to the /QNTC file system (windows share). I wanted to update the status and date field after I write out the XML. I thought a simple to do, I have already done something similar with no problem. Then.....
SQL0511
SQL0511N
The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified.Explanation:
The result table of the SELECT or VALUES statement cannot be updated.
On the database manager, the result table is read-only if the cursor is based on a VALUES statement or the SELECT statement contains any of the following:
- The DISTINCT keyword
- A column function in the SELECT list
- A GROUP BY or HAVING clause
- A FROM clause that identifies one of the following:
- More than one table or view
- A read-only view
- An OUTER clause with a typed table or typed view
- A set operator (other than UNION ALL).
- A FROM clause that identifies one of the following:
- More than one table or view
- A read-only view
- An OUTER clause with a typed table or typed view
- A data change statement
The statement cannot be processed.
User response:
Do not perform updates on the result table as specified.
Federated system users: isolate the problem to the data source failing the request (see the problem determination guide for procedures to follow to identify the failing data source). If a data source is failing the request, examine the restrictions for that data source to determine the cause of the problem and its solution. If the restriction exists on a data source, see the SQL reference manual for that data source to determine why the object is not updatable.
sqlcode: -511
sqlstate: 42829
Oh me oh my. I don’t have any more time to spend on this and decided to skip the update of the status and data field at this time. The program is called remotely from a batch file that will also run a step that processes the XML into the Midretail allocation program. I will capture the result of the update to Midretail in the batch program and if successful will send another remote command to update the status and date of the records processed.
Exec SQL declare mainCursor cursor
for Select stat, vstr, vdte, sum(vqty), vcls, vven, +
vsty, vclr, vstp, vdtp
from MidSalesPF
where vdte = :yesterday
group by stat, vstr, vdte, vcls, vven, vsty, vclr, vstp, vdtp
order by vstr, vdte, vdte, vcls, vven, vsty, vclr, vstp;
Exec SQL open mainCursor; // SQL open cursor
Exec SQL fetch next from mainCursor // Load data Structure
into :mainds;
exsr ClrHdr; // Clear or create XML header record
dow sqlstt = '00000' ; // Continue if no SQL error [DO Loop]
exsr Load_SSColSR; // Write detail XML records to IFS
Exec SQL fetch next from mainCursor // Get next record
into :mainds;
enddo ;
Exec SQL close mainCursor; // Close SQL file
~Richard
The road to success is dotted with many tempting parking places. ~Author Unknown
Friday, April 20, 2012
DB2 SQL Where Timestamp field = Date
My SQL challenge this morning required me to write a SQL select to select a date from a Timestamp field. I have started playing around with WDSC 7.0 Data perspective. I did not take to long to figure out, I am really digging SQL on the iSeries!
IVDTE is my Timestamp defined field in MIDINVPF.
SELECT *
FROM rbryant.MIDINVPF
where CHAR(DATE(ivdte), ISO) = '2012-04-12'
~Richard
People who look through keyholes are apt to get the idea that most things are keyhole shaped. ~Author Unknown
IVDTE is my Timestamp defined field in MIDINVPF.
SELECT *
FROM rbryant.MIDINVPF
where CHAR(DATE(ivdte), ISO) = '2012-04-12'
~Richard
People who look through keyholes are apt to get the idea that most things are keyhole shaped. ~Author Unknown
Wednesday, March 21, 2012
Finish the day with a win.
I have my SQLRPGLE daily sales program to working and loaded sales into the MIDRetail application with a few errors to chase. Only 145 errors in over 45,000 records processed. I’ll be on the hunt in the morning. I also found out that my source data file is missing April thru July sales, don’t think I’ll get 52 weeks of sales from that file. Rut Ro!
I got another service call around 6:00PM, the user could not SNDST from the iSeries to a Domino server. The user is a Unix admin and knew a little about the iSeries. I walked him through checking the TCP setting and Directory entries. Found the problem in the SMTPA configuration, wrong mail router specified.
Always great to finish off the day with a win and a positive email.
Hi Xxx,
Our AS400 issue is solved quickly by Richard.
I really want to you know how happy I am about Richard's work.
He is very knowledgeable about AS400 system and can quickly pin point the
problem with no time.
I would like to say " Thank you very much for your help"
Best Regards,
xxxxxx xxxxxxxxxx
Senior UNIX System Administrator
I am not sure how this has happened, my day work of iSeries operations has become my night job and my night work of programming has become my day job.
~Richard
Patience is the companion of wisdom. ~St. Augustine
I got another service call around 6:00PM, the user could not SNDST from the iSeries to a Domino server. The user is a Unix admin and knew a little about the iSeries. I walked him through checking the TCP setting and Directory entries. Found the problem in the SMTPA configuration, wrong mail router specified.
Always great to finish off the day with a win and a positive email.
Hi Xxx,
Our AS400 issue is solved quickly by Richard.
I really want to you know how happy I am about Richard's work.
He is very knowledgeable about AS400 system and can quickly pin point the
problem with no time.
I would like to say " Thank you very much for your help"
Best Regards,
xxxxxx xxxxxxxxxx
Senior UNIX System Administrator
I am not sure how this has happened, my day work of iSeries operations has become my night job and my night work of programming has become my day job.
~Richard
Patience is the companion of wisdom. ~St. Augustine
Embedded SQL SQLSTT 42703 and 42618 errors...
Great news, found out yesterday that my contract is extended to the end of May. The company has decided to keep me on to help with implementation of the MIDRetail.
I ran into a problem with my daily sales XML output. I added date selection as a host variable to the Where clause in my SQL Select statements. I ran the program and no output, WTF!
I ran the program in debug and found SQLSTT is throwing an error 42703. After browsing Google I determined that the host variable is the Where clause is suspect. I used sub-procedures to make the SQL as generic as possible. I have a couple of SQL statements in the code and am changing the statements by evaluating a temporary field SQLV as needed.
* Standalone Variables
d sqlV s 32000a varying varying as needed
d sql1 s 32000a varying Store/style/color
d sql2 s 32000a varying Update sales type
d sql3 s 32000a varying Store/style/color/sz
begsr CrtSQLSR;
// Daily Sales summerized by store, style, color = MAINDS
sql1 = ('Select stat, vstr, vdte, sum(vqty), vcls, vven, +
vsty, vclr, vstp +
from MidSalesPF +
where vdte = :yesterday and 1 = 1 +
group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr +
order by vstr, vdte, vdte, vstp, vcls, vven, vsty, vclr' );
// Daily Sales summerized by store, style, color, size = MAIN1DS
sql3 = ('Select stat, vstr, vdte, sum(vqty), vcls, vven, +
vsty, vclr, vsiz, vstp +
from MidSalesPF +
where vdte = :yesterday and 1 = 1 +
group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr, +
vsiz +
order by vstr, vdte, vstp, vcls, vven, vsty, vclr, vsiz' );
endsr;
-------------------------------------------------
begsr Day_SscsizSR;
clear sqlV; // Clear temp field
sqlV = sql3; // SQL statement to run
first = 'Y'; // First pass flag
declare(); // SQL declare
openCursor(); // SQL open cursor
Exec SQL fetch next from mainCursor //Load data Structure
into :main1ds;
blah,blah,blah.......
------------------------------------------------
p declare b
d declare pi
c/exec sql
c+ declare mainCursor cursor
c+ for mainStatement
c/end-exec
c/exec sql
c+ prepare mainStatement
c+ from :sqlV My problem child
c/end-exec
p declare e
*-------------------------------------------------------------------
p openCursor b
d openCursor pi
c/exec sql
c+ open mainCursor
c/end-exec
p openCursor e
*-------------------------------------------------------------------
p closeCursor b
d closeCursor pi
c/exec sql
c+ close mainCursor
c/end-exec
p closeCursor e
------------------------------------------------------------------------------------------------------
From what I can tell SQL does not like a variable inside a variable. The Where clause :yesterday inside :SQLV.
So I took it all apart and got rid of the sub-procedures.
begsr Day_SscsizSR;
Exec SQL declare mainCursor2 cursor
for Select stat, vstr, vdte, sum(vqty), vcls, vven, +
vsty, vclr, vsiz, vstp
from MidSalesPF
where vdte = :yesterday
group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr, vsiz
order by vstr, vdte, vdte, vstp, vcls, vven, vsty, vclr, vsiz;
Exec SQL open mainCursor2; // SQL open cursor
Exec SQL fetch next from mainCursor2 //Load data Structure
into :main1ds;
blah,blah,blah........
So I am on the hunt for why my XML is no longer being written. I must have cut something out by mistake while getting rid of the sub-procedures.
~Richard
I ran into a problem with my daily sales XML output. I added date selection as a host variable to the Where clause in my SQL Select statements. I ran the program and no output, WTF!
I ran the program in debug and found SQLSTT is throwing an error 42703. After browsing Google I determined that the host variable is the Where clause is suspect. I used sub-procedures to make the SQL as generic as possible. I have a couple of SQL statements in the code and am changing the statements by evaluating a temporary field SQLV as needed.
* Standalone Variables
d sqlV s 32000a varying varying as needed
d sql1 s 32000a varying Store/style/color
d sql2 s 32000a varying Update sales type
d sql3 s 32000a varying Store/style/color/sz
begsr CrtSQLSR;
// Daily Sales summerized by store, style, color = MAINDS
sql1 = ('Select stat, vstr, vdte, sum(vqty), vcls, vven, +
vsty, vclr, vstp +
from MidSalesPF +
where vdte = :yesterday and 1 = 1 +
group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr +
order by vstr, vdte, vdte, vstp, vcls, vven, vsty, vclr' );
// Daily Sales summerized by store, style, color, size = MAIN1DS
sql3 = ('Select stat, vstr, vdte, sum(vqty), vcls, vven, +
vsty, vclr, vsiz, vstp +
from MidSalesPF +
where vdte = :yesterday and 1 = 1 +
group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr, +
vsiz +
order by vstr, vdte, vstp, vcls, vven, vsty, vclr, vsiz' );
endsr;
-------------------------------------------------
begsr Day_SscsizSR;
clear sqlV; // Clear temp field
sqlV = sql3; // SQL statement to run
first = 'Y'; // First pass flag
declare(); // SQL declare
openCursor(); // SQL open cursor
Exec SQL fetch next from mainCursor //Load data Structure
into :main1ds;
blah,blah,blah.......
------------------------------------------------
p declare b
d declare pi
c/exec sql
c+ declare mainCursor cursor
c+ for mainStatement
c/end-exec
c/exec sql
c+ prepare mainStatement
c+ from :sqlV My problem child
c/end-exec
p declare e
*-------------------------------------------------------------------
p openCursor b
d openCursor pi
c/exec sql
c+ open mainCursor
c/end-exec
p openCursor e
*-------------------------------------------------------------------
p closeCursor b
d closeCursor pi
c/exec sql
c+ close mainCursor
c/end-exec
p closeCursor e
------------------------------------------------------------------------------------------------------
From what I can tell SQL does not like a variable inside a variable. The Where clause :yesterday inside :SQLV.
So I took it all apart and got rid of the sub-procedures.
begsr Day_SscsizSR;
Exec SQL declare mainCursor2 cursor
for Select stat, vstr, vdte, sum(vqty), vcls, vven, +
vsty, vclr, vsiz, vstp
from MidSalesPF
where vdte = :yesterday
group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr, vsiz
order by vstr, vdte, vdte, vstp, vcls, vven, vsty, vclr, vsiz;
Exec SQL open mainCursor2; // SQL open cursor
Exec SQL fetch next from mainCursor2 //Load data Structure
into :main1ds;
blah,blah,blah........
So I am on the hunt for why my XML is no longer being written. I must have cut something out by mistake while getting rid of the sub-procedures.
~Richard
Wednesday, January 11, 2012
Job market heating up....
After a really slow first week I am becoming optimistic on the prospects of finding a new job. I am currently partnered up with a services company for remote administration and chasing another one. I have not seen any work yet buy I am hopeful. I have an interview for a two month developer contract position tomorrow, I hope it will go well. Several other possibilities are floating but all are contract or contract to hire.
A few days ago I noticed free online education from Stanford. The entire course; video taped lectures, exercises, assignments and handouts are available to the public. So I decided to go back to basics and see if what I learned in the school of hard knocks are comparable to what is being taught.
Integrating the course with Eclipse WDSC 6.0 was challenging and I hope to post how I got it working.
Just got a call from one of my clients for a quick programming job. Should not take me more than fours to code and test. The Pups need dog bones so I better get to work!
Have your best day!
~Richard
A few days ago I noticed free online education from Stanford. The entire course; video taped lectures, exercises, assignments and handouts are available to the public. So I decided to go back to basics and see if what I learned in the school of hard knocks are comparable to what is being taught.
Integrating the course with Eclipse WDSC 6.0 was challenging and I hope to post how I got it working.
Just got a call from one of my clients for a quick programming job. Should not take me more than fours to code and test. The Pups need dog bones so I better get to work!
Have your best day!
~Richard
Subscribe to:
Posts (Atom)