What is supported syntax of .sql files?

[mloskot]

The current README.md says:

# connect to a postgres database and run the commands contained in script.sql
$ usql pg://localhost/ -f script.sql

and

-f, --file=FILE ...          execute commands from file and exit

but I've failed to find what is the supported flavour and syntax of .sql files.

Brief testing suggests me it does not seem to me to be backend specific, but usql reads line by line executing each as an individual command.

Could anyone clarify all that, ideally, in the README.md?

[nineinchnick]

It reads files as you would type them or paste them in the interactive mode. Queries are processed to interpolate variables, but otherwise sent to the backend as-is, that is we don't have any SQL parser.

Can you give an example query if you're having issues with executing it?

[mloskot]

Can you give an example query if you're having issues with executing it?

I looked again at the issue and the first thing I noticed is the lack of handling of the SQL Server-specific GO statement. More accurately, the statement specific to sqlcmd and osql and SQL Server Management Studio. So, running some of my existing .sql scripts with usql fails, obviously:

error: sqlserver: 102: Incorrect syntax near 'GO'.

Another issue I noticed is with handling of SQL Server's DECLARE:

-- NOTE: Uses SQL Server 2016 syntax with IF EXISTS
DROP TABLE IF EXISTS [test_usql_complex]

CREATE TABLE [test_usql_complex] (
    [fid] INT NOT NULL,
    [n] INT NOT NULL,
    [x_coordinate] FLOAT NOT NULL,
    [y_coordinate] FLOAT NOT NULL
    PRIMARY KEY (fid)
);

DECLARE @rows_max INT = 7;
DECLARE @item_count INT = 0;
DECLARE @x FLOAT = 10.123;
DECLARE @y FLOAT = 5.123;
DECLARE @name VARCHAR(128);

WHILE @item_count < @rows_max
BEGIN
    SET @name = CONCAT('Road ', @item_count)
    INSERT INTO [test_usql_complex] ([fid],[n],[x_coordinate],[y_coordinate]) VALUES (ABS(@item_count),@name,@x,@y);
    SET @x = @x + 10
    SET @y = @y + 10
    SET @item_count = @item_count + 1
END;

PS1> usql -f .\sqlserver_complex.sql sqlserver://user:pass@server/database
DROP TABLE
DECLARE 1
DECLARE 1
DECLARE 1
DECLARE 1
DECLARE
error: sqlserver: 137: Must declare the scalar variable "@item_count".
error: sqlserver: 137: Must declare the scalar variable "@item_count".

[meloblerd]

I noticed this too. I can't really tell what the problem is. If I remove the semicolons I can see the statement show up in sql trace. Doesn't look like the datasset is returned back to usql though.

Anything else we can try? @nineinchnick

[kenshaw]

@meloblerd This handles scripts like psql would. which means that SQL statements are terminated either with a semicolon or with a \g. It does not recognize "GO" statements:

pg:postgres@=> select 1;
 ?column?
----------
        1
(1 row)

pg:postgres@=> select 'a string';
 ?column?
----------
 a string
(1 row)

pg:postgres@=>

As to the "flavour" of SQL that usql supports, it supports ANSI style, which means there are regular strings, numbers, etc. All of the databases that are listed have been tested and work just fine with usql.

If you are trying to use usql to script things like stored procedures on Microsoft SQL Server you may have to escape a semicolon (;) with a backslash (ie, \;):

CREATE PROCEDURE a_2_in_2_out(@param_one INTEGER, @param_two INTEGER, @return_one INTEGER OUTPUT, @return_two INTEGER OUTPUT) AS
BEGIN
  SET @return_one = @param_one\;
  SET @return_two = @param_two\;
END;

In usql, that would look like this:

ms:sa@=> CREATE PROCEDURE a_2_in_2_out(@param_one INTEGER, @param_two INTEGER, @return_one INTEGER OUTPUT, @return_two INTEGER OUTPUT) AS
ms:sa@-> BEGIN
ms:sa@->   SET @return_one = @param_one\;
ms:sa@->   SET @return_two = @param_two\;
ms:sa@-> END;
CREATE PROCEDURE
ms:sa@=>

[kenshaw]

I would rewrite your script like the following:

-- NOTE: Uses SQL Server 2016 syntax with IF EXISTS
DROP TABLE IF EXISTS [test_usql_complex];

CREATE TABLE [test_usql_complex] (
  [fid] INT NOT NULL,
  -- [n] INT NOT NULL,
  -- this was changed, since below you are inserting the @name variable
  [name] varchar(128) not null default '',
  [x_coordinate] FLOAT NOT NULL,
  [y_coordinate] FLOAT NOT NULL
  PRIMARY KEY (fid)
);

