Possible to change database connection at runtime in a Sharp App?

That PostgreSQL doesn’t let you query an integer column with a string parameter, you’d need an explicit cast for types where it doesn’t exist or convert the value in the string queryDictionary to be an int.

For postgres it’s easier just to add the explicit cast to the column, e.g:

#each qs.Keys where !ignore.contains(it.sqlVerifyFragment().lower())
    `${sqlQuote(it)}::text = @${it}` |> addTo => filters
/each

I’ve also added a sqlCast DbScript which will allow you to perform an RDBMS agnostic cast that should work in all supported RDBMS’s, e.g:

#each qs.Keys where !ignore.contains(it.sqlVerifyFragment().lower())
    `${sqlQuote(it).sqlCast('varchar')} = @${it}` |> addTo => filters
/each

This is available in the latest x v0.0.27 dotnet tool just published.

Thank you. Will try it out tomorrow.

Has the Nuget package been updated yet? When I do this from the context of a .NET Core app it says it can’t find sqlCast.

Yeah it is now, I thought you were using the x tool for running sharpdata scripts.

Anyway in the latest v0.0.28 of the x tool it now also supports registering multiple RDBMS connections in app.settings, e.g:

Here’s an example of configuring a main SQL Server connection + a SQLite & PostgreSQL named connection:

debug false
name Multi RDBMS test

# Configure below. Supported dialects: sqlite, mysql, postgres, sqlserver
db sqlserver
db.connection $MSSQL_CONNECTION
db.connections[northwind]  { db:sqlite, connection:'northwind.sqlite' }
db.connections[techstacks] { db:postgres, connection:$TECHSTACKS_DB }

Which you can use without needing a C# plugin by including a useDb configuration in your script, which if you put in a directory + file structure like:

_db\_table.html

{{ {namedConnection:db} |> useDb  }}

```code|quiet
var ignore = ['db','format','orderby','skip','take']
var sql = `SELECT * FROM ${sqlQuote(table)}`
var filters = []
#each qs.Keys where !ignore.contains(it.sqlVerifyFragment().lower())
    `${sqlQuote(it).sqlCast('varchar')} = @${it}` |> addTo => filters
/each
#if !filters.isEmpty()
    sql = `${sql} WHERE ${filters.join(' AND ')}`
/if

#if qs.orderBy
    sql = `${sql} ORDER BY ${sqlOrderByFields(qs.orderBy)}`
/if
#if qs.skip || qs.take
    sql = `${sql} ${sqlLimit(qs.skip,qs.take)}`
/if

sql |> dbSelect(qs.toObjectDictionary().withoutKeys(ignore)) |> return
```
{{ ifError |> show(sql) }}
{{htmlError}}

You’ll be able to call your named RDBMS’s with the optimal paths like:

/northwind/customer
/techstacks/tecnhology

These scripts can be quickly downloaded in a new folder with mix:

$ md test && cd test
$ x mix sharpdata

Awesome, works great!!!

I thought you were using the x tool for running sharpdata scripts

I was but it proved to be a little disorienting for me. I prefer the full solution development experience in Visual Studio 2019 and being able to debug the plugins, etc. So I’m going back towards running these sharp scripts within that context.

Thanks!

Looks like a Guid type value isn’t working. No error, but not returning anything.

https://localhost:5001/data/test_model/?db=platform&id=547b41344f444a70832fbda848070168

Does the sqlCast script need to be updated to handle this?

The script is only going to support string comparisons or fields that can be coerced into strings.

First find out if you can use normal SQL (e.g. in a SQL Editor like DataGrip, pgAdmin or even psql) to query that field with a string value, if you can, use that exact string in the comparison.

This works in pgAdmin with id being a uuid field

SELECT id, name
FROM public.test_model
WHERE id = '6f542c312bff4e7b8d08c70615fc64bf'

Does this?

SELECT id, name
FROM public.test_model
WHERE id::varchar = '6f542c312bff4e7b8d08c70615fc64bf'

Nope. No errors, just no results.

So you’re not using the exact string value, have a look at what SELECT id::varchar ... returns.

Ok, looks like omitting the -'s in the guid was the problem. It’s interesting that under normal circumstances (pgAdmin, OrmLite Where clause within .NET Core app, etc) omitting the -'s still produces results. I’m guessing the scripting mechanism is more sensitive to how the data is represented.

Thanks

Is there anyway to get all the AutoQuery niceties like fields, include, skip, etc to be available from within DbScripts? For example:

https://localhost:5001/data/test_model/?db=platform&fields=Name&include=Total

It’s important for your understanding that none of the behavior is impacted by #Script which just runs the generated SQL Query. This should be clear from the implementation, which is just constructing the SQL query from queryString input then executes the SQL with dbSelect and returns the result.

i.e. whatever SQL is executed is what the RDBMS will return and why you should verify the query in pgAdmin/psql so you know what values you need to provide, e.g. it’s using the varchar cast operator, so you’d need to find out the exact string value the field gets casts to.

Nope it’s a simple 16 LOC script that executes raw SQL and returns the generic resultset as-is, anything else you want it to do you’ll have to implement yourself. (skip/take is already there, also OrderBy works like AutoQuery’s, e.g. can do multiple ASC/DESC ?orderBy=A,-B,C)

Got it, thanks! Not a huge thing right now so I think we can eventually work this into our own custom script to for example, build out the columns in the select statement based on the passed in “fields” parameter. That way we’re not always pulling all data from the database.

The included OrderBy, Skip/Take is great!

1 Like

FYI fields is easy to implement:

```code|quiet
var ignore = ['db','fields','format','orderby','skip','take']
var fields = qs.fields ? qs.fields.split(',').map(x => sqlQuote(x)).join(','):'*'
var sql = `SELECT ${fields} FROM ${sqlQuote(table)}`
var filters = []
#each qs.Keys where !ignore.contains(it.sqlVerifyFragment().lower())
    `${sqlQuote(it).sqlCast('varchar')} = @${it}` |> addTo => filters
/each
#if !filters.isEmpty()
    sql = `${sql} WHERE ${filters.join(' AND ')}`
/if
#if qs.orderBy
    sql = `${sql} ORDER BY ${sqlOrderByFields(qs.orderBy)}`
/if
#if qs.skip || qs.take
    sql = `${sql} ${sqlLimit(qs.skip,qs.take)}`
/if
sql |> dbSelect(qs.toObjectDictionary().withoutKeys(ignore)) |> return
```
{{ ifError |> show(sql) }}
{{htmlError}}

Amazing! Exactly what we needed. Thanks!

You wouldn’t happen to already have an example of this for an HTTP Post and INSERT statement would you?

No this just performs SELECT queries, none of it would be useful for INSERT’s.

FYI you might be interested in the more capable SharpData script I’m using in /db/_db/_table/index.html.

I’ve also built a UI around this script which you can view from the latest Announcement Post: