Board index » delphi » Strings, memos, ODBC, Access and {*word*76}y Microsoft

Strings, memos, ODBC, Access and {*word*76}y Microsoft

Arg!!!

Just spent today trying to work out what the hell was going on with my
code, and I think I've worked it out.

The app in question needs to run on both a local Access database and one
hosted on SQL Server.  To this end, it's all done with TQuery components
via ODBC.  This appeared to be working well.

My development box has the MDAC 2.1 driver set on it, but when it was
tested today on a machine with MDAC 2.5 on it, it broke spectacularly
when using an Access database, although it was ok on SQL Server.

On further investigation, it transpired that the app was getting back
memo fields from the database where it was expecting strings.  The
fields affected appeared to be those that were created on the fly by the
queries by joining together multiple fields, for example:

SELECT Forename + ' ' + Surname AS FullName FROM Contacts

The FullName field would be presented by ODBC as a string field under
MDAC 2.1, but as a memo under 2.5.

I did some more testing.

It appears that under MDAC 2.1, any such 'amalgamated' field is returned
as a string, and thus as a TStringField when in the IDE.  If the result
of the concatenation is longer than 255 characters, tough, you'll get
the first 255 back.

Under 2.5, the returned field is a memo, or TMemoField in the IDE, even
if the resultant string is only a few characters long.

What on earth possessed MS to change this!?!?  They've obviously made a
decision that, as they can't guarantee the resultant field will be under
255 characters, they'll return it as a memo instead.  This is a
fundamental change and breaks things badly.

What's worse is that with the latest SQL Server ODBC driver in MDAC 2.5,
all such fields are still returned as strings, which makes my life a
misery when trying to write an app that won't care what database is
under it.  For the moment, the only way I've found to get round this is
to not use such fields in the queries themselves, but just yank out the
original fields and use a calculated field and do it myself in code.
This ain't pretty but it works.

Anyone else got any insights or comments on this?
--
Toby

 

Re:Strings, memos, ODBC, Access and {*word*76}y Microsoft


Toby,
It's fairly well known that the MS Access ODBC driver developed a number of
problems with the release of MDAC 2.1 SP2.  The problems got even worse with
MDAC 2.5 and they are not likely to get any better since Microsoft is
officially phasing out ODBC in favor of ADO.  They really aren't spending
any more on ODBC development.

Which version of Delphi are you using?  You'll be better served in the long
run by dropping ODBC and the BDE completely and going with an ADO solution.
You won't need to distribute the BDE.  You should be able to use the same
TADODataSet (or TADOQuery, etc.) components (assuming you have D5 Enterprise
or can get Borland's ADOExpress for D5) with both databases (assuming table
and element names are the same) by just changing the ADO connection string.

Ray Porter

Quote
"Toby Groves" <n...@iconia.org.uk> wrote in message

news:YaM1PRAE4ve7EwW$@iconia.org.uk...
Quote
> Arg!!!

> Just spent today trying to work out what the hell was going on with my
> code, and I think I've worked it out.

> The app in question needs to run on both a local Access database and one
> hosted on SQL Server.  To this end, it's all done with TQuery components
> via ODBC.  This appeared to be working well.

> My development box has the MDAC 2.1 driver set on it, but when it was
> tested today on a machine with MDAC 2.5 on it, it broke spectacularly
> when using an Access database, although it was ok on SQL Server.

> On further investigation, it transpired that the app was getting back
> memo fields from the database where it was expecting strings.  The
> fields affected appeared to be those that were created on the fly by the
> queries by joining together multiple fields, for example:

> SELECT Forename + ' ' + Surname AS FullName FROM Contacts

> The FullName field would be presented by ODBC as a string field under
> MDAC 2.1, but as a memo under 2.5.

> I did some more testing.

> It appears that under MDAC 2.1, any such 'amalgamated' field is returned
> as a string, and thus as a TStringField when in the IDE.  If the result
> of the concatenation is longer than 255 characters, tough, you'll get
> the first 255 back.

> Under 2.5, the returned field is a memo, or TMemoField in the IDE, even
> if the resultant string is only a few characters long.

> What on earth possessed MS to change this!?!?  They've obviously made a
> decision that, as they can't guarantee the resultant field will be under
> 255 characters, they'll return it as a memo instead.  This is a
> fundamental change and breaks things badly.

> What's worse is that with the latest SQL Server ODBC driver in MDAC 2.5,
> all such fields are still returned as strings, which makes my life a
> misery when trying to write an app that won't care what database is
> under it.  For the moment, the only way I've found to get round this is
> to not use such fields in the queries themselves, but just yank out the
> original fields and use a calculated field and do it myself in code.
> This ain't pretty but it works.

> Anyone else got any insights or comments on this?
> --
> Toby

Re:Strings, memos, ODBC, Access and {*word*76}y Microsoft


In article <9m2pkr$an...@news2.isis.unc.edu>, Ray Porter
<ray_por...@unc.edu> writes

