How can I execute this sql backup script with JDBC?

Question

How can I execute the following query against my database instance using JDBC, Everything I am trying is returning "no result set" errors.

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

What do I do when I'm not really looking data back as such?

.sql file

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @fileFolder VARCHAR(20) -- used for file name


-- specify database backup directory
SET @path = 'C:\SQLBackups\'


-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 


DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   


WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  


       FETCH NEXT FROM db_cursor INTO @name   
END   


CLOSE db_cursor   
DEALLOCATE db_cursor

My code for executing said query (so far)

    public static void connect(String instance, String saPassword, String query)  {
    Connection conn = null;
    query = "DECLARE @name VARCHAR(50) -- database name   " +
            "DECLARE @path VARCHAR(256) -- path for backup files   " +
            "DECLARE @fileName VARCHAR(256) -- filename for backup   " +
            "DECLARE @fileDate VARCHAR(20) -- used for file name " +
            "-- specify database backup directory " +
            "SET @path = 'C:\\SQLBackups\\'   " +
            "-- specify filename format " +
            "SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  " +
            "DECLARE db_cursor CURSOR FOR   " +
            "SELECT name  " +
            "FROM master.dbo.sysdatabases  " +
            "WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases " +
            "OPEN db_cursor    " +
            "FETCH NEXT FROM db_cursor INTO @name    " +
            "WHILE @@FETCH_STATUS = 0    " +
            "BEGIN    " +
            "       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'   " +
            "       BACKUP DATABASE @name TO DISK = @fileName   " +
            "       FETCH NEXT FROM db_cursor INTO @name    " +
            "END    " +
            "CLOSE db_cursor    " +
            "DEALLOCATE db_cursor ";
    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
        String dbURL = "jdbc:sqlserver://10.0.0.0\\"+ instance;
        String user = "user";
        String pass = saPassword;
        conn = DriverManager.getConnection(dbURL, user, pass);
        Statement stmt = conn.createStatement();
        stmt.executeQuery(query);

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }

I can execute my query just fine in SSMS, is the conversion of the .sql text into the string causing issues here? I'm not too bothered about a result here as it should be creating a lot of .bak files on the server (which does happen when I execute it via SSMS, I changed around the query with grabbing a simple SELECT resultset which works just fine, so I'm a tad lost now.

overwriting the (query) in the method is just for testing purposes, once I get the .bak files landing on the remote server directory I will tidy it up, credentials masked for obvious reasons.

Thanks

Vasu · Accepted Answer · 2017-04-02 15:14:25Z

2

Your code is NOT readable (and therefore hard to maintain) as you are mixing both SQL & Java languages together, so I strongly recommend to use callableStatement, you can look here for a simple example. In simple terms, you need to move all your SQL code to a procedure in the database and then callableStatement.execute()

edited Apr 2, 2017 at 15:14

answered Apr 2, 2017 at 13:26

Vasu

22.6k11 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mark Rotteveel Over a year ago

Technically you are right that you should not be able to execute multiple statements at once, however allowMultiQueries=true is MySQL specific, and the question is about SQL Server. And SQL Server apparently does allow the execution of multiple statements.

Vasu Over a year ago

Thanks Mark, I did not aware that it is MySQL specific, I have updated my answer (with only one option now)

symon Over a year ago

thank you guys, I have added it as a SP and invoking it doesn't land my files on the server so after a little break I'm going to start tackling the permissions issues going on now

symon Over a year ago

Hi Javaguy, Ive finally seen my .bak files created on the remote server, excellent. is it ok to be just executing a query using JDBC e.g - "EXEC DatabaseBackups" to call the SP? thats what I'm doing and its working currently, but curious of your thoughts. Thanks also @MarkRotteveel

Vasu Over a year ago

Yes, it is fine, you can use call DatabaseBackups as well

score 2 · Accepted Answer · 2017-04-02 13:19:19Z

2

In your java code call execute() instead of executeQuery(). The latter supposes that your query needs to return a result set, but the former does not assume that.

So, instead of:

stmt.executeQuery(query);

call:

stmt.execute(query);

edited Apr 2, 2017 at 13:19

answered Apr 2, 2017 at 13:17

user784540

6 Comments

symon Over a year ago

I changed to stmt.execute(query); However no .bak files are appearing on the server, tho it has cleared up the exceptions when running, the query itself runs fine manually, does my query string look like it could be the problem there?

user784540 Over a year ago

What about folder/file writing permissions on your server for the user who runs this code?

symon Over a year ago

I assumed since im connecting to the sql instance using sa via JDBC (which is the same user I also login as on SSMS to manually execute it it would be fine? will investigate some logs now

user784540 Over a year ago

Please check C:\SQLBackups folder writing permissions. And check, who can write to this folder. When you run your code manually, the effective windows-user is the user who runs this code manually, but when you make call via jdbc, the effective windows user is the user, that is being used to run sql-agent service.

symon Over a year ago

this potentially explains another issue of mine with inability to makeDirectory(), interesting i'll check it out

|

Collectives™ on Stack Overflow

How can I execute this sql backup script with JDBC?

2 Answers 2

5 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related