B4J Question get MySql User's grants

[rbirago]

Using jSql library I can perform any SELECT by ExecQuery or another sql command using ExecNonQuery. But what for example if I want to ask for User's grants (SHOW GRANTS FOR CURRENT_USER) ?
I tried using ExecQuery, but the ResultSet seems to have not the result of the operation.
Any suggestion?
thanks
Roberto

 

[Erel]

It didn't work with my local test db, but you should try it:

Dim metadata As JavaObject = sql.As(JavaObject).GetFieldJO("connection").RunMethod("getMetaData", Null)
Dim rs As ResultSet = metadata.RunMethod("getTablePrivileges", Array(Null, Null, "*.*"))
Do While rs.NextRow
    For i = 0 To rs.ColumnCount - 1
        Log(rs.GetColumnName(i) & ": " & rs.GetString2(i))
    Next
Loop
rs.Close

 

[rbirago]

Actually the problem is different. Using these instructions:

Sub Button1_Click
    Dim rs As ResultSet = Sql1.ExecQuery("show grants for current_user")
    Dim dumint As Int = rs.ColumnCount
    Dim dumColName As String = rs.GetColumnName(0)
    Dim dummy As String = rs.GetString2(0)
End Sub

the first three statements work fine.
When I try to get the value of the element it gives the error:

java.sql.SQLException: Before start of result set

 

[LucaMs]

Sql1.ExecQuery("show grants for current_user")

That's a command to run as a command line, not in a query.

 

[rbirago]

Really it was a trial, knowing that this is not a query...but: first of all the operation gives no error, ColumnCount gives a result of 1 and GetColumnName gives a positive response title ("Grants for user xxxx"). Is it possible that in the ResultSet there is in some way the list of the user's Grants? Other ways to obtain the Grants of an User?

 

[alwaysbusy]

I think you are close. try this:

Dim cur As ResultSet = SQL.ExecQuery("show grants for current_user")
   
Do While cur.NextRow
        For i = 0 To cur.ColumnCount - 1
            Log(cur.GetColumnName(i) & ": " & cur.GetString2(i))           
        Next       
Loop
cur.Close

Returns for example:

Grants for TST_OneTwo@localhost: GRANT USAGE ON *.* TO 'TST_OneTwo'@'localhost'
Grants for TST_OneTwo@localhost: GRANT ALL PRIVILEGES ON `TST_OneTwo`.* TO 'TST_OneTwo'@'localhost'

Alwaysbusy

 

[rbirago]

You are right! The only problem was that I have not opened the element by NEXTROW. It works now like a breeze.
Thank you.