For those accustomed to Microsoft's SQL Server GUI , there are free, open source options available for keeping the Microsoft look while maintaining the MySQL functionality. SearchOpenSource.com's new MySQL expert, Scott Noyes, describes several examples and where to download them.
In this tip, Noyes explains the syntax differences between Microsoft's SQL Server and MySQL, offers code for a timestamp automation workaround and talks about why it's important to keep your version of MySQL current.
How can differences in Microsoft SQL Server and MySQL affect the syntax of a dynamic query?
Scott Noyes: There is no "TOP" clause in MySQL. Instead, you need to use ORDER BY and LIMIT clauses at the end of the query to return the first few rows from a set:
MySQL offers two types of variables. User variables, prefaced with an '@' symbol, persist beyond the routine. Local variables exist only within the routine. In either case, variable assignments in MySQL stored procedures use the SET or SELECT...INTO statements. Building dynamic queries requires use of a user variable, rather than a local variable, along with the SQL syntax for prepared statements:
What are some GUIs for MySQL that are similar to Microsoft SQL Server?
Noyes: There are several GUI interfaces to MySQL. Some are free under GPL; others require purchase of a per-user license. Official products from the company include MySQL Query Browser and MySQL Administrator and are available for free download here.
A popular Web interface is phpMyAdmin. See also DBDesigner, SQLFront and SQLyog.
What advice d...
To continue reading for free, register below or login
To read more you must become a member of SearchEnterpriseLinux.com
o you have for users who are working with MySQL 3.23 or earlier versions?
Noyes: MySQL 3.23 is a very old version; the current recommended release is 5.0. Version 3.23 will still work, but you will miss out on a number of features and bug fixes added in the last few years.
What are some errors that can be avoided in SQL syntax?
Noyes: Like most computer languages, SQL is picky about the characters used for syntax. Sometimes, "smart quotes" are inserted by some word processors. Stick to straight quotes (') around strings and backticks (`) around identifiers (table and column names).
How can users connect to Web pages with MySQL?
Noyes: Connecting a Web page to MySQL requires JavaScript on the client to issue the request, MySQL on the server to handle the query and a server-side scripting language to stand in-between. You may use any language you wish; PHP, Perl and Python are popular candidates.
Can you suggest a workaround for the lack of automation in timestamping multiple fields for a single table in MySQL?
Noyes: There are a few ways around this limitation. One solution is to forego attempts at automatic timestamps, and explicitly set the time in the data manipulation statements, using the NOW() or CURRENT_DATE() functions:
A closer step to automation is to define only the update_time as a timestamp, and explicitly set the time during creation:
In versions 4.0 and older, the first timestamp field in a table (and only that one!) would automatically update unless given some other value. Starting in version 4.1, you have a great deal more control over which field automatically updates and when.
The closest we can get with MySQL is to build a trigger on the table to automatically populate the fields:
