Does JDBC support 'ALTER USER userid IDENTIFIED BY password' command
Does JDBC support ALTER USER userid IDENTIFIED BY password command.
More precisely does PreparedStatement / Statement object supports DCL (GRANT/REVOKE/ALTER USER)commands.
I am using JDBC thin Type4 driver to connect to Oracle 8.1.7 from appliaction.
I am getting java.sql.SQLException: ORA-01935: missing user or role name in execute Update() method.
Does PreaparedStatement inadvertently puts quote to the passing variable since when I run the command in sqlplus in unix with the same userid it works fine.see below (I used it in 2 ways)
SQL> ALTER USER 'webload' IDENTIFIED BY 'Citibank3';
ALTER USER 'webload' IDENTIFIED BY 'Citibank3'
*
ERROR at line 1:
ORA-01935: missing user or role name
SQL> ALTER USER webload IDENTIFIED BY Citibank3;
User altered.
[846 byte] By [
Romita] at [2007-11-27 3:36:28]
# 1
> Does JDBC support ALTER USER userid IDENTIFIED BY
> password command.
Nope.
And JDBC doesn't support something like the following either "select * from mytable".
Databases do.
JDBC communicates with databases.
Now it is very likely that if all of the following are true then you can use JDBC to do that (although there might be other non-JDBC related items that impact it)
- The database allows it.
- The syntax is correct.
- Permissions are set correctly.
> Does PreaparedStatement inadvertently ...
And are you using prepared statement parameters?
Parameters have a very limited subset of what they can impact.
# 2
The database allows it and permissions are set correctly since I can change password by directly logging in to the database as that user.
The application is designed as such each user directly connects to the database(no datasource/no connection pooling) to run its part.
About syntax I am not sure since I cannot see what it pareses dynamically.
I am building the string passing it then ;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
PreparedStatement preparedstatement= ((PreparedStatement)sqlstatements.prepare(connection, jdcfile.isSearch()));
++++++++++++++++++++++++++++++++++++++++++++
public Statement prepare(Connection connection) throws SQLException
{
return prepare(connection, false);
}
public Statement prepare(Connection connection, boolean bool)
throws SQLException
{
if (mstrSQL == null)
return connection.createStatement();
if (isCallable())
{
System.out.println("Preparing Call " + buildString(bool));
return connection.prepareCall(buildString(bool));
}
System.out.println("Preparing Statement " + buildString(bool));
if(determineALTERUSER() ==1)
{
System.out.println("I AM HERE1");
return connection.createStatement();
}
else
return connection.prepareStatement(buildString(bool));
}
public String prepareAlter(boolean bool)
{
return buildString(bool);
}
# 3
> About syntax I am not sure since I cannot see what it> pareses dynamically.No one here can see it either - that isn't part of jdbc.So you would have to determine from whatever API that it is using what is being returned, for example by printing the string(s).
# 4
If I print the string
In WebSphere Studio Console I am getting the following message:
5/4/07 17:06:33:328 EDT] 209d8ace SystemOutO 05/04/2007 17:06:33 : JDCTemplate.load(): Opening C:\Program Files\ibm\WebSphere Studio\Application Developer\v5.1.1\PIDevWeb1\WebContent\PIDev\system_admin\change_pw\change_pw_results.htx
[5/4/07 17:06:33:488 EDT] 209d8ace SystemOutO Preparing Statement ALTER USER ? IDENTIFIED BY ?
[5/4/07 17:06:33:488 EDT] 209d8ace SystemOutO I AM HERE1
[5/4/07 17:06:33:488 EDT] 209d8ace SystemOutO 1ALTER USER ? IDENTIFIED BY ?
[5/4/07 17:06:33:498 EDT] 209d8ace SystemOutO 05/04/2007 17:06:33 : JDCTemplate.buildFile(): Caught Exception java.sql.SQLException: ORA-01935: missing user or role name
[5/4/07 17:06:33:498 EDT] 209d8ace SystemOutO 05/04/2007 17:06:33 : java.sql.SQLException: ORA-01935: missing user or role name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
at com.cbol.servlet.JDCTemplate.buildFile(JDCTemplate.java:145)
at com.cbol.servlet.JDCServlet.executeJDC(JDCServlet.java:466)
at com.cbol.servlet.JDCServlet.doPost(JDCServlet.java:366)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInsta
# 5
> ALTER USER ? IDENTIFIED BY ?
I doubt that will work.
Nothing in the orcle documentation suggests that 'user' (the first paramter) is a text literal.
Using a parameter that is the only way it can be expressed.
I didn't look at the second part of the expression but the same can be said of it.
As I first noted parameters can not be used every where.
# 6
Thank you very much.Your clarification helped me a lot to understand and it is appreciated.
