Semarchy xDI uses internally the java library java.util.zip.Inflater and java.util.zip.Deflater which are based on the Zlib compression for the field ACP_EXE_BLO, therefore a script action is needed.
With a scripting beanshell action called in a process, you will be able to read your BLOB_COMPRESSED table line and inflate it (decompression).
Here is a code snippet based on your environment information (Semarchy xDI 5.3.2, jdk 11 LTS) for an hardcoded action id in the table.
For any other xDI version, this code needs to be adapted. The library versions may be upgraded.
Code snippet steps to publish the BLOB content in the session variable
1/ Create a process with a scripting beanshell action
2/ Action expression
import java.lang.Class;
import java.lang.ClassNotFoundException;
import java.sql.*;
import java.util.zip.*;
import java.nio.charset.StandardCharsets;
import java.util.zip.Deflater;
import java.util.zip.Inflater;
// Database connection information
String jdbcUser = "%x{$XDILOGSSCHEMA/tech:jdbcUser()}x%";
String jdbcPassword = "%x{$XDILOGSSCHEMA/tech:jdbcPassword()}x%";
String jdbcUrl = "%x{$XDILOGSSCHEMA/tech:jdbcUrl()}x%";
String jdbcClass = "%x{$XDILOGSSCHEMA/tech:jdbcDriver()}x%";
String module = "%x{$XDILOGSSCHEMA/tech:module()}x%";
String SchemaName = "%x{$XDILOGSSCHEMA/tech:schemaName()}x%";
// Connection Class
cl = com.indy.engine.core.module.classloader.ClassLoaderServiceProvider.INSTANCE.getClassLoader(module, jdbcClass);
clazz = Class.forName(jdbcClass, true, cl);
drv = clazz.newInstance();
// Connection instance
Properties props = new Properties();
props.setProperty("user", jdbcUser);
props.setProperty("password", jdbcPassword);
con = drv.connect(jdbcUrl, props);
// Input value
spSessId = "6471496801889b4d0368e0f201f402fd";
spActId = "9eccd4f700c222fd7bd317b7c6d47d3d";
// Query execution
String CallString = "";
CallString = CallString + " SELECT A.SESS_ID, A.SESS_ITER, A.ACT_ID, A.ACT_ITER, A.ACP_EXE_FORMAT, A.ACP_EXE_BLO ";
CallString = CallString + " FROM " + SchemaName + ".STB_LOG_ACTION_PROP_ACP A ";
CallString = CallString + " WHERE 1=1 " ;
CallString = CallString + " AND A.SESS_ID = '" + spSessId + "'" ;
CallString = CallString + " AND A.ACT_ID = '" + spActId + "'" ;
CallString = CallString + " AND A.ACP_SHORT_NAME = 'CORE_ACTION_TXT' ";
CallString = CallString + " AND A.ACP_EXE_FORMAT = 'BLOB_COMPRESSED' ";
Statement s = con.createStatement(); //creating statement
ResultSet rs = s.executeQuery(CallString); //executing statement
// Compute resultset
while(rs.next()){
String sess_id = rs.getString("SESS_ID");
String act_id = rs.getString("ACT_ID");
String sess_iter = rs.getString("SESS_ITER");
String act_iter = rs.getString("ACT_ITER");
String act_exe_format = rs.getString("ACP_EXE_FORMAT");
java.sql.Blob blob = rs.getBlob("ACP_EXE_BLO");
// BufferedInputStream
byte[] data = new byte[(int) blob.length()];
BufferedInputStream instream = null;
try {
instream = new BufferedInputStream(blob.getBinaryStream());
instream.read(data);
} catch (Exception ex) {
throw new Exception(ex.getMessage());
} finally {
instream.close();
}
// ByteArrayInputStream
int length = 0;
int chunk = 32765;
ByteArrayInputStream bis = new ByteArrayInputStream(data);
byte[] buffer = new byte[chunk];
ByteArrayOutputStream bos = new ByteArrayOutputStream();
while ((length = bis.read(buffer, 0, chunk)) != -1) {
bos.write(buffer, 0, length);
}
bos.close();
bis.close();
// decompresser Inflater
Inflater decompresser = new Inflater(true);
decompresser.setInput(buffer, 0, chunk);
// max size, impossible to be higher than 32765 kr
byte[] result = new byte[32765];
int resultLength = decompresser.inflate(result);
decompresser.end();
// Decode the bytes into a String
String outstr = new String(result, 0, resultLength, "UTF-8");
__ctx__.publishVariable("~/SESS#"+sess_id+"#ACT#"+act_id, outstr);
}
con.close(); //closing connection
Hi Denis, can you confirm if you reproduce this issue using a query tool outside of xDI, like DBeaver or SQL developer? This seems to be a pure oracle issue that we could investigate on the internet to help you.
Yes, I can confirm that we republish with an external Oracle editor.
We would like to be able to query the Semarchy log Oracle database to extract data from the STB_LOG_ACTION_PROP_ACP table from the ACP_EXE_VAR, ACP_EXE_CLO, ACP_EXE_BLO columns. The problem is that we can't decompress the ACP_EXE_BLO field while extracting the other fields.
Thanks for your help
M
Mathias Bonnargent
said
3 months ago
Hi Mickael,
Can you send us a line with INSERT SQL of your table?
In Dbeaver for example
Best regards,
Mathias
M
Mickael DENIS (BPCE-SI)
said
3 months ago
Hello,
The example :
INSERT INTO ETD_LOG_VAL_1Q.STB_LOG_ACTION_PROP_ACP
Semarchy xDI uses internally the java library java.util.zip.Inflater and java.util.zip.Deflater which are based on the Zlib compression for the field ACP_EXE_BLO, therefore a script action is needed.
With a scripting beanshell action called in a process, you will be able to read your BLOB_COMPRESSED table line and inflate it (decompression).
Here is a code snippet based on your environment information (Semarchy xDI 5.3.2, jdk 11 LTS) for an hardcoded action id in the table.
For any other xDI version, this code needs to be adapted. The library versions may be upgraded.
Code snippet steps to publish the BLOB content in the session variable
1/ Create a process with a scripting beanshell action
2/ Action expression
import java.lang.Class;
import java.lang.ClassNotFoundException;
import java.sql.*;
import java.util.zip.*;
import java.nio.charset.StandardCharsets;
import java.util.zip.Deflater;
import java.util.zip.Inflater;
// Database connection information
String jdbcUser = "%x{$XDILOGSSCHEMA/tech:jdbcUser()}x%";
String jdbcPassword = "%x{$XDILOGSSCHEMA/tech:jdbcPassword()}x%";
String jdbcUrl = "%x{$XDILOGSSCHEMA/tech:jdbcUrl()}x%";
String jdbcClass = "%x{$XDILOGSSCHEMA/tech:jdbcDriver()}x%";
String module = "%x{$XDILOGSSCHEMA/tech:module()}x%";
String SchemaName = "%x{$XDILOGSSCHEMA/tech:schemaName()}x%";
// Connection Class
cl = com.indy.engine.core.module.classloader.ClassLoaderServiceProvider.INSTANCE.getClassLoader(module, jdbcClass);
clazz = Class.forName(jdbcClass, true, cl);
drv = clazz.newInstance();
// Connection instance
Properties props = new Properties();
props.setProperty("user", jdbcUser);
props.setProperty("password", jdbcPassword);
con = drv.connect(jdbcUrl, props);
// Input value
spSessId = "6471496801889b4d0368e0f201f402fd";
spActId = "9eccd4f700c222fd7bd317b7c6d47d3d";
// Query execution
String CallString = "";
CallString = CallString + " SELECT A.SESS_ID, A.SESS_ITER, A.ACT_ID, A.ACT_ITER, A.ACP_EXE_FORMAT, A.ACP_EXE_BLO ";
CallString = CallString + " FROM " + SchemaName + ".STB_LOG_ACTION_PROP_ACP A ";
CallString = CallString + " WHERE 1=1 " ;
CallString = CallString + " AND A.SESS_ID = '" + spSessId + "'" ;
CallString = CallString + " AND A.ACT_ID = '" + spActId + "'" ;
CallString = CallString + " AND A.ACP_SHORT_NAME = 'CORE_ACTION_TXT' ";
CallString = CallString + " AND A.ACP_EXE_FORMAT = 'BLOB_COMPRESSED' ";
Statement s = con.createStatement(); //creating statement
ResultSet rs = s.executeQuery(CallString); //executing statement
// Compute resultset
while(rs.next()){
String sess_id = rs.getString("SESS_ID");
String act_id = rs.getString("ACT_ID");
String sess_iter = rs.getString("SESS_ITER");
String act_iter = rs.getString("ACT_ITER");
String act_exe_format = rs.getString("ACP_EXE_FORMAT");
java.sql.Blob blob = rs.getBlob("ACP_EXE_BLO");
// BufferedInputStream
byte[] data = new byte[(int) blob.length()];
BufferedInputStream instream = null;
try {
instream = new BufferedInputStream(blob.getBinaryStream());
instream.read(data);
} catch (Exception ex) {
throw new Exception(ex.getMessage());
} finally {
instream.close();
}
// ByteArrayInputStream
int length = 0;
int chunk = 32765;
ByteArrayInputStream bis = new ByteArrayInputStream(data);
byte[] buffer = new byte[chunk];
ByteArrayOutputStream bos = new ByteArrayOutputStream();
while ((length = bis.read(buffer, 0, chunk)) != -1) {
bos.write(buffer, 0, length);
}
bos.close();
bis.close();
// decompresser Inflater
Inflater decompresser = new Inflater(true);
decompresser.setInput(buffer, 0, chunk);
// max size, impossible to be higher than 32765 kr
byte[] result = new byte[32765];
int resultLength = decompresser.inflate(result);
decompresser.end();
// Decode the bytes into a String
String outstr = new String(result, 0, resultLength, "UTF-8");
__ctx__.publishVariable("~/SESS#"+sess_id+"#ACT#"+act_id, outstr);
}
con.close(); //closing connection
Mickael DENIS (BPCE-SI)
Hello,
I need to export ACP_EXE_BLO from system log table STB_LOG_ACTION_PROP_ACP .
But this column is of type BLOB COMRESS.
I've try : utl_raw.cast_to_varchar2( dbms_lob.substr(utl_compress.lz_uncompress(ACP_EXE_BLO),2000,1))
but I get the error ORA-29294.
I need it to export logs in JSON format to THEIA
Thanks for your help
Hi Mickael,
Semarchy xDI uses internally the java library java.util.zip.Inflater and java.util.zip.Deflater which are based on the Zlib compression for the field ACP_EXE_BLO, therefore a script action is needed.
With a scripting beanshell action called in a process, you will be able to read your BLOB_COMPRESSED table line and inflate it (decompression).
Here is a code snippet based on your environment information (Semarchy xDI 5.3.2, jdk 11 LTS) for an hardcoded action id in the table.
For any other xDI version, this code needs to be adapted. The library versions may be upgraded.
Code snippet steps to publish the BLOB content in the session variable
1/ Create a process with a scripting beanshell action
2/ Action expression
import java.lang.Class; import java.lang.ClassNotFoundException; import java.sql.*; import java.util.zip.*; import java.nio.charset.StandardCharsets; import java.util.zip.Deflater; import java.util.zip.Inflater; // Database connection information String jdbcUser = "%x{$XDILOGSSCHEMA/tech:jdbcUser()}x%"; String jdbcPassword = "%x{$XDILOGSSCHEMA/tech:jdbcPassword()}x%"; String jdbcUrl = "%x{$XDILOGSSCHEMA/tech:jdbcUrl()}x%"; String jdbcClass = "%x{$XDILOGSSCHEMA/tech:jdbcDriver()}x%"; String module = "%x{$XDILOGSSCHEMA/tech:module()}x%"; String SchemaName = "%x{$XDILOGSSCHEMA/tech:schemaName()}x%"; // Connection Class cl = com.indy.engine.core.module.classloader.ClassLoaderServiceProvider.INSTANCE.getClassLoader(module, jdbcClass); clazz = Class.forName(jdbcClass, true, cl); drv = clazz.newInstance(); // Connection instance Properties props = new Properties(); props.setProperty("user", jdbcUser); props.setProperty("password", jdbcPassword); con = drv.connect(jdbcUrl, props); // Input value spSessId = "6471496801889b4d0368e0f201f402fd"; spActId = "9eccd4f700c222fd7bd317b7c6d47d3d"; // Query execution String CallString = ""; CallString = CallString + " SELECT A.SESS_ID, A.SESS_ITER, A.ACT_ID, A.ACT_ITER, A.ACP_EXE_FORMAT, A.ACP_EXE_BLO "; CallString = CallString + " FROM " + SchemaName + ".STB_LOG_ACTION_PROP_ACP A "; CallString = CallString + " WHERE 1=1 " ; CallString = CallString + " AND A.SESS_ID = '" + spSessId + "'" ; CallString = CallString + " AND A.ACT_ID = '" + spActId + "'" ; CallString = CallString + " AND A.ACP_SHORT_NAME = 'CORE_ACTION_TXT' "; CallString = CallString + " AND A.ACP_EXE_FORMAT = 'BLOB_COMPRESSED' "; Statement s = con.createStatement(); //creating statement ResultSet rs = s.executeQuery(CallString); //executing statement // Compute resultset while(rs.next()){ String sess_id = rs.getString("SESS_ID"); String act_id = rs.getString("ACT_ID"); String sess_iter = rs.getString("SESS_ITER"); String act_iter = rs.getString("ACT_ITER"); String act_exe_format = rs.getString("ACP_EXE_FORMAT"); java.sql.Blob blob = rs.getBlob("ACP_EXE_BLO"); // BufferedInputStream byte[] data = new byte[(int) blob.length()]; BufferedInputStream instream = null; try { instream = new BufferedInputStream(blob.getBinaryStream()); instream.read(data); } catch (Exception ex) { throw new Exception(ex.getMessage()); } finally { instream.close(); } // ByteArrayInputStream int length = 0; int chunk = 32765; ByteArrayInputStream bis = new ByteArrayInputStream(data); byte[] buffer = new byte[chunk]; ByteArrayOutputStream bos = new ByteArrayOutputStream(); while ((length = bis.read(buffer, 0, chunk)) != -1) { bos.write(buffer, 0, length); } bos.close(); bis.close(); // decompresser Inflater Inflater decompresser = new Inflater(true); decompresser.setInput(buffer, 0, chunk); // max size, impossible to be higher than 32765 kr byte[] result = new byte[32765]; int resultLength = decompresser.inflate(result); decompresser.end(); // Decode the bytes into a String String outstr = new String(result, 0, resultLength, "UTF-8"); __ctx__.publishVariable("~/SESS#"+sess_id+"#ACT#"+act_id, outstr); } con.close(); //closing connection- Oldest First
- Popular
- Newest First
Sorted by Oldest FirstStephanie Fourrier
Hi Denis, can you confirm if you reproduce this issue using a query tool outside of xDI, like DBeaver or SQL developer? This seems to be a pure oracle issue that we could investigate on the internet to help you.
Please confirm your oracle version as well as there seems to be a logged bug for this function on older oracle versions : https://support.oracle.com/knowledge/Oracle%20Database%20Products/1544982_1.html
Best regards,
Stéphanie.
Mickael DENIS (BPCE-SI)
Hello, Stéphanie,
Yes, I can confirm that we republish with an external Oracle editor.
We would like to be able to query the Semarchy log Oracle database to extract data from the STB_LOG_ACTION_PROP_ACP table from the ACP_EXE_VAR, ACP_EXE_CLO, ACP_EXE_BLO columns. The problem is that we can't decompress the ACP_EXE_BLO field while extracting the other fields.
Thanks for your help
Mathias Bonnargent
Hi Mickael,
Can you send us a line with INSERT SQL of your table?
In Dbeaver for example
Best regards,
Mathias
Mickael DENIS (BPCE-SI)
Hello,
The example :
INSERT INTO ETD_LOG_VAL_1Q.STB_LOG_ACTION_PROP_ACP
(SESS_ID, SESS_ITER, ACT_ID, ACT_ITER, ACP_NAME, ACP_SHORT_NAME, ACP_TYPE, ACP_CUMUL, ACP_EXE_VAR, ACP_EXE_CLO, ACP_EXE_BLO, ACP_EXE_FORMAT, ACP_SRC_VAR, ACP_SRC_CLO, ACP_SRC_BLO, ACP_SRC_FORMAT, PTY_TYPE_N, PCA_TYPE_N, ACP_NUM, ACP_BND_VAR, ACP_BND_CLO, ACP_BND_BLO, ACP_BND_FORMAT)
VALUES('6471496801889b4d0368e0f201f402fd', 1, '9eccd4f700c222fd7bd317b7c6d47d3d', 1, 'INSER_METN_TR6R_S/INSER_WRK0_WJ81_A7_TR6R_1/S1-GTS-Staging/STAGING/Register Sub Query/CORE_ACTION_TXT', 'CORE_ACTION_TXT', 'String', NULL, NULL, NULL, '78DAED1C5D6FDBB6F6D9FC157C9384D89E28D98E0334051459C69C5A96AFC434C993813B74DBC3860DDD86E102FDF1979F12295112ED3869D2AAD8224B3C24CFF7393CA404FEFAF4DBA79FFE1E8DEEF3457E88FC0326D7699CC5198C0AB84507FA730CEACD09BE61EDC181FD2600C55DEA82382A1278FF63B283F87689A6AB28CED23DBC49F07D429EAD229C4027F08370E223F29F03A3DD4A7B3A9B84BE03C1883E77C16834627FD8587196ED1FE02EC370B383AEE323DF1943275C2EE865B6B8A2970089BB25BDCC17337E0939246220218344CB80DDCD437E99711076E7876C1424DA42365130E397391F651EF051E6ECEE8A7747885F585B18048E0729FA9498E2EEA6C0B90B31F683697143FE8E215A90FFA107AFA1B3E2A0590EEB047362E76C72B4F0ED878C1D0AE8913FA40BA62201A39A78D26C57C04D017777DB2D6965403EF9916C09D84481218FC884803D2700E406784CFCE1218D731C2763756C17DF2E1E0F84009C476B8A0A1329A54D6B78F75EB4306AF0EDE59AE84B7697C7F0DD3524A361976A0542A5AE144C551845C04894AEA378BF4AF69C7FFB644BF9B74FF68C7FA2A3CA0EDA238D3FC6C983C21026109F5080E88DEC55EB7608A629FE406752FA95AC1C71666A80EC29E5A7D2282707B249F2BAC179C6F7D981C31F36BB0D3EA00EF6975CAE33F97D378F5F057FE32CDA26459C4892D20F39CEC73ED56FBFE233FDA1D21FAD5607A2B6F8C7C26524AA544B02897A270F8441317657D1235CE7590A5B813D780111312CDA0BD1C94D2E4B6253E27C4F718E02CE2D26798A390364E2AC88A36C457510AE2075D01A1FCEA04A734D9582A627379B73973D1F6FD0AD7EEA892AC703D5111AE7AA71EB9D22E9608282498884A439F166C850831C99439C019E83336E8CBC56ED36C6421E1EFCAB050F584B7E77C9EFD805F90B1994E8DD8C07C1B90882AC03126D48F4E383F18741C06361C0217D1EF6C2408F4A9671493116DEAB3B2C8D747F2A355D094EAABDF056E3B86A40E5D90312979E302EF20C49A45D40573117FDFAE9D40935535A915A59B6CE01D575F80D1FA12BBC956AB6FB5AD508DE5F1B323C302AFD419BB9F07915BDEFD08D57ABF9C7A8FE0B288579F0B359C05126A0916B436F8DE0168A159275435079A17905BFD73EBAE3A5487A173262529458F26515315F7F023C44CB215A0ED1F2AB474BFB7039C4CB215E1E152FBF46B8BCD4C265F0AD948AF02AFA7AA5223AB9E5FA7E79E0F06FAD5474247FED4A459D3597D34A2D4788E24A1345F01D278E5A74D3825BC822E885ACCEC1AA3C57D2F931694FF86C7D6C1962CFE4E2B1EEA0DB732BDB9057C7F015867013CDE748B23AB224AE1D13837634A068D1D6468B1AC9D6A08EDFB33A5AA534C8979EDCAE04F08A22EBF16B7C575FB6F01D4A15A0B40DB6E952B38E3CF620B12914405535E9A0F926955DDDC724CA0D1DBF7C81CEC4A117065D82B74C74813CE8AEB33C8D3074AEAE1C4F8C4098EAB97C2767823C176FD2A4C051BAF7A0BA1F2371EA418A4E52E145CDD4B19E66B75297FE5D298B84AB974E883F54F54731CED6D188F2F902CE9357780EAFA72E4107E7F76DC6E2C1FACF69FDEA1E87A507904742C4A1902E37D0EB053CCFBC1DF3FC49966596D53354E56A064FF35DA459484BB39E65C1DC2CC23ECB2AD9A6F06B53F6ED5A4FBF5695B1AFF4B66EE134AB9E6AC9D354EFD4740F3C636DF788CAEEB36EE2D4ABB54A48F78F30B980995CF2F094458D59C10DB52AC3AEDE79EDCE98BD9C9ABCBCC2DCE565521791B954F592FEBCC566DD62B76AF1CAA907FF38F8C7AFEE1F43E91F839376525E8F6BECDB30E9DD2FE9DD2EB1D9124133C94FBA2732EE238D9A02214CBE2160F4DF0D9FE071276E74379B9B68572E8F44EA6839E875E7A0485D7435C4760CDEA0C553AAA8439195572D92F39ACCCF4B59A9255AABD1AA8CA29F1FD2CD8E8ADE3F52EED4540E2C0C6CA9332F984F17AE81B6D35BF78D2887493B4CA74C5C6095B8946CD152178BCC45E96899BB543D4ECC5E6C92177D9293D2175175653CB8BF5DAC4BDD22CA97DFE477B8DF492DA4A6A2276BEADBD652179C7018AAC17932410BEF59906BB0FF52B2DF5401300981C6DF3E29D81A5E2B45A02B651299A3923256D9B39618B79D1F23526C019B89BCC153FCAF92F81C9DD12C2577C373715778E3C1B5BDA46B3B591DCDFC2E558F7A090B9134465389350FD62AA5F3E8F595D4EB59A4A7EA834EBF159D76DDFA06F625094DD14E0DA5CA42509ECB924072B40B681C07EFFB87C17B398AD79B26047EA971F485E8347A785657EAD68D5C7937D95C2F31AC3D4B56230B930A504960AC99543D7D60ABE0B1E378E54A585B27D035A2C2B6E0C0578D5A6A450BFED152BC3CA9ADFEA5A0187E4DB86A5CB2542FE26DBC2703431AD7DC1AF7545E9B5E519FC9CABCF9A5844A4BFD52376A0ADB101175C0507860280A4CD0D3ACBE56A1B44B49B40265D7A973431E51DB9A6D9C84769B15184F3BA9EC96A7150413CA0E9E72C4B8B6DDEAAB6722B462A625FA6117FA56D8D7906FC1DD0EF5F2A70A523EE53F0057CB1953CBE4A1E922CC2AC95F626E2A66F576B2A88618E0A8BE28709AE9D43E07B09EA6B8C8349FA87F104085912EA2720D734115DDAA5BD3EF4534DE7EE6706429456FC0CF9FFFF87DE42AFF30FA0F11F1E13EFFE04F555709B565963CCBCEFF6D933586D91D4E72789B1103D3C650108050BDD3C7C8EAEB387640EA5AEB211E02CAE6E677321AB071E6010B34931DF6A7945D90F116D860C584D0838D8021BF810801EBE96AF591B8E896927905B56650EF3B81E2A4D8E0585486593A493208602D95CA5F43CD7B032B9928DEBE57261AAC2E936E895C3289505B0436126146DB2B13015549853FE0CCBCA65CD41A841330094C21C48E0E6045840D0935025AD46A3EA1614D8132A9551DA85BADEA844A77917E2C08A5D80F20DB5CA828DDEC762DE68698B9218D294A9CBED6772BE26DB1C763FAF98790BA72862E7D4E32B38872CE21976DB64F1CA58DCC96ADB84C2B0A3A2CDA427B96BAEED088D8CF7A0AD5CBFA2532B25E1D43FF8A4FC78955564B3284A00B085125CC1EAFBD563CBF563402FD1E5B2D31F5FB6C1D5A72B7DE80F1A32CEBC15D5C4AF4342A803D09C710D082BE8E3C71288544BF05FB34C1BB295DFA40B60002DD9A4B97487D9ACB61A4F7ED993687E2F527D0332F7D43AA7F660E555A0D7FB08AD66901D5F764FC1F50F043A86654C01E55A6959D98222B4C511D535462DAFCA08D6CB7FCA48D11BCF6511B7D62BE0B29BDC615F927561A9435DD4167F108C5420F74393DBE4EEC737B02AA727CEC814532C3E17AD3193EBE39F2D82900E814BE8DE86B822FC5DE382009FEFDF5D3E74F23175D23BA9620F0D0354FEDF87EE09826DDE79B155B93EF9388AD9C7EF9FCC73F7FC2FFFEAF89DDD830F4180E5FF97A99AF7CB5737AF8A0D7F041AFE3B56678B5FABCAF56BF15FBFCB6DEA23E85EBC3892B23FB86D355DFECE9AAE3C43C1CA61A76675FE830D5531573383B75CCD9292D580E671E86330F673EF3A0EAD7A90700348FF0A42DFF316C2ECF00F83F177F2CD7', 'BLOB_COMPRESSED', NULL, NULL, NULL, NULL, 0, NULL, 2287, NULL, NULL, NULL, NULL);
Jean-Edouard Mayette
Hi Mickael,
Semarchy xDI uses internally the java library java.util.zip.Inflater and java.util.zip.Deflater which are based on the Zlib compression for the field ACP_EXE_BLO, therefore a script action is needed.
With a scripting beanshell action called in a process, you will be able to read your BLOB_COMPRESSED table line and inflate it (decompression).
Here is a code snippet based on your environment information (Semarchy xDI 5.3.2, jdk 11 LTS) for an hardcoded action id in the table.
For any other xDI version, this code needs to be adapted. The library versions may be upgraded.
Code snippet steps to publish the BLOB content in the session variable
1/ Create a process with a scripting beanshell action
2/ Action expression
import java.lang.Class; import java.lang.ClassNotFoundException; import java.sql.*; import java.util.zip.*; import java.nio.charset.StandardCharsets; import java.util.zip.Deflater; import java.util.zip.Inflater; // Database connection information String jdbcUser = "%x{$XDILOGSSCHEMA/tech:jdbcUser()}x%"; String jdbcPassword = "%x{$XDILOGSSCHEMA/tech:jdbcPassword()}x%"; String jdbcUrl = "%x{$XDILOGSSCHEMA/tech:jdbcUrl()}x%"; String jdbcClass = "%x{$XDILOGSSCHEMA/tech:jdbcDriver()}x%"; String module = "%x{$XDILOGSSCHEMA/tech:module()}x%"; String SchemaName = "%x{$XDILOGSSCHEMA/tech:schemaName()}x%"; // Connection Class cl = com.indy.engine.core.module.classloader.ClassLoaderServiceProvider.INSTANCE.getClassLoader(module, jdbcClass); clazz = Class.forName(jdbcClass, true, cl); drv = clazz.newInstance(); // Connection instance Properties props = new Properties(); props.setProperty("user", jdbcUser); props.setProperty("password", jdbcPassword); con = drv.connect(jdbcUrl, props); // Input value spSessId = "6471496801889b4d0368e0f201f402fd"; spActId = "9eccd4f700c222fd7bd317b7c6d47d3d"; // Query execution String CallString = ""; CallString = CallString + " SELECT A.SESS_ID, A.SESS_ITER, A.ACT_ID, A.ACT_ITER, A.ACP_EXE_FORMAT, A.ACP_EXE_BLO "; CallString = CallString + " FROM " + SchemaName + ".STB_LOG_ACTION_PROP_ACP A "; CallString = CallString + " WHERE 1=1 " ; CallString = CallString + " AND A.SESS_ID = '" + spSessId + "'" ; CallString = CallString + " AND A.ACT_ID = '" + spActId + "'" ; CallString = CallString + " AND A.ACP_SHORT_NAME = 'CORE_ACTION_TXT' "; CallString = CallString + " AND A.ACP_EXE_FORMAT = 'BLOB_COMPRESSED' "; Statement s = con.createStatement(); //creating statement ResultSet rs = s.executeQuery(CallString); //executing statement // Compute resultset while(rs.next()){ String sess_id = rs.getString("SESS_ID"); String act_id = rs.getString("ACT_ID"); String sess_iter = rs.getString("SESS_ITER"); String act_iter = rs.getString("ACT_ITER"); String act_exe_format = rs.getString("ACP_EXE_FORMAT"); java.sql.Blob blob = rs.getBlob("ACP_EXE_BLO"); // BufferedInputStream byte[] data = new byte[(int) blob.length()]; BufferedInputStream instream = null; try { instream = new BufferedInputStream(blob.getBinaryStream()); instream.read(data); } catch (Exception ex) { throw new Exception(ex.getMessage()); } finally { instream.close(); } // ByteArrayInputStream int length = 0; int chunk = 32765; ByteArrayInputStream bis = new ByteArrayInputStream(data); byte[] buffer = new byte[chunk]; ByteArrayOutputStream bos = new ByteArrayOutputStream(); while ((length = bis.read(buffer, 0, chunk)) != -1) { bos.write(buffer, 0, length); } bos.close(); bis.close(); // decompresser Inflater Inflater decompresser = new Inflater(true); decompresser.setInput(buffer, 0, chunk); // max size, impossible to be higher than 32765 kr byte[] result = new byte[32765]; int resultLength = decompresser.inflate(result); decompresser.end(); // Decode the bytes into a String String outstr = new String(result, 0, resultLength, "UTF-8"); __ctx__.publishVariable("~/SESS#"+sess_id+"#ACT#"+act_id, outstr); } con.close(); //closing connection