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

ServiceStack
41

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

42

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

43

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

44

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)

45

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
46

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}}
47

Amazing! Exactly what we needed. Thanks!

48

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

49

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

50

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: