Sample fpODBC process

Richard Hane yoresoft at sbcglobal.net
Mon Apr 15 10:54:06 PDT 2013 

Yes Walter I will when I get a chance.
 
Mondays are bad because of payroll and production meetings.
 
Probably tomorrow.
 
Rick
 
 

________________________________
 From: Walter D Vaughan Jr <wvaughan at steelerubber.com>
To: filePro Mailing List <filepro-list at lists.celestial.com> 
Sent: Monday, April 15, 2013 12:43 PM
Subject: RE: Sample fpODBC process
  

> -----Original Message-----
> From: filepro-list-bounces+wvaughan=steelerubber.com at lists.celestial.com
> [mailto:filepro-list-
> bounces+wvaughan=steelerubber.com at lists.celestial.com] On Behalf Of
> Richard Hane
> Sent: Monday, April 15, 2013 1:31 PM
> To: Jay Ashworth
> Cc: file Pro Mailing List
> Subject: Re: Sample fpODBC process
> 
> Jay,
> 
> I have attached the prc process called getbom.prc

[Walter D Vaughan Jr] 
Can you attach it in ASCII encoded .prc or did the scrubbing kill it?


> 
> 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
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list

_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes
http://mailman.celestial.com/mailman/listinfo/filepro-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20130415/7091d078/attachment.html

More information about the Filepro-list mailing list