Need assistance with fpODBC

[Rick Hane]

-----Original Message-----
From: Kenneth Brody [mailto:kenbrody at spamcop.net] 
Sent: Tuesday, October 23, 2012 1:01 PM
To: Rick Hane
Cc: Richard Hane; file Pro Mailing List
Subject: Re: Need assistance with fpODBC

On 10/23/2012 10:56 AM, Rick Hane wrote:
>> Hi Rich,
>>
>> I think I was not clear in my email.  Of course fp reads Excel files 
>> via import.  That is the step I want to remove.
>>
>> I want to set up the fpODBC to read the data directly from the SQL 
>> data base.  I know this is possible.  The problem is how.
[...]

>Since you say you only need to import the data, rather than have "live"

>access to it, you can use a simple low-level ODBC query to get the data
and import it into filePro.
>
>Basically:
>
>Establish the connection:
>
>    handle = new odbc_connection( DSN )
>
>(You need to determine the appropriate DSN to connect to your SQL
database.)
>
>(Check for a positive "handle" value, to make sure it succeeded.)
>
>Create a query handle:
>
>    qhandle = new odbc(handle)
>
>(Check for a positive handle value, to make sure it succeeded.)
>
>Query the database:
>
>     odbc qhandle query "select * from mytable"
>
>(Adjust the query as appropriate, including a possible "where" clause.)
>
>(Check for a null @odbcerror["1"], to make sure it succeeded.)
>
>Use the @odbc.qhandle[] array to access the fields, and post as
appropriate.
>
>Scroll through the records using:
>
>     odbc qhandle getnext
>
>and use @odbc.qhandle.eof[] to test for end-of-file.
>
>--
>Kenneth Brody

Ken,

Thanks for the reply and information.  Here is the info I copied from
the ODBC link in Excel.

Connection String
DSN=server2010 IERP60;Description=Intuitive ERP
Datasource;UID=sa;Trusted_Connection=Yes;APP=Microsoft(r)
Query;WSID=D9FH6621;DATABASE=IERP60;UseProcForPrepare=0;QuotedId=No

Command Type
SQL

Command Text
SELECT IMA.IMA_ItemID, IMA.IMA_AcctValAmt, IMA.IMA_StdCostAmt
FROM IERP60.dbo.IMA IMA

>From this I see most that I need to set up the ODBC Link in fp.
I looked through the ODBC 'Sample' files and I have three questions.  I
am still not understanding the flow.

1. In what filePro file do you put this link? New or do you use the
'sample' files they came with fp? 

2. If it is a new file, do you create a record for each table to read?

3. If it is only one link definition, do you "use" the appropriate table
in each processing?

As I said I'm just not getting the flow.  Actually it just my age (64).

Thank you in advance,
Rick Hane
Controller
Deluxe Stitcher Company Inc
ISP Stitching Products
www.deluxestitcher.com
773-777-6500

Rick Hane