Sample fpODBC process

Richard Hane yoresoft at sbcglobal.net
Mon Apr 15 10:31:25 PDT 2013 

Jay,
 
I have attached the prc process called getbom.prc
 
Just create a 1 field file called ctrl.  Field is 1,*
 
Hope this helps.
 
Rick
 
 

________________________________
 From: Jay Ashworth <jra at baylink.com>
To: Richard Hane <yoresoft at sbcglobal.net> 
Sent: Sunday, April 14, 2013 10:55 PM
Subject: Re: Sample fpODBC process
  

Any chance you could repost that, and just include the source code
file (the raw file with colon separators) that people could use/edit
without having to rekey it all? :-)

----- Original Message -----
> From: "Richard Hane" <yoresoft at sbcglobal.net>
> To: "file Pro Mailing List" <filepro-list at lists.celestial.com>
> Sent: Saturday, April 13, 2013 9:55:01 AM
> Subject: Sample fpODBC process
> To all those who requested that I post a sample of my ODBC processing,
> here it is.
> 
> Couple of things to note:
> 1. I run all my processing from a 1 field 1 file called 'ctrl'.
> 2. This sample as well as all of my ODBC processing is using the 'Low
> Level' method.
> 3. I am reading data from a program written in MS SQL Server. I write
> nothing to the SQL database.
> 4. If have set up my odbc data source as 'DSN=fp-sqlserver'. This is
> set up as a 'user' not a 'system' data source as I want to limit who
> can use it. The choice is yours. A word of caution here. If you are
> working on a workstation that is Windows 7 but running under a virtual
> XP mode (like I do) make sure you set up the 'user' data source under
> the XP mode NOT the Win7 mode.
> 5. In the processing below the SQL table is called 'PST' and I am
> writing to a filepro file called 'BOM'. As you may guess this copies
> the full Bill Of Material file from PST to BOM. 78K records in less
> than a minute.
> 
> Finally, this is a simple 1 SQL table to 1 fp file with a rather
> simple Where condition.
> 
> I wanted to post one I am working on now which is a complex 3 table, 3
> INNER JOIN with heavy 3 statement Where condition. However, I am
> having some real problems in how filePro handle INNER JOINS and
> multiple tables. I just don't get the 'AS' 'FROM' filePro statement I
> found in the help files. I will be sending this to the list and
> fpsupport to see if they can set me straight.
> 
> So far I have written 7 processes like the one below. Once you get the
> format and structure it is simple and the processing speed fast.
> 
> With all that said here is the table. I'll answer any questions you
> may have.
> 
> Rick Hane
> Controller
> Deluxe Stitcher Company Inc
> http://www.deluxestitcher.com/
> 
> 
> LABEL D E F I N E P R O C E S S I N G 5.0.15R9
> ────────────────────────────────────────────────────────────────────────────────
> 1 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: declare connection(8,.0,g) ◄
> 2 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: declare tablename(20,,g) ◄
> 3 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: declare queryname(8,.0,g) ◄
> 4 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: declare recordset(5,.0,g) ◄
> 5 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: declare myquery(250,*,g) ◄
> 6 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: declare stemp(250,*,g) ◄
> 7 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: connection = new ODBC_CONNECTION("DSN=fp-sqlserver;") ◄
> 8 ------- - - - - - - - - - - - - - - - -
> ◄ If: connection le "0" ◄
> Then: show "@Cannot not open a connection to "<connection ◄
> 9 ------- - - - - - - - - - - - - - - - -
> ◄ If: connection le "0" ◄
> Then: msgbox "Last ODBC error:\n" & @odbcerror["1"] & @odbcerror["2"]
> ◄
> 10 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: tablename = "PST" ◄
> 11 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: stemp="Select PST_ParentItemID," ◄
> 12 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: stemp=stemp<"PST_CompItemID," ◄
> 13 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: stemp=stemp<"PST_QtyPerAssy," ◄
> 14 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: stemp=stemp<"PST_EffStartDate" ◄
> 15 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: stemp=stemp<"from"<tablename ◄
> 16 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: stemp=stemp<"WHERE PST_EffStopDate is Null" ◄
> 17 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: recordset = new ODBC(connection, stemp) ◄
> 18 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: odbc recordset query stemp ◄
> 19 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: n(3,.0)=@odbc.recordset["0"]; r(5,.0)="1" ◄
> 20 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: odbc recordset getfirst ◄
> 21 ------- - - - - - - - - - - - - - - - -
> setfld ◄ If: ◄
> Then: a(20,*)=@odbc.recordset ("PST_ParentItemID") ◄
> 22 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: show "Processing Record"<r<"Part"<a{"" ◄
> 23 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: b(20,*)=@odbc.recordset ("PST_CompItemID") ◄
> 24 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: q(9,.4)=@odbc.recordset ("PST_QtyPerAssy") ◄
> 25 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: d(10,mdyy/)=@odbc.recordset ("PST_EffStartDate") ◄
> 26 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: r=r+"1" ◄
> 27 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: gosub sendit ◄
> 28 ------- - - - - - - - - - - - - - - - -
> ◄ If: @odbc.recordset.eof ne "1" ◄
> Then: odbc recordset getnext; goto setfld ◄
> 29 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: end ◄
> 30 ------- - - - - - - - - - - - - - - - -
> sendit ◄ If: a eq "" ◄
> Then: return ◄
> 31 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: lookup miv = bom r=free -e ◄
> 32 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: miv(1)=a;miv(2)=b;miv(3)=q;miv(4)=d ◄
> 33 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: write miv ◄
> 34 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> Then: return ◄
> 35 ------- - - - - - - - - - - - - - - - -
> ◄ If: ◄
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://mailman.celestial.com/pipermail/filepro-list/attachments/20130413/c032e05d/attachment.html
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list

-- 
Jay R. Ashworth                  Baylink                      jra at baylink.com
Designer                     The Things I Think                       RFC 2100
Ashworth & Associates    http://baylink.pitas.com/         2000 Land Rover DII
St Petersburg FL USA               #natog                      +1 727 647 1274
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20130415/09f9dbaa/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: getbom.prc
Type: application/octet-stream
Size: 1220 bytes
Desc: not available
Url : http://mailman.celestial.com/pipermail/filepro-list/attachments/20130415/09f9dbaa/attachment.obj

More information about the Filepro-list mailing list