Need assistance with fpODBC

[Walter Vaughan]

Answer inline

Rick Hane wrote:

>-----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.
>  
>
I was in the same boat for years after I got my copy of fpODBC.

Forget for a moment about the OBDC and just think about this problem as
if you were just using the IMPORT command. Sometimes it's a whole lot 
easier when doing
an import of data to be standing in a temporary  filepro table, import 
the data, manipulate, and then
do a lookup to a free record to send it on it's merry way.

So do the same thing.

Stand in a temporary filePro table
Use the OBDC to select a batch of records
Read in a row of the selected records
Manipulate as needed
Do a lookup free to the destination filePro table
Put the data into that destination table
Loop as needed
delete the record you are standing on and exit the temporary table

Does that help?

I got really hung up on using fpODBC like a normal filePro and drove 
myself nuts.
Don't do the same. And use normal filepro dummy variables in the middle, 
if only
so you can debug in a manner you have delt with in the past

a=@odbc.qhandle[0]
msgbox a
lookup(1)=a

>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
>
>_______________________________________________
>Filepro-list mailing list
>Filepro-list at lists.celestial.com
>Subscribe/Unsubscribe/Subscription Changes
>http://mailman.celestial.com/mailman/listinfo/filepro-list
>
>  
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20121025/1c889d21/attachment.html