All posts tagged with 'MySQL'

SQL Snippets

19-Dec-13

Some useful SQL Snippets: Oracle Create a date without any formatting hassle (like to_date): SELECT DATE’2014-01-01′ FROM dual; Extract values as numbers from a date object SELECT EXTRACT (YEAR FROM DATE’2013-05-06’) FROM dual; SELECT EXTRACT (MONTH FROM DATE’2013-05-06’) FROM dual; SELECT EXTRACT (DAY FROM DATE’2013-05-06’) FROM dual; Conversion of geographical coordinates into different coordinate systems: […]

Read the complete article »

mysqldump / mysql tips

14-Feb-13

Two tips to make your life with mysqldump easier and your backups better: The following command creates a full backup of your mysql server including all databases and structures (including views and(!) stored procedures) along with the data: mysqldump -uroot -proot –opt –routines –add-drop-database –default-character-set=utf8 –create-options –all-databases | \ bzip2 > backup.sql.bz2 You can also […]

Read the complete article »

Fixing hibernate “Cannot release connection” exception using DBCP and MySQL.

21-Nov-11

Every 8 hours i got a Hibernate exception “Cannot release connection” within a Java application using Hibernate, Apache DBCP on Tomcat: org.hibernate.exception.GenericJDBCException: Cannot release connection at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29) .. .. Caused by: java.sql.SQLException: Already closed. Not only that the messages polluted my inbox, the exception was visible to the enduser, […]

Read the complete article »

MySql compatible AES encryption / decryption in Java

18-Jul-11

MySQL has an aes_encrypt/aes_decrypt pair of functions (Encryption and Compression Functions) that enable encryption and decryption of data using the official AES algorithm. The functions are easy to use (select AES_ENCRYPT(‘text’,’password’)) and the result is easy to store (insert into secrets values HEX(AES_ENCRYPT(‘text’,’password’))) as hex values. I used this technique for a while but i […]

Read the complete article »