Sample fpODBC process
Richard Hane
yoresoft at sbcglobal.net
Sat Apr 13 06:55:01 PDT 2013
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
More information about the Filepro-list
mailing list