A new fiddle for dba.se

SQLFiddle has been an essential tool and a great complement to dba.se for a very long time, but it has gone downhill a bit in the last year or two.

I have also wondered for some time what a 'fiddle' designed specifically with dba.se and markdown in mind might look like, and over the last few days I've put together a prototype of how I think it should be.

Please have a play with the site and let me know here if you have suggestions for improvements or design changes.

The current list of supported databases is:

• Db2 Developer-C 11.1 (with lots of thanks to @mustaccio)
• Firebird 3.0
• MariaDB 10.2, 10.3 and 10.4
• MySQL 5.6, 5.7 and 8.0
• Oracle 11.2 and 18
• Postgres 8.4, 9.4, 9.5, 9.6, 10, 11 and 12 (beta)
• SQL Server 2012, 2014, 2016, 2017, 2017 Linux and 2019 Linux CTP (but 2019 is currently offline for security reasons)
• SQLite 3.8, 3.16 and 3.27 (but 3.16 is currently offline for security reasons)

Here's an example (the image links to the fiddle):

I've edited some of my answers (e.g. this one) to use db<>fiddle.

As of Apr 2018 the site uses ssl by default — there are some benefits even for a site like db<>fiddle and it's free now so why not?

As of Apr 2019 you can now hide batches selectively.

• +1, +10, +1000000000000000000 – ypercubeᵀᴹ Feb 16 '17 at 18:28
• ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ – MDCCL Feb 16 '17 at 21:14
• +2 also. It would be a welcome addition. As wishes are cheap: having several versions (i.e.: PostgreSQL 9.4, 9.5, 9.6...) of the same DB would be a plus. Sometimes you don't know when your problem is just version specific... and you'd better upgrate quickly. – joanolo Feb 19 '17 at 6:38
• @joanolo "As wishes are cheap" not at all, thanks for the suggestion. I've added 9.5 and 9.4, and will add others at some point! – Jack Douglas Feb 19 '17 at 18:16
• As sqlfiddle is an established site, and you seem to have already invested time and effort in to it according to the about page, couldn't SE just adopt it and make sql fiddle great again, by pushing some resources towards it's development? – Tanner Feb 21 '17 at 10:34
• @Tanner when I saw the writing on the wall a couple of years ago, I asked that question, but although help was offered, it wasn't the sort that Jake needed. – Jack Douglas Feb 21 '17 at 10:37
• @JackDouglas fair enough, just thought I'd ask... you know, reinventing the wheel n all. good luck, would be interested to see how it develops. – Tanner Feb 21 '17 at 10:54
• @Tanner thanks, feel free to post feature requests here if you have ideas. – Jack Douglas Feb 21 '17 at 11:10
• Forgive my ignorance, but what are SQLfiddle/DBFiddle? I followed the links but they didn't have any explanatory text. I also couldn't find any reference on DBA.SE about them. – BradC Feb 23 '17 at 16:37
• @BradC Try this one: dbfiddle.uk/… the point is that you can make a reproducible test case demonstrating a question or problem, and then others can tinker with it (try changing the sql in the link in this comment) – Jack Douglas Feb 23 '17 at 17:12
• @BradC also, there is a bit of documentation on the home page for each RDBMS: dbfiddle.uk/?rdbms=sqlserver_next – Jack Douglas Feb 23 '17 at 17:13
• @JackDouglas: Others who don't have the target dbms installed can tinker. That's the big deal for me. I'd usually rather see the SQL in the question, myself. There's no reason people can't include SQL in the question and in a fiddle, but that's not as common as I would like it to be. – Mike Sherrill 'Cat Recall' Feb 28 '17 at 16:49
• The site has evolved. By now, in all modesty, I'd like to say it's absolutely marvellous. – Erwin Brandstetter Apr 10 '17 at 1:18
• @lad2025 thanks for spotting that bug, now fixed. Regarding db support, pg11 will be added soon, but Oracle 12c probably never as there is no XE version. 18c XE is thought to be coming up (possibly in July) and if that materializes I will add it. – Jack Douglas May 28 '18 at 8:36
• @Mitch both dbfiddle and my church are intended to help people, and both are the product of a lot of effort. I don't push either on anyone — and I hope to have the same respect shown to me by those who believe differently. – Jack Douglas May 30 '18 at 9:39

Bug for MySQL-8.0

SELECT
json_tb.item_id,
json_tb.model_number,
json_tb.quantity
FROM JSON_TABLE
(
'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
, "$[*]" COLUMNS ( item_id int PATH "$.item_id",
model_number varchar(100) PATH "$.model_number", quantity int PATH "$.quantity"
)
) json_tb


Returns:

SELECT command denied to user 'fiddle_SMYHCBMIQRDQGWHKBNDS'@'localhost' for table 'json_table'


