Re: Size of CLOB...

2023-03-24 Thread John English 

On 24/03/2023 16:33, Russell Bateman wrote:
Late yesterday afternoon, I hit upon the idea of inserting a '?' where 
the CLOB would be, then using PreparedStatement to satisfy it. *This 
worked and I'm able to insert even 20Mb-long rows*:


And you know what, you should always do this anyway to avoid SQL 
injection attacks.


--
John English

Re: Size of CLOB...

2023-03-24 Thread John English 

On 24/03/2023 16:33, Russell Bateman wrote:
Late yesterday afternoon, I hit upon the idea of inserting a '?' where 
the CLOB would be, then using PreparedStatement to satisfy it. *This 
worked and I'm able to insert even 20Mb-long rows*:


Yes, the limit is purely a limit on the length of string literals, not 
on the clob itself.


--
John English

Re: Size of CLOB...

2023-03-24 Thread Russell Bateman 
Late yesterday afternoon, I hit upon the idea of inserting a '?' where 
the CLOB would be, then using PreparedStatement to satisfy it. *This 
worked and I'm able to insert even 20Mb-long rows*:


final String INSERT_CLOB = "INSERT INTO ... ? ...)"; Clob  clob  =  
connection.createClob();

clob.setString(1,MsgContent  );

PreparedStatement  preparedStatement  =  
connection.prepareStatement(INSERT_CLOB  );

preparedStatement.setClob(1,clob  );


Your answers, received just this morning, would have led me to this same 
solution. Thank you, very much!


Love Derby, I do!

Russ

On 3/23/23 14:54, Russell Bateman wrote:
In fact, experimentation seems to suggest that the breaking point is a 
length of 32K+. Whether or not the length of the rest of the INSERT 
INTO statement counts I do not know for certain, but the delta between 
MsgContent and the rest is negligeable, so I don't care.


On 3/23/23 13:02, Russell Bateman wrote:
According to what I'm reading, a CLOB should easily hold more than 
the 128K I'm trying to put into it.


My table schema:
private static final StringTABLE_SCHEMA ="\n" +" MsgId BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,\n" +" MsgContent CLOB DEFAULT NULL,\n" +" MsgReceived TIMESTAMP NOT NULL WITH DEFAULT 
CURRENT_TIMESTAMP,\n" +" MsgSource VARCHAR(256) DEFAULT NULL,\n" +" MsgFacility VARCHAR(256) DEFAULT NULL,\n" +" MsgType1 VARCHAR(256) DEFAULT NULL,\n" +" MsgType2 VARCHAR(256) DEFAULT NULL,\n" 
+" MsgAltId1 VARCHAR(256) DEFAULT NULL,\n" +" MsgAltId2 VARCHAR(256) DEFAULT NULL,\n" +" MsgMisc1 VARCHAR(256) DEFAULT NULL,\n" +" MsgMisc2 VARCHAR(256) DEFAULT NULL,\n" +" MsgMisc3 VARCHAR(256) 
DEFAULT NULL,\n" +" MsgStatus CHAR(3) DEFAULT NULL,\n" +" MsgStatusDate TIMESTAMP DEFAULT NULL,\n" +" MsgStatusInfo VARCHAR(256) DEFAULT NULL\n";
My insert string:

*INSERT INTO* ssdi_small_tbl( MsgContent, MsgReceived, MsgSource,
MsgFacility, MsgType1, MsgType2, MsgAltId1, MsgAltId2, MsgMisc1,
MsgStatus, MsgStatusDate, MsgStatusInfo )
*VALUES*(
    '

',
'2023-02-23 12:49:13',
'MIRTH_0ed9b98c-a94f-4c38-964800743a00c0061',
    'MOCKH',
    'ADT-A28',
    'EPIC',
    '1',
'228750038',
    'ZZZ TEST NEW',
    'REJ',
'2023-03-23 12:49:13',
    'Test Messages'
 )


My Java code performing the insertion:
try {
   PreparedStatement preparedStatement = connection.prepareStatement( INSERT );
   return( preparedStatement.executeUpdate() ==1 );
}
catch( SQLException throwables )
{
   throwables.printStackTrace();
   return false;
}
The SQLException I'm getting:

A string constant starting with "
Shorter insert string. For this string, Derby's happy and I am able 
to perform the insertion as well as select out the row later 
successfully (same code, etc.):
static final StringCONTENTS ="'MSH|^~&|LAB|HCA_OGDR|LAB||201112051038||ORU^R01^ORU_R01|4257313..LAB.COCCBM|P|2.1\\r\n" 
+" 
PID|1|665892|23422234234^^^HCA_OGDR||HERMAN^MUNSTER||18501031|M||W|1313 
MOCKINGBIRD LANE^^MOCKINGBIRD 
HEIGHTS^CA^90210||^626^2959626||English|M^Married^HL73202|Catholic|Q02690008175|529609893||202005061342|\\r\n" 
+" 
PV1|1|I|Q.IMC^Q.109^A|EM|||VICTOR^FRANKENSTEIN^IGOR^HCA||VICTOR^FRANKENSTEIN^IGOR^HCA|IN||01|||COCCBM||ADM|||202002040238\\r\n" 
+" NK1|0222555|MUNSTER^LILY^DRACULA|FA|1313 MOCKINGBIRD 
LANE^^MOCKINGBIRD HEIGHTS^CA^90210|(626)111-Mockingbird 
Heights Clinic\\r\n" +" OBR|1|L3266930^LAB|^LAB|BMP^BASIC METABOLIC 
PANEL^L|||201312050507|||QLB.XXP|||DT699 
STROKE|202002050514||VICTOR^FRANKENSTEIN^^HCA||1205:CBM:C03008R201112051038|||F\\r\n" 
+" NTE|1\\r\n" +" NTE|2||Site Legend:.brML:MOCKINGBIRD HEIGHTS CLINIC 15475 
NORTH HOLLYWOOD BOULEVARD MOCKINGBIRD HEIGHTS CA 90210.br\\r\n" +" TQ1|R^Routine^HL70485\\r\n" +" 
OBX|1|NM|NA^SODIUM^L^2951-2^SODIUM^LN|1|144|mmol/L|136-145|N||AS|F|201108241525|ML:MOCKINGBIRD 
HEIGHTS CLINIC|||ML:MOCKINGBIRD HEIGHTS CLINIC^15475 NORTH 
HOLLYWOOD BOULEVARD MOCKINGBIRD HEIGHTS CA 90210\\r\n" +" NTE|1|AD|Performed At: ML\\r\n" +" OBX|25|ST|GFR^GLOMERULAR FILTRATION 
RATE^L|1|>60||60-130|N||AS|F|201104071132|ML:MOCKINGBIRD HEIGHTS 
CLINIC|||ML:MOCKINGBIRD HEIGHTS CLINIC^15475 NORTH HOLLYWOOD 
BOULEVARD MOCKINGBIRD HEIGHTS CA 90210\\r\n" +" NTE|1|AD|Performed At: ML\\r\n" +" NTE|1||If the patient is African American, multiply by 1.210\\r\n" +" NTE|2\\r\n" +" NTE|3||eGFR Reference Range for adults =60 ml/min/1.73 m2\\r\n" +" 
OBR|2|L3261930^LAB|^LAB|PHOS^PHOSPHORUS^L|||202002050507|||QLB.XXP|||DT699 
STROKE|201112050514||FRANKENSTEIN^Victor^IGOR^HCA||1205:CBM:C8R202002051038|||F\\r 
NTE|1\\r\n" +" NTE|2||Site Legend:.brML:MOCKINGBIRD HEIGHTS CLINIC15475 
NORTH HOLLYWOOD BOULEVARD MOCKINGBIRD HEIGHTS CA 90210.br\\r\n" +" TQ1|R^Routine^HL70485\\r\n" +"

Re: Size of CLOB...

2023-03-24 Thread John English 

On 24/03/2023 07:38, Stanimir Stamenkov via derby-user wrote:

Thu, 23 Mar 2023 14:54:50 -0600, /Russell Bateman/:

In fact, experimentation seems to suggest that the breaking point is a 
length of 32K+.


This seems to match the VARCHAR and LONG VARCHAR specifications:

* https://db.apache.org/derby/docs/10.16/ref/rrefsqlj41207.html
* https://db.apache.org/derby/docs/10.16/ref/rrefsqlj15147.html

This happens to be the string literal in your INSERT statement.  If you 
want to insert more than 32K in your CLOB column you would need to use 
the JDBC interfaces:


Or:

INSERT INTO foo(id,myclob) VALUES(rownum,'32K of data');
UPDATE foo SET myclob = myclob || 'another 32K of data' WHERE id=rownum;
UPDATE foo SET myclob = myclob || 'yet another 32K' WHERE id=rownum;

and so on.
--
John English

Re: Size of CLOB...

2023-03-24 Thread Marco Ferretti 
I can confirm this: my application happily inserts CLOB data well over the 32K 
using JPA

On Mar 24 2023, at 5:38 am, Stanimir Stamenkov via derby-user 
 wrote:
