[resolved] Read sql server blob field with text inside

[Anonymous]

Hi,
I need to extract a blob field in sql server 2005 table. I put first my request in a query with tMSSqlInput like :
SELECT field_Id,CONVERT(varchar(8000), convert(binary(8000), memo)) as Texte
FROM SYSADM.My_Table.
It works but all caracters with accents are lost and specials caracters too (Like @, ?, Etc.....)
Is there another way to do it right ?
Thanks

[Anonymous]

Hello

Is there another way to do it right ?

Write some java code to read blob type data. In Talend, you can write java code in a routine, then call it in a job.
Bes regards
shong

[Anonymous]

Well: first can i still use an TSSqlInput to have my db connector.
second : with the row i put a TjavaRow or Tjava ?

I'am not specialise in Java, i have a peace of code but i don't know if it can works with Talend possibilities
try
{
// Ouput directory with final back-slash
var OuputDir = "C:\\Temp\\export\\";
//SQL query to extract text document from the document management table
//fields "nrid" , "title", "memo" are mandatory
//Work only for text document
var strSQL = "SELECT nrid, title, memo " +
"FROM SYSADM.dm0 " +
"WHERE (extension IS NULL OR extension='txt') "+
"AND title IS NOT NULL "
//Execute the query with Selligent object
var MyQryObj = CreateSelligentObject("SqlHelper", CurrentSessionID);
var MyQuery = MyQryObj.ExecuteSql(strSQL);
var MyXmlDoc = InitXml(MyQuery);
var MyRows : System.Xml.XmlNodeList = FindItem("Flds", MyXmlDoc, true);
var NbrOfRows = MyRows.Count;
for ( var i=0 ; i<NbrOfRows; i++)
{
//Text is enconding in binary
//this line code convert binary content in text
var objEncoding = new System.Text.UTF8Encoding();
var strDocContent = GetItemValue("DocContent", MyRows );
var strDocContentBytes = Convert.FromBase64String(strDocContent);
var strNoteContent = objEncoding.GetString(strDocContentBytes);
var strDocNRID = GetItemValue("DocNRID", MyRows);
var strDocTitle = GetItemValue("DocTitle", MyRows);
var FileName = strDocNRID + "_" + strDocTitle;
//Write text in a file
var filewriter = System.IO.File.CreateText(OuputDir + FileName + ".txt");
filewriter.Write(strNoteContent);
filewriter.Close();
}
}
catch(e)
{
return ("<Error>" + e.description + "</Error>");
}

[Anonymous · Accepted solution]

Hello

Well: first can i still use an TSSqlInput to have my db connector.
second : with the row i put a TjavaRow or Tjava ?

No, write your connection in routine, do select(select field_id from tablename) on tMsSQLInput, then link to tJavaRow,eg:
assum your routine call GetBlobFile(int id),
tMsSQLInput(select field_id from tablename)-->tJavaRow(GetBlobFile(input_row.field_id) //read the record of blog type based on the id column.

package routines;
import java.sql.*;
import java.io.*;
public class GetBlobFile {
	public static void readBlob(int field_id) {
		String driver = "net.sourceforge.jtds.jdbc.Driver";
		String url = "jdbc:jtds:sqlserver://192.168.0.39:1433/talend;";
		String user = "sa";
		String passwd = "Phebe.22";
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, passwd);
			PreparedStatement ps = conn
					.prepareStatement("select image from blob2  where id   =   ?");
			ps.setInt(1, field_id);
			ResultSet rs = ps.executeQuery();
			rs.next();
			InputStream in = rs.getBinaryStream("image"); // image is the column
															// name in real
															// table
			int length = in.available();
			FileOutputStream out = new FileOutputStream("d:/file/test/out/"
					+ field_id + ".png");
			byte[] b = new byte;
			int len = 0;
			while ((len = in.read(b)) != -1) {
				out.write(b, 0, len);
				out.flush();
			}
			out.close();
			in.close();
			rs.close();
			ps.close();
		} catch (Exception ex) {
			ex.printStackTrace(System.out);
		} finally {
			try {
				conn.close();
			} catch (Exception ex) {
			}
		}
	}
}

Please see my screenshots.
PS: Your peace code is not Java code.
Best regards

shong

[Anonymous]

Hi,
I have a similar problem.
Is there any solution to skip database connection and file creation in Java, using tJavaRow code?
My BLOB column name is FileContent, which I can access with the following code:
input_row.FileContent
All I want to do is to convert this to String (-> output_row.FileContent).
Components in my job:
tMSSqlInput --> tJavaRow --> tXSDValidator --> tExtractXMLField --> tMap --> tMSSqlOutput
(tXSDValidator is in Flow mode)
(Temporarely FileContent datatype is varchar(max) to skip BLOB to String conversion)
Thanks in advance,
Gabor Varga