json_table is a function more than a table (ref: JSON_TABLE function manual)

• This was reported earlier and we were waiting for MySQL to release a fix, but thanks for bringing it up again as it prompted me to check back and discover the release was ready. It should all be working now. – Jack Douglas Oct 23 '18 at 15:24

this morning I tried to use dbfiddle.uk with PostgreSQL 11.

Extremely simple sample SQL:

CREATE TABLE test (bill INTEGER, fred TEXT);

and then:

INSERT INTO test VALUES (1, 'sdfs'), (2, 'vcxf'), (3, 'tyiro');

followed by:

SELECT * FROM test;

Result in PostgreSQL 10 (as expected):

bill    fred
1    sdfs
2    vcxf
3    tyiro


But PostgreSQL 11 fails to do anything - not even the ticks for correctly creating the table and inserting the data.

The fiddle is here. Just as an aside, it works fine for all versions of MySQL.

• Thanks, Erwin alerted me to this in a comment and I think it is fixed. I need to find a way of restricting rogue fiddles from using up all available disk space… – Jack Douglas May 2 at 18:38
• Yes - it's fixed now. Wow, that took all of 17 minutes from bug report to fix. If only my bug reports to suppliers were sorted so quickly! :-) – Vérace May 2 at 18:45
• well it was a bit longer than that since Erwin's comment, but thanks :) – Jack Douglas May 2 at 19:54

Comments/Bugs link on dbfiddle.uk directs me to here, so I suppose "answering" is the way to submit bugs:

Select MySQL 5.7 as database and try this extra simple DML command:

create table errorTest (id integer, issue text);
insert into errorTest (id, issue) values (1,'issue #1');


It is expected to work but comes up with an error message saying:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into errorTest (id, issue) values (1,'issue #1')' at line 2

• yes this is exactly the right way to submit bugs, thanks :) the MySQL PHP driver we use does not accept multiple statements per line, so you have to split them up: dbfiddle.uk/… – Jack Douglas May 4 at 17:46
• I see you have used — it's a bit confusing that the SQL Server driver is happy to have multiple statements per batch and MySQL is not. – Jack Douglas May 4 at 18:05
• actually it looks like it is possible, but when I've asked before I've been told it isn't necessary. Do you think it would be helpful compared to just splitting up the fields? – Jack Douglas May 4 at 18:11
• Sorry I don't understand the comments exactly, as far as I understand, you are saying it is a fault of PHP. Driver accepts multiple statements with 65536 parameter, no? Anyway, I am not a fan of MySQL. The same query works well on db-fiddle.com and sqlfiddle.com. – Cetin Basoz May 4 at 18:39
• I'll let you know when we get multiple batches working (using mysqli, so not as simple as passing 65536 would be unfortunately, but apparently still possible) – Jack Douglas May 4 at 22:29
• Hi Cetin, I've implemented this feature — please let me know if it doesn't work the way you expect. Here is your example code working: dbfiddle.uk/… – Jack Douglas May 6 at 6:01
• Thanks for the update, it is working fine now :) I am glad, I prefer dbfiddle.uk over other sites (wish I knew hosting problem before). – Cetin Basoz May 6 at 11:56
• Thanks for the encouragement and support — feel free to bring up anything else here, eg improvement suggestions. – Jack Douglas May 6 at 15:02

Any chance you could do some user agent sniffing and completely ditch the fancy code boxes in favour of just plain old text boxes if the user is using a cellphone?

Or even simpler to implement and probably better; just a toggling link/button switcher that flips between "use fancy editor/use basic text box editor" so I can flip the mode myself?

I can't get copy or paste to work at all on an iPhone (to be fair, I haven't tested Android) with the fancy text boxes on dB<>fiddle, which kinda makes the entire site unusable for all but the simplest queries as I'd have to literally type some OP's entire query in manually off of SO etc

Many thanks!

• Thanks Caius, I wasn't aware cut/paste doesn't work on iPhone. I'm on holiday right now, but will take a look in a couple of weeks and think about what's best to do to improve that. – Jack Douglas Jun 23 at 16:51

Attempting to run the following two SQL Server batches on any available db<>fiddle version:

SET LANGUAGE Italiano;
SELECT CONVERT(datetime, '2019.06.30', 0);

SET LANGUAGE Italiano;
SET DATEFORMAT YMD;
SELECT CONVERT(datetime, '2019.06.30', 0);


Produces an error:

The code runs 'successfully' in SSMS producing the expected message and error for the first statement:

L'impostazione della lingua è stata sostituita con Italiano.
Msg 242, Level 16, State 3, Line 2
La conversione di un tipo di dati varchar in datetime ha generato un valore non compreso nell'intervallo dei valori consentiti.


The second statement does not produce an error, but does produce the informational message:

