<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><span>Jay,</span></div><div><span></span> </div><div><span>I have attached the prc process called getbom<var id="yui-ie-cursor"></var>.prc</span></div><div><span></span> </div><div><span>Just create a 1 field file called ctrl. Field is 1,*</span></div><div><span></span> </div><div><span>Hope this helps.</span></div><div><span></span> </div><div><span>Rick</span></div><div><span></span> </div><div><br></div> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div dir="ltr"> <div style="margin: 5px 0px; padding: 0px; border: 1px solid rgb(204, 204, 204); height: 0px; line-height: 0; font-size: 0px;" class="hr" contentEditable="false" readonly="true"></div> <font size="2"
face="Arial"> <b><span style="font-weight: bold;">From:</span></b> Jay Ashworth <jra@baylink.com><br> <b><span style="font-weight: bold;">To:</span></b> Richard Hane <yoresoft@sbcglobal.net> <br> <b><span style="font-weight: bold;">Sent:</span></b> Sunday, April 14, 2013 10:55 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: Sample fpODBC process<br> </font> </div> <div class="y_msg_container"><br>
Any chance you could repost that, and just include the source code<br>file (the raw file with colon separators) that people could use/edit<br>without having to rekey it all? :-)<br><br>----- Original Message -----<br>> From: "Richard Hane" <<a href="mailto:yoresoft@sbcglobal.net" ymailto="mailto:yoresoft@sbcglobal.net">yoresoft@sbcglobal.net</a>><br>> To: "file Pro Mailing List" <<a href="mailto:filepro-list@lists.celestial.com" ymailto="mailto:filepro-list@lists.celestial.com">filepro-list@lists.celestial.com</a>><br>> Sent: Saturday, April 13, 2013 9:55:01 AM<br>> Subject: Sample fpODBC process<br>> To all those who requested that I post a sample of my ODBC processing,<br>> here it is.<br>> <br>> Couple of things to note:<br>> 1. I run all my processing from a 1 field 1 file called 'ctrl'.<br>> 2. This sample as well as all of my ODBC processing is using the 'Low<br>> Level' method.<br>> 3. I am reading
data from a program written in MS SQL Server. I write<br>> nothing to the SQL database.<br>> 4. If have set up my odbc data source as 'DSN=fp-sqlserver'. This is<br>> set up as a 'user' not a 'system' data source as I want to limit who<br>> can use it. The choice is yours. A word of caution here. If you are<br>> working on a workstation that is Windows 7 but running under a virtual<br>> XP mode (like I do) make sure you set up the 'user' data source under<br>> the XP mode NOT the Win7 mode.<br>> 5. In the processing below the SQL table is called 'PST' and I am<br>> writing to a filepro file called 'BOM'. As you may guess this copies<br>> the full Bill Of Material file from PST to BOM. 78K records in less<br>> than a minute.<br>> <br>> Finally, this is a simple 1 SQL table to 1 fp file with a rather<br>> simple Where condition.<br>> <br>> I wanted to post one I am working on now which is a complex 3 table,
3<br>> INNER JOIN with heavy 3 statement Where condition. However, I am<br>> having some real problems in how filePro handle INNER JOINS and<br>> multiple tables. I just don't get the 'AS' 'FROM' filePro statement I<br>> found in the help files. I will be sending this to the list and<br>> fpsupport to see if they can set me straight.<br>> <br>> So far I have written 7 processes like the one below. Once you get the<br>> format and structure it is simple and the processing speed fast.<br>> <br>> With all that said here is the table. I'll answer any questions you<br>> may have.<br>> <br>> Rick Hane<br>> Controller<br>> Deluxe Stitcher Company Inc<br>> http://www.deluxestitcher.com/<br>> <br>> <br>> LABEL D E F I N E P R O C E S S I N G 5.0.15R9<br>>
────────────────────────────────────────────────────────────────────────────────<br>> 1 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: declare connection(8,.0,g) ◄<br>> 2 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: declare tablename(20,,g) ◄<br>> 3 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: declare queryname(8,.0,g) ◄<br>> 4 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: declare recordset(5,.0,g) ◄<br>> 5 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: declare myquery(250,*,g) ◄<br>> 6 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: declare stemp(250,*,g) ◄<br>> 7 ------- - - - - - - - - - - - - - -
- -<br>> ◄ If: ◄<br>> Then: connection = new ODBC_CONNECTION("DSN=fp-sqlserver;") ◄<br>> 8 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: connection le "0" ◄<br>> Then: show "@Cannot not open a connection to "<connection ◄<br>> 9 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: connection le "0" ◄<br>> Then: msgbox "Last ODBC error:\n" & @odbcerror["1"] & @odbcerror["2"]<br>> ◄<br>> 10 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: tablename = "PST" ◄<br>> 11 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: stemp="Select PST_ParentItemID," ◄<br>> 12 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: stemp=stemp<"PST_CompItemID," ◄<br>> 13 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: stemp=stemp<"PST_QtyPerAssy," ◄<br>> 14 ------- - - - - - - - - - - - - - - -
-<br>> ◄ If: ◄<br>> Then: stemp=stemp<"PST_EffStartDate" ◄<br>> 15 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: stemp=stemp<"from"<tablename ◄<br>> 16 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: stemp=stemp<"WHERE PST_EffStopDate is Null" ◄<br>> 17 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: recordset = new ODBC(connection, stemp) ◄<br>> 18 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: odbc recordset query stemp ◄<br>> 19 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: n(3,.0)=@odbc.recordset["0"]; r(5,.0)="1" ◄<br>> 20 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: odbc recordset getfirst ◄<br>> 21 ------- - - - - - - - - - - - - - - - -<br>> setfld ◄ If: ◄<br>> Then: a(20,*)=@odbc.recordset ("PST_ParentItemID")
◄<br>> 22 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: show "Processing Record"<r<"Part"<a{"" ◄<br>> 23 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: b(20,*)=@odbc.recordset ("PST_CompItemID") ◄<br>> 24 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: q(9,.4)=@odbc.recordset ("PST_QtyPerAssy") ◄<br>> 25 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: d(10,mdyy/)=@odbc.recordset ("PST_EffStartDate") ◄<br>> 26 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: r=r+"1" ◄<br>> 27 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: gosub sendit ◄<br>> 28 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: @odbc.recordset.eof ne "1" ◄<br>> Then: odbc recordset getnext; goto setfld ◄<br>> 29 ------- - - - - - - - - - - - - - - - -<br>> ◄ If:
◄<br>> Then: end ◄<br>> 30 ------- - - - - - - - - - - - - - - - -<br>> sendit ◄ If: a eq "" ◄<br>> Then: return ◄<br>> 31 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: lookup miv = bom r=free -e ◄<br>> 32 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: miv(1)=a;miv(2)=b;miv(3)=q;miv(4)=d ◄<br>> 33 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: write miv ◄<br>> 34 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> Then: return ◄<br>> 35 ------- - - - - - - - - - - - - - - - -<br>> ◄ If: ◄<br>> -------------- next part --------------<br>> An HTML attachment was scrubbed...<br>> URL:<br>> <a href="http://mailman.celestial.com/pipermail/filepro-list/attachments/20130413/c032e05d/attachment.html"
target="_blank">http://mailman.celestial.com/pipermail/filepro-list/attachments/20130413/c032e05d/attachment.html</a><br>> _______________________________________________<br>> Filepro-list mailing list<br>> <a href="mailto:Filepro-list@lists.celestial.com" ymailto="mailto:Filepro-list@lists.celestial.com">Filepro-list@lists.celestial.com</a><br>> Subscribe/Unsubscribe/Subscription Changes<br>> <a href="http://mailman.celestial.com/mailman/listinfo/filepro-list" target="_blank">http://mailman.celestial.com/mailman/listinfo/filepro-list</a><br><br>-- <br>Jay R. Ashworth Baylink <a href="mailto:jra@baylink.com" ymailto="mailto:jra@baylink.com">jra@baylink.com</a><br>Designer The Things I Think
RFC 2100<br>Ashworth & Associates <a href="http://baylink.pitas.com/" target="_blank">http://baylink.pitas.com/</a> 2000 Land Rover DII<br>St Petersburg FL USA #natog +1 727 647 1274<br><br><br></div> </div> </div> </div></body></html>