<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Answer inline <br>
<br>
Rick Hane wrote:<br>
<blockquote
cite="midE51F2CE505929F40AD4C67F2BF704BF9544A2C@server2008.domain.deluxe"
type="cite">
<pre wrap="">-----Original Message-----
From: Kenneth Brody [<a class="moz-txt-link-freetext" href="mailto:kenbrody@spamcop.net">mailto:kenbrody@spamcop.net</a>]
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:
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!---->[...]
</pre>
<blockquote type="cite">
<pre wrap="">Since you say you only need to import the data, rather than have "live"
</pre>
</blockquote>
<pre wrap=""><!---->
</pre>
<blockquote type="cite">
<pre wrap="">access to it, you can use a simple low-level ODBC query to get the data
</pre>
</blockquote>
<pre wrap=""><!---->and import it into filePro.
</pre>
<blockquote type="cite">
<pre wrap="">Basically:
Establish the connection:
handle = new odbc_connection( DSN )
(You need to determine the appropriate DSN to connect to your SQL
</pre>
</blockquote>
<pre wrap=""><!---->database.)
</pre>
<blockquote type="cite">
<pre wrap="">(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
</pre>
</blockquote>
<pre wrap=""><!---->appropriate.
</pre>
<blockquote type="cite">
<pre wrap="">Scroll through the records using:
odbc qhandle getnext
and use @odbc.qhandle.eof[] to test for end-of-file.
--
Kenneth Brody
</pre>
</blockquote>
<pre wrap=""><!---->
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.
</pre>
</blockquote>
I was in the same boat for years after I got my copy of fpODBC. <br>
<br>
Forget for a moment about the OBDC and just think about this problem as
<br>
if you were just using the IMPORT command. Sometimes it's a whole lot
easier when doing<br>
an import of data to be standing in a temporary filepro table, import
the data, manipulate, and then<br>
do a lookup to a free record to send it on it's merry way.<br>
<br>
So do the same thing.<br>
<br>
Stand in a temporary filePro table<br>
Use the OBDC to select a batch of records<br>
Read in a row of the selected records<br>
Manipulate as needed<br>
Do a lookup free to the destination filePro table<br>
Put the data into that destination table<br>
Loop as needed<br>
delete the record you are standing on and exit the temporary table<br>
<br>
Does that help?<br>
<br>
I got really hung up on using fpODBC like a normal filePro and drove
myself nuts.<br>
Don't do the same. And use normal filepro dummy variables in the
middle, if only <br>
so you can debug in a manner you have delt with in the past<br>
<br>
<a class="moz-txt-link-abbreviated" href="mailto:a=@odbc.qhandle[0">a=@odbc.qhandle[0</a>]<br>
msgbox a<br>
lookup(1)=a<br>
<blockquote
cite="midE51F2CE505929F40AD4C67F2BF704BF9544A2C@server2008.domain.deluxe"
type="cite">
<pre wrap="">
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
<a class="moz-txt-link-abbreviated" href="http://www.deluxestitcher.com">www.deluxestitcher.com</a>
773-777-6500
Rick Hane
_______________________________________________
Filepro-list mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Filepro-list@lists.celestial.com">Filepro-list@lists.celestial.com</a>
Subscribe/Unsubscribe/Subscription Changes
<a class="moz-txt-link-freetext" href="http://mailman.celestial.com/mailman/listinfo/filepro-list">http://mailman.celestial.com/mailman/listinfo/filepro-list</a>
</pre>
</blockquote>
<br>
</body>
</html>