ODBC Issues

Yahoo Archive
1
Brian,

I'll address your concerns in a general way on the list, you can
contact me off the list if you wish me to duplicate a specific
query for testing.

We are on 5.10.113, our database is 1.1GB in size - we went live
in 1998. We are using the Merant 3.6 SQL92 driver for Progress
9.1C. Our Progress server is a Dell PowerEdge 4450 with two 1GHz
XEONs and 1GB RAM, with the database on a striped and mirrored
array.

I fire off an Access program to populate an Excel spreadsheet that
is distributed to all production leads every morning. The Access
program runs two make-table queries that nest into a union query
that in turn exports the results in Excel format. The largest
table involved is OrderRel. The net result is about 20 seconds,
including ODBC login.

If you'd like me to benchmark a particular query, let me know.

thanks,
john


> -----Original Message-----
> From: B_Stinger [mailto:bstenglein@...]
> Sent: Monday, January 21, 2002 6:00 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] ODBC Issues
2
I was out a couple of days last week so I'm just now getting caught
up reading all of the messages. There has been a lot of discussion
on various methods of getting data out of Vantage and into Excel or
Access. Some of you are doing some very interesting things with 4GL
and Programming with Progress. Others are using the export tool. It
sounds like most everyone is staying away from ODBC, which is why I'm
a little concerned, because we are using it quite a bit.

I've heard talk about security risks using ODBC and I was wondering
if anyone can explain that a little more clearly to me. I think the
risk of someone seeing something they shouldn't and understanding it
is minimal for us because we don't use Vantage Payroll and don't use
exact $'s in the Employee Screen for the Labor Rate. I'm more
concerned about the possibility of someone being able to change the
data through ODBC. Whenever I open an ODBC connected table in Access
I'm told that it is read only, and I don't have the ability to change
any info. In what case would this not hold true? I am able to use
Access security in my applications to keep my applications secure.
It is very unlikely that anyone here would try to create their own DB
and be able to connect to ODBC and know enough about the table
structure to look at anything meaningful. I know this is a bad
method of defense but I think it works here.

I also have a question and statement about ODBC performance. I
originally tried setting up my Access programs using ODBC linked
tables. As the amount of data in Vantage grew the linked tables
quickly became very slow. Now I am having the user run a macro that
will run a Make Table Query for each table needed in that program.
The Make Table Query essentially is like doing an exported table,
only they are doing it from within my application. The tables take
anywhere form 5 to 20 seconds to create depending on the qty of data
in the table and the # of fields I'm including in the table. So far
this is working well for us. I've created applications that allow us
to print labels, include pictures on Job Travelers, analyze
commission info, track late shipments, and project future cash flow
based on each companies avg days to pay. My fears are that in some
future release ODBC won't work for us like it does now, or as our DB
grows the performance will become poor. Has anybody who has a couple
of years worth of data in Vantage tried running a make table query in
Access? We've been live on 5.0 since May 2001. We are using Merant
ODBC 3.6. If anyone with a similar setup and a large DB would be
willing to try measure how long it takes for a Make table query to
run contact me off list and we can talk about setting up a test. I
guess I'm looking for arguments as to why ODBC is not worth using, in
which case I will need to learn some of these other methods that are
being discussed.

Thanks,

Brian Stenglein
Clow Stamping Company
bstenglein@...