Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • Jan 31 2019 21:30
    roji commented #2307
  • Jan 31 2019 20:35
    YohDeadfall commented #2307
  • Jan 31 2019 10:20
    capan starred npgsql/npgsql
  • Jan 31 2019 06:10
    SeanFarrow synchronize #2308
  • Jan 30 2019 20:37
    austindrenski commented #2308
  • Jan 30 2019 20:37
    pentagra commented #1445
  • Jan 30 2019 20:33
    SeanFarrow commented #2308
  • Jan 30 2019 20:31
    austindrenski commented #2308
  • Jan 30 2019 20:27
    SeanFarrow commented #2308
  • Jan 30 2019 20:09
    austindrenski labeled #2308
  • Jan 30 2019 20:05
    roji commented #1520
  • Jan 30 2019 19:50
    gyzod commented #1445
  • Jan 30 2019 19:06
    SeanFarrow commented #1520
  • Jan 30 2019 18:54
    roji commented #1520
  • Jan 30 2019 18:53
    roji commented #1520
  • Jan 30 2019 18:52
    roji commented #2050
  • Jan 30 2019 18:45
    Trolldemorted commented #2050
  • Jan 30 2019 18:21
    SeanFarrow commented #1520
  • Jan 30 2019 18:16
    SeanFarrow review_requested #2308
  • Jan 30 2019 18:16
    SeanFarrow review_requested #2308
Alberto Passalacqua
@AlbertoPa
@pm64 stackoverflow usually works (TM)
Shay Rojansky
@roji
The pgsql-help mailing list would be the PG place to go to
Stackoverflow is also generally good
pm64
@pm64
I can't deal with Stackoverflow, will try pgsql-help
Thanks @roji for the tip and for all you do
Sean
@DapperDeer
hey there, I'm getting this problem with npgsql and am very lost on how to fix it, googling doesn't really get me anywhere. I posted on StackOverflow but am impatient (and want to try multiple avenues) https://stackoverflow.com/questions/66340732/npgsql-attempted-to-read-past-the-end-of-the-stream
jongruk aripoo
@xchinjo
Hi all , How to fix "Npgsql.NpgsqlOperationInProgressException (0x80004005): A command is already in progress".
Jeremy Gamble
@GHosaPhat

Is there something I just haven't found within Npgsql that would make it possible for me to get a parsed "tree" of a SQL statement using Npgsql? Something that breaks down the statement into its individual clauses (SELECT, FROM, JOIN, WHERE, ORDER BY, etc.) with the "sub-elements" (e.g., the actual column list from the SELECT clause, or the specific conditions in the WHERE clause) exposed as well?

I'd really like to be able to parse an existing statement, modify one or more clauses (add an ORDER BY condition, alter the WHERE condition, etc.), then have it recompiled for execution so that I don't have to either rebuild the statement entirely or do some "funky" RegEx or String.Replace() calls to get the SQL I want in the end.

Admittedly, this is something of an "edge use" scenario. Using a .NET WinForms application, I'm pulling a SQL statement from a Crystal Reports .rpt document that I'm wanting to modify and push back into the document for the actual report generation. Like I said, I know I can do it manually, but I'd love to be able to do something like WHEREClause.Add("column1" = "'value'") or ORDERBYClause.Add("column2", .Ascending).
Jeremy Gamble
@GHosaPhat
I've tried working with a trial of the commercial General SQL Parser, but I've had limited success (not to mention the fact that I'm pretty sure I'm not going to get my boss to approve the cost of that library anyway) getting what I really want/need out of that in a way that's usable. I've also tried working with the Microsoft.SqlServer.Management.SqlParser.dll library, but that doesn't support PostgreSQL-specific syntax (e.g., string concatenation using ||).
Jeremy Gamble
@GHosaPhat
Other libraries I've tried seem to be either too generic or too specific (but, of course, not for PostgreSQL) to support the correct syntax for what I'm trying to accomplish. I really don't want to have to "roll my own" parser so, if I can't find a usable solution, I'll probably just "drop back and punt" and rebuild the statement instead. I just thought it'd be fantastic if a tool I'm already using could "solve" my issue. ;)
Jeremy Gamble
@GHosaPhat
If there isn't something like this exposed by Npgsql, perhaps this could serve as a feature request as a PostgreSQL "companion" to the Microsoft.SqlServer.Management.SqlParser.dll library?
Jeremy Gamble
@GHosaPhat

