有关如何将BLOB导出为文件的基本信息 [英] Basic info on how to export BLOB as files
问题描述
我已经研究了如何将BLOB导出到图像.DB具有 IMAGE 列，该列存储数千张图像.我本打算导出表，但是在 EMS SQL Manager 中针对InterBase和Firebird出现 BLOB 文件错误.
I have researched on how to export BLOBs to image. A DB has an IMAGE column storing several thousand images. I thought of exporting the table but I get a BLOB file error in EMS SQL Manager for InterBase and Firebird.
有好的帖子，但我仍然无法成功.
There have been good posts, but I have still not been able to succeed.
此示例已出现在许多页面上，包括Microsoft的网站.我正在使用INTERBASE(Firebird).我还没有发现与为Firebird启用xp_shell或为InterBase和Firebird启用 EMS SQL Manager (我也已安装)相关的任何信息.我的猜测是:不可能.我还尝试了安装SQL Server Express，SQL Server 2008和SQL Server2012.即使没有连接到服务器，我也处于死胡同.原因是我尚未设法启动服务器.遵循了technet.microsoft上的指南:如何:启动SQL Server代理，但是在我右边的窗格中没有任何服务.
This example has appeared on numerous pages, including Microsoft's site. I am using INTERBASE (Firebird). I have not found anything related to enabling xp_shell for Firebird, or EMS SQL Manager for InterBase and Firebird (which I have also installed). My guess is: its not possible. I also tried Installing SQL Server Express, SQL Server 2008, and SQL Server 2012. I am at a dead end without having even connected to the server. The reason being I have not managed to start the server. Followed the guide at technet.microsoft: How to: Start SQL Server Agent but there are no services on the right pane to me.
用于下载整列的PHP文件(由于rep限制，可能无法发布链接).
PHP file to download entire column (may not post link due to rep limitation).
它的MySQL连接部分令我望而却步.我计算机上的数据库是 GDB 文件，我也有XAMPP.我可以找出一种将其用作本地主机环境的方法.我希望这是有道理的.
It has a MySQL connect section that daunts me. There on my computer is the DB as a GDB file, I also have XAMPP. I can figure out a way to use this as a localhost environment. I hope this is making sense.
最后的解决方案是使用 bcp ，这是在Stack Overflow上发布的想法，标题为:将blob从表导出到单个文件的最快方法.我阅读了文档，将其安装了，但是无法连接到服务器.我使用 -S PC-PC -U xxx -P xxx (服务器一定是错误的)，但是我发现的信息全部使用 -T (Windows身份验证)
Last solution is to use bcp, an idea posted on Stack Overflow titled: fastest way to export blobs from table into individual files. I read the documentation, installed it, but cannot connect to server. I use -S PC-PC -U xxx -P xxx (The server must be wrong) But the information I find all uses -T (Windows Authentication)
总结.我正在使用Firebird，作为 EMS SQL Manager .我尝试将所有图像从图像表中提取到单个文件中.这些工具都具有SQL脚本屏幕，但似乎与xp shell结合使用.你有什么建议?我使用了错误的SQL管理器来完成此操作吗?
Summing up. I am using Firebird, as EMS SQL Manager. I try to extract all images from images table into individual files. These tools both have SQL script screens, but it appears to be in conjunction with xp shell. What would you suggest? Am I using the wrong SQL manager to accomplish this?
推荐答案
有几种方法:
- 使用isql命令
ibase_blob_get循环从blob中获取字节，并将其写入文件中.
我既不使用也不了解EMS SQL Manager，所以我不知道是否可以(以及如何)导出Blob.
I don't use nor know EMS SQL Manager, so I don't know if (and how) you can export a blob with that.
您链接到的示例以及您提到的几乎所有工具都是针对Microsoft SQL Server的，而不是针对Firebird的；所以也难怪那些都不行.
The example you link to, and almost all tools you mention are for Microsoft SQL Server, not for Firebird; so it is no wonder those don't work.
使用Java 8(可能在Java 7上也可以)将blob保存到磁盘的基本示例是:
A basic example to save blobs to disk using Java 8 (might also work on Java 7) would be:
/**
* Example to save images to disk from a Firebird database.
* <p>
* Code assumes a table with the following structure:
* <pre>
* CREATE TABLE imagestorage (
* filename VARCHAR(255),
* filedata BLOB SUB_TYPE BINARY
* );
* </pre>
* </p>
*/
public class StoreImages {
// Replace testdatabase with alias or path of database
private static final String URL = "jdbc:firebirdsql://localhost/testdatabase?charSet=utf-8";
private static final String USER = "sysdba";
private static final String PASSWORD = "masterkey";
private static final String DEFAULT_FOLDER = "D:\\Temp\\target";
private final Path targetFolder;
public StoreImages(String targetFolder) {
this.targetFolder = Paths.get(targetFolder);
}
public static void main(String[] args) throws IOException, SQLException {
final String targetFolder = args.length == 0 ? DEFAULT_FOLDER : args[0];
final StoreImages storeImages = new StoreImages(targetFolder);
storeImages.store();
}
private void store() throws IOException, SQLException {
if (!Files.isDirectory(targetFolder)) {
throw new FileNotFoundException(String.format("The folder %s does not exist", targetFolder));
}
try (
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT filename, filedata FROM imagestorage")
) {
while (rs.next()) {
final Path targetFile = targetFolder.resolve(rs.getString("FILENAME"));
if (Files.exists(targetFile)) {
System.out.printf("File %s already exists%n", targetFile);
continue;
}
try (InputStream data = rs.getBinaryStream("FILEDATA")) {
Files.copy(data, targetFile);
}
}
}
}
}
C#示例
下面是C#中的示例，与上面的代码相似.
Example in C#
Below is an example in C#, it is similar to the code above.
class StoreImages
{
private const string DEFAULT_FOLDER = @"D:\Temp\target";
private const string DATABASE = @"D:\Data\db\fb3\fb3testdatabase.fdb";
private const string USER = "sysdba";
private const string PASSWORD = "masterkey";
private readonly string targetFolder;
private readonly string connectionString;
public StoreImages(string targetFolder)
{
this.targetFolder = targetFolder;
connectionString = new FbConnectionStringBuilder
{
Database = DATABASE,
UserID = USER,
Password = PASSWORD
}.ToString();
}
static void Main(string[] args)
{
string targetFolder = args.Length == 0 ? DEFAULT_FOLDER : args[0];
var storeImages = new StoreImages(targetFolder);
storeImages.store();
}
private void store()
{
if (!Directory.Exists(targetFolder))
{
throw new FileNotFoundException(string.Format("The folder {0} does not exist", targetFolder), targetFolder);
}
using (var connection = new FbConnection(connectionString))
{
connection.Open();
using (var command = new FbCommand("SELECT filename, filedata FROM imagestorage", connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string targetFile = Path.Combine(targetFolder, reader["FILENAME"].ToString());
if (File.Exists(targetFile))
{
Console.WriteLine("File {0} already exists", targetFile);
continue;
}
using (var fs = new FileStream(targetFile, FileMode.Create))
{
byte[] filedata = (byte[]) reader["FILEDATA"];
fs.Write(filedata, 0, filedata.Length);
}
}
}
}
}
}
这篇关于有关如何将BLOB导出为文件的基本信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
