RE: Changes in 7.4 re. LONG

Michael Slinn wrote:
> 
> I am using SAPDB 7.4.3.27 on RedHat 9 and on Windows 2000 Professional
> with identical results.
> 
> CREATE TABLE test2 (
>    fragmentApplet  LONG VARCHAR DEFAULT '',
>    fragmentCreated TIMESTAMP    DEFAULT timestamp,
>    fragmentId      integer      NOT NULL PRIMARY KEY
> )
> // This works:
> INSERT INTO test2 (
>    fragmentCreated,fragmentId
>    ) VALUES (
>    timestamp('1999-01-01 14:30:08'),1
> )
> // This works too:
> INSERT INTO test2 (
>    fragmentApplet,fragmentId
>    ) VALUES (
>    'test applet',2
> )
> 
> // This doesn't work:
> INSERT INTO test2 (
>    fragmentApplet,fragmentCreated,fragmentId
>    ) VALUES (
>    'test applet',timestamp('1999-01-23 14:30:08'),3 )

ah, here we are.
You are using a function in the insert which causes the insert
to be handled differently from insert without functions.
And it is a superfluous function.
MaxDB knows by its own, that a string-literal specified at a position
where a date/time/timestamp is expected, IS that date/time/timestamp-value,
without a function around.

--> just use VALUES ('......', <integer>) and it will do.

BTW: even other functions have to be avoided with a normal insert
if a LONG-column is part of the insert-table and that long-column will be
filled with any value <> NULL, no matter if by specifying that value explicitly or by
using the default-value of that column.
If you use a function in that case, the LONG-value will not be stored correctly
resulting in different (not very funny) errors during selecting that data.
A bugfix for this will be part of the next version.

Elke
SAP Labs Berlin

> // ---- Error -------------------------------
> // Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
> // Integrity constraint violation;-8006 POS(1) Data types must be
> compatible:FRAGMENTAPPLET.
> 
> Mike
> 
> 
> 
> > Michael Slinn wrote in the mailing list and for an unknown reason
> > he asked our support, too:
> >
> >> Because SAPDB has a limitation of 8K per record, I need to 
> use BLOBs
> >> (called  LONG VARCHAR by SAPDB).
> >
> > They are called LONG. The keyword VARCHAR is just for show and
> > compatibility reasons.
> >
> >> The issues that arise are:
> >>  - Need to be able to perform an in-process search of that field
> >> throughout the database; via SQL would be best, via some internal
> >> programming interface would second best
> >
> > As written here some dozend time:
> > searching a long-column is not possible with MaxDB.
> > Longs have to be read to the client and checked there.
> >
> >
> >>  - Insertion of string values into a blob was not possible prior to
> >> 7.4,
> >> but it may be possible now.
> >
> > This is not true. It was possible before. And it is possible now.
> >
> >>
> >> If a field BIG is defined as CHAR or VARCHAR, one would write:
> >>
> >> CREATE TABLE Users (
> >>    mykey number NOT NULL PRIMARY KEY,
> >>    big   varchar(5000) DEFAULT ''
> >> )
> >> //
> >> INSERT INTO mytable (mykey,big) VALUES (1234,'blah')
> >>
> >> This does not work if big is defined to be LONG VARCHAR.  
> What is the
> >> SQL syntax for the insert if big is LONG VARCHAR?
> >
> > This is not true.
> > I used 7.4.3.38
> > I used your statements written here.
> > I changed of course the unknown tablename mytable to the 
> known tablename
> > users. And it worked perfectly.
> >
> > If you will not tell us
> > - the full version number you are using.
> > - the error which occurs
> > - the statement which may look different to that given here 
> (this will
> > cause error -4004) it is of no use to repeat a question 
> using different
> > channels to MaxDB
> >
> > Elke
> > SAP Labs Berlin
> >
> >>
> >> CREATE TABLE Users (
> >>    mykey number NOT NULL PRIMARY KEY,
> >>    big   LONG VARCHAR DEFAULT ''
> >> )
> >> //
> >> INSERT INTO mytable (mykey,big) VALUES (1234,'what magic 
> goes here?')
> >>
> >> Mike
> >>
> >>
> >> > Michael Slinn wrote:
> >> >> Specifically, I am looking to be able to INSERT a record
> >> with a LONG
> >> >> VARCHAR (and to discover what the syntax might be), and 
> to SELECT
> >> records based on the contents of a LONG VARCHAR, and to compare a
> >> LONG
> >> >>  VARCHAR with a string like '%keyword%'.
> >> >
> >> > To insert LONG values, it is best to use
> >> PreparedStatements. Although
> >> > you can have LONG literals in INSERTs, there are 
> restricted in size.
> >> The
> >> >  is no way to use LONG columns in predicates (except for 
> IS NULL I
> >> > think).
> >> >
> >> > Daniel Dittmar
> >> >
> >> > --
> >> > Daniel Dittmar
> >> > SAP DB, SAP Labs Berlin
> >> > [email protected]
> >> > http://www.sapdb.org
> \
> 
>