I'm trying to teach myself how to use ANTLR and their plsql grammar to (hopefully) get where I want to be but, as I said, it would be a lot nicer if I didn't have to go through all of that (or could, at least, eventually replace that).

I would assume that Npgsql does some sort of parsing at some level behind the scenes. I'm certainly not saying I think this request is "simple", but I would guess that there's at least a partial framework in place for some of the task.

Jeremy Gamble
@GHosaPhat

Well, after a week of trying to get ANTLR to a place I could at least do some testing, I've given up for now. If a feature like I've described eventually becomes available in Npgsql (or if it's already there and I'm just not able to find it) or, if it's available in some other library somewhere that I haven't yet found, I'd be thrilled to hear about it. In the meantime, I'm just going to have to rebuild the SQL statements I need as I go.

I've posted a question on StackOverflow to this effect (.NET Library to Parse/Modify/Reconstruct SQL Statement) but, as I stated before, I'd certainly prefer to have it as a part of the Npgsql library I'm already using for most of my database communication anyway. If anyone else here has any suggestions or thoughts, I'd be glad to hear them. Thanks.

James
@pha3z
https://www.npgsql.org/doc/prepare.html shows an example of two UPDATE statements. It explains that the individual statements are prepared. However, it doesn't clearly state if using multiple statements with a single prepare in one command is actually the best practice. What if I want to do 200 Updates or 200 Inserts? I put all 200 Updates in a single parameterized query and call Prepare() once followed by ExecuteQuery(). Is this the optimal way to do it? Would it perform differently if I created 1 single Update statement in one query, prepared it, executed.... and then put the remaining 199 in a second query without preparing it? I feel like it would help if I understood better what's happening under the hood.
Another example on the same page demonstrates Preparing a query, Executing it, then changing the parameters and Executing it again (in a loop possibly). But would be a poor practice for something like a series of UPDATES or INSERTS, right? Changing parameters and executing costs round-trip delay. Maybe some guidance on how to use prepare based on what KIND of operation you're doing would be helpful.
James
@pha3z
@GHosaPhat SQL parsing is special problem all of its own. SQL syntax varies depends on the db provider and the db provider does its own parsing. Npgsql doesn't need to concern itself with the SQL structure. Adding a parser to npgsql would add unnecessary code to maintain. Npgsql is an ado.net compliant postgres driver and nothing more. It should remain that way
Jeremy Gamble
@GHosaPhat
@pha3z Thank you for the response. While I understand this position, as I've stated, having a parse tree available from within Npgsql would make my life so much easier for the project I'm currently working on. I guess it's back to the frustration of trying to learn how to use ANTLR in a VB.NET application to accomplish my goals. Thank you again.
James
@pha3z
@GHosaPhat What do you need to do the parsing for? And is this a commercial project?
@GHosaPhat Oh nevermind. I went back and read your original problem statement. You have a SQL statement already constructed and you need to modify some clauses in it
Jeremy Gamble
@GHosaPhat
Yep. That's it. ;)
Like I said, I can do it "manually", but I was looking for something a bit more "flexible" and reusable for a long-term solution.
James
@pha3z
Is this a commercial project and is there an opportunity to outsource the work? I am a consultant. I have my own text tokenizer and my own postgres ddl parser. It would not be a big deal for me to write you a sql parser for postgres
Jeremy Gamble
@GHosaPhat
It's not a commercial project in that it isn't something that our company will be selling to others. It's for an internal system that I've been building for our employees to use.
James
@pha3z
Alright. Well if there is an opportunity for consulting to assist with the sql parsing problem, let me know. I'd be glad to help.
Jeremy Gamble
@GHosaPhat
As for outsourcing, I wish I could say yes, but my budget is basically nil. I've paid for a couple of tools myself to do some testing on stuff (didn't ask for reimbursement b/c I wasn't sure if it would help).
James
@pha3z
i understand
Jeremy Gamble
@GHosaPhat
The "good news" is that I think I'm finally starting to make a little bit of progress with ANTLR, so maybe by 2025 I'll have something... :P
James
@pha3z
lol
mibiio
@mibiio
Hello, is there any way to modify the "DefaultValueType" of a mapping? Specifically I want to change the "DefaultValueType" of the ZonedDateTime mapping to "ZonedDateTime" to be able to retrive a ZonedDateTime with Dapper - currently it always returns an Instant as this is the default "DefaultValueType" that npgsql implements.
Ankkatalo
@Ankkatalo
Good evening. I'm looking for a .NET 6 version for prototyping, I guess I have to compile it myself, but what git branch or tag should I use as a basis?
Becky Conning
@beckyconning
Hi! What is the connection string, provider name and options that i need to use npgsql? thanks!
not sure even what a "provider name" is
Becky Conning
@beckyconning
ok so providername is Npgsql
now i'm doing {[CollectionName = "Tables"]}[Data]()and getting too few arguments error, will check docs.
Shay Rojansky
@roji
@GHosaPhat (and everyone else) sorry for answering so late, I was on vacation.
No, Npgsql doesn't come with a SQL parser - its job is only to take the SQL strings you provide it and send that to PostgreSQL. Parsing SQL is quite a complicated task as you've found out, especially if you start taking into accounts database-specific dialects...
@pha3z I think we covered preparation and batching in npgsql/doc#91
Jeremy Gamble
@GHosaPhat
OMG! You mean you aren't looking at code and issues 24/7 and actually took a little time for yourself? That's unacceptable! </sarcasm>
Shay Rojansky
@roji
:)
I usually try to be very reactive, so answering this late is very annoying to me
@Ankkatalo you definitely don't need to compile .NET in order to use .NET 6 - previews are released on a monthly basis. Simply go to https://dotnet.microsoft.com/download/dotnet/6.0 and download the latest preview.
Becky Conning
@beckyconning
has anyone had experience using npgsql with power bi / m / power query? the connection seems to work but i get a table like the following and i'm not sure what to do next. what i'm expecting is a dialog that allows the user to select what tables they want.
CollectionName,NumberOfRestrictions,NumberOfIdentifierParts,Data,Function
MetaDataCollections,0,0,Table,Function
Restrictions,0,0,Table,Function
Databases,1,1,Function,Function
Tables,4,3,Function,Function
Columns,4,4,Function,Function
Views,3,3,Function,Function
Users,1,1,Function,Function
Indexes,4,4,Function,Function
IndexColumns,5,5,Function,Function
Shay Rojansky
@roji
@GHosaPhat FWIW EF Core internally uses a SQL expression tree to represent the queries as it builds them - and the PG provider extends that with PG-specific constructs and language elements. So at the end of the query pipeline you actually have a full expression tree representing the query, which is then rendered into text. However, this isn't parsing - it's constructing. If you find you need to parse a completed SQL in order to tweak it, it's obviously far better to go to the source which generated it and do your thing there. But I obviously know nothing about what you're doing and you probably can't for some reason.
@beckyconning that looks like the results for NpgsqlConnection.GetSchema... What exactly are you trying to do, which SQL query?
Becky Conning
@beckyconning
@roji i'd love to have a dialog that shows the available tables for the user to select
like the "Navigator" window that comes up for other sources
i am using the following AdoDotNet.DataSource("Npgsql", "Server=redacted;Port=5432;User Id=redacted;Password=redacted;Database=redacted")
Becky Conning
@beckyconning
are the functions' meanings and arguments detailed somewhere? for example if i call {[CollectionName = "Tables"]}[Data]() then i get a too few arguments (expecting 4) error.
Jeremy Gamble
@GHosaPhat

No, Npgsql doesn't come with a SQL parser - its job is only to take the SQL strings you provide it and send that to PostgreSQL. Parsing SQL is quite a complicated task as you've found out, especially if you start taking into accounts database-specific dialects...

I totally understand. I've kinda shelved that "side project" (parsing SQL statements) for now since I can't seem to get anything working correctly with ANTLR and I'm just having my application recreate the SQL statement completely instead of trying to manipulate its individual components.

That being said, for what I'm doing - extracting/parsing the SQL statement from a Crystal Reports document, updating one or more clauses, then push the modified SQL back into the document before the final report is generated/printed - it would still be nice to instead "simply" be able to access and modify that parse tree with the tools I'm already using (instead of banging my head against the ANTLR wall). As I said, I fully realize this is a fairly "one-off" type of usage situation in the grand scheme of things and there are other, "brute-force" methods of accomplishing the same goals.