You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Radek Terber <ls...@post.cz> on 2006/09/18 14:09:56 UTC
Insert large using SQL
I need insert large text (over 100 KB) into column type CLOB (created as
max. available size - cca 2GB) using SQL command, but derby (latest
release) inserts only cca 32 KB. If text is larger, derby throws SQL
exception with description 'A string constant starting with ''inserted
text ... &' is too long'.
Is it a derby bug, or I do enything wrong ?
I am new in derby.
Thanks for every response.
Re: Insert large using SQL
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Radek Terber <ls...@post.cz> writes:
> I need insert large text (over 100 KB) into column type CLOB (created
> as max. available size - cca 2GB) using SQL command, but derby (latest
> release) inserts only cca 32 KB. If text is larger, derby throws SQL
> exception with description 'A string constant starting with ''inserted
> text ... &' is too long'.
I think this works (|| is the concatenation operator):
insert into t values
cast ('a long string, but not more than 32K ....' as clob) ||
cast ('next part... keep this one smaller than 32K too ....' as clob)
At least, I managed to insert a 33K clob this way.
--
Knut Anders
Re: Insert large using SQL
Posted by Radek Terber <ls...@post.cz>.
Michael Segel wrote:
>
>> -----Original Message-----
>> From: Radek Terber [mailto:lst.ara@post.cz]
>> Sent: Monday, September 18, 2006 11:21 AM
>> To: Derby Discussion
>> Subject: Re: Insert large using SQL
>>
>> Michael Segel wrote:
>>
>>>> -----Original Message-----
>>>> From: Radek Terber [mailto:lst.ara@post.cz]
>>>> Sent: Monday, September 18, 2006 10:56 AM
>>>> To: Derby Discussion
>>>> Subject: Re: Insert large using SQL
>>>>
>>>> Michael Segel wrote:
>>>>
>>>>
>>>>>> -----Original Message-----
>>>>>> From: Radek Terber [mailto:lst.ara@post.cz]
>>>>>> Sent: Monday, September 18, 2006 10:25 AM
>>>>>> To: Derby Discussion
>>>>>> Subject: Re: Insert large using SQL
>>>>>>
>>>>>> Thanks for response, but i have NO acces to database over JDBC, but
>>>>>>
>>>>>>
>>>> only
>>>>
>>>>
>>>>>> via text stream, thus i MUST use SQL commands (e.g. "INSERT INTO ....
>>>>>> (...) VALUES (...)" ).
>>>>>> I cannot call metgods of JDBC driver directly.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> Ok,
>>>>> Uhm, silly question.
>>>>>
>>>>> If you can access the database via a text stream, why can't you access
>>>>>
>>>>>
>>>> it
>>>>
>>>>
>>>>> via JDBC?
>>>>>
>>>>>
>>>>>
>>>> because the database is on remote server, vhich comunicate via text
>>>> stream with client program. I have no direct access to this server.
>>>> At this time, we are using several other databases (Sybase ASE, Borland
>>>> JDataStore, Oracle, Hypersinic ...), and all them have method to insert
>>>> large texts.
>>>> We are testing the Derby database, because it is free, pure java,
>>>> simple, speed .... and this appears as only limitation to use it.
>>>>
>>>>
>>> [mjs]
>>> Ok,
>>> So if I understand you...
>>>
>>> ClientA<--->ServerA<--->RDBMS
>>>
>>> Where, ClientA connects via TCP/IP to an app on ServerA that listens to
>>>
>> the
>>
>>> port, passing the SQL statements to the RDBMS which resides on ServerA.
>>>
>>> Is this correct?
>>> If so, how is the app on ServerA communicating to the RDBMS?
>>>
>>>
>> Yes, it is correct.
>> ServerA communicating with RDBMS using JDBC, but it use only "limited"
>> subset of JDBC methodst (the system is desired to "universal as
>> possible").
>> The server 'ServerA' does much more operations than only comunicate with
>> RDBMS. It is inpossible for us rewrite server at this moment.
>>
>>
>>
>>
>
> [mjs]
> You wouldn't have to "rewrite" your application.
> (Well, ok, that's making some assumptions...)
>
> A certain amount of code is going to be database centric.
>
> I'm going on an assumption that the Server side application is using JDBC's
> standard method(s) for writing a C/B/LOB to the database?
>
The application is generated using standard SQL queries (in text form).
Those qyeries are sent to server where are executed as
'Statement.executeQuery(...), Statement.execute(...)'.
The server does only 'microanalyze' of SQL begin to determine type of
SQL query, but it NOT parse whole query.
For 99% situations, the size of column 32 KB i suficient. But we have
several situations where is large text (HTML pages, for example).
Entire system (eg. clieents, server ..) is stable, well tested and
functional, using in hundresd slient mosules. One of them qs 'MSQL'
(interactive SQL commander/builder).
> If so, then it may make sense for you to review your design and to see how
> you're passing the data to your server side app. Even though its currently
> working with other databases, you may find that there's a better way so that
> any JDBC compliant databases will work.
>
> Again, I haven't seen your code, so take my observation with a grain of
> Kosher Salt.
>
I know than the server desigm may be better, but it is not priority task
for this day. My original query was if can i insert large text using
text form of SQL query (which is on server prodessed by
Statement.execute(...) ). If not, we continue using other databases.
RE: Insert large using SQL
Posted by Michael Segel <ms...@segel.com>.
> -----Original Message-----
> From: Radek Terber [mailto:lst.ara@post.cz]
> Sent: Monday, September 18, 2006 11:21 AM
> To: Derby Discussion
> Subject: Re: Insert large using SQL
>
> Michael Segel wrote:
> >> -----Original Message-----
> >> From: Radek Terber [mailto:lst.ara@post.cz]
> >> Sent: Monday, September 18, 2006 10:56 AM
> >> To: Derby Discussion
> >> Subject: Re: Insert large using SQL
> >>
> >> Michael Segel wrote:
> >>
> >>>> -----Original Message-----
> >>>> From: Radek Terber [mailto:lst.ara@post.cz]
> >>>> Sent: Monday, September 18, 2006 10:25 AM
> >>>> To: Derby Discussion
> >>>> Subject: Re: Insert large using SQL
> >>>>
> >>>> Thanks for response, but i have NO acces to database over JDBC, but
> >>>>
> >> only
> >>
> >>>> via text stream, thus i MUST use SQL commands (e.g. "INSERT INTO ....
> >>>> (...) VALUES (...)" ).
> >>>> I cannot call metgods of JDBC driver directly.
> >>>>
> >>>>
> >>>>
> >>>>
> >>> Ok,
> >>> Uhm, silly question.
> >>>
> >>> If you can access the database via a text stream, why can't you access
> >>>
> >> it
> >>
> >>> via JDBC?
> >>>
> >>>
> >> because the database is on remote server, vhich comunicate via text
> >> stream with client program. I have no direct access to this server.
> >> At this time, we are using several other databases (Sybase ASE, Borland
> >> JDataStore, Oracle, Hypersinic ...), and all them have method to insert
> >> large texts.
> >> We are testing the Derby database, because it is free, pure java,
> >> simple, speed .... and this appears as only limitation to use it.
> >>
> >
> > [mjs]
> > Ok,
> > So if I understand you...
> >
> > ClientA<--->ServerA<--->RDBMS
> >
> > Where, ClientA connects via TCP/IP to an app on ServerA that listens to
> the
> > port, passing the SQL statements to the RDBMS which resides on ServerA.
> >
> > Is this correct?
> > If so, how is the app on ServerA communicating to the RDBMS?
> >
> Yes, it is correct.
> ServerA communicating with RDBMS using JDBC, but it use only "limited"
> subset of JDBC methodst (the system is desired to "universal as
> possible").
> The server 'ServerA' does much more operations than only comunicate with
> RDBMS. It is inpossible for us rewrite server at this moment.
>
>
>
[mjs]
You wouldn't have to "rewrite" your application.
(Well, ok, that's making some assumptions...)
A certain amount of code is going to be database centric.
I'm going on an assumption that the Server side application is using JDBC's
standard method(s) for writing a C/B/LOB to the database?
If so, then it may make sense for you to review your design and to see how
you're passing the data to your server side app. Even though its currently
working with other databases, you may find that there's a better way so that
any JDBC compliant databases will work.
Again, I haven't seen your code, so take my observation with a grain of
Kosher Salt.
Re: Insert large using SQL
Posted by Radek Terber <ls...@post.cz>.
Michael Segel wrote:
>> -----Original Message-----
>> From: Radek Terber [mailto:lst.ara@post.cz]
>> Sent: Monday, September 18, 2006 10:56 AM
>> To: Derby Discussion
>> Subject: Re: Insert large using SQL
>>
>> Michael Segel wrote:
>>
>>>> -----Original Message-----
>>>> From: Radek Terber [mailto:lst.ara@post.cz]
>>>> Sent: Monday, September 18, 2006 10:25 AM
>>>> To: Derby Discussion
>>>> Subject: Re: Insert large using SQL
>>>>
>>>> Thanks for response, but i have NO acces to database over JDBC, but
>>>>
>> only
>>
>>>> via text stream, thus i MUST use SQL commands (e.g. "INSERT INTO ....
>>>> (...) VALUES (...)" ).
>>>> I cannot call metgods of JDBC driver directly.
>>>>
>>>>
>>>>
>>>>
>>> Ok,
>>> Uhm, silly question.
>>>
>>> If you can access the database via a text stream, why can't you access
>>>
>> it
>>
>>> via JDBC?
>>>
>>>
>> because the database is on remote server, vhich comunicate via text
>> stream with client program. I have no direct access to this server.
>> At this time, we are using several other databases (Sybase ASE, Borland
>> JDataStore, Oracle, Hypersinic ...), and all them have method to insert
>> large texts.
>> We are testing the Derby database, because it is free, pure java,
>> simple, speed .... and this appears as only limitation to use it.
>>
>
> [mjs]
> Ok,
> So if I understand you...
>
> ClientA<--->ServerA<--->RDBMS
>
> Where, ClientA connects via TCP/IP to an app on ServerA that listens to the
> port, passing the SQL statements to the RDBMS which resides on ServerA.
>
> Is this correct?
> If so, how is the app on ServerA communicating to the RDBMS?
>
Yes, it is correct.
ServerA communicating with RDBMS using JDBC, but it use only "limited"
subset of JDBC methodst (the system is desired to "universal as possible").
The server 'ServerA' does much more operations than only comunicate with
RDBMS. It is inpossible for us rewrite server at this moment.
RE: Insert large using SQL
Posted by Michael Segel <ms...@segel.com>.
> -----Original Message-----
> From: Radek Terber [mailto:lst.ara@post.cz]
> Sent: Monday, September 18, 2006 10:56 AM
> To: Derby Discussion
> Subject: Re: Insert large using SQL
>
> Michael Segel wrote:
> >
> >> -----Original Message-----
> >> From: Radek Terber [mailto:lst.ara@post.cz]
> >> Sent: Monday, September 18, 2006 10:25 AM
> >> To: Derby Discussion
> >> Subject: Re: Insert large using SQL
> >>
> >> Thanks for response, but i have NO acces to database over JDBC, but
> only
> >> via text stream, thus i MUST use SQL commands (e.g. "INSERT INTO ....
> >> (...) VALUES (...)" ).
> >> I cannot call metgods of JDBC driver directly.
> >>
> >>
> >>
> > Ok,
> > Uhm, silly question.
> >
> > If you can access the database via a text stream, why can't you access
> it
> > via JDBC?
> >
> because the database is on remote server, vhich comunicate via text
> stream with client program. I have no direct access to this server.
> At this time, we are using several other databases (Sybase ASE, Borland
> JDataStore, Oracle, Hypersinic ...), and all them have method to insert
> large texts.
> We are testing the Derby database, because it is free, pure java,
> simple, speed .... and this appears as only limitation to use it.
[mjs]
Ok,
So if I understand you...
ClientA<--->ServerA<--->RDBMS
Where, ClientA connects via TCP/IP to an app on ServerA that listens to the
port, passing the SQL statements to the RDBMS which resides on ServerA.
Is this correct?
If so, how is the app on ServerA communicating to the RDBMS?
Re: Insert large using SQL
Posted by Radek Terber <ls...@post.cz>.
Michael Segel wrote:
>
>> -----Original Message-----
>> From: Radek Terber [mailto:lst.ara@post.cz]
>> Sent: Monday, September 18, 2006 10:25 AM
>> To: Derby Discussion
>> Subject: Re: Insert large using SQL
>>
>> Thanks for response, but i have NO acces to database over JDBC, but only
>> via text stream, thus i MUST use SQL commands (e.g. "INSERT INTO ....
>> (...) VALUES (...)" ).
>> I cannot call metgods of JDBC driver directly.
>>
>>
>>
> Ok,
> Uhm, silly question.
>
> If you can access the database via a text stream, why can't you access it
> via JDBC?
>
because the database is on remote server, vhich comunicate via text
stream with client program. I have no direct access to this server.
At this time, we are using several other databases (Sybase ASE, Borland
JDataStore, Oracle, Hypersinic ...), and all them have method to insert
large texts.
We are testing the Derby database, because it is free, pure java,
simple, speed .... and this appears as only limitation to use it.
RE: Insert large using SQL
Posted by Michael Segel <ms...@segel.com>.
> -----Original Message-----
> From: Radek Terber [mailto:lst.ara@post.cz]
> Sent: Monday, September 18, 2006 10:25 AM
> To: Derby Discussion
> Subject: Re: Insert large using SQL
>
> Thanks for response, but i have NO acces to database over JDBC, but only
> via text stream, thus i MUST use SQL commands (e.g. "INSERT INTO ....
> (...) VALUES (...)" ).
> I cannot call metgods of JDBC driver directly.
>
>
Ok,
Uhm, silly question.
If you can access the database via a text stream, why can't you access it
via JDBC?
Re: Insert large using SQL
Posted by Radek Terber <ls...@post.cz>.
Thanks for response, but i have NO acces to database over JDBC, but only
via text stream, thus i MUST use SQL commands (e.g. "INSERT INTO ....
(...) VALUES (...)" ).
I cannot call metgods of JDBC driver directly.
Thomas Kellerer wrote:
> Radek Terber wrote on 18.09.2006 14:09:
>> I need insert large text (over 100 KB) into column type CLOB (created
>> as max. available size - cca 2GB) using SQL command, but derby
>> (latest release) inserts only cca 32 KB. If text is larger, derby
>> throws SQL exception with description 'A string constant starting
>> with ''inserted text ... &' is too long'.
>> Is it a derby bug, or I do enything wrong ?
>> I am new in derby.
>>
>
> When dealing with CLOB columns I always use the setCharacterStream()
> method to supply the values:
>
> String clobContent = whatever...
> stmt = con.prepareStatement("INSERT INTO clob_table (id, clob_col)
> VALUES (?,?)";
> stmt.setInt(1, 42);
> Reader r = new StringReader(clobContent);
> stmt.setCharacterStream(2, r, clobContent.length());
> stmt.executeUpdate();
> con.commit();
>
>
> This works fine accross most of the JDBC drivers I came across so far
> including Derby.
>
> Hope this helps
>
> Thomas
>
>
Re: Insert large using SQL
Posted by Thomas Kellerer <sp...@gmx.net>.
Radek Terber wrote on 18.09.2006 14:09:
> I need insert large text (over 100 KB) into column type CLOB (created as
> max. available size - cca 2GB) using SQL command, but derby (latest
> release) inserts only cca 32 KB. If text is larger, derby throws SQL
> exception with description 'A string constant starting with ''inserted
> text ... &' is too long'.
> Is it a derby bug, or I do enything wrong ?
> I am new in derby.
>
When dealing with CLOB columns I always use the setCharacterStream() method to
supply the values:
String clobContent = whatever...
stmt = con.prepareStatement("INSERT INTO clob_table (id, clob_col) VALUES (?,?)";
stmt.setInt(1, 42);
Reader r = new StringReader(clobContent);
stmt.setCharacterStream(2, r, clobContent.length());
stmt.executeUpdate();
con.commit();
This works fine accross most of the JDBC drivers I came across so far including
Derby.
Hope this helps
Thomas