[raihan26]

I have this sql block that I want to write in java using something like this

public static final String create_sql = "Below sql Block"

DECLARE
  t_count INTEGER;
  v_sql VARCHAR2(1000) := 'create table TEST (
            id number NOT NULL PRIMARY KEY,  
            url varchar(1000) NOT NULL,  
            urlHash varchar(255) NOT NULL,  
            contentHash varchar(255),  
            modDate varchar(30),  
            contentLocation varchar(100),  
            status integer,  
            lastCrawlDate varchar(30))';
BEGIN
  SELECT COUNT(*)
    INTO t_count
    FROM user_tables
   WHERE table_name = 'TEST';

  IF t_count = 0 THEN
    EXECUTE IMMEDIATE v_sql;
  END IF;
END;
/

Can anyone help me with this. Any help will be appreciated.

[macosxnerd101]

You can use PreparedStatements to execute your SQL statements. However, your Java application should only be responsible for SELECT, DELETE, UPDATE, and INSERT commands. It really shouldn't be creating tables in the database.

[raihan26]

macosxnerd101, on 26 May 2012 - 06:10 PM, said:

You can use PreparedStatements to execute your SQL statements. However, your Java application should only be responsible for SELECT, DELETE, UPDATE, and INSERT commands. It really shouldn't be creating tables in the database.

Yes I know but my question is- How can I represent that whole query as a String in java.
And also I want to create a table if it doesn't exists in the oracle database, so that is the reason I wrote that query, so it will check in the database if the table is already there, then it will not create it, that is the reason I wanted to represent that query as a String

This post has been edited by raihan26: 26 May 2012 - 06:23 PM

[macosxnerd101]

Surround it with double quotes.

[raihan26]

macosxnerd101, on 26 May 2012 - 06:21 PM, said:

Surround it with double quotes.

That double quotes is making me more confused as I am not sure how should I do that. Any help will be appreciated.

	public static final String sql = "DECLARE " +
	"t_count INTEGER; " +
	"v_sql VARCHAR2(1000) := 'create table TEST ( " +
	            "id number NOT NULL PRIMARY KEY, " +
	            "url varchar(1000) NOT NULL, " +
	            "urlHash varchar(255) NOT NULL, " +
	            "contentHash varchar(255),  " +
	            "modDate varchar(30),  " +
	            "contentLocation varchar(100),  " +
	            "status integer,  " +
	            "lastCrawlDate varchar(30))';" +
	"BEGIN " +
	"SELECT COUNT(*) " +
	"INTO t_count " +
	"FROM user_tables " +
	"WHERE table_name = 'TEST'; " +

	"IF t_count = 0 THEN " +
	"EXECUTE IMMEDIATE v_sql; " +
	"END IF; " +
	"END; " +
	"/";

This post has been edited by raihan26: 26 May 2012 - 06:30 PM

[macosxnerd101]

This is how you create Strings:

String s = "This is a String";
String query = "SELECT * FROM table_name";

And always compile your code. That is the easiest way to determine if you are doing something wrong.

[x68zeppelin80x]

Why don't you just save the query as a text file i.e. test.sql and read it in using the java.util.Scanner class?

Scanner Read File

Quote

import java.io.File;
import java.util.Scanner;

public class Exercise {
    public static void main(String[] args)  throws Exception {
	// Indicate that you are planning to opena file
	File fleExample = new File("Example.xpl");
        // Prepare a Scanner that will "scan" the document
        Scanner opnScanner = new Scanner(fleExample);

	// Read each line in the file
        while( opnScanner.hasNext() ) {
            // Read each line and display its value
	    System.out.println("First Name:    " + opnScanner.nextLine());
	    System.out.println("Last Name:     " + opnScanner.nextLine());
	    System.out.println("Hourly Salary: " + opnScanner.nextLine());
	    System.out.println("Is Full Time?: " + opnScanner.nextLine());
	}
            
    	// De-allocate the memory that was used by the scanner
        opnScanner.close();
    }
}

Instead of printing each line in the while... just:

String sql = "";
while( opnScanner.hasNext() ) {
  sql += opnScanner.nextLine();
}

This post has been edited by x68zeppelin80x: 26 May 2012 - 09:00 PM