Size of CLOB...

Thu, 23 Mar 2023 12:02:18 -0700

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 &gt;=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

Reply via email to