Working with SQL via filePro

[Robert Haussmann]

I can speak to fpsql--think of that as a utility that performs simple SQL-like queries on your existing filePro data.  Current versions have no need to do any "mapping" (actually I've used it for quite some time and do't remember such a requirement ever, but maybe that was before my time)?  I say "SQL-like" because the command syntax is somewhat non-standard (but if you are fluent in SQL queries, there's no problem adjusting).  By default the queries results are displayed on screen, but you can send the results to a flat-file export if you wish.

To give you an idea about functionality, see below (taken from the fpsql help file).  It's important to note that fpsql is read-only, supporting only the SELECT statement (that is to say, you can't use an INSERT or UPDATE statement and expect it to change your filePro data).  

Sample2 - Query

        SET title 'Prices Greater Than 99'
        SELECT product_number, description, price
        FROM sproduct
        WHERE price > 99
        ORDER BY price

 Sample2 - Results

 Apr 29, 2003  15:58        Prices > 99

 product_number description                  price
 -------------------------------------------------
  67-8901       Humidity Meter               99.50
  78-9012       Barometer Kit               199.60
  23-4567       Metal Tracker               219.00


Bob Haussmann

> -----Original Message-----
> From: Filepro-list [mailto:filepro-list-
> bounces+haussma=nextdimension.net at lists.celestial.com] On Behalf Of
> Jose Lerebours via Filepro-list
> Sent: Friday, November 24, 2017 11:39 AM
> To: filepro-list at lists.celestial.com
> Subject: Re: Working with SQL via filePro
> 
> Well, as mentioned by Mark, there is an fpSQL out there but I am not sure if
> it ever hit main stream ... Last I remember you needed to "map"
> the SQL table to the filePro file so that runtime can properly store data in
> corresponding fields or columns.
> 
> That said, solutions are abundant in this community and to each his/her
> own.  Long ago I wrote a few shell scripts that simply took a text file and ran a
> sequence of sql commands to C[R]UD records as needed; I ran the scripts
> just as the "input" process successfully saved a record.  I chose then to use
> @rn as the unique ID in the SQL tables so that I can run SQL command where
> if "duplicate", then "update" in lieu of "insert".
> 
> You can use straight SQL commands, PHP, Python or anything you feel
> comfortable with should filePro not be able to store directly to your SQL
> tables as you intend.
> 
> So, you should be able to quickly and easily create a JSON object with your
> data and roll it to SQL pretty fast and easy ... google "JSON to SQL" and ...
> happy coding!
> 
> Regards,
> 
> 
> On 11/24/2017 10:23 AM, William J. McEachran via Filepro-list wrote:
> > Most of the SQL work I've had to do was creating sqldump files for
> > periodic refreshes to mySQL.
> >
> > I've got a request to do real-time exchanges between filePro and mySQL.
> >
> > What method(s) are being used to get filePro to read SQL data?  ODBC?
> >
> > Is there a method for a SQL query/CRUD to directly update filePro
> > data?  I suspect I almost need an API for that to happen.
> >
> > Is the happy medium to spin out some files into SQL which both filePro
> > and SQL interact?
> >
> > Thanks for any suggestions.
> > --
> > Bill McEachran
> > -------------- next part -------------- An HTML attachment was
> > scrubbed...
> > URL:
> > <http://mailman.celestial.com/pipermail/filepro-list/attachments/20171
> > 124/2b18448c/attachment.html>
> > _______________________________________________
> > Filepro-list mailing list
> > Filepro-list at lists.celestial.com
> > Subscribe/Unsubscribe/Subscription Changes
> > http://mailman.celestial.com/mailman/listinfo/filepro-list
> 
> --
> Jose D. Lerebours
> 954-559-7186
> https://www.cargosaas.com
> http://www.ezbookeep.com
> http://www.ezdaemon.com
> http://www.fp2php.com
> 
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list