-- note we are now in a block -- note that all the semicolons in this block need to be escaped:
DECLARE @rows_max INT = 7\;
DECLARE @item_count INT = 0\;
DECLARE @x FLOAT = 10.123\;
DECLARE @y FLOAT = 5.123\;
DECLARE @name VARCHAR(128)\;

WHILE @item_count < @rows_max
BEGIN
  -- a cast was missing here
  SET @name = CONCAT('Road ', str(@item_count))
  INSERT INTO [test_usql_complex] ([fid], [name],[x_coordinate],[y_coordinate]) VALUES (ABS(@item_count), @name, @x, @y)

  SET @x = @x + 10
  SET @y = @y + 10
  SET @item_count = @item_count + 1

-- the semicolon on the END is actually required part of the begin/end syntax, and is not
-- a terminating semicolon. as such, you need two semicolons here, the escaped
-- semicolon is sent to SQL Server, and the second one is what terminates the
-- statement in usql:
END\;;

select * from [test_usql_complex];

Which has the following output:

$ usql ms:// -f x.sql
Connected with driver sqlserver (Microsoft SQL Server 16.0.4105.2, RTM, Express Edition (64-bit))
DROP TABLE
CREATE TABLE
DECLARE 39
 fid |      name       |    x_coordinate    |    y_coordinate
-----+-----------------+--------------------+--------------------
   0 | Road          0 |             10.123 |              5.123
   1 | Road          1 | 20.122999999999998 | 15.123000000000001
   2 | Road          2 | 30.122999999999998 |             25.123
   3 | Road          3 |             40.123 | 35.123000000000005
   4 | Road          4 |             50.123 | 45.123000000000005
   5 | Road          5 |             60.123 | 55.123000000000005
   6 | Road          6 |  70.12299999999999 |             65.123
(7 rows)

[meloblerd]

@kenshaw
This is what I was trying to run. It actually returns the SQL Version in a parseable format.

DECLARE @tableVar TABLE (attribute_id INT, attribute_name VARCHAR(60), attribute_value VARCHAR(255));
INSERT INTO @tableVar EXEC sp_server_info '2';
SELECT LEFT(attribute_value,25) FROM @tableVar;

I get the same error as he did error: sqlserver: 1087: Must declare the table variable "@tablevar".

This appears to be because each statement is executed in isolation.

So, I removed the semicolons besides the last one and even tried it with \g

DECLARE @tableVar TABLE (attribute_id INT, attribute_name VARCHAR(60), attribute_value VARCHAR(255))
INSERT INTO @tableVar EXEC sp_server_info '2'
SELECT LEFT(attribute_value,25) FROM @tableVar;

or

DECLARE @tableVar TABLE (attribute_id INT, attribute_name VARCHAR(60), attribute_value VARCHAR(255))
INSERT INTO @tableVar EXEC sp_server_info '2'
SELECT LEFT(attribute_value,25) FROM @tableVar
\g

As far as I can tell from the SQL Trace the full statement is being run. I can even copy it from the sql trace and run it successfully.

usql however returns DECLARE 2

So I removed the '2' and then I get DECLARE 58.

[kenshaw]

@meloblerd Ok, so usql works differently than the other tools you've used with SQL Server in the past. This is a function of how the underlying Go SQL drivers work.

Specifically, each statement that's being sent to the database is being sent as a prepared query, and usql only parses the first few words of the query to determine what to do with it, in this case it's a DECLARE, which just means it executes the query, and doesn't try to read any return data. Unfortunately, Go's standard lib supports either executing a query, or querying data. It's not able to do both

As usql is picking up DECLARE as the first statement and has no way currently of forcing a query instead of a execution, this isn't currently possible currently with usql in the way you are attempting here.

For this kind of script, I would use the native driver's command line tools. I will think about if there's a way to support this kind of compound query in usql in the future.

That said, there are many other ways to get that same information in usql:

ms:sa@=> SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition');
      1      |  2  |            3
-------------+-----+--------------------------
 16.0.4105.2 | RTM | Express Edition (64-bit)
(1 row)

[meloblerd]

I had figured it was something like that. I know there are other options, but my use case is for installers. So I want to be able to run any script without modifying it. This just happens to be one of the first scripts I had to work with.

I can't use sqlcmd because that assumes SQL is already installed. I need to run scripts on other servers before installing sql on the local machine. I guess I have to write a wrapper around the OLE DB/ODBC/ADO.NET stuff.

[kenshaw]

Is this on a Unix host? If so, there are many better ways to process this information with usql, such as using usql's variables and piping it into a shell command.

[meloblerd]

Windows in my case. I know I can work around it. Create a real table then I can insert in a second command, query the information then drop the table after.

There are just too many scripts of various complexity for that to be practical.

[kenshaw]

@meloblerd I likely don't have enough information about what you're trying to accomplish. I'd be glad to help you, if it's something within my ability to do so. Please email me directly -- kenshaw@gmail.com -- if there were more details you could share.