filePro, and SQL

[Chris Sellitto]

<<Bottom Post>>

From: Richard Hane [mailto:yoresoft at sbcglobal.net] 
Sent: Wednesday, October 23, 2013 12:51 PM
To: Kenneth Brody; Chris Sellitto; filepro-list at lists.celestial.com
Cc: Rick Hane
Subject: Re: filePro, and SQL

<< Sorry top posted >>
 
Hi Ken,
 
Let me take this concept a little deeper.  I do a lot of pulling of SQL data each night in to our filePro DB.  That works better than I ever hoped for.  All is done low level.
 
Let's assume I have a couple of files in filePro that contain customer notes.  And from within my MRP software in the Customer Tables I would like to be able to call up and view this data.  Could I create a matching SQL Table?  Yes I know about the record number field being required.  Then either read the data in to the table from filePro as requested via a lookup or just propagate the SQL Table with the filePro data and leave it as an SQL Table.
 
Thank you,
Rick Hane
Controller
Deluxe Stitcher Company Inc
ISP Bindery & Stitching Products
http://www.deluxestitcher.com/
 
  
From: Kenneth Brody <kenbrody at spamcop.net>
To: Chris Sellitto <sellich at guaranteedreturns.com>; "filepro-list at lists.celestial.com" <filepro-list at lists.celestial.com> 
Sent: Wednesday, October 23, 2013 10:47 AM
Subject: Re: filePro, and SQL

On 10/23/2013 11:37 AM, Chris Sellitto wrote:
> filePro(r) 5.7.03
> Windows Server 2008
> Windows Workstations: WinXP, Win 7
>
> All,
>
> We are looking to take advantage of filePro(r)'s ODBC capabilities.  What
> we would like to know is if anyone has used filePro(r) to access SQL
> Server as its main database?  In other words, can you use filePro(r) to
> attach to an SQL database, thus eliminating the need for a KEY file?  All
> the while, keeping your filePro(r) code in tact?
>
> If this concept is doable, how difficult is it, and if someone out there
> has already had success with this, would they be willing to consult for
> us for a fee?
>
> I hope I explained it well enough.
>
> Any input would be greatly appreciated.

First, make sure that the SQL table has something that can be used like 
filePro's "record number".  Typically, this is an auto-increment numeric field.

Next, in define files, specify that you want to create an ODBC file.  Use 
the dialog boxes that come up to specify the ODBC data source.  filePro will 
then read the ODBC table definition and build a filePro map.

You can then use that file (almost) just like any filePro file.

There is also "low level" ODBC access, where everything is done in 
processing -- connect to the ODBC source, execute queries, read results, etc.

-- 
Kenneth Brody

Thanks Ken, and Richard,

Ken, can the "record number" field be located anywhere in the database? Or does it have to be the first field?  Another question I had was how does indexing work on filePro®'s end of things?

So far, I think we only plan to write to the SQL file from filePro®, and as far as the SQL side, it would be a read only basis.

Thank you again
Chris