CODEWITHSUNDEEP
mysqlsqlunit-testingsyntax
eternalnewb
eternalnewb

Reputation: 303

Test that I'm using MySQL syntax?

Thanks to some organizational oddities, my work team now has somewhat limited access to a random database, but we're not entirely sure what syntax we should be using with it. In an ideal world, we'd like it to be MySQL.

Can anyone describe or point me toward a set of table-creation and selection queries that will allow us to test whether this is MySQL? Ideally, this set of queries will be something that works only on MySQL, throwing an error on other systems.

Upvotes: 0

Views: 80

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562368

Each RDBMS has their respective protocol and client library. The client of one RDBMS generally won't work to connect to the wrong brand of RDBMS.

I would suggest you test connecting using the MySQL client. If that doesn't work, it isn't MySQL. :-)

Re comments:

MySQL does not restrict syntax based on privileges, so you should never get a syntax error. And you can call most builtin functions even if you have no privileges to access any databases or tables. I confirmed this:

mysql> grant usage on *.* to 'dummy'@'%';  /* minimal privilege */

$ mysql -udummy

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.5.31-30.3-log |
+-----------------+

If you got a syntax error on that statement, I would say you're actually connected to a different RDBMS that doesn't have a VERSION() function.

For example, Microsoft SQL Server has a @@VERSION global variable, and they have the SERVERPROPERTY() function that you can use to query the server version and other information. (See links for documentation.)

In my experience, sites that use Microsoft SQL Server seldom call it Microsoft SQL Server, they mistakenly call it "SQL" or even "MySQL". So my first guess is that you're using that product. Use the global variable and function I mention above to test this.

Upvotes: 1

hd1
hd1

Reputation: 34657

You can test your DDL statements on sqlfiddle and then, run them on MySQL, which should take care of most of your SQL issues. Alternatively, use hibernate or a similar ORM and don't worry about SQL at all.

Upvotes: 1

Chad Cook
Chad Cook

Reputation: 619

Try running the MySQL select version command:

 SELECT VERSION()

http://dev.mysql.com/doc/refman/5.0/en/installation-version.html

Does it have to be a table creation and/or selection query or will the above work?

Upvotes: 4

Ricky Mutschlechner
Ricky Mutschlechner

Reputation: 4409

select * from v$version;

This could work, but I'm not sure if this is specific to MySQL let alone if it even works in MySQL - I thought I'd share. Let us know if it works!

Upvotes: 0

Related Questions