date:20230323

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

Size of CLOB...

2023-03-23 Thread Russell Bateman

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

Re: Size of CLOB...

Re: Size of CLOB...

Size of CLOB...

3 matches

Site Navigation

Mail list logo

Footer information