L'impostazione della lingua è stata sostituita con Italiano.

• Running just the first one fails on most but shows an error on 2017: dbfiddle.uk/…. Do you get the same error in SSMS? – Jack Douglas Jul 5 at 9:07
• Yes the error is expected - answer updated to clarify (sorry about that) – Paul White Jul 5 at 9:13

For Postgres please add the ltree extension :) That would be great. Sometimes there are SO questions referencing it.

• I've added ltree for versions 10,11 and 12 :) – Jack Douglas Jul 18 at 9:43
• Great! Thank you! – S-Man Jul 18 at 10:02

SQLFormat has an API (and code) to clean up syntax to make it readable.

I am missing the possibility to copy the table results for the SO editor. If I simply copy the table I end up with things like that:

user_id     check_date  day     time_in     time_out    hours   status
53  2018-07-16 00:00:00+01  Monday      08:15:17    09:38:03    25.3794444444444    present
53  2018-07-17 00:00:00+01  Tuesday                 absent
53  2018-07-18 00:00:00+01  Wednesday   06:34:13    08:05:17    1.51777777777778    present


The columns and values are not really aligned together. So I always have to append a manual formatting step (e.g. converting tabs into spaces):

user_id   check_date               day         time_in    time_out   hours              status
53        2018-07-16 00:00:00+01   Monday      08:15:17   09:38:03   25.3794444444444   present
53        2018-07-17 00:00:00+01   Tuesday                                              absent
53        2018-07-18 00:00:00+01   Wednesday   06:34:13   08:05:17   1.51777777777778   present


In SQLFiddle you can chose the output between (table, plaintext, markdown). Even plaintext looks much better and can be taken for copying into SO without any addional work:

| user_id |           check_date |       day |  time_in | time_out |                hours |  status |
|---------|----------------------|-----------|----------|----------|----------------------|---------|
|      53 | 2018-07-16T00:00:00Z | Monday    | 08:15:17 | 09:38:03 |   25.379444444444445 | present |
|      53 | 2018-07-17T00:00:00Z | Tuesday   |   (null) |   (null) |               (null) |  absent |
|      53 | 2018-07-18T00:00:00Z | Wednesday | 06:34:13 | 08:05:17 |   1.5177777777777777 | present |


Edit: dbfiddle markdown syntax

> user_id | check_date             | day       | time_in  | time_out | hours               | status
> ------: | :--------------------- | :-------- | :------- | :------- | :------------------ | :------
>      53 | 2018-07-16 00:00:00+01 | Monday    | 08:15:17 | 09:38:03 | 25.3794444444444    | present
>      53 | 2018-07-17 00:00:00+01 | Tuesday   | <em>null</em>     | <em>null</em>     | <em>null</em>                | absent
>      53 | 2018-07-18 00:00:00+01 | Wednesday | 06:34:13 | 08:05:17 | 1.51777777777778    | present


As you can see, the second row is not really aligned. Furthermore you can find the <em> tags and things like that.

select 1, null;

?column? | ?column?
-------: | :-------
1 | null
`
• have you tried the 'markdown' link at the top? – Jack Douglas Oct 31 '18 at 14:41
• Yes, of course. But it creates markdown syntax which is not a really good result (appending example) – S-Man Oct 31 '18 at 14:45
• The <em> tag is the only one — and it's there to distinguish between "null" (the string) and an actual null result. I guess it would be nice if the alignment took that into account, but does it really matter for SO? Also, when I paste in the markdown directly from db<>fiddle to an SO answer it doesn't look like yours: I'll edit an example into your question. – Jack Douglas Oct 31 '18 at 15:12
• Yours is indented and missing the <pre> tags – Jack Douglas Oct 31 '18 at 15:14
• @JackDouglas Thanks for you great tool. But I still miss the feature of a plain text output. I have different use cases where I need a good text style as in the psql console. The only fiddle engine that has it integrated is SQL Fiddle (as shown above). I really don't need markdown, just simple table lines. That's one of the last reasons when I have to use SQL Fiddle instead of your great tool. I would be really appreciated and it would really really help me a lot if you could integrate such an output option as well. SQL Fiddle also offers the 3 versions. – S-Man Jan 20 at 9:08
• Is there any news on this one? Although I love your tool, but for textual representation without markdown syntax I have to use another tool which is quite frustrating. DBeaver improved its support for great text output in version 6. – S-Man Jul 18 at 8:24
• still thinking about this — you can paste the markdown into SO and then copy the text output from there (just like you can copy the "added by Jack" table directly from the output above). Does that give you the kind of text output you need (albeit with an extra step)? – Jack Douglas Jul 18 at 9:46
• Hm, yes this could be a work around... But the extra step does not feel very good imho :D – S-Man Jul 18 at 10:03