Your Technical Buddy
ADD/ENABLE constraint exception like ORA-02437, ORA-02299 etc
Today while working on one assignment I came across situation where I have to enable a unique key constraint on the table which some one disabled and forget due to which many duplicate records get entered and started creating problem for the application.
Now challenge is to find out all those records there are ways to find duplicates by writing some queries but this is easiest and faster way by using Oracle Exceptions table.
While running junit using in memory database H2 I was hitting this error. I was testing my spring DAO classes that are using JDBCTemplate. In a single Junit testing, to test various scenario I was calling a single DAO method with different parameters . It start giving the error "The object is already closed" as it finishes testing the DAO method with first parameter.
Then I investigated in JDBCTemplate and found that it closes connection after every execution of query using Finally statement.
finally {
JdbcUtils.closeStatement(stmt);
DataSourceUtils.releaseConnection(con, getDataSource());
}
To solve this issue I override the JDBCTemplate and created my own JDBCTemplate in which I have removed the Finally statement.
This error appears when you have not specified required privileges to user in SCRIPT file for running the statements. Use the commands as follows just after user creation grant access as DBA like below:
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
While running HSQLDB in standalone mode you may face the error
java.sql.SQLException: User not found: SA
This is because while creating the DB using script file you are not creating the user
CREATE USER SA PASSWORD ""
The above command you have to include in your db.script file db is your database name) this will creates a user by name SA with blank password.
I have installed MySql 5.1 version on my lappy and when I try to connect with the DB it was giving me following error:
“Client does not support authentication protocol requested by server; consider upgrading MySQL client”
MySQL 5.1 uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. If you upgrade the server from 4.0, attempts to connect to it with an older client may fail with the above message.
To solve this problem, you should use one of the following approaches:
Upgrade all client programs to use a 4.1.1 or newer client library.
When connecting to the server with a pre-4.1 client program, use an account that still has a pre-4.1-style password.
Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function:
mysql> SET PASSWORD FOR -> ‘some_user‘@’some_host‘ = OLD_PASSWORD(‘newpwd‘);
Alternatively, use UPDATE and FLUSH PRIVILEGES:
mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(‘newpwd‘) -> WHERE Host = ‘some_host‘ AND User = ‘some_user‘; mysql> FLUSH PRIVILEGES;
Earlier to this command password look like this
mysql> select Host,User,Password from mysql.user;
+———–+——-+——————————————-+
| Host | User | Password |
+———–+——-+——————————————-+
| localhost | root | *F02BB955B742538740F1CF4B7196B8804001C74F |
| % | nitin | *F02BB955B742538740F1CF4B7196B8804001C74F |
+———–+——-+——————————————-+
After the command it changed it to this.
mysql> select Host,User,Password from mysql.user;
+———–+——-+——————————————-+
| Host | User | Password |
+———–+——-+——————————————-+
| localhost | root | *F02BB955B742538740F1CF4B7196B8804001C74F |
| % | nitin | 5b58a62e4240429c |
+———–+——-+——————————————-+
This works for me. Hope this will help
Reference:http://dev.mysql.com/doc/refman/5.1/en/old-client.html
SQL0407N Assignment of a NULL value to a NOT NULL column name is not allowed.
If you encounter this error and If the value for name is of the form “TBSPACEID=n1, TABLEID=n2, COLNO=n3”, you can determine the table name and column name using the following query:
SELECT C.TABSCHEMA, C.TABNAME, C.COLNAME FROM SYSCAT.TABLES AS T, SYSCAT.COLUMNS AS C WHERE T.TBSPACEID = 2 AND T.TABLEID = 26 AND C.COLNO = 0 AND C.TABSCHEMA = T.TABSCHEMA AND C.TABNAME = T.TABNAME |