Live Query Editting

ServiceStack #Script
1

I read about the sharp scripts live document editing, reviewed the database scripts as well as watched a few of the youtube videos you posted but I can’t seem to get a simple query to run against a local database.

I updated the web tool to .44 and this is the code for a file called test.ss running with `web run/watch test.ss:

{{ "select * from Region"
   |> to => selectSql
}}

{{selectSql}}

{{ selectSql 
   |> dbSelect({ connectionString: "Server=localhost;Initial Catalog=mydb;Integrated Security=True", provider:"mssql" })
   |> htmlDump({caption:"here"}) 
}}

It outputs the selectSql but nothing for the query.

2

Did you register the provider in your DB Factory?

dbFactory.RegisterProvider("mssql", SqlServer2017Dialect.Provider);

If this is a .ss Sharp Script you can specify the provider + connection string in the page arguments:

3

It is a stand alone .ss file (test.ss) and the sample code provides the connection string to the dbSelect which the docs indicate should work by passing in an object with the connection string (my sample code) and provider ( {{ sql |> dbSelect({ connectionString:sqlServerConnString, provider:"mssql" }) }}. Changing it to the following using page args doesn’t work either (the connection string is valid and is what is being used on another project).

I am trying to get a simple sql server .ss page to run any adhoc query.

<!--
    db sqlserver
    db.connection "Server=localhost;Initial Catalog=mydb;Integrated Security=True"
-->

```code

dbTableNamesWithRowCounts |> textDump({ caption: 'Tables' })

```

{{ "select * from Region"
   |> to => selectSql
}}


{{selectSql}}

{{ dbTableNames |> textDump({caption: "tables" })}}

{{ 
   selectSql 
   |> dbSelect
   |> htmlDump({caption:"here"}) 
   |> return
}}
4

I’ve linked and quoted the docs which shows that you need to register the provider you want to use in order to be able to use it.

But if you’re using a .ss Sharp Script then you should be using page arguments instead, also shown. Don’t add additional quotes to the string, it’s already parsed as a string. i.e. try just:

<!--
db sqlserver
db.connection Server=localhost;Initial Catalog=mydb;Integrated Security=True
-->

If it doesn’t work, please provide details of the error or full page output.

5

Here is the full script simplified:

<!--
    db sqlserver
    db.connection Server=localhost;Initial Catalog=mydb;Integrated Security=True
-->

Runnning database
```code

dbTableNamesWithRowCounts |> textDump({ caption: 'Tables' })

```
{{ continueExecutingFiltersOnError }}
{{ lastError |> typeName }}
{{ lastErrorMessage }}
{{ lastErrorStackTrace }}

Output:

Runnning database

null
6

Don’t put spaces in front of the arg names, i.e, use what I’ve shown above:

<!--
db sqlserver
db.connection Server=localhost;Initial Catalog=mydb;Integrated Security=True
-->
1 Like
7

Your note said to remove the quotations from the value but I didn’t realize the page args were sensitive to spacing. Noted and now it works. I’m looking forward to using these live queries/documents (youtube videos are great examples).

1 Like
8

To continue this thread, how do I pass in a database connection string to the script. This doesn’t seem to work:

<!--
db sqlserver
db.connection ARGV.first()
-->

Also, Is there a way to do multi-tenancy with the DBSelect. As I noted in a prior thread, it doesn’t seem to work when run as a standalone script dbSelect({connectionString: '', provider:''}). If not there it isn’t a big deal, but I think exposing the factory somehow to do registrations in the script might help.

9

Page arguments only contains static arguments, not evaluatable code.

The OrmLite Connection Factory needs to be configured with the dialect provider before the script is run, you wont be able to register the dialect provider in the script, but you can start with the connection defined in the page arguments then switch to use a different connection with the useDb script, e.g:

{ connectionString: "..." } |> useDb

After which the db* scripts will use instead.

As I noted in a prior thread, it doesn’t seem to work when run as a standalone script dbSelect({connectionString: '', provider:''}) .

As has already been mentioned in comments, including the docs that documents {connectionString,provider}, you need to register the dialect provider in order to be able to use it. i.e.

dbFactory.RegisterProvider("mssql", SqlServer2017Dialect.Provider);

Which you wont be able to do in stand-alone .ss scripts on their own, but as Sharp Scripts are run in context of Sharp Apps you can use plugins to run custom code like registering multiple dialects and named connections with the configured OrmLiteConnectionFactory.