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

score 2 · Answer 1 · 2017-04-02T13:19:19.993

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 02 '17 at 13:19

answered Apr 02 '17 at 13:17

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? – symon Apr 02 '17 at 13:19
What about folder/file writing permissions on your server for the user who runs this code? – Apr 02 '17 at 13:21
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 – symon Apr 02 '17 at 13:22
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. – Apr 02 '17 at 13:25
1

this potentially explains another issue of mine with inability to makeDirectory(), interesting i'll check it out – symon Apr 02 '17 at 13:26
@symon You may also need to read all the results of the statement execution before things happen. For an example see: http://stackoverflow.com/a/14829425/466862 – Mark Rotteveel Apr 02 '17 at 15:36

Vasu · Accepted Answer · 2017-04-02T15:14:25.653

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 02 '17 at 15:14

answered Apr 02 '17 at 13:26

Vasu

21,832
11
51
67

2

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. – Mark Rotteveel Apr 02 '17 at 14:48
Thanks Mark, I did not aware that it is MySQL specific, I have updated my answer (with only one option now) – Vasu Apr 02 '17 at 15:14
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 Apr 02 '17 at 17:08
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 – symon Apr 02 '17 at 17:22
Yes, it is fine, you can use `call DatabaseBackups` as well – Vasu Apr 02 '17 at 17:26

How can I execute this sql backup script with JDBC?

2 Answers2