[resolved] Read sql server blob field with text inside

Six Stars

[resolved] Read sql server blob field with text inside

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

Accepted Solutions
Community Manager

Re: [resolved] Read sql server blob field with text inside

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

Re: [resolved] Read sql server blob field with text inside

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: [resolved] Read sql server blob field with text inside

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>");
}
Community Manager

Re: [resolved] Read sql server blob field with text inside

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Read sql server blob field with text inside

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

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download