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:
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:
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.
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.
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.
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.