[resolved] Read sql server blob field with text inside

Four 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
Four 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