> Thu, 23 Mar 2023 14:54:50 -0600, /Russell Bateman/:
>
> > In fact, experimentation seems to suggest that the breaking point is a
> > length of 32K+.
>
> This seems to match the VARCHAR and LONG VARCHAR specifications:
> * https://db.apache.org/derby/docs/10.16/ref/rrefsqlj41207.html
> * https://db.apache.org/derby/docs/10.16/ref/rrefsqlj15147.html
>
> This happens to be the string literal in your INSERT statement. If you
> want to insert more than 32K in your CLOB column you would need to use
> the JDBC interfaces:
>
> *
> https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/PreparedStatement.html#setCharacterStream(int,java.io.Reader,int)
> *
> https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/PreparedStatement.html#setClob(int,java.io.Reader,long)
>
> and for reading:
> *
> https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/ResultSet.html#getCharacterStream(int)
> *
> https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/ResultSet.html#getCharacterStream(int)
>
> – Stanimir
>
> > On 3/23/23 13:02, Russell Bateman wrote:
> >
> >> According to what I'm reading, a CLOB should easily hold more than the
> >> 128K I'm trying to put into it.
> >>
> >> [...]
> >> My insert string:
> >>
> >> INSERT INTO ssdi_small_tbl( MsgContent, MsgReceived, MsgSource,
> >> MsgFacility, MsgType1, MsgType2, MsgAltId1, MsgAltId2, MsgMisc1,
> >> MsgStatus, MsgStatusDate, MsgStatusInfo )
> >> VALUES(
> >> '
> >>  >> xmlns:sdtc="urn:hl7-org:sdtc"...
> >> ...about 128K of well formed XML...
> >> /
> >> ',
> >> [...]
>
> --

Re: Size of CLOB...

2023-03-23 Thread Stanimir Stamenkov via derby-user 

Thu, 23 Mar 2023 14:54:50 -0600, /Russell Bateman/:

In fact, experimentation seems to suggest that the breaking point is a 
length of 32K+.


This seems to match the VARCHAR and LONG VARCHAR specifications:

* https://db.apache.org/derby/docs/10.16/ref/rrefsqlj41207.html
* https://db.apache.org/derby/docs/10.16/ref/rrefsqlj15147.html

This happens to be the string literal in your INSERT statement.  If you 
want to insert more than 32K in your CLOB column you would need to use 
the JDBC interfaces:


* 
https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/PreparedStatement.html#setCharacterStream(int,java.io.Reader,int)
* 
https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/PreparedStatement.html#setClob(int,java.io.Reader,long)


and for reading:

* 
https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/ResultSet.html#getCharacterStream(int)
* 
https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/ResultSet.html#getCharacterStream(int)


– Stanimir



On 3/23/23 13:02, Russell Bateman wrote:

According to what I'm reading, a CLOB should easily hold more than the 
128K I'm trying to put into it.


[...]
My insert string:

INSERT INTO ssdi_small_tbl( MsgContent, MsgReceived, MsgSource,
MsgFacility, MsgType1, MsgType2, MsgAltId1, MsgAltId2, MsgMisc1,
MsgStatus, MsgStatusDate, MsgStatusInfo )
VALUES(
    '

',
[...]


--

Re: Size of CLOB...

2023-03-23 Thread Russell Bateman 
In fact, experimentation seems to suggest that the breaking point is a 
length of 32K+. Whether or not the length of the rest of the INSERT INTO 
statement counts I do not know for certain, but the delta between 
MsgContent and the rest is negligeable, so I don't care.


On 3/23/23 13:02, Russell Bateman wrote:
According to what I'm reading, a CLOB should easily hold more than the 
128K I'm trying to put into it.


My table schema:
private static final StringTABLE_SCHEMA ="\n" +" MsgId BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,\n" +" MsgContent CLOB DEFAULT NULL,\n" +" MsgReceived TIMESTAMP NOT NULL WITH DEFAULT 
CURRENT_TIMESTAMP,\n" +" MsgSource VARCHAR(256) DEFAULT NULL,\n" +" MsgFacility VARCHAR(256) DEFAULT NULL,\n" +" MsgType1 VARCHAR(256) DEFAULT NULL,\n" +" MsgType2 VARCHAR(256) DEFAULT NULL,\n" 
+" MsgAltId1 VARCHAR(256) DEFAULT NULL,\n" +" MsgAltId2 VARCHAR(256) DEFAULT NULL,\n" +" MsgMisc1 VARCHAR(256) DEFAULT NULL,\n" +" MsgMisc2 VARCHAR(256) DEFAULT NULL,\n" +" MsgMisc3 VARCHAR(256) 
DEFAULT NULL,\n" +" MsgStatus CHAR(3) DEFAULT NULL,\n" +" MsgStatusDate TIMESTAMP DEFAULT NULL,\n" +" MsgStatusInfo VARCHAR(256) DEFAULT NULL\n";
My insert string:

*INSERT INTO* ssdi_small_tbl( MsgContent, MsgReceived, MsgSource,
MsgFacility, MsgType1, MsgType2, MsgAltId1, MsgAltId2, MsgMisc1,
MsgStatus, MsgStatusDate, MsgStatusInfo )
*VALUES*(
    '

',
    '2023-02-23 12:49:13',
'MIRTH_0ed9b98c-a94f-4c38-964800743a00c0061',
    'MOCKH',
    'ADT-A28',
    'EPIC',
    '1',
    '228750038',
    'ZZZ TEST NEW',
    'REJ',
    '2023-03-23 12:49:13',
    'Test Messages'
 )


My Java code performing the insertion:
try {
   PreparedStatement preparedStatement = connection.prepareStatement( INSERT );
   return( preparedStatement.executeUpdate() ==1 );
}
catch( SQLException throwables )
{
   throwables.printStackTrace();
   return false;
}
The SQLException I'm getting:

A string constant starting with "
Shorter insert string. For this string, Derby's happy and I am able to 
perform the insertion as well as select out the row later successfully 
(same code, etc.):
static final StringCONTENTS ="'MSH|^~&|LAB|HCA_OGDR|LAB||201112051038||ORU^R01^ORU_R01|4257313..LAB.COCCBM|P|2.1\\r\n" 
+" 
PID|1|665892|23422234234^^^HCA_OGDR||HERMAN^MUNSTER||18501031|M||W|1313 
MOCKINGBIRD LANE^^MOCKINGBIRD 
HEIGHTS^CA^90210||^626^2959626||English|M^Married^HL73202|Catholic|Q02690008175|529609893||202005061342|\\r\n" 
+" 
PV1|1|I|Q.IMC^Q.109^A|EM|||VICTOR^FRANKENSTEIN^IGOR^HCA||VICTOR^FRANKENSTEIN^IGOR^HCA|IN||01|||COCCBM||ADM|||202002040238\\r\n" 
+" NK1|0222555|MUNSTER^LILY^DRACULA|FA|1313 MOCKINGBIRD 
LANE^^MOCKINGBIRD HEIGHTS^CA^90210|(626)111-Mockingbird 
Heights Clinic\\r\n" +" OBR|1|L3266930^LAB|^LAB|BMP^BASIC METABOLIC 
PANEL^L|||201312050507|||QLB.XXP|||DT699 
STROKE|202002050514||VICTOR^FRANKENSTEIN^^HCA||1205:CBM:C03008R201112051038|||F\\r\n" 
+" NTE|1\\r\n" +" NTE|2||Site Legend:.brML:MOCKINGBIRD HEIGHTS CLINIC 15475 
NORTH HOLLYWOOD BOULEVARD MOCKINGBIRD HEIGHTS CA 90210.br\\r\n" +" TQ1|R^Routine^HL70485\\r\n" +" 
OBX|1|NM|NA^SODIUM^L^2951-2^SODIUM^LN|1|144|mmol/L|136-145|N||AS|F|201108241525|ML:MOCKINGBIRD 
HEIGHTS CLINIC|||ML:MOCKINGBIRD HEIGHTS CLINIC^15475 NORTH 
HOLLYWOOD BOULEVARD MOCKINGBIRD HEIGHTS CA 90210\\r\n" +" NTE|1|AD|Performed At: ML\\r\n" +" OBX|25|ST|GFR^GLOMERULAR FILTRATION 
RATE^L|1|>60||60-130|N||AS|F|201104071132|ML:MOCKINGBIRD HEIGHTS 
CLINIC|||ML:MOCKINGBIRD HEIGHTS CLINIC^15475 NORTH HOLLYWOOD 
BOULEVARD MOCKINGBIRD HEIGHTS CA 90210\\r\n" +" NTE|1|AD|Performed At: ML\\r\n" +" NTE|1||If the patient is African American, multiply by 1.210\\r\n" +" NTE|2\\r\n" +" NTE|3||eGFR Reference Range for adults =60 ml/min/1.73 m2\\r\n" +" 
OBR|2|L3261930^LAB|^LAB|PHOS^PHOSPHORUS^L|||202002050507|||QLB.XXP|||DT699 
STROKE|201112050514||FRANKENSTEIN^Victor^IGOR^HCA||1205:CBM:C8R202002051038|||F\\r 
NTE|1\\r\n" +" NTE|2||Site Legend:.brML:MOCKINGBIRD HEIGHTS CLINIC15475 
NORTH HOLLYWOOD BOULEVARD MOCKINGBIRD HEIGHTS CA 90210.br\\r\n" +" TQ1|R^Routine^HL70485\\r\n" +" 
OBX|1|NM|PHOS^PHOSPHORUS^L^2777-1^PHOSPHATE^LN|1|2.4|mg/dL|2.5-4.9|L||AS|F|202002241521|ML:MOCKINGBIRD 
HEIGHTS CLINIC|||ML:MOCKINGBIRD HEIGHTS CLINIC^15475 NORTH 
HOLLYWOOD BOULEVARD MOCKINGBIRD HEIGHTS CA 90210\\r\n" +" NTE|1|AD|Performed At: ML\\r'";


Any thoughts?

Russ