At 07:40 AM 03-10-01 +0800, you wrote:

>Hi,
>
>On 2 Oct 2001, at 15:35, Jason Wharton wrote:
>
> > > I think that's the problem: "the dialect of the db is 3". You must
> > > connect to it using dialect 3 also.
> > > Check the property "SQL Dialect" in you TIB_Connection.
> >
> > That won't be the problem because as a part of getting a connection IBO
> > asks the database to tell it what it is and this overrides what that is set
> > to. The only time you need to actually set it to something is if you are
> > calling CreateDatabase so that it knows what dialect to create it as.
>
>The column is defined as
>
> "MSG_DATE" DATE NOT NULL
>
>the data appears as 9/19/01 format. What could be the conflict here,
>does anyone else use a datatype of DATE ?

Are you sure that the gds32.dll on your client machine is not a 5.x version? If IBO won't connect your database as Dialect 3, check the version on the gds32.dll file on the client machine.

In which case, the general SQL error comes from passing a date-only literal to a type that requires a date + time literal.

CREATE TABLE "PAPURI"
(
"CARRIER" VARCHAR(10) NOT NULL,
"MSG_DATE" DATE NOT NULL,
"MSG_TIME" TIME NOT NULL, <----------------------------------------------------------
"TEXT_MESSAGE" BLOB SUB_TYPE TEXT SEGMENT SIZE 400
);

OTOH, in Dialect 3, you can't pass a date literal to the date and time types, you have to cast it:

UPDATE MYTABLE SET MYDATECOL = CAST('9/19/01' as DATE)

FYI, The DATE type of Dialect 1 is equivalent to the TIMESTAMP type of Dialect 3. Dialect 1 doesn't have a TIME type. In Dialect 3, the DATE type is date-only.

Also, I don't think BLOB SUB_TYPE TEXT is valid DDL either. BLOB SUB_TYPE 1 is what you specify for text blobs.

rgds,
Helen


All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________