How to store files in database and get it from DB with WD application?

[former_member186148]

Hi all!

I need to improve an existing webservice to be able store files (doc, excel sheets and so on) in database and return files to user by request. I create field in Dictionary with type binary and corresponding field in Bean with type byte[].

After recreate model and redeploy my WD application when i try to run application i get followed exception:

com.sap.tc.webdynpro.services.exceptions.TypeNotFoundException: type java:byte not found
	at com.sap.tc.webdynpro.services.datatypes.core.DataTypeBroker.getDataType(DataTypeBroker.java:224)
	at com.sap.tc.webdynpro.progmodel.context.DataAttributeInfo.init(DataAttributeInfo.java:318)
	at com.sap.tc.webdynpro.progmodel.context.NodeInfo.initUnmappedAttributes(NodeInfo.java:687)
	at com.sap.tc.webdynpro.progmodel.context.DataNodeInfo.doInit(DataNodeInfo.java:238)

It seems that using type byte[] isn't good way to store files in database.

So subquestion are:

-what type of database field i should use?

-how i can implement storing files in DB?

-how I should get these files from DB after storing?

sorry for my English

Thanks & regards

Lev.

[former_member186148]

Hi all again.

Well, here the answers:

1) I using a type "string" for field in Dictionary

2) Followed code implementing store files in DB. It must be in the method wich handle upload event:

IPrivateAddEditDoc.IContextElement element = wdContext.currentContextElement();
FileInputStream fis = (FileInputStream)element.getDocResource().read(true);
byte[] b = new byte[fis.available()+1];
fis.read(b);
String sDoc = Base64.encodeBytes(b);
wdContext.currentSingleDocumentForBookingElement().setDocumentData(sDoc);
wdContext.currentSingleDocumentForBookingElement().setDocumentType(element.getDocResource().getResourceType().getFileExtension());
try{
    manager.reportSuccess("File successfully uploaded");
}catch(Exception e){
    wdThis.wdGetDocumentContentController().writeException(manager,e);
}finally{
    fis.close();
}

3) Let's imagine that we have table wich shows file names and links to files. I have a few modifications of on the Bertram Ganz's method https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/b0e10426-77ca-2910-7eb5-d7d8982c... (it's great article!). There're two differences from this article. Firstly I don't using supply function and calling create0ByteResource() in wdDoInit() method. Secondly I implement getter for calculated attribute in that way:

    //@@begin getVnViewAllDocumentsForBookingDocumentDataStreamCalc(IPrivateDocumentContent.IVnViewAllDocumentsForBookingElement)
	IWDInputStream onDemandStream = null;
	try {
                                // getting whole data (include file content) for document
		executeGetDocumentByPK(element.getDocumentId());
                                // getDocumentData() returned file content
		byte[] b = Base64.decode(wdContext.currentResultDocumentByPKElement().getDocumentData());
		onDemandStream = WDResourceFactory.createInputStream(b);
	} catch (Exception e) {
		writeException(wdComponentAPI.getMessageManager(),e);
	}
	return onDemandStream;
    //@@end

Base64 is a free java class wich convert data from byte[] to string and vice versa. I found that class in the Internet.

A one little question remains: now I can't check (and don't know how to do it) does my way provide "on-demand" loading file or no. I just hope that it does

Thanks to all who hepl me

Regards, Lev.

[Former Member]

Hi

you can also use BLOB.

SQL BLOB is a built-in type that stores a Binary Large Object as a column value in a row of a database table. The driver implements Blob using an SQL locator(BLOB), which means that a Blob object contains a logical pointer to the SQL BLOB data rather than the data itself. A Blob object is valid for the duration of the transaction in which is was created.

<a href="http://www.sap-img.com/java/help-with-sql-blob.htm">Code Sample</a>

<a href="http://www.princeton.edu/~storacle/jdbc8_doc/oracle.sql.BLOB.html">SQL Methods</a>

Regards

Abhijith YS

[Former Member]

Hi

1. you can use CLOB for your requirement.

The mapping in the JavaTM programming language for the SQL CLOB type. An SQL CLOB is a built-in type that stores a Character Large Object as a column value in a row of a database table. By default drivers implement a Clob object using an SQL locator(CLOB), which means that a Clob object contains a logical pointer to the SQL CLOB data rather than the data itself. A Clob object is valid for the duration of the transaction in which it was created.

2.

<a href="http://db.apache.org/derby/manuals/reference/sqlj130.html">Get the reference code</a>

3. to retrieve file from the database you need to know the type of the file, then you can just get it using getByte() and store or open using that file type.

Regards

Abhijith YS

[former_member186148]

Abhijith!

Thanks for your so quick reply!

1. I open my Dictionary project, select table, then create new column and can see CLOB in the field "JDBC type" only when I set "built-in type" to "string". Is it right?

2. This link describes a way wich using JDBC connection, but i'm working with DB through EJB. And i don't know how using PrepareStatement in EJB or what i should use instead PrepareStatement (i mean line

ps.setAsciiStream(2, fin, fileLength);

from your link)

3. OK, I'll try it later

Thanks & regars

Lev

[Former Member]

Hi go through this :

import java.io.*;

import java.sql.*;

import java.util.*;

public class InsertFileToMaxDB {

String ImgFile = "d:/mm02data.txt";

/** Creates a new instance of InsertImage */

public InsertFileToMaxDB() {

}

public void insertFile() throws Exception {

System.out.println("Before DataBase Connection ");

PreparedStatement pst = null;

try {

// Declaring the connection parameters

String Server = "localhost",Database="TEST",User="DBADMIN",Password="maxdb";

// Establishing a connection

Connection con = DriverManager.getConnection("jdbc:sapdb://" + Server + "/" + Database, User, Password);

// Preparing the query to be executed

pst = con.prepareStatement("insert into addimage values(?,?,?)");

// Setting the actual values in the query

pst.setString(1,"1");

pst.setString(2,"File1");

FileInputStream fis=new FileInputStream(ImgFile);

byte[] b= new byte[fis.available()+1];

fis.read(b);

pst.setBytes(3,b);

pst.execute();

System.out.println("File Stored Successfully");

// Closing the connection parameters

con.close();

pst.close();

} catch(Exception e) {

// Prints if any exception is raised

System.out.println(" Problem : "+e);

}

}

public static void main(String args[]) throws Exception {

try {

// This is where we load the driver

Class.forName("com.sap.dbtech.jdbc.DriverSapDB");

System.out.println("The Driver has been loaded");

} catch (ClassNotFoundException e) {

// Prints if any exception is raised

System.out.println("Unable to load Driver Class");

return;

}

InsertFileToMaxDB Img = new InsertFileToMaxDB();

// Calling the add image method

Img.insertFile();

}

}

Regards

Abhijith YS

[Former Member]

Hi,

Check this blog.

/people/anilkumar.vippagunta2/blog/2007/02/20/reading-and-writing-images-from-sqlserver-in-webdynpro

Regards

Ayyapparaj