Skip to main content

Posts

Showing posts from 2020

Boy I wish there was a way to create a table from a select query

I was doing some SQL work today when I had a thought:
Boy I wish there was a way to create a table from a select query
I started in Navicat to see if it was possible.  After find nothing I decided to google it all the while thinking in my head how I was going to create a script to do this if I couldn't find anything.
I found something. I was happy yet sad.  Happy because I didn't have to take the time to create something.  Sad because I didn't have to create the tool.  It sounded like a fun project.
What did I find you ask?
The "Create Table as" statement.
CREATE TABLE [ IF NOT EXISTS ] new_table [ AS ] SELECT expressions FROM existing_tables [WHERE conditions];
The best part about it is the statement knows to go to the existing tables to get the column type, size etc.   Such a tremendous time saver.  I cannot believe in all my years of programming I never stumbled upon this type of statement.
Source: Tech on the Net

Adding Custom JDBC Options to your cfconfig datasource

I was getting a weird timezone error when connecting to a MySQL 8 database.
The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
I was able to get around this by adding a custom jdbc option to my datasource:
cfconfig datasource save name=test dbdriver=mysql host=localhost port=3306 database=testdb username=randy password=test custom="serverTimezone=America/New_York"