Elke,
Perhaps we might have a language barrier. I am having difficulty
understanding what you are trying to tell me. It would be helpful if you
could show me exactly what you mean for an INSERT statement that works.
Do you mean to say that there is a MAXDB bug that prevents functions from
being used in an INSERT statement if one of the columns being inserted is
a LONG, even if the function is applied to a column with a data type other
than LONG? If so, then the following is the only syntax that will work
until the bug is fixed:
INSERT INTO test2 (
fragmentApplet,fragmentCreated,fragmentId
) VALUES (
'test applet',123456,3
)
Mike
> 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
>> \
>>
>>
> _______________________________________________
> sapdb.general mailing list
> [EMAIL PROTECTED]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
Best regards,
Michael Slinn
Founder & CTO
http://zamples.com
650-345-1140
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
