|Subject||Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure|
Thank you, Mark, for clearing up my misunderstanding. 😊
When I found some sample code on StackOverflow that demonstrated the use of the "FOR-DO" construct, I realized what I had misunderstood.
I believe I sent you an email that suggested my correction to my understanding of this construct.
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.
I am finding the Firebird Engine to be the most quixotic engine I have worked with to date but after seeing that Microsoft's SQL Server LocalDB Engine was not really suited for embedded database applications (though I wrote a complete installer with the use of the small system API to make the installation as user-friendly as possible), I decided to delve into Firebird with the determination to finally understand its differences when compared to the other engines I have worked with.
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.
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 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.
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.
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.
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 have always believed that these types of issues have kept Firebird from being realized as a top-contender in the database world. It is based upon an excellent technology foundation and should be exposed more generally for the excellent and highly efficient engine that it really is...