Call stored sql procedure

1

How can I execute a stored procedure in kotlin? I tried this:

val pStmt = con?.prepareCall("\\{call master.sys.fn_varbintohexsubstring(0,HASHBYTES('SHA1', CONVERT" + "(varchar(255), ${user.clearPassword})+'DEL'+${user.salt}),1,0)\\}")
    pStmt?.registerOutParameter("result", Types.VARCHAR)
    val cursor = pStmt?.executeQuery()
    if (cursor != null) {
        while (cursor.next()) {
            val result = cursor.getString("result")
            }
    }

Exception:

W/System.err: java.sql.SQLException: Invalid JDBC call escape at line position 6.

1 Like
2

Basically it should work the same as in Java code.

I can guess that user.clearPassword is probably unquoted string value, and one should quote it to pass to CONVERT, like: ...CONVERT(varchar(255), '${user.clearPassword}')...

But that’s just a blind guess.

1 Like
3

Thanks for your reply. But its not working. I changed the first line to:

val pStmt = con?.prepareCall("{call master.sys.fn_varbintohexsubstring(0,HASHBYTES('SHA1', CONVERT" + "" +
                "(varchar(255), '${user.clearPassword}')+'DEL'+'${user.iSalt}'),1,0)}")

But now i get:

java.sql.SQLException: Invalid JDBC escape syntax at line position 81 ‘}’ character expected.

4

Well, I’m not an expert in particular SQL dialect. So we have:
...prepareCall("{call master.sys.fn_varbintohexsubstring(0,HASHBYTES('SHA1', CONVERT" + "" + "(varchar(255), '${user.clearPassword}')+'DEL'+'${user.iSalt}'),1,0)}")

Let’s replace all Kotlin expressions with corresponding String values and concat them:

...prepareCall("{call master.sys.fn_varbintohexsubstring(0,HASHBYTES('SHA1', CONVERT(varchar(255), 'real_password')+'DEL'+'real_salt'),1,0)}")

Does this still fail?