A Simple JSON Example

Question: How do I dump one row to a json file?

Answer: Here's an adhoc query that writes all the Foxhound 5 Monitor Options values for one target database to a json text file:

-- Start - Foxhound5 - 2 Adhoc Query via ISQL
UNLOAD
SELECT REPLACE ( json_raw_table.json_raw_string, ',"', ',\X0D\X0A"' ) AS monitor_options_json
  FROM ( SELECT *
           FROM alerts_criteria
          WHERE alerts_criteria.criteria_set_type = 'Target Specific'
            AND alerts_criteria.sampling_id       = 13 -- Inventory17
            FOR JSON RAW
       ) AS json_raw_table ( json_raw_string ) 
TO 'C:\\temp\\monitor_options.json'
   DELIMITED BY '' 
   ESCAPES OFF 
   HEXADECIMAL OFF 
   QUOTES OFF;

[{"criteria_set_type":"Target Specific",
"sampling_id":13,
"send_alert_emails":"N",
"mail_recipient":"",
"use_html_in_emails":"Y",
"attach_files_to_emails":"Y",
"email_url_host_port":"localhost",
"display_alerts":"Y",
"autodrop_mail_recipients":"",
"autodrop_mail_recipients_same_as_for_alerts":"Y",
"previous_sample_set_number_processed":3935794,
"previous_gathered_sample_finished_at":"2020-12-10 08:28:08.609",
"current_sample_finished_at":"2020-12-10 08:28:08.609",
"alert1_enabled":"Y",
"alert1_is_in_effect":"N",
"alert1_in_effect_at":"2020-12-07 11:13:00.567",
"alert1_all_clear_at":"2020-12-08 07:48:11.438",
"alert1_threshold_lost_sample_accumulated_msec":60000,
"alert1_actual_lost_sample_accumulated_msec":0,
...

The docs are light on simple questions like this, perhaps because they lead to more simple questions...

How do I get SELECT FOR JSON output into a string?

Answer: Use a SELECT INTO clause, or a subquery.

How do I actually code SELECT FOR JSON in a subquery?

Answer: Code a derived table with an alias table name and a single alias column name, because the select list for SELECT * FOR JSON consists of one single column.

How do I get line breaks?

Answer: Use REPLACE, and hope your data doesn't contain any comma-doublequote pairs... or write better code than this example :)

Foxhound 5 Giveth, Foxhound 5 Taketh Away

New Feature

The ADHOC user id can create procedures, functions, tables, indexes and views

If you like running adhoc queries on the Foxhound database, you'll love being able to CREATE your own procedures... and tables, and views, and functions.

For more information see What kind of adhoc queries can I write?

But . . .

Behavior Change: The ADHOC user id cannot be used in SQL Central to connect to the Foxhound database

You can use ISQL but not SQL Central; that door has been closed.

So . . .

Windows Start menu - Foxhound5 - 2 Adhoc Query via ISQL is your friend :)

---

Foxhound Shock! (Server Messages)

There's a new feature in Foxhound 5 that may come as a shock:

SQL Anywhere server messages are stored and displayed on the Monitor and History pages.

First, some background: Server messages appear in the SQL Anywhere console log, and in the dbsrv17.exe -o text file if you specify that option (you should).

Server messages are mostly boring "Information" messages

I. Database "ddd16" (ddd16.db) started at Wed Dec 02 2020 07:48
I. Finished checkpoint of "ddd16" (ddd16.db) at Wed Dec 02 2020 07:48

Some server messages are interesting

I. Database "ddd" mirroring:  becoming primary server

and some are very interesting

W. Database "ddd" mirroring:  Warning: Mirror is running behind the primary server by more than 60 seconds.
E. Database "ddd" mirroring:  mirror connection to server "partner2_server" failed

Foxhound 5 displays server messages as a convenience:

If you're going to the trouble of running Foxhound, why should you have to look somewhere else to see server messages?

But . . . server messages can be a bit of a surprise

. . . when you suddenly start seeing server messages in the Monitor page after upgrading Foxhound 5:

They can also be a SHOCK

If your application code or stored procedures contain MESSAGE TO CONSOLE statements,

• and you are not in the habit of looking at the SQL Anywhere console log,

• or you don't know where (or what) the SQL Anywhere console log is,

suddenly seeing a storm of MESSAGE TO CONSOLE output in the Foxhound Monitor page can be a shock.

Relax, you can turn off the storm

Just click on the little black gear (*) icon on one of the messages, and it'll take you directly to the Monitor Options page

In section 18. Server Messages, make sure you've selected the right target database,

• then uncheck Display server messages on the Foxhound Monitor and Sample History pages

• and click on Save.

But, maybe you don't want to

Server messages might be your friend :)

---