Quote
>Toby,
>It's fairly well known that the MS Access ODBC driver developed a number of
>problems with the release of MDAC 2.1 SP2.  The problems got even worse with
>MDAC 2.5 and they are not likely to get any better since Microsoft is
>officially phasing out ODBC in favor of ADO.  They really aren't spending
>any more on ODBC development.

That's good to hear, now I've based my application around it :((

Quote

>Which version of Delphi are you using?

D5 Pro.

Quote
>  You'll be better served in the long
>run by dropping ODBC and the BDE completely and going with an ADO solution.

I've never really looked at ADO, as the components I need for it are
only in the Enterprise edition, which costs serious money.

Quote
>You won't need to distribute the BDE.

Sounds good already! :)

Quote
>  You should be able to use the same
>TADODataSet (or TADOQuery, etc.) components (assuming you have D5 Enterprise
>or can get Borland's ADOExpress for D5) with both databases (assuming table
>and element names are the same) by just changing the ADO connection string.

Well as I said, I'm currently using D5 Pro, talking to either SQL Server
or Access MDB via ODBC, using TQuery components all over the shop.
Actually, once I've found and coded round the idiosyncrasies of ODBC, it
does appear to work quite well.

I've been wondering about Enterprise, both for it's ADO support and
better SQL Server support, but the cost is very high.  I'd like to try
to justify a move from D5 Pro to D6 Ent in the near future, but my boss
is going to want me to justify the cost.

Given what I'm attempting to do with both local and remote databases,
can you give me some good arguments in favour of the upgrade that I can
hit him with?

TIA :)
--
Toby

Re:Strings, memos, ODBC, Access and {*word*76}y Microsoft


Hi Toby,
You may not have to make the move to Enterprise.  With D6, Borland has
migrated the ADO stuff down from the Enterprise version to the Pro version.
They've changed the name from ADOExpress to something like dbGo (can't
remember the new name for certain) because MS no longer allows anyone to use
the ADO acronym in a product name.  Even if you stick with D5, ADOExpress is
available as a separate purchase from Borland for around $200-$300 (don't
hold me to that price).  I think D6 Pro will meet all your needs as you've
described them in this thread.

I use ODBC extensively for Sybase (using ODBCExpress, the BDE was a no go
from the beginning).  I used to use ODBC for MS Access (also with
ODBCExpress) but the problems that began appearing in the MS Access ODBC
driver with MDAC 2.1, led me to quickly switch to ADO for Access.  We will
be migrating our major applications from Sybase to Oracle in the near future
and I plan to switch entirely to ADO at that point.

Working for a University, I'm facing another problem.  Borland has changed
the license for the academic version of Delphi to pretty much destroy its
usefulness for anything outside of a computer lab.  Based on my reading of
the current D6 Academic license, you can't even develop and distribute
freeware with it.  That seems to mean that we, as a shop that develops
administrative applications for the university, can no longer use the
academic version for our work, which means that my management will no longer
approve the purchase of Delphi for work on this campus.  Our management has
a strict policy of only buying software that is discounted for academic and
non-profit institutions unless there is no other alternative.  Now I'm faced
with the possibility of going back to VB (yuck), VC++ (double yuck) or
finding some way to rewrite 100,000 lines of production code in Java without
surrendering too much functionality (impossible given the way management
wants applications written and the functionality my customers demand).

Ray Porter

Quote
"Toby Groves" <n...@iconia.org.uk> wrote in message

news:cBGEpLBSqUh7Ewfj@iconia.org.uk...
Quote
> In article <9m2pkr$an...@news2.isis.unc.edu>, Ray Porter
> <ray_por...@unc.edu> writes
> >Toby,
> >It's fairly well known that the MS Access ODBC driver developed a number
of
> >problems with the release of MDAC 2.1 SP2.  The problems got even worse
with
> >MDAC 2.5 and they are not likely to get any better since Microsoft is
> >officially phasing out ODBC in favor of ADO.  They really aren't spending
> >any more on ODBC development.

> That's good to hear, now I've based my application around it :((

> >Which version of Delphi are you using?

> D5 Pro.

> >  You'll be better served in the long
> >run by dropping ODBC and the BDE completely and going with an ADO
solution.

> I've never really looked at ADO, as the components I need for it are
> only in the Enterprise edition, which costs serious money.

> >You won't need to distribute the BDE.

> Sounds good already! :)

> >  You should be able to use the same
> >TADODataSet (or TADOQuery, etc.) components (assuming you have D5
Enterprise
> >or can get Borland's ADOExpress for D5) with both databases (assuming
table
> >and element names are the same) by just changing the ADO connection
string.

> Well as I said, I'm currently using D5 Pro, talking to either SQL Server
> or Access MDB via ODBC, using TQuery components all over the shop.
> Actually, once I've found and coded round the idiosyncrasies of ODBC, it
> does appear to work quite well.

> I've been wondering about Enterprise, both for it's ADO support and
> better SQL Server support, but the cost is very high.  I'd like to try
> to justify a move from D5 Pro to D6 Ent in the near future, but my boss
> is going to want me to justify the cost.

> Given what I'm attempting to do with both local and remote databases,
> can you give me some good arguments in favour of the upgrade that I can
> hit him with?

> TIA :)
> --
> Toby

