xmlStream to blob

Hi Yves, following your idea about a physical table you should save your XML in a CLOB not a BLOB because an XML should be always a pure text content. About table structure: you should look for "oracle mapping" into Uniface CHM  to identify which Field Interface is best suited for you to map directly to native Oracle CLOB. Take care Oracle driver has different "mapping modes" built during years; you should use latest when possible. You could also build with Uniface a "web service" enabling other applications to integrate with your application at logical level intead of physically through a database table. In this way you could deliver your XML to others when they need it whatever is structured your db table. I am sure with few quick test you will find your best solution. Regards, Gianni

Hi Gianni, Thanks for your answer. But as I said I'm not the creator of the table. It's used by an accounting program, and that program defines the structure of the table. So I can't neither create a web service to deliver the XML nor change the blob to clob. (I think it uses this same table to transfer other form of files, therefor it's blob and not clob. just my idea) I have to fully follow the defined rules. I can't do as I want  best regards Yves

To shortcut the debate, try SC*, this maps to text in sql server. (If you do a Deploy->SQL Table it will show oyu if it wants to generate overflows.  )

Hi Yves, it looks like you are already tight to a fixed table structure. So it looks like "all we have to do" is to make uniface capable to access these data. We need a datamodel (based on the already existing database) and a way to read/write the data. Wouldn't it be a good approach when you provide us with the DDL statement for this table or the documentation from the accounting program you mentioned. Greetings from Frankfurt/Germany, Uli P.S. As the table is located in an ORACLE database, you may use "Load Definition Utility" to get the raw datamodel.

Hi Uli This is the table definition CREATE TABLE erp_transfer     (z_fileid                 VARCHAR2(128 BYTE) NOT NULL,   -- Name || timestamp      z_filetyp               VARCHAR2(10 BYTE) NOT NULL,     -- Cst      z_filecontent         BLOB NOT NULL,                          -- Xml or File      z_filesize              VARCHAR2(20 BYTE) NOT NULL,     -- Blob size      z_erptimestamp     VARCHAR2(20 BYTE),                    -- file create file or timestamp      z_erpuser             VARCHAR2(30 BYTE),                    -- user id      z_filearchiv           VARCHAR2(36 BYTE) NOT NULL,      -- status flag      z_msc_timestamp  VARCHAR2(20 BYTE),                    -- timestamp      z_insertuser          VARCHAR2(30 BYTE),                     --  sql user      id                       NUMBER(*,0),                               --  increment      z_envir                VARCHAR2(50 BYTE),                     -- source      z_file_typ             VARCHAR2(10 BYTE),                     -- internal use      z_filecoding          VARCHAR2(50 BYTE),                     -- internal use      z_filecontentbin     VARCHAR2(255 BYTE))                   -- internal use /   Hope this will help you understand my problem   Thanks and regards   Yves

ulrich-merkel said P.S. As the table is located in an ORACLE database, you may use "Load Definition Utility" to get the raw datamodel.

Don't forget to add this to your ASN file for the Load Definitions on Oracle to work: [ENTITIES] USER_TABLES.ORA6        UNIFACE_TABLES.* More info on this in the help files...

Hello, first of all... we use Oracle (currently mostly Oracle 11.2). We use CLOB, BLOB, NCLOB fields. We got rid of those "stupid" overflow tables many years ago... For a BLOB field we use a field with Data Type "Raw data" and Interface "SR*" - we use this for storing any binary data (including pdf, xlsx, png and so on). By the way, should you need CLOB, this is String with SC* interface. Should you need NCLOB, that is String and SW* interface. You should also look at the Uniface Oracle option "nclob_ext" - we use this and all data seems to be ok. [DRIVER_SETTINGS] USYS$ORA_PARAMS = open cursors 200, nclob_ext Kind regards, Zdenek Socha

Hello Yves, now I see you have all text (string/varchar) fields defined as BYTE. It seems that your DB is in Unicode and the varchar fields are defined as BYTE (not CHAR). Be warned that you can have a serious problems with Uniface. It seems that Uniface uses CHAR fields always. That is, the field in Uniface defined as String VC10 is then in Oracle DB defined as VARCHAR2(10 CHAR). We were unable to solve this problem... since Uniface does not allow us to set the field interface to char of byte. :( Zdenek

Hello Yves, as you are dealing with an existing table in a RELATIONAL database, uniface will NEVER create "one the fly", there are a couple of options to make the task easier (as an example: in relational databases the sequence of fields doesn't matter). But it takes a more detailed conversation, so if you like, please contact me under ulrichmerkel -at- web.de Greetings from Frankfurt/Germany, Uli

Hi Folks Thanks for all your ideas and suggestions Finally I use a pl-sql procedure to store my xml in the db. I put my stream in a file on the disk (on a shared directory so oracle can access to the directory)

;create a file on vPath with unique name vFinalName $instanceName.exportFileToUser(vPath, pFile, "<TRUE>", "xml", "<FALSE>", vFinalName)

and I call my sql-procedure from uniface, giving the oracle Directory to find the file

; set the SQL_DIR for Oracle vSql = "create or replace DIRECTORY 'SQL_DIR' AS '%%vPath%%%'" sql vSql, "dev" ;Call the sql stored proc vSql = "call sql_blob.add_blob( vFinalName, p2, p3 )" sql vSql, "dev"

and finally I delete the file because I don't want to keep it on the server

; delete the temporary stored file lfiledelete %%vPath%%%\%%vFinalName%%%"

The stored procedure is (in package sql_blob)

PROCEDURE add_blob(p_name VARCHAR2, p_User VARCHAR2, p_Domain VARCHAR2) IS v_blob BLOB; v_bfile BFILE; v_length NUMBER; v_Id NUMBER; BEGIN -- Declare a pointer to the file (SQL_DIR defined by create DIRECTORY 'SQL_DIR' AS '%%vPath%%%) v_bfile := bfilename(directory => 'SQL_DIR', filename => p_name); -- Open File (to get it length dbms_lob.fileopen(v_bfile); v_length := dbms_lob.getlength(v_bfile); -- increment the id  (shoud be a sequence !) select max(id) into v_Id from erp_transfer; v_Id := v_Id + 1; -- Create a line in the ERP with a null blob and get the blob pointer to a var INSERT INTO erp_transfer (z_fileid, z_filetyp, z_filecontent, z_filesize, z_erptimestamp, z_erpuser, z_filearchiv, z_msc_timestamp, z_insertuser, id, z_envir, z_file_typ, z_filecoding, z_filecontentbin) VALUES (p_name || to_char(SYSDATE, 'yyyymmddhh24miss'), 'DTA', empty_blob(), v_length, to_char(SYSDATE, 'yyyymmddhh24miss'), p_User, '1', null, null, v_Id, p_Domain, 'DTA', null, null ) RETURNING z_filecontent INTO v_blob; -- put the file in the blob dbms_lob.loadfromfile(v_blob, v_bfile, v_length); -- close the file dbms_lob.fileclose(v_bfile); END add_blob;

It's certainly possible to do easier, but unfortunately I don't have time to search more. So again many Thanks for all your suggestions. Best regards   Yves 

Hi Yves, looks like it's always the best to do processings like yours outside of the uniface application: just deposit the payload somewhere and "ring the bell". In the past, a lot was done by: - create a file with -filename- - spawning a simple ring_bell.bat -filename- in synchronous mode - delete the file after spawn has finished (if delete was not done inside the bat file)