Home » Developer & Programmer » JDeveloper, Java & XML » file to base64 (need some tips)
file to base64 [message #531620] |
Thu, 17 November 2011 02:26 |
|
wtfn00b
Messages: 115 Registered: October 2011 Location: Latvia
|
Senior Member |
|
|
Good day, I have wroten code in JAVA. And I want to know if it's good or not so good or maybe don't do that thing.
Please give me some advices.
public TestServiceBean() {
try
{
Blob value = null;
BigDecimal idValue;
Context ic = new InitialContext();
ds = (DataSource)ic.lookup("....");
if (ds != null)
{
System.out.println("OK");
Connection conn = null;
try
{
conn = ds.getConnection();
PreparedStatement stmt =
conn.prepareStatement("SELECT 1000 AS id FROM DUAL WHERE 1=1");
ResultSet rs = stmt.executeQuery();
while (rs.next())
{
BigDecimal idValue =
rs.getBigDecimal(1, "100.00"); //something wrong here ?
}
byte [] buffer = new byte[2048];
InputStream input = new FileInputStream(data);
int data = input.read();
BigDecimal data2;
OutputStream output = data2.setBinaryStream(0L);
while(data != -1)
{
int valueLen = (int)data.length();
int valueLeft = 1;
while (valueLeft <= 0)
{
output.write(buffer, buffer.length / 4, buffer.length);
valueLeft = valueLen - 2048;
}
data = input.read();
}
input.close();
string base64String = Convert.ToBase64String(data2);
PreparedStatement stmt2 =
conn.prepareStatement("UPDATE TABLE_NAME SET base64 =
"'+ base64String +'" WHERE id="'+ idValue +'"");
stmt2.executeQuery();
conn.commit();
}
catch (SQLException e)
{
try
{
conn.rollback();
}
catch (SQLException ignore){}
}
finally
{
if (conn != null)
{
try
{
conn.close();
}
catch (SQLException ignore) {}
}
conn = null;
}
} else {
System.out.println("DS not found!");
}
}
catch (Exception e)
{
throw new RuntimeException(e);
}
I think that I have many mistakes but maybe not.
Best regards,
wtfn00b.
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: file to base64 [message #531641 is a reply to message #531636] |
Thu, 17 November 2011 03:49 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Here's an example of what you can do:
SQL> create table t (id integer, base64 blob);
Table created.
SQL> insert into t values (100, empty_blob());
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace directory FILESDIR as 'C:\';
Directory created.
SQL> host type c:\t.txt
I am Michel
SQL> CREATE OR REPLACE PROCEDURE p (p_id integer)
2 IS
3 tmp_lob BLOB;
4 dest_lob BLOB;
5 file BFILE;
6 lg PLS_INTEGER;
7 i PLS_INTEGER;
8 piece RAW(32000);
9 src_off PLS_INTEGER := 1;
10 dst_off PLS_INTEGER := 1;
11 BEGIN
12 select base64 into dest_lob from t where id = p_id for update;
13 DBMS_LOB.CREATETEMPORARY (tmp_lob, false, dur=>dbms_lob.call);
14 file := BFILENAME('FILESDIR','T.TXT');
15 DBMS_LOB.OPEN (file);
16 DBMS_LOB.LOADBLOBFROMFILE (tmp_lob, file, dbms_lob.lobmaxsize, src_off, dst_off);
17 lg := DBMS_LOB.GETLENGTH (tmp_lob);
18 i := 1;
19 WHILE i <= lg LOOP
20 piece := DBMS_LOB.SUBSTR (tmp_lob, 32000, i);
21 DBMS_LOB.APPEND (dest_lob, UTL_ENCODE.BASE64_ENCODE (piece));
22 i := i + 32000;
23 END LOOP;
24 END;
25 /
Procedure created.
SQL> exec p(100);
PL/SQL procedure successfully completed.
SQL> col val format a50
SQL> select id, utl_raw.cast_to_varchar2(utl_encode.base64_decode(base64)) val from t;
ID VAL
---------- --------------------------------------------------
100 I am Michel
1 row selected.
Regards
Michel
|
|
|
|
|
|
|
|
|
|
Re: file to base64 [message #531693 is a reply to message #531688] |
Thu, 17 November 2011 05:30 |
|
wtfn00b
Messages: 115 Registered: October 2011 Location: Latvia
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 17 November 2011 13:18Quote:I tried your code and have this error:
I first executed:
Quote:create or replace directory FILESDIR as 'C:\';
Did you?
Regards
Michel
I modify procedure.
Steps:
1.
SQL> select * from files_base64
ID FILE_BLOB
---------------------- ----------
100 (BLOB)
2.
SQL> create or replace directory FILESDIR as 'C:\';
directory FILESDIR created.
3.
SQL> host type c:\t.txt
test this now
4.
SQL>
CREATE OR REPLACE PROCEDURE hellow_world (p_id integer)
IS
tmp_lob BLOB;
dest_lob BLOB;
file BFILE;
lg PLS_INTEGER;
i PLS_INTEGER;
piece RAW(32000);
src_off PLS_INTEGER := 1;
dst_off PLS_INTEGER := 1;
BEGIN
select file_blob into dest_lob from files_base64 where id = p_id for update;
DBMS_LOB.CREATETEMPORARY (tmp_lob, false, dur=>dbms_lob.call);
file := BFILENAME('FILESDIR','T.TXT');
DBMS_LOB.OPEN (file);
DBMS_LOB.LOADBLOBFROMFILE (tmp_lob, file, dbms_lob.lobmaxsize, src_off, dst_off);
lg := DBMS_LOB.GETLENGTH (tmp_lob);
i := 1;
WHILE i <= lg LOOP
piece := DBMS_LOB.SUBSTR (tmp_lob, 32000, i);
DBMS_LOB.APPEND (dest_lob, UTL_ENCODE.BASE64_ENCODE (piece));
i := i + 32000;
END LOOP;
END;
PROCEDURE hellow_world compiled
5.
SQL>exec hellow_world(100);
Error starting at line 1 in command:
exec hellow_world(100)
Error report:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_LOB", line 937
ORA-06512: at "JANIS.HELLOW_WORLD", line 15
ORA-06512: at line 1
22288. 00000 - "file or LOB operation %s failed\n%s"
*Cause: The operation attempted on the file or LOB failed.
*Action: See the next error message in the error stack for more detailed
information. Also, verify that the file or LOB exists and that
the necessary privileges are set for the specified operation. If
the error still persists, report the error to the DBA.
Best regards,
wtfn00b.
[Updated on: Thu, 17 November 2011 05:58] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: file to base64 [message #531705 is a reply to message #531703] |
Thu, 17 November 2011 06:20 |
|
wtfn00b
Messages: 115 Registered: October 2011 Location: Latvia
|
Senior Member |
|
|
Okey, Michel, I understand all the answers.
And your procedure is doing it. If file is placed on server than everything of your procedure is very good.
But I need to create package with XML and procedures who can do this:
Using soapUI (client side):
<id>100</id> [id is taking from table]
<filename>t.txt</filename> [t.txt contains "test this now", file is uploaded by user from local machine]
<base64>dGVzdCB0aGlzIG5vdw==</base64> [2 ways to create this: 1) MTOM with JAVA; 2) procedure like you wrote me]
Maybe is possible something else ?
User don't need to see base64 code.
User need only show/choice the file from his local machine.
Like is web pages you can use browser buttom to find file on your local machine.
So is it possible to do what I need with PL/SQL.
It will be very great if possible.
Best regards,
wtfn00b.
[Updated on: Thu, 17 November 2011 06:20] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: file to base64 [message #531794 is a reply to message #531740] |
Thu, 17 November 2011 12:10 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I am not sure that I understand the requirements. Here is what I think I understand. You want the user to be able to pass a file name and directory name to a procedure that will then upload a unique id, the file name, and the contents of the file in base 64 in xml format into a table and be able to select the decoded base 64 data. I have taken the modifications that you did to what Michel provided and modified that to do this below. Note that it requires that the file be somehow moved to the server before the procedure is executed.
SCOTT@orcl_11gR2> host type c:\my_oracle_files\t.txt
test this now
SCOTT@orcl_11gR2> CREATE TABLE files_base64
2 (id INTEGER,
3 file_xml XMLTYPE);
Table created.
SCOTT@orcl_11gR2> CREATE SEQUENCE id_seq;
Sequence created.
SCOTT@orcl_11gR2> create or replace directory FILESDIR as 'C:\my_oracle_files';
Directory created.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE hellow_world
2 (p_filename VARCHAR2,
3 p_dir VARCHAR2)
4 IS
5 tmp_lob BLOB;
6 file BFILE;
7 src_off PLS_INTEGER := 1;
8 dst_off PLS_INTEGER := 1;
9 lg PLS_INTEGER;
10 i PLS_INTEGER;
11 v_id INTEGER;
12 v_data VARCHAR2(32767);
13 dest_lob CLOB;
14 piece RAW(32000);
15 BEGIN
16 DBMS_LOB.CREATETEMPORARY (tmp_lob, false, dur=>dbms_lob.call);
17 file := BFILENAME(p_dir, p_filename);
18 DBMS_LOB.OPEN (file);
19 DBMS_LOB.LOADFROMFILE (tmp_lob, file, dbms_lob.lobmaxsize, src_off, dst_off);
20 lg := DBMS_LOB.GETLENGTH (tmp_lob);
21 i := 1;
22 SELECT id_seq.NEXTVAL INTO v_id FROM DUAL;
23 v_data :=
24 '<table>'
25 || '<id>' || v_id || '</id>'
26 || '<filename>' || p_filename || '</filename>'
27 || '<base64>';
28 DBMS_LOB.CREATETEMPORARY (dest_lob, false, dur=>dbms_lob.call);
29 DBMS_LOB.WRITEAPPEND
30 (dest_lob,
31 LENGTH (v_data),
32 v_data);
33 WHILE i <= lg LOOP
34 piece := DBMS_LOB.SUBSTR (tmp_lob, 32000, i);
35 DBMS_LOB.WRITEAPPEND
36 (dest_lob,
37 LENGTH (UTL_RAW.CAST_TO_VARCHAR2 (UTL_ENCODE.BASE64_ENCODE (piece))),
38 UTL_RAW.CAST_TO_VARCHAR2 (UTL_ENCODE.BASE64_ENCODE (piece)));
39 i := i + 32000;
40 END LOOP;
41 v_data := '</base64></table>';
42 DBMS_LOB.WRITEAPPEND
43 (dest_lob,
44 LENGTH (v_data),
45 v_data);
46 INSERT INTO files_base64 (id, file_xml)
47 VALUES (v_id, XMLTYPE (dest_lob));
48 DBMS_LOB.FREETEMPORARY (tmp_lob);
49 DBMS_LOB.FREETEMPORARY (dest_lob);
50 DBMS_LOB.CLOSE (file);
51 END hellow_world;
52 /
Procedure created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> exec hellow_world ('t.txt', 'FILESDIR');
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> col file_xml format a65
SCOTT@orcl_11gR2> select * from files_base64
2 /
ID FILE_XML
---------- -----------------------------------------------------------------
1 <table>
<id>1</id>
<filename>t.txt</filename>
<base64>dGVzdCB0aGlzIG5vdw0K</base64>
</table>
1 row selected.
SCOTT@orcl_11gR2> column id format a3
SCOTT@orcl_11gR2> column filename format a8
SCOTT@orcl_11gR2> column data format a65
SCOTT@orcl_11gR2> select extractvalue (x.column_value, '/table/id') id,
2 extractvalue (x.column_value, '/table/filename') filename,
3 utl_raw.cast_to_varchar2
4 (utl_encode.base64_decode
5 (utl_raw.cast_to_raw
6 (extractvalue (x.column_value, '/table/base64')))) data
7 from files_base64 t,
8 table (xmlsequence (t.file_xml)) x
9 /
ID FILENAME DATA
--- -------- -----------------------------------------------------------------
1 t.txt test this now
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
Re: file to base64 [message #531806 is a reply to message #531794] |
Thu, 17 November 2011 14:52 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you have no other method of getting your file from the client to the server, you can use Oracle's SQL*Loader utility to load the data into a staging table, then select it from there. SQL*Loader can be executed using DBMS_SCHEDULER. DBMS_SCHEDULER can be used in a procedure and you can call that procedure from your existing procedure. I have provided a full demonstration below. You will need to make sure that you have all of the proper privileges. I have used dbms_lock.sleep to allow enough time for the load into the staging table to complete before trying to select data from it, but you may want to do something different with that. This is just a brief example. Also, my example is for Windows operating system. It may be somewhat different on other operating systems.
SCOTT@orcl_11gR2> host type c:\my_oracle_files\t.txt
test this now
SCOTT@orcl_11gR2> host type c:\my_oracle_files\test.ctl
load data
into table staging_tab
fields
(staging_col char(5000))
SCOTT@orcl_11gR2> CREATE TABLE staging_tab
2 (staging_col CLOB)
3 /
Table created.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE load_staging
2 (p_filename IN VARCHAR2,
3 p_dir_path IN VARCHAR2)
4 AS
5 BEGIN
6 DBMS_SCHEDULER.CREATE_JOB
7 (job_name => 'test_job',
8 job_type => 'EXECUTABLE',
9 job_action => 'c:\windows\system32\cmd.exe',
10 number_of_arguments => 3,
11 start_date => SYSTIMESTAMP,
12 enabled => FALSE);
13 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
14 ('test_job', 1, '/q');
15 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
16 ('test_job', 2, '/c');
17 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
18 ('test_job',
19 3,
20 'SQLLDR scott/tiger CONTROL=''c:\my_oracle_files\test.ctl'' LOG=''c:\my_oracle_files\test.log'' DATA='''
21 || p_dir_path || p_filename || '''');
22 DBMS_SCHEDULER.ENABLE ('test_job');
23 END load_staging;
24 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE TABLE files_base64
2 (id INTEGER,
3 file_xml XMLTYPE);
Table created.
SCOTT@orcl_11gR2> CREATE SEQUENCE id_seq;
Sequence created.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE hellow_world
2 (p_filename IN VARCHAR2,
3 p_dir_path IN VARCHAR2)
4 IS
5 tmp_lob CLOB;
6 lg PLS_INTEGER;
7 v_id INTEGER;
8 v_data VARCHAR2(32767);
9 dest_lob CLOB;
10 BEGIN
11 load_staging (p_filename, p_dir_path);
12 -- allow enough time for job to finish running:
13 dbms_lock.sleep (15);
14 DBMS_LOB.CREATETEMPORARY (tmp_lob, false, dur=>dbms_lob.call);
15 SELECT staging_col INTO tmp_lob FROM staging_tab;
16 lg := DBMS_LOB.GETLENGTH (tmp_lob);
17 SELECT id_seq.NEXTVAL INTO v_id FROM DUAL;
18 v_data :=
19 '<table>'
20 || '<id>' || v_id || '</id>'
21 || '<filename>' || p_filename || '</filename>'
22 || '<base64>';
23 DBMS_LOB.CREATETEMPORARY (dest_lob, false, dur=>dbms_lob.call);
24 DBMS_LOB.WRITEAPPEND
25 (dest_lob,
26 LENGTH (v_data),
27 v_data);
28 DBMS_LOB.APPEND
29 (dest_lob,
30 UTL_RAW.CAST_TO_VARCHAR2
31 (UTL_ENCODE.BASE64_ENCODE
32 (UTL_RAW.CAST_TO_RAW (tmp_lob))));
33 v_data := '</base64></table>';
34 DBMS_LOB.WRITEAPPEND
35 (dest_lob,
36 LENGTH (v_data),
37 v_data);
38 INSERT INTO files_base64 (id, file_xml)
39 VALUES (v_id, XMLTYPE (dest_lob));
40 DBMS_LOB.FREETEMPORARY (dest_lob);
41 END hellow_world;
42 /
Procedure created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> exec hellow_world ('t.txt', 'c:\my_oracle_files\');
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> col file_xml format a65
SCOTT@orcl_11gR2> select * from files_base64
2 /
ID FILE_XML
---------- -----------------------------------------------------------------
1 <table>
<id>1</id>
<filename>t.txt</filename>
<base64>dGVzdCB0aGlzIG5vdw==</base64>
</table>
1 row selected.
SCOTT@orcl_11gR2> column id format a3
SCOTT@orcl_11gR2> column filename format a8
SCOTT@orcl_11gR2> column data format a65
SCOTT@orcl_11gR2> select extractvalue (x.column_value, '/table/id') id,
2 extractvalue (x.column_value, '/table/filename') filename,
3 utl_raw.cast_to_varchar2
4 (utl_encode.base64_decode
5 (utl_raw.cast_to_raw
6 (extractvalue (x.column_value, '/table/base64')))) data
7 from files_base64 t,
8 table (xmlsequence (t.file_xml)) x
9 /
ID FILENAME DATA
--- -------- -----------------------------------------------------------------
1 t.txt test this now
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 07:10:23 CDT 2024
|