Re: Foxpro sql limitations

From: Steve G (steve_at_tracorp.com)
Date: 07/14/04

  • Next message: Jerry: "Dynamic SQL Statements" 
    Date: 14 Jul 2004 11:14:57 -0700

    Thanks for everyone replying to me, I appreciate the support.

    With regard to views and cursors, I've never had any experience with
    these so I'll look into them and see if they are viable for this
    situation.

    With regard to subqueries, I use them whenever possible but the two
    data sources with this project makes some things a little difficult.

    Some more information:
    The Foxpro data source I cannot change in any way, it is a third-party
    application that I am interfacing with and so it could be updated or
    changed at any time. The only thing I need guaranteed is that the
    object names and data types remain the same.

    My application is an online user interface for customers to log on to
    and check the status of their fleet of vehicles (the client is a fleet
    maintenance shop). The ASP site connects to the Foxpro database used
    by the vehicle maintenance program that the client uses to get this
    information.

    To solve the problem of users being able to log in and access their
    specific records, I created a database on the SQL server to match up
    login IDs and passwords with the appropriate records from the Foxpro
    database. This SQL db has since been expanded to include some more
    info about the customers.

    The application to interface with (called 'Yes') is an older
    application upgraded from a dos version (not rewritten), and so there
    are names that dont make sense and data bloat and whatnot. (It may be
    worth it to someone to note that vfp5 ships with Yes, I assume that is
    what they used to create the application.) ml_id is mailing list id,
    and is used now to associate a single customer with their many
    vehicles. I use the ml_id for linking an online login account with
    the customer records in Yes. The ml_ids are stored in the sql
    database to match up with the fp database. This is illustrated below.

    sql table customers
    id
    name
    login
    pw
    alert1
    alert2
    email
    ...

    sql table customer_ids
    cust_id (from above table)
    ml_id (matching id in fp table, one or more per cust)

    fp table yes02v (Yes vehicles table)
    ml_id
    vehicle_id
    make
    model
    ...

    The current problem deals with automatically emailing customers 30 and
    7 days before their vehicles are due for periodic maintenance. There
    is an additional sql table for this:

    sql table vehicle_alerts
    vehicle_id
    period

    This lists the time period (days) between one maintenance event and
    the next for each vehicle. It would have been nice to use a field in
    the fp db yes02v table for this, but I can't change the structure of
    the database. The logic for the algorithm flows something like this:

    * Find all customers in the sql db where the alert flags indicate they
    want to receive alerts
    * Get all ml_ids that these customers correspond to from the sql
    customer_ids table (this step and the above step are encapsulated into
    a single query)
    * Get all vehicles linked to these ml_ids from the fp yes02v table
    (vehicles table for Yes; this long statement causes it to blow up)
    * Find the last maintenance date (this involves looking in a couple
    other tables, for invoices, repair history, etc to determine the
    repair type and date)
    * Determine if this vehicle needs an alert sent out for either 30 or 7
    days based on the date and if so send it

    So I need information from 3 sql tables, and 3 or 4 fp tables to
    decide if an alert needs to be sent.

    I'm not sure if this matters, but I'll describe how the fp data source
    is accessible. My connection string for ASP looks like this for the
    foxpro connection:
    fp_dbcon_string="Driver={Microsoft Visual FoxPro
    Driver};SourceType=DBC;SourceDB=e:\\path\\to\\yes\\YES02V.dbc;Exclusive=No";
    The database and tables are left in place with the Yes software, so
    the ODBC driver goes and grabs them straight from there to use them.
    The web server in this case is also the local Yes application server.

    After further thought, I've realized that execution time for this is
    not so much a factor, this algorithm will be automatically executed
    once daily and emails automatically sent based on the outcome.
    Therefore, a user won't be waiting for it to finish so execution time
    isn't much a concern for me. What I'm after now is simplicity, I
    would like this to be as elegant as possible to avoid obscure bugs
    arising out of a complex algorithm. Splitting up the query into
    batches of ml_ids doesn't seem very elegant but it will probably get
    the job done. As I said above, I'll look into people's suggestions,
    and if anyone has another please let me know.

    Thanks again for the help, I apologize for the novella.

    Steve

  • Next message: Jerry: "Dynamic SQL Statements"

    Relevant Pages

    • PostgreSQL and Customer Database
      ... I've been working on both SQL and Perl for a while now, ... get them all onto an SQL database, and be able to keep track of sales, as ... the database available to customers and potential customers, ...
      (perl.beginners)
    • RE: datamining beginner question
      ... I have not worked with SQL 2000 Analysis Services, but from what I hear, ... We are using the SQL Server 2000 as our database server, ... I know the 20k customers is not a big number, ...
      (microsoft.public.sqlserver.datamining)
    • Re: Want to write your SQL statements and even stored procedures in pure C#?
      ... This is C# code, not SQL. ... > can be regenerated at any time to be kept in sync with database ... So if you want to filter on orders to get the customers, ... Just 'customer', 'order' and a filter. ...
      (microsoft.public.dotnet.framework.adonet)
    • Hyperlinking in a returned recordset
      ... I have a customers table in my database (SQL 2k) with a column called ... can just click on the email address field and have it pull up a new mail ...
      (microsoft.public.inetserver.asp.general)
    • Re: Modifying Data Directly through Studio Manager
      ... 'tool' to allow your customers to manage their data. ... I've seen people comment on turned on the query pane, ... Many of my customers are starting to upgrade to SQL 2005, ... Isaac Shloss, MCSE ...
      (microsoft.public.sqlserver.tools)
    Loading