Working with SQL via filePro
Richard Kreiss
rkreiss at verizon.net
Fri Nov 24 09:26:10 PST 2017
I have had FPSQL since version 1 and the only "mapping" was defined in the query created to tell fpsql what file(s) to use. This is no different than any SQL query.
If an actual connection to the SQL file is necessary, the using ODBC might be the best alternative. If you can run in a "batch" mode, the using FPSQL to create an output file alone with a script to post from this file should work.
I have found that it is sometimes fast a lot faster to create a filePro output to create a csv file with the records wanted then to run an fpsql query. This is something that needs to be tested.
Richard Kreiss
GCC Consulting
> -----Original Message-----
> From: Filepro-list [mailto:filepro-list-
> bounces+rkreiss=verizon.net at lists.celestial.com] On Behalf Of Robert
> Haussmann via Filepro-list
> Sent: Friday, November 24, 2017 11:49 AM
> To: Jose Lerebours <fpgroups at gmail.com>; Filepro List <filepro-
> list at celestial.com>
> Subject: RE: Working with SQL via filePro
>
> 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/201
> > > 71
> > > 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
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list
More information about the Filepro-list
mailing list