I'm trying to update an MS Access-database using the following SQL:

UPDATE Table1 SET Table1.Time1=#9/21/1998 4:03:04 PM#

This works fine when run from Access but when run from my application
(Delphi 4) it gives me the following error:
"Field '03:04' is of an unknown type"

What could be wrong? Does it have something to do with BDE? Without the
time it also works ok but I really need the time.

Any help is well appreciated.

--
Henri Suuronen

What is happening is that delphi is seeing the colon : before the 03:04 and
thinking that you are trying to pass a parameter.  What you should do is
either put quotes around the date/time you are trying to pass or put it into
a parameter -

UPDATE Table1 SET Table1.Time1= :YOURDATETIME
query.ParambYname('YOURDATETIME').AsString := '9/21/1998 4:03:04 PM';

or something like that.

--
Michael Glatz
mgl...@briefcase.com

On Mon, 21 Sep 1998 16:08:19 +0300, Suuronen Henri

Is the TQuery.RequestLive property set to True? If so, try setting it to
False.

Microsoft Access uses a number of different nonstandard syntax conventions,
one of which is the use of the pound sign (#) to specify DATE and TIMESTAMP
literals in SQL statements (pretty much all other database systems use the
standard quotation marks for this purpose). Producing live queries by
setting RequestLive to True requires strict compliance with SQL-92
standards for SQL, the nonstandard syntax like this can cause problems.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                "I don't work for any company named Inpoop."
Technical Publications                           -- Steve Koterski, 1957-?
INPRISE Corporation
http://www.inprise.com/delphi

Now this is strange.... As Michael Glatz correctly stated I should use
quotes around my datetime-value. The following works perfectly:
UPDATE Table1 SET Table1.Time1="9/21/1998 4:03:04 PM"

So what's strange? The fact that Access wants it's dates surrounded with
'#'-characters. So does BDE do a conversion here or what?

--
Henri Suuronen

I tried the following with no luck:
UPDATE Table1 SET Table1.Time1 = "#9/21/1998 4:03:04 PM#"   -> type
mismatch
UPDATE Table1 SET Table1.Time1 = #"9/21/1998 4:03:04 PM"#   -> syntax
error in date

Why is BDE trying to preprocess the query? Is there any way to avoid
this?

--
Henri Suuronen

I tried setting the RequestLive-property to false but it didn't help.

I'm a little confused about this: these kind of extensions for SQL work
just fine using Visual Basic. I have used MS Access and MS SQL-Server
which both have different kind of extensions and had no problems. Is BDE
trying to do some preprocessing and why it is trying to do so? And can't
you really get an updatable result-set when using non-SQL-92-standard
syntax?

A previous reply suggested it had something to do with parameters in
query (the colon in time). I think that could be the reason but how to
work around it?

--
Henri Suuronen

On Tue, 22 Sep 1998 08:46:59 +0300, Suuronen Henri

[...]

You might also want to check the SQLQRYMODE parameter for the Microsoft
Access driver. Use the BDE Administrator to check or change this parameter.
If this parameter is currently set to LOCAL (or not set at all), try
setting it to SERVER.

Are you even a little surprised at this? Access, SQL Server, and Visual
Basic are all made by the same company (Microsoft). If a company chooses to
use nonstandard SQL syntax in its database back-end, I would expect their
own front-end applications to support the same nonstandard syntax -- no
matter what effect this might have on the front-end products of any other
company. Just because this nonstandard syntax works in a Microsoft product
does not make it any less a deviation from standard SQL. Try using pound
signs to delimit DATE literals in SQL for any other database type (Oracle,
Sybase, InterBase, Informix, or what-have-you).

This is one of the drawbacks of implementing nonstandard syntax. Other
companies that use industry-approved syntax in their products may not deal
well with the deviations -- or support it at all.

This could be the case. Then again, it might not be. What type of exception
is actually generated? I suspect an EDBEngineError. Try using exception
handling to trap the error. Parse the EDBEngineError exception object to
get more information on the error. The EDBEngineError.Errors property will
contain one or more TDBError objects, each reflecting a different error for
the same error condition. The TDBError object has a NativeError property.
If this property has a value, it could well indicate the error is being
raised by the Access data engine, and not the BDE. Whichever one initiates
the error, it will still be passed back to the application through the BDE
and become accessible to the front-end application as an EDBEngineError
exception object.

The updatability of a query result set is a product of behind-the-scenes
sleight-of-hand on the part of the BDE. The result set is really a static,
read-only set of rows and columns. When you change data values
(programmatically or through visual data controls), the BDE composes
statements to apply the updates to the base table (after which the result
set is refreshed to reflect the changes), these statements executed
invisibly in the background. In order to properly compose those updating
statements and update the correct rows in the base table, the original
statement used to retrieve the data is used, and that statement must be
usable to the BDE.

A decision was made to use industry-accepted standards for SQL in the
composing of these update statements. It is just not practical to extend
this to every nonstandard variation on the SQL language, like Joe-Bob's SQL
Database use of a CREATE CLOWN statement.

So often the choice may boil down to one of:

A. Use standard SQL and a live query.

B. Use nonstandard and a read-only query.

The latter can, of course, be overcome using a TClientDataSet component
(Client/Server and Enterprise editions, only) or a combination of cached
updates and update objects. Either will make what would otherwise be a dead
dataset into an updatable one.

This is possible. To force a TQuery to ignore colons in a statement (useful
when the colons do not denote a parameter), set the TQuery.ParamCheck
property to False. (In older versions, before advent of the ParamCheck
property, you had to double up the colons.)

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "What is success in this world? I would say
Technical Publications         it consists of four simple things: to live
INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
http://www.inprise.com/delphi  from it all, to learn a lot."
                                                     -- Richard J. Needham