使用JDBC在Oracle数据库上创建Java [英] Create Java on Oracle database with JDBC

问题描述

我正在尝试使用JDBC在oracle数据库上创建Java源对象.

I'm trying to create a Java source object on an oracle database using JDBC.

我要创建的源如下:

create or replace and resolve java source named "BlobIO" as package dbjava;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;


public class BlobIO {

    /**
     * Stores a blob into a local file (or even UNC-path)
     * @param blob The blob locator that should be stored in a file
     * @param filename The filename to write to
     * @param bufferSize The buffer size for data transfer
     * @return 1 if successful, 0 if failed
     */
    public static int blobToFile(java.sql.Blob blob, String filename, int bufferSize)
    {
        OutputStream os = null;
        InputStream is = null;
        boolean fail = true;
        try {

            is = blob.getBinaryStream();
            os = new FileOutputStream(filename);
            int amountRead = 0;
            byte[] buffer = new byte[bufferSize];
            while ((amountRead = is.read(buffer, 0, buffer.length)) != -1) {
                os.write(buffer, 0, amountRead);
            }
            is.close();
            os.flush();
            os.close();
            fail = false;
        } catch (IOException ex) {
            new File(filename).delete();
            System.err.println("Could not store blob to file.");
            System.err.println("File : " + filename);
            System.err.println("Reason : " + ex.getClass().getName() + " : " + ex.getMessage());
            fail = true;
        } catch (SQLException ex) {
            new File(filename).delete();
            System.err.println("Could not store blob to file.");
            System.err.println("File : " + filename);
            System.err.println("Reason : " + ex.getClass().getName() + " : " + ex.getMessage());
            fail = true;
        } finally {
            try {is.close();} catch (Exception ex) {}
            try {os.flush();} catch (Exception ex) {}
            try {os.close();} catch (Exception ex) {}
        }
        return fail? 0:1;
    }

    /**
     * Stores a blob into a local file (or even UNC-path)
     * @param query The query that should select ONLY the blob field
     * @param filename The filename to write to
     * @param bufferSize The buffer size for data transfer
     * @return 1 if successful, 0 if failed
     */
    public static int blobToFile(String query, String filename, int bufferSize) {
        try {
            Connection conn = DriverManager.getConnection("jdbc:default:connection:");
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery(query);
            InputStream is;


            if (rset.next())
            {
                int ret = blobToFile(rset.getBlob(1), filename, bufferSize);
                if (rset.next())
                {
                    new File(filename).delete();
                    System.err.println("Could not store blob to file.");
                    System.err.println("Blob query : " + query);
                    System.err.println("File : " + filename);
                    System.err.println("Reason : too many rows");
                    rset.close();
                    stmt.close();
                    return 0;
                } else {
                    rset.close();
                    stmt.close();
                    return ret;
                }
            } else {
                System.err.println("Could not store blob to file.");
                System.err.println("Blob query : " + query);
                System.err.println("File : " + filename);
                System.err.println("Reason : no records retrieved by query");
                rset.close();
                stmt.close();
                return 0;
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
            return 0;
        }

    }

}
/

我已经使用execute方法尝试了CallableStatement，这给了我error: "Missing IN/OUT parameters" 当我尝试在普通的Statement对象上使用execute方法时，出现错误:"

I have tried with a CallableStatement using the execute method and this gives me the error: "Missing IN/OUT parameters" When I try using the execute method on a normal Statement object I get the error: "

Non supported SQL92 token at position: 262"

有人知道我在做什么错吗?似乎在Google上也找不到任何内容.

Anyone has any idea what I'm doing wrong? Can't seem to find anything on google either.

这是我用来尝试执行脚本的代码(字符串sql包含您可以在上面看到的脚本，变量conn是Connection对象.

This the code I use to try to execute the script (the String sql contains the script you can see above, the variable conn is the Connection object.

CallableStatement stat = conn.prepareCall(sql);
stat.setEscapeProcessing(false);
stat.execute();

如果我仅尝试使用Statement，就是这样:

If i try with just Statement it is this:

Statement stat = conn.createStatement();
stat.execute(sql);

推荐答案

好吧，最终找到了问题，它必须是CallableStatement和setEscapeProcessing(false).

Ok found the problem eventually, it needed to be CallableStatement with setEscapeProcessing(false).

这篇关于使用JDBC在Oracle数据库上创建Java的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！