JDBC stored procedure MariaDB syntax

Question

I'm a beginner of creating a stored procedure using JDBC and MariaDB. When I create the procedure, I get an error. I ran in to the problem since yesterday:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@auth_id varchar(15),@auth_fname varchar(20) output,@auth_lname varchar(20) o...' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)

What I tried so far, I attached the code below. I don't know what causes the error.

public class Jdbcc {
    
    Connection con;
    PreparedStatement pst;
    
    public Jdbcc()
    {
        Connect();
       }
    public void Connect()
    {
      try 
      {
   Class.forName("com.mysql.jdbc.Driver");
       con = DriverManager.getConnection("jdbc:mysql://localhost/abclibrary", "root","");     
       String str = "CREATE PROCEDURE authors @auth_id varchar(15), @auth_fname varchar(20) output, @auth_lname varchar(20) output AS SELECT @auth_fname=auth_fname,@auth_lname= auth_lname FROM authors where auth_id=@auth_id";

                          Statement stmt = con.createStatement();
                          int k = stmt.executeUpdate(str);
                          System.out.println("Created.......");

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    
    }

    public static void main(String args[])
    {
        Jdbcc jk = new Jdbcc();
    }
}

I don't think this is the specific problem you're asking about, but the SQL you create from concatenating strings needs spaces between `auth_lname` and `FROM` and between `authors` and `where`. — khelwood, Apr 18 '21 at 09:56
@NisaYathu - It's much easier to preserve the format [using Java15 text block feature](https://stackoverflow.com/a/65676449/10819573). However, very few individuals and teams have started using Java15 yet. For a version lower than 15, the best way to avoid such problems is first, type `""` and then copy and paste the string between the double-quotes. — Arvind Kumar Avinash, Apr 18 '21 at 10:27
Assuming you are using IDEs like Eclipse or IntelliJ, your IDE will automatically break the string into multiple lines preserving the whitespace and also escaping the characters like ``\``, double quote etc. — Arvind Kumar Avinash, Apr 18 '21 at 10:41
`authors @auth_id varchar(15), @auth_fname varchar(20)...` should be inside parenthesis. Check the syntax at https://mariadb.com/kb/en/create-procedure/ — Arvind Kumar Avinash, Apr 18 '21 at 13:15

score 1 · Accepted Answer · answered Apr 18 '21 at 20:35

Use the following to create the stored procedure:

       String str = "CREATE PROCEDURE authors (p_auth_id varchar(15), OUT p_auth_fname varchar(20), OUT p_auth_lname varchar(20))\n" +
                    "BEGIN\n" +
                    "  SELECT auth_fname, auth_lname INTO p_auth_fname, p_auth_lname FROM authors where auth_id = p_auth_id;\n" +
                    "END;";

The changes I made were as follows:

Don't prefix procedure parameter names with @. I added p_ prefixes to parameter names so that they don't clash with column names.
You need to surround the list of procedure parameters with parentheses (...).
Output parameters are identified by specifying OUT before the parameter name, not with output afterwards.
You can't write SELECT variable1=column1, variable2=column2, ... as you may be able to do in other databases. Instead write SELECT column1, column2, ... INTO variable1, variable2, ....
The body of the procedure have been wrapped in a BEGIN ... END; block.

After I made these changes, I could use your Java class to create the stored procedure, and then call it from the MariaDB console.

Incidentally, if the stored procedure already exists, you can use CREATE OR REPLACE PROCEDURE ... instead of CREATE PROCEDURE ..., which will replace the existing stored procedure.

See also the MariaDB documentation for CREATE PROCEDURE.

score 0 · Answer 2 · answered Apr 18 '21 at 10:19

Take care about spaces:

String str = "CREATE PROCEDURE authors @auth_id varchar(15), @auth_fname varchar(20) output, @auth_lname varchar(20) output "
                           +"AS "
                           +"SELECT @auth_fname=auth_fname,@auth_lname= auth_lname " //you missed space here
                           + "FROM authors " //and here
                           +"where auth_id=@auth_id";

auth_lnameFROM and authorswhere were the problem

i update the code please have a look still problem – Nisa Yathu Apr 18 '21 at 13:03 — Nisa Yathu, Apr 18 '21 at 13:03

JDBC stored procedure MariaDB syntax

2 Answers2