topic Re: Image from MySQL to diretory.... in Talend Studio

Image from MySQL to diretory....

Anonymous — Sat, 16 Nov 2024 13:43:08 GMT

Yesterday I didn't know this software exist... today I have to transfer 10 000 users from one server with its own dating(home made) script to another server with other dating script (Abledating)...
Everything is problem, but one of the bigger is that on server one images and video are stored in the MySQL base an in the other server the images is in folders and users in MySQL...
2000 images and 58 videos to...
not much? The only way I done it before is with Ctrl+C and Ctrl+V....
Have been on forum for last 10 hour... learning...
But feel I need some basic advice... what to do, how, were to learn...
Have to put together thinks like hair color, weight .... athletic body and so on...
What I can do is to put both sites on the same server, will that help?
grateful for any advice....
Need to have it don in 3 days....
newbie x 2

Re: Image from MySQL to diretory....

Anonymous — Mon, 26 Oct 2009 02:06:22 GMT

Hello
You can see the related components on user documentation.
tMysqlInput is used to extract records from mysql table, and you can use tFileList to iterate one folder and copy each image/video with tFileCopy component.
Best regards

shong

Re: Image from MySQL to diretory....

Anonymous — Mon, 26 Oct 2009 18:12:17 GMT

Thank shong for the tips...
god start is:
http://www.talendforge.org/tutorials/menu.php
To have some control on the proces I will try to use your tips to sprakash tips from:
https://community.talend.com/t5/Design-and-Development/resolved-Data-Migration-between-two-MySQL-Host-with-logging/td-p/109177
will go on with reporting solutions and problem...

Re: Image from MySQL to diretory....

Anonymous — Tue, 27 Oct 2009 02:32:44 GMT

First stop on step one...can't get file from the MySQL base,... yes I have read ..blog output from oracle database... with header... but I need the most simple solution...
This look nice but just don't work for me..

Re: Image from MySQL to diretory....

Anonymous — Tue, 27 Oct 2009 02:57:44 GMT

Hello
To get blob type from mysql, you need to write some java code. If you know java knowledge, it is easy to do that. Here is a java example getting blog from mysql.
In Talend,
1)create a routine to get blob type from mysql (see screenshot1)eg:

package routines;
import java.io.*;
import java.sql.*;
public class GetBlobFromMysql {
	public static void getBlob(int id) {
		FileOutputStream image;
		Connection con = null;
		PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet res = null;
		StringBuffer query = null;
		String driverName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/";
		
		String dbName = "register";
		String userName = "root";
		String password = "root";
		try {
			Class.forName(driverName);
			con = DriverManager.getConnection(url + dbName, userName, password);
			stmt = con.createStatement();
			ResultSet rs = stmt
					.executeQuery("select * from picture where image_id="+id);
			if (rs.next()) {
				Blob test = rs.getBlob("image");
				InputStream x = test.getBinaryStream();
				int size = x.available();
				OutputStream out = new FileOutputStream("C:\\myfolder\\"+id+".jpg");
				byte b[] = new byte;
				x.read(b);
				out.write(b);
			}
		} catch (Exception e) {
			System.out.println("Exception :" + e);
		} finally {
			try {
				stmt.close();
				con.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}
}

2)then, call this routine on tJava, tJavaRow or tMap(see screenshot2)

Best regards

shong

Re: Image from MySQL to diretory....

Anonymous — Tue, 27 Oct 2009 05:13:19 GMT

yes it works now...

Re: Image from MySQL to diretory....

Anonymous — Fri, 17 Apr 2015 17:03:01 GMT

A simpler way is to use MySQL's cast() function to convert the blob to char e.g.,

select cast(Data as char(10000) ) cdb_data from Accounts where DataKey = '13023512558'