FAQ

Q: How do I use sqlize.com?

A: Simply enter some SQL code into the text editor and click "Execute". Your query must follow standard MySql syntax, and is only allowed to create temporary tables. If your query has invalid syntax or generates a runtime error, you will see the error output into the results area. Otherwise, the results from any SELECT statement that you perform will be displayed in a table underneath once you click "Execute". There are sample queries you can try by clicking the links located underneath the text editor on the main page.

Q: Why temporary tables?

A: For security reasons, the database user is only granted privileges to create temporary tables and perform standard CRUD actions. The privileges necessary to allow creating persistent tables also allow modifying the system databases. Also, there would be conflicts if the same users on the same database are trying to create the same table names. Temporary tables eliminate both of these concerns.

Q: What is a permalink?

A: Permalink stands for "permanent link". When you click on the permalink button, a unique url is generated that will always link to the query you just ran. You can copy and paste the url to other locations, and other users can view your query on sqlize.com. Try this one for example: http://sqlize.com/OX6t9o49xo. Once a permalink is generated, it can never be modified.

Q: Why do I get "CREATE command denied..." or "Access denied..."?

A: To protect the database from malicious use, certain commands are not allowed. The only privileges granted in executing your SQL statement are temporary tables and standard CRUD operations (SELECT, UPDATE, INSERT, DELETE). If you are trying to create a table, double-check that you didn't forget the keyword "temporary", which is necessary to create tables in the editor:

CREATE *TEMPORARY* TABLE  ...

Q: What does "You have an error in your SQL syntax..." mean?

A: MySql validates the syntax of queries before they are run. The syntax must match the standard exactly or this error is generated. If you look at the error output, you should see a short snippet of SQL from your query that indicates roughly where the error was caused. Check the code in the general vicinity against the documentation to verify you have entered it correctly. Also make sure that you have a semicolon (;) at the end of every statement, and that your ticks (`) and quotes (') are all properly closed.

Here is the syntax of some standard MySql statements (NOTE: Order of clauses is important):

CREATE TEMPORARY TABLE name (
	field1 type1,
	field2 type2,
	...
);

SELECT fields FROM tables WHERE condition
GROUP BY columns HAVING conditions
ORDER BY sortorder LIMIT maxrows;

UPDATE tables SET field1=value1, ...
WHERE condition
ORDER BY sortorder LIMIT maxrows;

INSERT INTO table [(field1,field2,...)]
VALUES (value1,value2,...),
	(value1,value2,...),
	...;

DELETE FROM table
WHERE condition
ORDER BY sortorder LIMIT maxrows;

FROM table1
INNER JOIN table2 ON table1.field = table2.field
LEFT JOIN table3 ON table1.field = table3.field
RIGHT JOIN table4 ON table1.field = table4.field

Q: What does "Can't reopen table: '{table_name}'" mean?

A: One of the limitations of temporary tables is that they cannot be referenced multiple times in the same query. This limitation does not apply to regular tables. The simplest solution is to create a copy of the table and reference the copy of the table.

Here is an example that will generate the error message "Can't reopen table: 'D'":

	CREATE TEMPORARY TABLE Data (id int, descr varchar(50));
	INSERT INTO Data VALUES
		(1, 'Test 1'), (2, 'Test 2'), (3, 'Test 3');

	SELECT * FROM Data D
	WHERE id = (SELECT max(id) FROM Data);

Run sample: http://sqlize.com/Yx3S4ZU19Z

Here is a fixed version that creates a copy of the table:

	CREATE TEMPORARY TABLE Data (id int, descr varchar(50));
	INSERT INTO Data VALUES
		(1, 'Test 1'), (2, 'Test 2'), (3, 'Test 3');

	CREATE TEMPORARY TABLE Data2 SELECT * FROM Data; -- create copy

	SELECT * FROM Data D
	WHERE id = (SELECT max(id) FROM Data2);

Run sample: http://sqlize.com/Ts148AwS6X

Q: What does "Commands out of sync; you can't run this command now" mean?

A: Only one SELECT statement is allowed per query that you run, and it must be the last statement in the query batch. If you have multiple SELECT statements, you must remove all of them except for one. Also check that you don't have anything appearing after the final SELECT statement, including comments.

Q: How do I include sample/test data in my query?

A: Sample data can be included in your query by creating and populating a temporary table. For example, suppose you have a sample collection of Person records with id's of 1, 2, and 3, and names of Jacob, Ryan, and Billy. You can use the following script to populate a table with this sample data and query it afterward:

	CREATE TEMPORARY TABLE Person (id int, name varchar(50));
	INSERT INTO Person VALUES
		(1, 'Jacob'),
		(2, 'Ryan'),
		(3, 'Billy');
	
	-- demonstrate querying the data
	SELECT * FROM Person;

Run sample: http://sqlize.com/J16220AG44

You can create as many temporary tables as you'd like and join them together in your query. If you are querying from the same temporary table multiple times in a single query, you may get an error message "Can't reopen table." See the relevant FAQ above for this situation.