InterBase BDE 5.2 SQL Links Update WARNING!!!
David R. Robinson
that people need to know about (IMHO) so [TeamB] please don't cancel
it. Follow-ups should be set to borland.public.bde (assuming I did it
right). I downloaded and installed Netscape just so I could post a
message with folllow-ups set properly.
If you use the BDE for database access for an InterBase 5.x database,
you need to be aware of the following problems introduced in the new BDE
SQL Links DLL that ships with Delphi 6:
Problem #1.) "Translate error. Value out of bounds". This occurs if
you are using using IB 5.x client & server.
Problem #2.) "General SQL error. Unable to complete network request to
host 'servername'. Error writing date to the connection. An existing
connection was forcibly closed by the remote host.". This occurs if you
are using using IB 6.x client & IB 5.x server.
If you use InterBase 5.x server, there is only one Date field type
(DATE). It stores both the date and time. If you want to retreive data
for a specific date (ignoring the time portion of the field) in a
parameterized query, you would probably use AsDate. You might have code
that looks like this:
Query1.ParamByName('DATEWORKED').AsDate := DateTimePicker1.DateTime;
If you use AsDate, you will get one of the above mentioned errors if you
use the new BDE SQL Links DLL that shipped with Delphi 6.
If you install the new BDE SQL Links DLL and are using InterBase *5.x*
client and InterBase 5.x server, you will get the following error:
"Translate error. Value out of bounds".
If you install the new BDE SQL Links DLL and while you are at it, happen
to install the InterBase *6* client (which someone would probably do if
they installed Delphi 6), you potentially have a serious problem if you
have an InterBase 5.x server. The user that updated their BDE and IB
client will see the following error: "General SQL error. Unable to
complete network request to host 'servername'. Error writing date to
the connection. An existing connection was forcibly closed by the remote
host.". When you get this error, you have lost your database connection
and will most likely have to kill the executable to shut it down. Ok,
so one user gets an error, no big deal. But that isn't the case. The
next time any user that was connected to any database on that server
tries to access the database, they will get the same error as well.
Yes, ALL USERS CONNECTED TO ALL DATABASES ON THAT IB 5.x SERVER WILL GET
THE SAME ERROR AND BE DISCONNECTED FROM THE DATABASE.
I know of two people in addition to myself that have code that does
exactly this same thing, so I have a feeling it is a common practice
when using InterBase and the BDE. Both of these problems have been
reported to Borland.
Borland's so called "solution" is the first one is to use AsDateTime.
AsDateTime returns the time portion and can give inaccurate query
results due to the various values of the time portion of the field
(that's why we are using AsDate to begin with). So, IMHO, that isn't a
solution. Even if it was, the result requires that you update your
program because of Borland's bug.
Borland's response to the second (and more serious problem) is that
InterBase does not support mismatched client and server versions, so
they aren't going to do anything to address it.
In my case, how am I going to prevent the 8,000 InterBase 5.x users that
we have deployed from having one user somewhere update the BDE.
Answer: I won't be able to. Since IB 6 is now out and there are many
other applications using IB 6, there is a good chance that someone will
update both the BDE and IB client and will have all users get
disconnected from the database.
What's worse is that this isn't even really an InterBase problem, it's a
BDE problem. IMHO, the BDE team needs to update the BDE SQL Links DLL
for InterBase to resolve this problem. At this point, I see no
indication that Borland is the least bit interested in doing this, so I
decided I'd at least post this message so everyone is familiar with the
problem.
I haven't had time to test with IB 6, so I'm not sure if this is a
problem with IB 6/Dialect 1 or not.
David R. Robinson
Director of Development
Sage U.S. Holdings, Inc.
InterBase Installation Info - http://ibinstall.defined.net
P.S. I have a sample Delphi project and small database that can be
easily used to reproduce the problem if anyone is interested.
Craig Stuntz (TeamB)
"David R. Robinson" wrote:
>
> Borland's so called "solution" is the first one is to use AsDateTime.
> AsDateTime returns the time portion and can give inaccurate query
> results due to the various values of the time portion of the field
> (that's why we are using AsDate to begin with). So, IMHO, that isn't a
> solution.
David, I use AsDateTime almost exclusively and it works correctly for
me. Rounding should not be an issue when the decimal portion of a float
is zero. Do you know of a situation where this does not work?
I agree that having to change your code is far from ideal, but I don't
think doing this should break anything. I'd appreciate hearing from
anyone who knows of something it would break. If your program is
designed for a 5.x server anyway then there's no such thing as a
date-only column.
We need to figure out for sure if this can be transparently substituted
or if it will break existing code. I looked at the TParam code and I
can't see anything which should be broken.
> Borland's response to the second (and more serious problem) is that
> InterBase does not support mismatched client and server versions,
Well, it doesn't, and it's hardly unusual in this respect.
> In my case, how am I going to prevent the 8,000 InterBase 5.x users that
> we have deployed from having one user somewhere update the BDE.
> Answer: I won't be able to. Since IB 6 is now out and there are many
> other applications using IB 6, there is a good chance that someone will
> update both the BDE and IB client and will have all users get
> disconnected from the database.
This is a real issue. Administrators need to know that a server and
all clients which use it need to be updated together. Your program
should work fine against a 6.x server (our software, which at present
only "officially" supports 5.6 works without modification on a 6.x
server). Your problem, as I see it, is not that someone might update
the BDE, but that someone might update the BDE *and* IB client, without
also updating IB server.
As InterBase becomes more popular, this is going to be a support issue
for those of us whose products are based upon IB. I am glad that
Borland fixed the ODS-9 compatibility issue in IB 6, because now we can
tell people to update to v6 if they have even one app which requires it
without breaking 5.6-based apps.
Firebird could be a real problem here as I don't believe it can coexist
with IB.
> What's worse is that this isn't even really an InterBase problem, it's a
> BDE problem. IMHO, the BDE team needs to update the BDE SQL Links DLL
> for InterBase to resolve this problem.
Even if Borland does this (and I'm not saying they shouldn't), it isn't
going to stop people from using mismatched clients and servers. It
might be a good idea if IB detected this.
> I haven't had time to test with IB 6, so I'm not sure if this is a
> problem with IB 6/Dialect 1 or not.
> P.S. I have a sample Delphi project and small database that can be
> easily used to reproduce the problem if anyone is interested.
Send it to me and I'll try v6 dialect 1.
-Craig
--
Craig Stuntz (TeamB) Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog: http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html
David R. Robinson
> me. Rounding should not be an issue when the decimal portion of a
float
> is zero. Do you know of a situation where this does not work?
We have had specific problems where the time portion of the field can
vary and causes queries to not retrieve all of the data. That's why we
have been using AsDate for a long time. Maybe it's not an issue with IB
and it is with another database. We support 6 different database
engines. I'll have to look into it and see. It would just be a whole
lot simpler if the BDE team would fix the DLL. :)
> I agree that having to change your code is far from ideal, but I don't
> think doing this should break anything.
There are other ways to update the code (for exmaple, you could use
Trunc). I just hate having to have customer calls and problems because
the BDE update is not backwards compatable. Also, we're talking about a
commercial application, not an in-house or corporate application where
it is easy to make changes and install a new version. We have customers
with hundreds of users and upgrades of any type (even simple patches)
are a major ordeal to them.
> > InterBase does not support mismatched client and server versions,
> Well, it doesn't, and it's hardly unusual in this respect.
I agree that don't normally expect mismatched versions of the
client/server to work perfectly. However, you also don't expect that if
one user happens to install D6 and IB 6 that all users on any database
on that server will be disconnnected either. :) The problem isn't an
InterBase problem, it's a BDE problem and can be resolved if the SQL
Links InterBase DLL was updated. IB works fine with IB 6 client and IB
5.x server.
> Your problem, as I see it, is not that someone might update
> the BDE, but that someone might update the BDE *and* IB
> client, without also updating IB server.
All someone has to do is install Delphi and pick the default settings
and it happens without them knowing it or the consequences.
Fortunately, I'm not sure we have a lot of customers that use Delphi,
but I know that some do.
If someone updates just the BDE, they will get the 1st error and it only
affects one user. This is not quite as bad. But you can guanratee that
it will generate a support call that our support department will have to
deal with. The chances of this will increase significantly once Borland
posts the update on the website.
If someone updates both, all user's connections will be dropped and
several people will probably lose data. We have a time-tracking
application that tracks every second of what someone is working on.
Data loss is likely and highly undesirable.
> As InterBase becomes more popular, this is going to be a support issue
> for those of us whose products are based upon IB. I am glad that
> Firebird could be a real problem here as I don't believe it can
coexist
> with IB.
Yes, I can see lots of problems as IB gets more popular. It's going to
be an install nightmare that could end up being as bad as the BDE
installation issues.
> Even if Borland does this (and I'm not saying they shouldn't), it
isn't
> going to stop people from using mismatched clients and servers. It
> might be a good idea if IB detected this.
Yes, that would be a good idea.
> Send it to me and I'll try v6 dialect 1.
Thanks. Will do. We just shipped the 4.0 update for our software a
couple of weeks ago, I took some time off to decompress and now I'm
trying to catch up on all of the things that I postponed for 4 months
plus getting my slides and final BorCon presentations ready.
David R.
Craig Stuntz (TeamB)
"David R. Robinson" wrote:
>
> We have had specific problems where the time portion of the field can
> vary and causes queries to not retrieve all of the data.
I've heard of this with SQL Server, but only when the time is non-zero.
> > > InterBase does not support mismatched client and server versions,
> > Well, it doesn't, and it's hardly unusual in this respect.
>
> I agree that don't normally expect mismatched versions of the
> client/server to work perfectly. However, you also don't expect that if
> one user happens to install D6 and IB 6 that all users on any database
> on that server will be disconnnected either. :)
No, ideally nothing would crash IB server. Unfortunately it's not
ideal in that respect (like most of the competition, FWIW). As it is,
there are some bugs, and a few of them can crash the server. That said,
mismatched client and server probably *shouldn't* work correctly, but it
would be nice if the error was a little more graceful.
> The problem isn't an
> InterBase problem, it's a BDE problem and can be resolved if the SQL
> Links InterBase DLL was updated. IB works fine with IB 6 client and IB
> 5.x server.
I think it's both. Bad data to the client should not crash the server,
even if it is bad data.
> Thanks. Will do.
Got it -- I'll look at this Monday.
Toni Martir
with the BDE 5.11 after installing Delphi 6, and of course don't install the
buggy BDE 5.2 in any client.
Also I didn't find a changes file from BDE 5.11 to BDE 5.2..., so why this
update if it do not take profit of Interbase 6 features?
It's incredible that betatesters not found this great bug, I have lot of
lines of code to change and debug to correct the problem...
I agree with you, this must be fixed now.
Toni Martir
> all clients which use it need to be updated together. Your program
> should work fine against a 6.x server (our software, which at present
> only "officially" supports 5.6 works without modification on a 6.x
> server). Your problem, as I see it, is not that someone might update
> the BDE, but that someone might update the BDE *and* IB client, without
> also updating IB server.
My problem is updating the BDE, if I left Interbase Client 5.6 and Interbase
server 5.6 my application didn't work because 'Translate Error'.
For now I restored the BDE 5.11 replacing BDE 5.2.
David R. Robinson
> update if it do not take profit of Interbase 6 features?
The InterBase SQL Links DLL update was changed so that people would have a
BDE driver that would support SQL Dialect 3 (the new default dialect for IB
6). Unfortunately, they didn't test it very well for backwards
compatability.
> It's incredible that betatesters not found this great bug, I have lot of
> lines of code to change and debug to correct the problem...
> I agree with you, this must be fixed now.
Any comments from someone on the BDE team? Is there any plan to fix this or
are we just out of luck? There's now 4 people that I know of that this
directly affects. What does it take to get this fixed?
David R.
Craig Stuntz (TeamB)
"Craig Stuntz (TeamB)" wrote:
>
> Got it -- I'll look at this Monday.
OK, I ran two tests:
TEST 1: IB 6.0.0.627 (old!) client and server, both on 2000.
TEST 2: IB 6.0.0.627 client on 2000 and IB 5.6 server on Win95.
Test 1 worked perfectly -- no crash. Test 2 crashed the server.
So it appears that IB6/dialect 1 is not an issue. The server will only
crash if the client and server are mismatched, which is documented as
unsupported.
FWIW,
Tsourinakis Antonis
Antonis Tsourinakis
Greece
On Sat, 7 Jul 2001 09:03:33 -0500, "David R. Robinson"
<ibin...@mail.com> wrote:
>
>Any comments from someone on the BDE team? Is there any plan to fix this or
>are we just out of luck? There's now 4 people that I know of that this
>directly affects. What does it take to get this fixed?
>
>David R.
>
Antonis Tsourinakis
Piraeus-Greece)
Stan Walker
Interbase 6 client. We are using Interbase 5.6. First he crashed the
database for everybody just like you said. After he removed the I6 client,
he got the translate error. Then he had to uninstall D6 and go back to D5. I
spent the evening looking for similar problems in the newgroups and I found
your post. Thanks a lot, at least I know we are not alone. We have a large
application, so I had to spend half a day changing all the AsDate to
AsDateTime, but at least we know what's happening. Thanks, Borland, for the
warning in the readme file.
David R. Robinson
to fix yours easily.
In my case we have thousands of users spread out all over the world.
Even if I did change our code, we have no way of forcing them to
upgrade. :(
David R.
"Stan Walker" <swa...@lamscowest.com> wrote in message
news:3b54b9e8$1_1@dnews...
David R. Robinson
BTW, another option is to:
* Make a backup copy of the D5 SQLLinks DLL (sqlint32.dll)
* Shut down the application that uses IB
* Install D6
* Replace sqlint32.dll with the older version you backed up
This will use BDE 5.2 with the older SQL Links DLL which should work
fine.
David R.