On 2018-05-04 20:10, blackfalconsoftw...@outlook.com [firebird-support] wrote: > I must admit that I have spent many years working with various > database engines, with SQL Server being the engine where the large > majority of my professional development had been emphasized. > Nonetheless, I have worked with Oracle, Sybase, MySQL, and a little > PostgreSQL and SQLite.
In my experience, each and every database engine has their oddities :) [..] > I believe I am finally getting the hang of it. However, one of the > areas that I am finding very confusing is the inconsistencies between > using raw PSQL code in a DB Manager and using it with the Firebird > ADO.NET Provider that one of your project's third-party contributors > provides. PSQL (or procedureal SQL) is specifically the syntax you use in stored procedures, execute block, stored functions and triggers. It is not the syntax that you use when executing queries in your database manager or the Firebird ADO.net driver. That syntax is called DSQL (dynamic SQL). This distinction is important, because some things you can do in PSQL are not possible in DSQL (and vice versa). > For example, if I want to run a "selectable" stored procedure in my DB > Manager, I enter the following for example... > > SELECT * FROM MY_PROCEUDRE_NAME (PARAM) > > However, if you do the same using my own data-access-layer, the > provider yields a parameter mismatch error. There is a very specific > reason for this. I'm not entirely sure what you mean with this. > I have written data access layers for a number of database engines, > including Oracle and PostgreSQL, along with Firebird, which was the > most recent layer I released. In all of layers when SQL code is > passed to a query method, I validate for whether there is a "SELECT" > statement present. If so, than the layer determines that is what is > being passed is raw SQL code. This is because in all cases, procedure > names are simply passed with the name and not with any preceding > "SELECT * FROM" clause. Are you saying your code is trying to parse SQL within your own application? Doing so will likely reject a lot of valid SQL (for example, I wonder what happens if a query including a CTE is passed through that, or something like an execute block). > However, with Firebird's documentation (and the lack thereof for the > ADO.NET provider) developers like myself who are new to Firebird would > naturally assume that the "SELECT * FROM" clause should be included in > that part of the query-string that is passed with the procedure name > to Firebird's ADO.NET Provider, which with my data access layer sees > the query-string as raw SQL code. As far as I'm aware (but haven't verified) the ADO.net driver allows you to pass the stored procedure name, or EXECUTE PROCEDURE procedurename(params) (for an executable procedure) or SELECT * FROM procedurename(params) for a selectable one. > However, after testing the method where the "parameter mismatch" error > was occurring without the "SELECT * FROM" clause, my data access layer > saw the query-string as a stored procedure and ran as expected with > the correct results being returned. Without a specific reproduction case I find it hard to understand what you mean, but it may have to do with the specific handling of the ADO.net driver. You may want to consider posting a question on the mailing list for the ADO.net provider. > I am hoping to begin writing a technical article soon that will > introduce database application developers to these idiosyncrasies who > are new to Firebird, making their initial forays into using this > database engine less frustrating. I'm looking forward to read it! Mark