Re:Strings, memos, ODBC, Access and {*word*76}y Microsoft


In article <9m5f20$1q...@news2.isis.unc.edu>, Ray Porter
<ray_por...@unc.edu> writes

Quote
>Hi Toby,
>You may not have to make the move to Enterprise.  With D6, Borland has
>migrated the ADO stuff down from the Enterprise version to the Pro version.
>They've changed the name from ADOExpress to something like dbGo (can't
>remember the new name for certain) because MS no longer allows anyone to use
>the ADO acronym in a product name.  Even if you stick with D5, ADOExpress is
>available as a separate purchase from Borland for around $200-$300 (don't
>hold me to that price).  I think D6 Pro will meet all your needs as you've
>described them in this thread.

Thanks for that, I did a bit of research myself last night and this
morning, and came to the same conclusion.  I had a quick chat with the
boss today and D6 Pro is arriving on Tuesday :))

Quote
>I use ODBC extensively for Sybase (using ODBCExpress, the BDE was a no go
>from the beginning).  I used to use ODBC for MS Access (also with
>ODBCExpress) but the problems that began appearing in the MS Access ODBC
>driver with MDAC 2.1, led me to quickly switch to ADO for Access.  We will
>be migrating our major applications from Sybase to Oracle in the near future
>and I plan to switch entirely to ADO at that point.

As I said, so far I've managed to work my way around most of the
problems with the ODBC drivers, but it's far from ideal.

I've managed, so far, to keep the app totally generic, so that it
doesn't actually know or care what's running behind the ODBC driver, you
just point it at the relevant DSN and off it goes.  Unfortunately, to do
this I've had to do numerous things myself in code that should really be
done in the database or SQL, such as enforcing integrity, unique ID
generation and field amalgamation.

ADO definately seems the way to go.

Quote

>Working for a University, I'm facing another problem.  Borland has changed
>the license for the academic version of Delphi to pretty much destroy its
>usefulness for anything outside of a computer lab.  Based on my reading of
>the current D6 Academic license, you can't even develop and distribute
>freeware with it.  That seems to mean that we, as a shop that develops
>administrative applications for the university, can no longer use the
>academic version for our work, which means that my management will no longer
>approve the purchase of Delphi for work on this campus.  Our management has
>a strict policy of only buying software that is discounted for academic and
>non-profit institutions unless there is no other alternative.

I'm not sure about the academic versions of Delphi, but the licence for
the Personal version stipulates that it may not be used for commercial
applications, and I'd assumed the academic licence was similar.

Given that you're an in-house shop developing for other departments
only, surely this is classed as non-commercial work?, or am I mistaken?

Quote
>  Now I'm faced
>with the possibility of going back to VB (yuck)

Some other poor soul in our office has to deal with that.

Quote
>, VC++ (double yuck) or

I used to be a C die-hard before I turned to Delphi.  I never did C++
though, as it's just too {*word*193} for words.

Quote
>finding some way to rewrite 100,000 lines of production code in Java without
>surrendering too much functionality (impossible given the way management
>wants applications written and the functionality my customers demand).

Thankfully, in my job I'm pretty much autonomous, and design and develop
the entire project myself.  This gives me an enormous amount of control
over the whole affair, and I'm free to write things the way I want to
(i.e. the right way).  My boss is a C programmer at heart, and doesn't
know that much about Delphi, so he trusts me to do it right, and leaves
me alone to get on with it.  So far I've not let him down.

Talking to friends of mine who also write code for a living, I'm
fortunate to be in such a position, and although I could probably make
more money working elsewhere, I'm not prepared to relinquish the control
I have for a few more bucks.
--
Toby

Re:Strings, memos, ODBC, Access and {*word*76}y Microsoft


Toby,

Quote

> I'm not sure about the academic versions of Delphi, but the licence for
> the Personal version stipulates that it may not be used for commercial
> applications, and I'd assumed the academic licence was similar.

> Given that you're an in-house shop developing for other departments
> only, surely this is classed as non-commercial work?, or am I mistaken?

You would have thought so but the academic license, as currently worded,
explicitly limits the distribution of applications developed with the
academic version only to other machines that also have Delphi 6 installed.
This seems to me to effectively eliminate even the development of freeware.
I would not have objected to a clause banning the sell of any applications
developed with the academic version (I wouldn't have liked it, but I
wouldn't have objected), but the current license totally destroys the
usefulness of the product outside the restricted confines of a computer lab.
I don't even think students will buy this version once the terms of the
license are known.  Of course, students are pretty notorious for ignoring
license terms.

It's really frustrating since Borland's web site specifically qualifies
administrative units of academic institutions for the discounted edition.
Why else would administrative units buy a development tool except to develop
internal, administrative applications?  We've been around and around about
this on the Borland news groups and John Kaster has recommended a direct
call to a couple of Borland staffers most responsible for the product.  I've
had some family problems that have taken all my time recently but once they
are cleared away, I plan on making those phone calls.

Ray Porter

Other Threads