Some useful SQL Snippets: Oracle Create a date without any formatting hassle (like to_date): SELECT DATE’2014-01-01′ FROM dual;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;select extract (year from date’2013-05-06′) from […]
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.bz2mysqldump -uroot -proot –opt […]
UTF-8 has always been a multi-byte encoding but you probably had to handle only 2 byte (16bit) UTF-8 characters. With the raise of Emojis 4 byte characters rose as well so handling 4 byte UTF-8 characters is not only of interest for handling exotic languages but also for the needs of average users who want […]
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, […]
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 […]