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*(
'<?xml version="1.0" encoding="UTF-8"?>
<ClinicalDocument xmlns="urn:hl7-org:v3"
xmlns:sdtc="urn:hl7-org:sdtc"...
...about 128K of well formed XML...
/<ClinicalDocument>
',
'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 "<?xml version="1.0"
encoding="UTF-8"?>
<ClinicalDocument xm&' is too long.
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-3333||||||||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:C03008R||||201112051038|||F\\r\n"
+" NTE|1\\r\n" +" NTE|2||Site Legend:\\\\.br\\\\ML: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:C00008R||||202002051038|||F\\r
NTE|1\\r\n" +" NTE|2||Site Legend:\\\\.br\\\\ML: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
