Cant add new structures to database, please need help!

Dear all,





I am actually trying to update a JChem database handled with derby, but when I click to the add button, I got this message:


" An SQL data change is not permitted for a read-only connection, user or database. "





I restart the TOMCAT and when I go the initial page "db_search/initsearch.jsp" I got also the same message :


"SQL error


SQLState: 25502


Message: An SQL data change is not permitted for a read-only connection, user or database.


Vendor: -1v "





But when I refresh the page , the marvin applet work and I can browse the database.(still can't add new molecules)





The derby access paramaters were checked and seems to be correct in the jsp sources.





Can someone help me please? all your comments will be interesting for me (as a new user of Chemaxon tools!)


Thanks.

This appears to be a question about the JSP demo application so I moved this topic to the Structure search and chemical database forum as that is a better place for this sort of question (but Instant JChem might be suitable for your needs as well as you seem to be wanting to create a chemical database for which Instant JChem is ideal).





The problems you describe appear to be related to the database connection details.


Can you post detils of the JDBC driver class name, and URL that you are using. Also, which version of Derby?





Thanks





Tim

tdudgeon wrote:

This appears to be a question about the JSP demo application so I moved this topic to the Structure search and chemical database forum as that is a better place for this sort of question (but Instant JChem might be suitable for your needs as well as you seem to be wanting to create a chemical database for which Instant JChem is ideal). The problems you describe appear to be related to the database connection details. Can you post detils of the JDBC driver class name, and URL that you are using. Also, which version of Derby? Thanks Tim

Thank you Tim for moving this post to the right topic and also for your quick reply.


So, This is how I acess to my database:


user and password, then:


Driver= "org.apache.derby.jdbc.ClientDriver";


Url= "jdbc:derby://MyLocalhost:1527/MyChemicalDB";


It is also how I access to it from ij.





My Derby version is 10.3.1.4.


Do you have any idea to get me solve this problem please?

That looks OK.


Presumably you are using an account with rights to modify the database (e.g you can create tables from ij with those same settings)?





Tim

tdudgeon wrote:

That looks OK. Presumably you are using an account with rights to modify the database (e.g you can create tables from ij with those same settings)? Tim

I try it, but when I connect with ij , I can't add tables!


Interesting because I got the same message:


ERREUR 25503 : Une DDL n'est pas autorisée pour une connexion, un utilisateur ou une base de données en lecture seule.





Is it the user that I use, who hasnt the rights to modify the database, or do you think that the database is on read only mode?

I would guess its the user you are using. You might need to grant the appropriate priviledges, and you should consult the Derby documentation for how to do this.


http://db.apache.org/derby/docs/10.3/ref/rrefsqljgrant.html


http://db.apache.org/derby/docs/10.3/devguide/cdevcsecure42374.html





It is possible to set up a read-only database, but you would probably know about it if you had done so!





Tim

tdudgeon wrote:

I would guess its the user you are using. You might need to grant the appropriate priviledges, and you should consult the Derby documentation for how to do this. http://db.apache.org/derby/docs/10.3/ref/rrefsqljgrant.html http://db.apache.org/derby/docs/10.3/devguide/cdevcsecure42374.html It is possible to set up a read-only database, but you would probably know about it if you had done so! Tim

Actually, I can't reach the database creator, that's why I am confused. I will check the documentation and let you in touch.





Thank you very much again Tim for your help.

I've tried these commands:





ij> connect 'jdbc:derby://MyLocalhost:1527/MyChemicalDB;';


ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(


'derby.database.sqlAuthorization','true');


ERREUR 25502 : Une modification des données SQL n'est pas autorisée pour une connexion, un utilisateur ou une base de données en lecture seule.





ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(


'derby.database.fullAccessUsers','test');


ERREUR 25502 : Une modification des données SQL n'est pas autorisée pour une connexion, un utilisateur ou une base de données en lecture seule.





Please, does that mean that my database is created on a read only mode? and can't be modified even by the user 'test' that created it?

Hi,





From our side we can say that we do not explicitly set the connection to read-only in our code, so the problem must be something about your database / user configuration.





I found some advice here that may be helpful:





http://objectmix.com/apache/646720-error-25502-sql-data-change-not-permitted-read-only-connection-user-database.html





Probably the most interesting hint there is that Derby may default to read-only mode if something went wrong during startup, so it's worth to take a look in the log files.


You can also find some of the more trivial tips there, e.g. file permissions, lock files, etc.





Best regards,





Szilard

Dear Szillar,





I was reading the log reports but any explanation is provided there about this error.


When I shutdown my tomcat, and restarted it, and after when I try to acess the database via the web interface, I get this error and the stack is reported here:


JChem version : 5.0.0


JVM : Sun Microsystems Inc. 1.6.0_03


OS : amd64 Linux 2.6.22-15-server





Stack trace:


------------





java.sql.SQLException: Une modification des données SQL n'est pas autorisée pour une connexion, un utilisateur ou une base de données en lecture seule.


at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)


at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)


at org.apache.derby.client.am.Statement.execute(Unknown Source)


at chemaxon.jchem.db.StructureCache.deleteOldUpdateLogs(StructureCache.java:336)


at chemaxon.jchem.db.StructureCache.load(StructureCache.java:284)


at chemaxon.jchem.db.StructureCache.loadIfNeeded(StructureCache.java:822)


at chemaxon.jchem.db.JChemSearch.loadCacheIfNeeded(JChemSearch.java:2757)


at chemaxon.jchem.db.JChemSearch.search1(JChemSearch.java:2500)


at chemaxon.jchem.db.JChemSearch.search(JChemSearch.java:2339)


at chemaxon.jchem.db.JChemSearch.access$2000(JChemSearch.java:126)


at chemaxon.jchem.db.JChemSearch$SearchThread.run(JChemSearch.java:535)


Caused by: org.apache.derby.client.am.SqlException: Une modification des données SQL n'est pas autorisée pour une connexion, un utilisateur ou une base de données en lecture seule.


at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)


at org.apache.derby.client.am.Statement.completeExecuteImmediate(Unknown Source)


at org.apache.derby.client.net.NetStatementReply.parseEXCSQLIMMreply(Unknown Source)


at org.apache.derby.client.net.NetStatementReply.readExecuteImmediate(Unknown Source)


at org.apache.derby.client.net.StatementReply.readExecuteImmediate(Unknown Source)


at org.apache.derby.client.net.NetStatement.readExecuteImmediate_(Unknown Source)


at org.apache.derby.client.am.Statement.readExecuteImmediate(Unknown Source)


at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)


at org.apache.derby.client.am.Statement.executeX(Unknown Source)


... 9 more

This is a Derby issue, not a JChem one, so there may not be much we can do to help you here. The Derby user mainling list is usually pretty good at providing support for Derby.





But it does look like there might be a problem with the database you created.


I would try creating another database from scratch and seeing if you have problems. Initially try to access it using the default account that shoud definitely have full access rights. Then once it is running you can set up a different user should you need this.








Tim