Access MySQL databases using Oracle SQL Developer
So this is a quick article on how to install the mySQL add-on for Oracle SQL Developer v3.0.04.
Why?
I've used various mySQL administration tools to manage mySQL databases over the years, all freeware until someone expresses an interest in using it and then the supplier will implement restrictions and trial based versions. Oracle SQL Developer is currently free at time of print (01-FEB-2013).
I also need to use Oracle databases in my day job so the Oracle SQL developer is already a pre-approved software for our work computers. We have SQL Server Management Studio (SSMS) as well for our SQL Server instances but it wasn't practical to link this to mySQL databases.
How?
Cheat Sheet for mySQL vs t-SQL
| MySQL | T-SQL | |
| Strings | ||
| String Replace | REPLACE(haystack,needle,replacement) | REPLACE(haystack,needle,replacement) |
| String Position | INSTR(haystack, needle) LOCATE(needle, haystack [, offset]) | PATINDEX(%needle%, haystack) CHARINDEX(needle, haystack [, offset]) |
| String Concatenation | CONCAT(string1,string2[,stringN]) | string1 + string2 SS v2012+: CONCAT(string1, string2 [, stringN ]) |
| String Substring | SUBSTRING( string,start,length ) | SUBSTRING( string,start,length ) |
| String Length | LENGTH(string) BIT_LENGTH(string) CHAR_LENGTH(string) | LEN(string) DATALENGTH(string) |
| Dates / Times | ||
| Weekday Name | DAYNAME(now()) | DATENAME(dd, getdate()) |
| Weekday Number | DAYOFWEEK(now()) | DATEPART(dw, getdate()) |
| Month Name | MONTHNAME(now()) | DATENAME(mm, getdate()) |
| Month Number | MONTH(now()) | DATEPART(mm, getdate()) |
| European Date (dd/mm/yyyy) | DATE_FORMAT(now(), '%d/%m/%Y') | CONVERT(varchar, getdate(), 103) |
| Time to Seconds | TIME_TO_SEC(now()) | (DATEPART(hour, getdate()) * 3600) + (DATEPART(minute, getdate()) * 60) + (DATEPART(second, getdate())) |
| Seconds to Time | SEC_TO_TIME( seconds ) | CONVERT(varchar, DATEADD(ms, seconds * 1000, 0), 114) |
| Add/Subtract Date | DATE_ADD(date,INTERVAL number datepart) DATE_SUB(date,INTERVAL number datepart) | DATEADD(datepart, number, date) DATESUB(datepart, number, date) |
| Other | ||
| If-then-else | IF( expression,value_if_true,value_if_false ) | IIF( expression,value_if_true,value_if_false ) |
| If Null | IFNULL(column_name,value_if_null) | ISNULL(column_name,value_if_null) |
| Rank/Rownum |
(@rownum:=@rownum+1) AS Rownum LIMIT from_index, number_of_rows | ROW_NUMBER() OVER(ORDER BY column_name ASC) AS Rownum |
Source(s):
- MSDN Microsoft Library (DATEPART (Transact-SQL))
- MSDN Microsoft Library (DATENAME (Transact-SQL))
- MySQL Documentation - String Functions
- MySQL Documentation - Date and Time Functions
Error 1065: Query was Empty
In view of the fact that this error pops up in so many systems I take over, I have often gone down the wrong path following the red herring as it were when in fact the answer to this is very common.
Why?
Bespoke systems are the usual suspects. Developers will have told the system what to do when newline, carriage returns and tab characters are entered in the data. Those who are security-conscious will also make their system escape apostrophes (') and double-quotes ("). But who can honestly build a system that thinks of everything from day one?
How?
Search a database for a string (MySQL, T-SQL)
I have a PHP script which behaves like PhpMyAdmin, in that it automatically lists a database structure and the fields to modify similar to an excel spreadsheet. Now without using PHP, I need standalone SQL scripts that can simply be used to search an entire database for a particular string of word(s).
I've posted my own as well as some others I've lifted from elsewhere, as they worked with my environment, to put them in one place on a website I've bookmarked (my personal site :c)
MySQL Group_concat equivalent in T-SQL and Oracle
Unfortunately I make a living using Microsoft and Oracle products. I shouldn't say unfortunately as I don't see myself doing any other job and it beats daytime television any day.
I use this quite a lot so I thought I'd put an article here somewhere. Based on the following concept:
- RowID column_to_return_as_string
- --------- --------------------------
- 1 Me
- 2 Myself
- 3 I
- -- to be returned as
- RowID my_field_name
- --------- --------------------------
- 1 Me,Myself,I