Re: [firebird-support] Re: Cannot delete Firebird database file as it is in use by the application..
Thank you very much for all your replies. I will try the "Clear Pools" option with the Firebird ADO.NET provider first. If that doesn't work, I have come up with the idea to start a small, external process that will check for the release of the file by the application and then delete it... Thank you again... Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com
[firebird-support] Re: Cannot delete Firebird database file as it is in use by the application..
Hello... I am developing a security extension, which is used as a loaded assembly to my main application, all of which use the Firebird Embedded Edition 2.59 for my application's database. As part of my security processes, I compress the file upon exiting the application. When the compression process is completed, I want to delete the Firebird FDB database file. However, I cannot do this as the Firebird database file is in use by the application. I have checked all of my data access coding and in every case the associated database connection is being properly closed upon completion of any database access method. As a result, I have no idea why the Firebird FDB database file is still being used by the master application. Does anyone have any ideas as to how I can get around this so I can complete the delete process? Thank you... Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com
Re: [firebird-support] Re: Securing Firebird Embedded database
Elmar... My current concept for making a Firebird Embedded Edition somewhat secure without a password (and as you say, using password means little if people already know a database;s internal structure) is to offer two levels of encryption. The first would be using DotNetZip to compress and encrypt the Firebird database file into a zip file with a pass word. The next level of encryption would be to take the zip file and encrypt it again using standard file encryption technologies (ie: AES) Each set of compression\encryption processes would use a different internally generated key every time the process is initiated. This may not be a perfect solution but I have it working to a point where the response time of these processes are acceptable on a Firebird database file of 1.5 gigs. However, it will be up to the user to set what they want through my application's security options, which will provide the option to do one or both security processes upon opening and closing the application or upon demand. My concept is distantly related to your own Encryption schemes in Firebird 3.0 where your security is supported by third-party plugins instead of using a singular security scheme built in to the database engine. Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com [cid:8036d6f0-36cc-4285-90d6-ba5982a222e5] From: firebird-support@yahoogroups.com on behalf of Elmar Haneke el...@haneke.de [firebird-support] Sent: Friday, December 20, 2019 4:35 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: Securing Firebird Embedded database > All other database engines that I have worked with provide password > protection, even SQLite, which is used primarily for desktop and device > applications. I'm not familiar which the technique SQLite uses. But either they are using the password to encrypt database or it is an fake protection and anyone can use an modified SQLite to access that database which is simply ignoring password. > I agree that the best way to protect any such database file is either through > internalized encryption, which I believe is now offered with Firebird 3.xx or > complete file encryption. However, would it not be easy enough for the > Firebird Development Group to simply implement the security constructs for > the embedded edition as it is for its server-side siblings given that all > such editions are primarily the same? Even the "server side security" is meaningless once the illegitimate user does have direct access to database file. Your idea sounds like the "security by obscurity" approach used in some closed-source systems as e.g. MS-Access - ask google how to remove that password protection. That approach is worth nothing in an open source context. Elmar [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Securing Firebird Embedded database
Mark... I am curious regarding your suggestion to use Firebird Server instead of the embedded edition. Is there a way to silently install the Firebird Server without any user intervention during my application's installation? Thank you... Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com [cid:075acb9c-c9a9-4a54-a258-c1892b39d161] From: firebird-support@yahoogroups.com on behalf of Mark Rotteveel m...@lawinegevaar.nl [firebird-support] Sent: Thursday, December 19, 2019 9:37 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: Securing Firebird Embedded database On 2019-12-18 16:30, Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support] wrote: > Is there any way to secure the FDB file from unauthorized access > (other than compression and encryption, which I am starting to > implement in my project) and if not, is there expected to be at least > password protection for the Embedded edition in Firebird 4.0? If you don't want a user to access a database, then use file system permission to prevent the user from opening the database. Otherwise, you should use Firebird server instead. Firebird 4 Embedded works the same as Firebird 3 Embedded, so there won't be password protection for Embedded in that version. Mark [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Securing Firebird Embedded database
Elmar... Thank you for your reply regarding protecting a Firebird Embedded Database. However, why is it assumed that a database file that is stored locally cannot be breached by unauthorized access? Given the nature of sophisticated security breaches and hacking endeavors today, such data files can be breached by determined specialists even if they are not on site. All other database engines that I have worked with provide password protection, even SQLite, which is used primarily for desktop and device applications. I agree that the best way to protect any such database file is either through internalized encryption, which I believe is now offered with Firebird 3.xx or complete file encryption. However, would it not be easy enough for the Firebird Development Group to simply implement the security constructs for the embedded edition as it is for its server-side siblings given that all such editions are primarily the same? In any event, thank you once again for your quick reply... Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com [cid:5dd563dd-e1c8-41ff-8c06-9187c92572a5] From: firebird-support@yahoogroups.com on behalf of Elmar Haneke el...@haneke.de [firebird-support] Sent: Thursday, December 19, 2019 4:15 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: Securing Firebird Embedded database > Is there any way to secure the FDB file from unauthorized access (other than > compression and encryption, which I am starting to implement in my project) > and if not, is there expected to be at least password protection for the > Embedded edition in Firebird 4.0? Password protection does assume the database file stored out of reach of illegitimate users. Legitimate server admin can always access database. Encryption is the only method to make illegitimate access to database content more difficult - its not really impossible if you have to store encryption keys within your application. Elmar [Non-text portions of this message have been removed]
[firebird-support] Re: Securing Firebird Embedded database
Hello... Is there any way to secure the FDB file from unauthorized access (other than compression and encryption, which I am starting to implement in my project) and if not, is there expected to be at least password protection for the Embedded edition in Firebird 4.0? Thank you... Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com [cid:2af36072-676f-4dce-8ef9-3036bf3aadf5] [Non-text portions of this message have been removed]
Re: [firebird-support] oracle numeric conversion (22).
Hi Dimitry... Is it that we can no longer reply to messages on the Firebird Yahoo Group? I went there to ask about FB 4.0 and I could see all the messages but there were no reply options. As to what I was trying to respond to, you note in your message below that you would recommend FB 4.0. However, that is not available yet in any version, other than beta, correct? Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com [cid:4751017d-f896-4ea3-bd6d-9a8b36145406] From: firebird-support@yahoogroups.com on behalf of Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] Sent: Tuesday, December 10, 2019 10:44 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] oracle numeric conversion (22). 10.12.2019 16:40, Norbert Saint Georges n...@tetrasys.eu [firebird-support] wrote: > what do you recommend for conversion? Numeric and FB 4. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links [Non-text portions of this message have been removed]
[firebird-support] Re: A recommendation (database migration)
Kevin... EMS Sql Manager has a great list of tools for Firebird and Interbase that will provide the very processes you are looking for... https://www.sqlmanager.net https://www.sqlmanager.net Granted these are commercial products but I have been using them for years and have found them to be quite reliable with decent technical support as well. However, if you are interested in developing such a tool, maybe we could collaborate. I work with the Microsoft development products and primarily develop for the desktop in VB.NET but I am fluent in C#. I haven't done any web development in years and prefer to stay away from it given the complete mess such development has become... With regards, Steve Naidamast Sr. Software Engineer
Re: [firebird-support] Re: How to index this table
Karol... My misunderstanding I should apologize to you... I misunderstood you when you said, "Firebird cannot use composite index here"... I thought you may have been trying to suggest them to the person who opened the thread but couldn't with the way his SQL query had been coded... Steve Naidamast Sr. Software Engineer
[firebird-support] Re: How to index this table
I was going the recommend the same join constructs as Karol did. However, I am not sure why Karol suggested the construct of composite indicies in his reply as I thought you were going to create 2 additional indices, one for each of the fields in the multiple or clauses. As a result, this would not define a composite index unless there is something internally that Firebird would do that I am not aware of In any case, if your query is currently working in terms of returning the correct data, the multiple and\or constructs are forcing the query to internally generate multiple if\select clauses, which will always add to the time for a query to complete. However, I found an article for designing indices when aggregate functions are used in a query. Though not related to the Firebird database engine, you may find it useful in terms of providing some insights to what you may elect to do to refine your query for faster speed... >>> See article at the link below https://stackoverflow.com/questions/50863962/speeding-up-queries-that-use-aggregate-functions-by-using-indexes https://stackoverflow.com/questions/50863962/speeding-up-queries-that-use-aggregate-functions-by-using-indexes <<< Though JOINS are historically rather inefficient ways to create queries (except for a LEFT INNER JOIN), in the case you provided, they may actually assist in speeding up the query as Karol suggested as a result of your multiple and\or clauses. Another point to consider is that I set up your query in a version 3.0.3 embedded database on my workstation along with the table definition you provided. I did this to see what the plan analyzer would provide. In this case it provided an interim SORT stage (as many such queries would), which will add significantly to your query when going through as many records as your table contains. However, the Firebird analyzer that my EMS Manager uses does not appear to be as detailed as that of other database engines I have used in the past. Steve Naidamast Sr. Software Engineer
[firebird-support] Re: What is the optimum pageSize?
Doesn't the page size definition as per a database depend on one designs their database tables and how such tables will be used? In any event, here is what the Developer Guide for version 3.0 of Firebird states... The actively supported versions of Firebird support the following page sizes: 4096, 8192 and 16384. The page size of 8192 is good for most cases. This is rather ambiguous for most database developers looking to optimize their databases. So a link to the IB-Experts site provides more guidelines on selecting database, page sizes. The information is somewhat dated as to the references to the earlier Firebird versions but the design information is credible as it relaters fairly standardized design paradigms in the industry... https://www.ibexpert.net/ibe/index.php?n=Doc.PageSize https://www.ibexpert.net/ibe/index.php?n=Doc.PageSize Steve Naidamast Sr. Software Engineer
Re: [firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
I found what we have been arguing over in the Firebird 2.5 Language Reference Manual since I don't seem to have the one for version 3.0. I looked for it at the Firebird site but it does not appear to be listed... >>> CHAR DATA TYPE (CHAR(n), CHARACTER(n)) --- CHAR is a fixed-length data type. If the entered number of characters is less than the declared length, trailing spaces will be added to the field. Generally, the pad character does not have to be a space: it depends on the character set, For example, the pad character for the OCTETS character set is zero. The full name of this data type is CHARACTER, but there is no requirement to use full names and people rarely do so. Fixed-length character data can be used to store codes whose length is standard and has a definite “width” in directories. An example of such a code is an EAN13 barcode—13 characters, all filled. However, the PDF language manual I have for version 2.5 does in fact state the following... "A fixed-length character data type. When its data is displayed, trailing spaces are added to the string up to the specified length. Trailing spaces are not stored in the database but are restored to match the defined length when the column is displayed on the client side. Network traffic is reduced by not sending spaces over the LAN. If the number of characters is not specified, 1 is used by default." But the above statement puts such descriptions at odds with each other based upon where one looks for such information. VARCHAR DATA TYPE (VARCHAR(n), CHAR VARYING, CHARACTER VARYING) --- VARCHAR is the basic string type for storing texts of variable length, up to a maximum of 32,765 bytes. The stored structure is equal to the actual size of the data plus 2 bytes where the length of the data is recorded. All characters that are sent from the client application to the database are considered meaningful, including the leading and trailing spaces. However, trailing spaces are not stored: they will be restored upon retrieval, up to the recorded length of the string. The full name of this type is CHARACTER VARYING. Another variant of the name is written as CHAR VARYING. <<< This is the information I have been going by in addition to the link that sent me to the IB-Experts web page of the same. If the CHAR data type is not being stored with the additional padded characters but only done so in memory as Ann Harrison has stated than I have so far only found this information to be at odds with other information I have found regarding this data type. Now what is being said is that internally, a CHAR data type no longer pads its field to the defined length at the point of creation. In this case then, the field would then always be expanded to the length required of updated data to the maximum defined at field definition time. VARCHAR data on the other hand will act in the same manner except to use its length-info bytes to base its updated storage field size on. So when did this change? Is this a new difference between Firebird 2.5 and 3.x.x? Here is a link to a detailed description as to how the major databases store CHAR and VARCHAR data internally. With the exception of SQL Server, which specifies that ANSI_PADDING has to be set to "ON" to store padded spaces with CHAR data types (which, to my knowledge, is how I mostly worked with this database engine), all of the listed database engines (with the exception of SQLite) say about the same thing for such storage as I have been stating here. For SQLite, this would be expected of since it is a not a strongly typed database engine. If in fact there was a significant change to how the Firebird engine stores such CHAR than it would be mirroring with SQLite and not the other major database engines available. I have worked with MS SQL Server, Oracle, Sybase, MySQL, PostgreSQL (Ingress), and SQLite. However, I did not like using the latter engine and is why I have chosen Firebird for all my current development efforts. Steve Naidamast Sr. Software Engineer
Re: [firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
The links provided to me had the information on the IB-Experts web site... If there information is incorrect, you should contact them to have it corrected... Steve Naidamast Sr. Software Engineer
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
What do you mean I am wrong? I have merely re-iterated how all database engines I have worked operate. And what you are saying is that a CHAR field in Firebird acts in the same manner as that of a VARCHAR field. If that were the case there would be no difference in how they are used and no reason to have two different data types. Historically, in database development CHAR fields were most often used for small string fields that would most often have a predtermined length such as a unit code whereas VARCHAR fields were used for data where that size at update was not generally known but would not exceed an expected maximum. Second, if you as Human, can observer the effects of processes being performed in milliseconds than you must have some level of magic on your side to be able to observe such differences. CHAR fields have always been known to be more efficient since there is no calculations required to extend or reduce the size of the CHAR field. This efficiency is minuscule in retrospect but it is still how most CHAR field definitions work as far as I know. However, there is a law of diminishing returns with such efficiencies if the data being stored in such a field tends to be far smaller then the defined length. In this latter case, then the field would be taking up unneeded space within a table row. VARCHAR fields may have defined lengths but are dynamic up to the defined length of the field. And I was not telling Ann anything different. This information is also within the links that were provided to me regarding the explanation between CHAR and VARCHAR fields in the Firebird developer guide...
[firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
Here is the link to the definition of CHAR and VARCHAR field definitions from the IB-Experts site... https://www.ibexpert.net/ibe/index.php?n=Doc.CHAR https://www.ibexpert.net/ibe/index.php?n=Doc.CHAR It defines the storing of CHAR and VARCHAR in the same manner, though the VARCHAR data type is provided the extra two bytes for actual length information. In neither the CHAR or VARCHAR definitions is it noted that unused space is filled with nulls or blank data as both are stored as variable length fields internally within a table. However, the CHAR data type, as would be expected, can only store as a maximum, the number of characters that its original definition set forth. The VARCHAR field can hold up to over 32,000+ bytes of data. Thus Firebird, does in fact store VARCHAR data within its tables as all other database engines do, Its only deviation is that the CHAR field definition is made variable, similar to a VARCHAR field but only up to the maximum length initially defined. So my understanding of how the VARCHAR field works is correct and what I have stated earlier. Anne Harrison's comments also mirror what this link provides as when either a CHAR or VARCHAR field is placed in memory than the full lengths of each field definition type is provided space for. The reading suggestions you recommended are what provided the link above to the IB-Experts site... Steve Naidamast Sr. Software Engineer
[firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
Your statement, which may be true, does not sit well with a previous statement that states that a VARCHAR field of 1000 characters is stored in the table with 1000 characters. Your statement is suggesting a null length (until the field is updated) with two bytes for an actual length, which is how VARCHAR fields work in all other databases to my knowledge. Otherwise, to follow the previous statement, a Firebird table could have a VARCHAR field for 1000 characters, be stored as such with the initial storage-info bytes holding the actual length, which would be 1000. When the field is updated to lets say 20 characters of data, the field would still have an actual size of 1000 characters but the storage-info would be 20. How does this make any sense? Steve Naidamast Sr. Software Engineer
[firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
Though I cannot dispute what you are saying, to me it does not make much sense. If a VARCHAR field is defined for 1000 characters and it is stored in the table at a 1000 character length than there is no purpose to an actual VARCHAR field definition. VARCHAR fields have always been dynamic in other database engines and what you are saying appears to have Firebird be the only database engine to not support dynamically allocated VARCHAR fields... Steve Naidamast Sr. Software Engineer
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
Thank you Anne, for your succinct comments regarding the differences between the efficiencies of a CHAR and VARCHAR field definitions. Human observation cannot actually elicit any observable effects between the two. However, internally, the CHAR field definition is more efficient as the database does not have to perform any field size calculations at the moment that data is being updated to a VARCHAR field. Again, this is not noticeable to a user unless the data that is being updated into a VARCHAR field is quite large. Steve Naidamast Sr. Software Engineer
[firebird-support] Re: Off-Topic: Firebird future
I do not like using Java applications on my workstation, mostly as a result of the extra clutter they bring in addition to the enormous number of Microsoft files I already have installed. That being said, I do agree with the commenters in this thread where the Firebird Development Group should at this point consider offering a bundled Firebird Database Manager with their engine. "Red Expert", from what I have seen of it so far, looks to be a very competent database manager along with DB-Beaver. However, until the Firebird Development Group enhances the engine's compilation error reporting, which in my view is very poor, no such manager will be able to provide superior levels of comfort for database developers using the Firebird Database Engine. For now, I will stay with EMS Sql Manager's Firebird Database Administrator for my primary manager as I have been using their tools for quite a number of years, all of which have limited, free versions available. Steve Naidamast Sr. Software Engineer
[firebird-support] Re: Storing a byte array in Firebird
I have found what I needed. Sorry for the bother... I was having trouble finding a way top convert the array into a string, which led me to believe I would have to store it in array format. I eventually found the correct solution after going through several incorrect ones. I couldn't remember that all I needed was to use the Base64 conversion... Steve Naidamast Sr. Software Engineer
[firebird-support] Storing a byte array in Firebird
Hello... I need to store a byte array for the security aspect of the application I am developing. Would you simply store it in a field defined with the BLOB type or use the ARRAY type if one knew the length of the array? Thank you... Steve Naidamast Sr. Software engineer
[firebird-support] Re: Will we move when yahoo Groups close theirs doors?
I never use anything by Google as I do not like how they handle their user data so cavalierly. In any event, If moving the Yahoo mail data to a Google Groups platform would make it easy for the development team than I would see this as an appropriate solution. However for the type of technical support and discussion that is going on within this area, I would prefer to see a technical forum implemented where it is easier to catalog and search through various topic threads for answers to questions that have posted solutions while allowing everyone to initiate new threads of discussion for new issues that arise. ProBoards has a free version of their forums, which looks quite good for this type of discussion... Just my 2cents... Steve Naidamast Sr. Software Engineer
Re: [firebird-support] Will we move when yahoo Groups close theirs doors?
Why not simply use a forum implementation, which is a better technology to use for such a form of support than that of a mailing list? Steve Naidamast Sr. Software Engineer
Re: [firebird-support] Off-Topic: Firebird future
Hi Alexey... I currently use the EMS SqlManager software for all my Firebird database administration I have been using their software for many years for the SQL Server, MySQL, and PostgreSQL databases with very few issues. However, I do have an issue with the Firebird SQL error reporting system in that it still does not narrow down the actual issue when an error is found during compilation of any Firebird SQL code (ie: stored procedures). Nonetheless, offering a complete DB Administrator for Firebird with the freely available downloadable product would go a long way to gaining Firebird increased popularity. Though I see Firebird as an excellent database engine on par with all its equivalent engines in the commercial and Open Source sectors, I have been critical in the way that the development team has remained with the Open Source style of marketing that was prevalent in the 1990s when Open Source was just beginning to emerge. Without more modern mechanisms to interact with the Firebird Community and a lack of professional, subscription based support for companies, Firebird has remained in the backwaters of the technical communities, which in my view is wholly unwarranted given the excellence of this engine. This reticence on the development team's part may be based on legitimate issues (ie: personal work schedules). Nonetheless, in reality, how difficult would it be for example to set up a Firebird Forum on one of the freely available forum hosting services such as ProBoards? Such a technology implementation is fairly standard now with all of the database vendors. Finally, as I have also written one or two articles on the subject that Firebird is not all that friendly to newcomers to this engine, even technical; professionals with years of experience as I had. The way in which Firebird implements its SQL is rather alien when compared to the other major database engines. This is not in anyway to be critical of this implementation but documentation should be available for professionals (and hobbyists) who would like to migrate their databases to Firebird and need to understand the differences between the SQL implementations they have become used to and the way Firebird does it. For example, my article at the following link will describe some of the issues that people new to Firebird will encounter... https://blackfalconsoftware.wordpress.com/2018/05/11/the-firebird-database-engine-the-frustrations-of-the-long-distance-database-application-developer/ https://blackfalconsoftware.wordpress.com/2018/05/11/the-firebird-database-engine-the-frustrations-of-the-long-distance-database-application-developer/ In closing, I believe that any enhancement to the way that this database engine is promoted, such as a freely available and complete database administration tool, would go a long way to securing Firebird as mainstay engine in the future... Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com
[firebird-support] Re: Install a DB Firebird on a web site
Antonio... I believe you are asking how you install a Firebird Database Engine on a "hosted web site" by one of the many providers available to developers on the Internet. If this is the case, than you will not find many that support the Firebird Database intrinsically whereas most support either SQL Server and\or MySQL. In fact, a casual search for such providers yielded very little for this type of support. Unfortunately, Firebird is not as popular an Open Source alternative as MySQL or PostgreSQL. As a result, you may have to build your hosted web site with an external access to a Firebird Database Server, which the provider you choose may assist in your implementation. If however, you are asking how to load a Firebird Database to a web site, in essence as part of a web application that is being hosted, than Mark is correct in his reply in that you do not load a database engine to a web site. Normally in well defined distributed systems, the database is implemented on a completely separate, physical tier. However, since administrators have found it easier to simply add a database engine on to the same server that is hosting web site processes, you now normally find what would normally be an application server also running a database engine. Thus for example an application server would have IIS supporting the running of ASP.NET web applications while also have a database engine installed on the same machine. If you could clarify what you are seeking to do, it would help you in getting a more concise answer to your original question... Steve Naidamast Sr. Software Engineer
[firebird-support] Re: New Firebird project/OpenEMR/Wind8.1/asgalib20/#201902a
Mark is correct in his previous reply as I went through some of the documentation for the Open EMR System. The OpenEMR system makes use of the MySQL database engine, not Firebird. You can look at the following two links for installation guidance for this system on windows... https://www.open-emr.org/wiki/index.php/OpenEMR_5.0.1_XAMPP_Package_Installation https://www.open-emr.org/wiki/index.php/OpenEMR_5.0.1_XAMPP_Package_Installation https://www.open-emr.org/wiki/index.php/OpenEMR_5.0.1_Windows_Installation https://www.open-emr.org/wiki/index.php/OpenEMR_5.0.1_Windows_Installation However, you should be asking your questions on that system's community forums, not here. Steve Naidamast Sr. Software Engineer
Re: ODP: [firebird-support] Query optimization on FB3
Hi Helen... Thank you for clearing this up for me. However, could you clarify what you mean between a session or a transaction? Steve Naidamast Sr. Software Engineer
Re: ODP: [firebird-support] Query optimization on FB3
By using a global table in Firebird, could not multiple users cause a conflict if two such users were to issue the same query against the global table? Coming from a SQL Server background, I am used to using local temporary tables, which are isolated on a query by query basis... Steve Naidamast Sr. Software Engineer
[firebird-support] Re: Built in RegEx Capability?
I believe this has been done with such database engines as SQL Server and PostgreSQL. However, the Firebird Database Engine has a stricter standard as to what is implemented in the SQL language than other such engines. Nonetheless, it appears that there is some similar capabilities as the following link describes... https://stackoverflow.com/questions/21432507/repetitions-with-regexes-in-firebird https://stackoverflow.com/questions/21432507/repetitions-with-regexes-in-firebird It may be more convenient to pull out data via a Regex expression in SQL code but it is rather redundant since all of the major development languages already support extensive Regex pattern matching capabilities... Steve Naidamast Sr. Software Engineer
[firebird-support] Re: Introducing Firebird Butler
Just a question about the use of ZeroMQ... Has anyone considered using ZeroC's Ice Framework? It appears to be a much more finished product with better diocumentation than ZeroMQ and is completely Open Source... Steve Naidamast Sr. Software Engineer
[firebird-support] Re: Introducing Firebird Butler
I have two comments on this thread... I... The examples provided (I & II) do not necessarily show a correct pattern of either. In the first example, the assumption can be made that the description is unique and thus requires a unique example. The second is merely a reverse of the first, as a unique example requires a unique description. Where the second pattern example fails is when you can have multiple examples of the same concept, thus requiring basically a single description. For example here are two examples of adding to an integer variable in C#... CIntVar = CIntVar + 1 CIntVar++ Both examples would only need a single description, which describes that there are several ways to increment an integer variable in C#; the first using plus(+) operator, the second using the plus-plus(++) operator. II... My understanding of the Firebird Butler project could be two-fold... A specification for best practices for developing distributed systems using the Firebird Database Engine A set of enterprise tools to implement such systems (ie: an equivalent to Windows Communication Foundtaion: hence the attribution to ZeroMQ Just my thoughts... Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com
[firebird-support] Re: Altering views
Hi Daniel... I have done extensive database development in a very long career in the field. As a result, I have learned the following axioms... 1... If you are using a localized database (ie: Firebird Embedded) it really doesn't matter where you define your SQL code since there is no chance of sending bad data attached to a SQL string. 2... If you are using a multi-user database (ie: Firebird Server) than it is always best to create Stored-Procedures and Stored-Views for your SQL, which are more difficult to attack by external entities. This being said, if you use Stored-Procedures and Stored-Views with an Embedded Firebird Engine than you will have no problems in moving them into a Firebird Database Server since the core kernels are primarily the same. A note on your Views being updateable... This is often not a good idea as Views tend to be used by end-users so you would want maximum control over what they are allowed to do. Thus Stored-Procedures should always be used to perform updates, which always have some level of control over them within an application. Steve Naidamast Sr. Software Engineer
[firebird-support] Re: very slow firebird embedded connection on a web application
May I ask why you are using the embedded version of Firebird for a web application? This version of Firebird does not have the optimization advantages of the server version, even if it is using the same core kernel... Steve Naidamast Sr. Software Engineer
Re: [firebird-support] Working with SQL (might be using loop)
These type of string operations are better done at the client side in C#, VB.NET, Java, or any other high level language. Using any variant of SQL to do such tasks would be quite onerous. However, if this is a homework assignment and you need some assistance, please let me know and I would be glad to write up some tips on what SQL operations should be considered. Steve Naidamast Sr. Software Engineer
[firebird-support] Re: Cannot create new database: "Can not access lock files directory /tmp/firebird/"
Karen... For configuration issues such as the one you described, you may want to purchase a copy ofthe Firebird Migration Guide at the link below... https://www.firebirdnews.org/migration-guide-to-firebird-3/ https://www.firebirdnews.org/migration-guide-to-firebird-3/ Though it is titled as a migration guide it covers a lot more than just moving databases from Firebird 2.5 to 3.x. It also provides a lot of recommendations for how your Firebird installation should be set up... Steve Naidamast Sr. Software Engineer
Re: [firebird-support] Mailing list change?
Mark... Do you know how the data is stored in the Yahoo mailing list? If not, could you contact the administrator to find out? There may be a way to convert it to another platform in case the project leaders decide to move it to another platform... Steve Naidamast Sr. Software Engineer
[firebird-support] Re: Is something wrong with Firebird Embedded 3.x.x FBClinet.dll ???
That seems to have fixed the issue... However, the other day I believe I had tried the 32bit client and it still wasn't being seen as a valid client library. Just tried it again and now it is working as expected. Maybe when I tried the 32bit client something was stuck in the application's buffer. Thank you but this is very strange... Steve Naidamast http://www.blackfalconsoftware.com http://www.blackfalconsoftware.com
Re: [firebird-support] Is something wrong with Firebird Embedded 3.x.x FBClinet.dll ???
I don't believe that is the problem here... Steve Naidamast http://www.blackfalconsoftware.com http://www.blackfalconsoftware.com
Re: [firebird-support] Re: Firebird 3: Not able to UPDATE RDB$Procedures
Thanks for the heads up, Mark... However, Carlos' explanation when I read through it again, does appear to be a little confusing at the least. Why not simply disallow it altogether and be consistent? As I just mentioned in another reply to you, no one breaks into databases to steal stored procedure source code...
Re: [firebird-support] Firebird 3: Not able to UPDATE RDB$Procedures
My thoughts exactly... Even with scripting out all the database objects before the sources are deleted, sorting through them on large database can become a timely effort if you need to recompile only a few. Besides, who breaks into a database to steal stored procedure code??? And if you can disassemble the BLR to get the original code, the crooks can also..
[firebird-support] Re: Query - Regarding a Firebird Source Control Application
Thank you all for your kind and informative replies. It appears that the information you all provided gives me encouragement to review my current product for a conversion to the Firebird Database Engine. To be sure, this will be a lot of work since the entirety of the SQL Server Shared Management Objects API, which is what is used to extract the SQL Server database objects and scripts, will have to be replaced. However, it does appear that the replacement with simple ADO.NET queries to the Firebird system tables will be far more straight forward. With such a conversion I could make one version freeware like the current product but another priced at a very reasonable fee. That is the problem with Open Source. It literally destroyed the flourishing software cottage industry of the 1990s as it went to far to the extreme. Even software engineers and developers have to eat... Thank you, again... Steve Naidamast http://www.blackfalconsoftware.com http://www.blackfalconsoftware.com
[firebird-support] Query - Regarding a Firebird Source Control Application
Hello... About two years ago I had completed a fully working SQL Server Source Control application that had the same basic features as the similar tools of its much larger competitors, Red Gate and ApexSQL. I attempted to market the application to .NET developers who were using SQL Server for their application development. Since no one was interested in purchasing my software (being technicians) I turned it into Freeware. The current state of this project is that it has been updated to work with all SQL Server database engines up to SQL Server 2016. I have been contemplating to upgrading it again to SQL Server 2017. However, considering that Firebird is not supported to the same extent as SQL Server, my software may make a useful addition to the growing number of third-party tools for Firebird. The problem is, I need to know a way to access the source code for all the relevant database objects (ie: stored procedures) so that I can import them into my application for version control. I came across some information in Carlos Cantu's very well written migration guide for Firebird 3.x.x that describes the use of the RDB$ tables for such database objects. I would like to know if it these are the tables that would allow me to access such source code for all such relevant database objects. If so, I would consider a conversion of my software to the Firebird Database Engine. For those who would be interested in looking at my original product, please go to the following link for the download... https://blackfalconsoftware.com/software/ https://blackfalconsoftware.com/software/ Select the package entitled... SQL Server Source Control for Developers 2.2.1 The package comes with complete documentation in a CHM file. Thank you...
[firebird-support] Re: Upgrade from superclassic to superserver
Thomas... You may want to consider purchasing Carlos' e-book on Firebird Migration at the following link... https://www.firebirdnews.org/migration-guide-to-firebird-3/ https://www.firebirdnews.org/migration-guide-to-firebird-3/ It is very well written and has a lot of information regarding the different types of Firebird servers and how to decide between them...
Re: [firebird-support] Firebird ADO.NET: ExecuteNonQuery Returns -1 On Successful Insert
Thank you Helen, for your reply to my original query regarding ADO.NET and Firebird... I did in fact find the Firebird .NET provider Email list after I had submitted my query here and then forwarded the same query over there. Jiri was kind enough to respond. His reply included the information that he apparently could not provide the Records Affected by an actionable DML statement within a stored procedure in ADO.NET since the database engine did not provide that information for him to acquire it in that vein. As a result, one has to manually enter the RETURNING clause to such DML with the ROW_COUNT system variable to get such information returned to the ADO.NET provider. Prior to this, I incorrectly stated that I saw this as a weakness in the Firebird engine, which maybe should be changed by the database development team. This was incorrect on my part as I was not considering the technical philosophy that underscores the foundations of Firebird, which I imagine was the foundations for the original Interbase in 1986. However, I come from an extensive background with the development of database applications using SQL Server primarily. However, I have used Oracle, Sybase, and MySQL as well. All of these database engines' ADO.NET providers return a Records Affected number. As a result, after using so many different database systems over a very long career in this way, I commented that I saw this missing attribute in Firebird as a weakness instead of something akin to how Firebird is expected to process actionable DML. That being said, I produced a technical paper a few days ago on my own technical article site that I announced on this forum, which you may have read. This article was designed with the intention of attracting other .NET development professionals who have similar backgrounds to mine by describing the inconsistencies they will find with Firebird when working with a db-manager as well as ADO.NET. And such professionals with my type of background will see a number of issues with Firebird that will not be seen as expected database behavior. I hope that my paper will assist such professionals in understanding Firebird by demonstrating these inconsistencies while also providing them with the appropriate solutions to work with them in a manner that will make them more comfortable with this database engine. Nonetheless, it is my contention that such issues viewed as inconsistencies are a factor in Firebird's lack of similar popularity in the States when compared to the other major systems commonly used. Despite this, a recent poll of database usage shows Firebird slowly moving up the popularity indices. And yet, there is nothing that I can see that should prevent Firebird from being a serious consideration for database organizations that want a powerful database engine that also has a small footprint while also offering an embedded edition that is completely aligned with its server counterparts. No other database vendor has successfully accomplished this achievement that I am aware of. From your community member, Mark Rotteveel, he suggested that the reason for this lack of Records Affected information from stored procedures with actionable DML was the idea that stored procedures act as isolated components (ie: decoupling). However, from my experience, this is taking the concept of decoupling a little too far. in the 2000s this concept was taken to such an extent that anything a developer created should be done via a generic design, which is an impossibility since generic design can only be efficiently implemented on static constructs such as for example, a date validation api. This prominent support for generic design was eventually forced back into reality when it was found that just the time alone to create all such development along this concept was unjustified. In any event, whatever I have written about Firebird has always been very positive since I was immediately impressed with my first experiences with Interbase many years ago when I tinkered with it on my own. And I have been tracking the progress of Firebird for many years. However, my experiences with it discouraged me from using it until I decided that for the work I am doing now it was the best engine available. And happily, I was correct... Thank you again for your recent response to this query... Steve Naidamast http://www.blackfalconsoftware.com http://www.blackfalconsoftware.com
[firebird-support] Firebird ADO.NET: ExecuteNonQuery Returns -1 On Successful Insert
Hello. I am using the Firebird 2.5 Embedded Edition with the Firebird ADO.NET provider version 5.5 or 5.7. I am not sure which, since it has been so long since I set up the references for my project and the assembly being used is not taken from the actual provider library that has all the files in it.. In any event, during a test of my current application development I successfully inserted a record into a table using a Firebird stored procedure with the ADO.NET ExecuteNonQuery method, which should return a "records affected" count of "1". Instead, I am finding that the returned "records affected" count is "-1". I have never seen this before with all the other database engines I have worked with over the years. Nonetheless, I believe I have seen this issue raised with Firebird ADO.NET before but some time ago. Does anyone have any idea as to what is causing this? Thank you... Steve Naidamast blackfalconsoftw...@outlook.com
[firebird-support] Re: Can't connect to local Firebird server
The query as written should work for both the embedded server as well as a remote server. Can you provide the query PSQL and the exact error message/information that is being thrown?
Re: [firebird-support] Article for experienced developers looking learn the Firebird Database
Thank you, Karol... I'll take your information and update the article's section on views... With warm regards, Steve
[firebird-support] Article for experienced developers looking learn the Firebird Database
For many years I have been tracking the development of the Firebird Database Engine and have watched it move from its 1.5 version to its most recent release, version 3.0.3. On a number of occasions I have attempted to work with this database engine. And though I have many years of extensive database application development with quite a few database engines, Firebird always seem to allude my attempts at mastering it. From major to minor "gotchas" from a lack of good introductory documentation regarding the many differences between this database engine and its many contemporaries, which more or less towed a standardized SQL line, even with the many enhancements made to any one engine, Firebird frustrated my efforts to the point on these occasions where I decided the efforts in learning it were simply not justified. Nonetheless, Firebird still beckoned as mastering it was an accomplishment I still wanted to achieve. As a result, seeing the mostly negative aspects of using Microsoft's SQL Server LocalDB for a desktop application I have been developing, I decided to "bite the bullet" and somehow find a way to teach myself Firebird in order take advantage of its excellent embedded engine also known as the Firebird Embedded Server. The result of this endeavor, which has been very successful so far, is a detailed article that attempts to make it much easier for experienced professionals to learn Firebird who also, like I, have many years of development experience with other database engines. The article points out the many differences when using this engine for scripting within query and action screens in a db-manager or creating database modules with Firebird PSQL that can drive even experienced professionals to the point of distraction. Hopefully, my experiences will help such professionals who want to master Firebird as well a much easier learning experience. The article can be found at the following link for everyone's review... https://blackfalconsoftware.wordpress.com/2018/05/11/the-firebird-database-engine-the-frustrations-of-the-long-distance-database-application-developer/ https://blackfalconsoftware.wordpress.com/2018/05/11/the-firebird-database-engine-the-frustrations-of-the-long-distance-database-application-developer/ Admittedly, there may be some mistakes in this piece since it was developed around my own learning experiences with this database engine. As a result, if anyone has any comments, corrections, or suggestions please let me know at my email address... blackfalconsoftw...@outlook.com In addition, if anyone would like a PDF copy of this piece, please let me know as well and I will prepare one and send it out...
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. 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. 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... Steve Naidamast
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
I am not sure if I understand your explanation here... 1... Why would I test the input parameter, "PI_KEY_IN" for each row? I am only doing this to ensure that the client never sends an input value of zero(0). 2... In the other email you sent you stated that... "A FOR .. DO is a loop construct, which either execute a single statement for each iteration (each row) in the loop, or a block of statements delimited by BEGIN and END." However. if this follows as most constructs, the "DO" part is the end statement to the "FOR". Or is this better understood as "FOR" everything I do here, "DO" the following... ??? Steve
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
How so? The "SUSPEND" statement is the last statement in the procedure or are you referring to the fact that it is continued as a result of the previous "DO" statement? Steve From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> on behalf of Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] <firebird-support@yahoogroups.com> Sent: Friday, May 4, 2018 12:25 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure 04.05.2018 18:13, Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support] wrote: > If you look at my procedure below, you will note that there is only a single > "SUSPEND" > statement. If you look a little more, you'll see that it is called in a loop. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item Firebird: The true open source database for Windows, Linux ...<http://www.firebirdsql.org/> www.firebirdsql.org Firebird SQL: The true open-source relational database on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
Dimitry ...
Thank you for your reply...
However, I am not sure about the multiple "SUSPEND" statements on a per-record
basis.
If you look at my procedure below, you will note that there is only a single
"SUSPEND" statement. Yet, all of the rows for the entered date that is used to
execute the procedure (select statement follows module code) are returned as
expected (9 rows returned)...
>>>
>>> Stored Procedure
>>>
CREATE PROCEDURE SP_GET_MSGLOG_DISTINCT_DATES(
PS_DATE_IN VARCHAR(10) NOT NULL)
RETURNS(
PS_DATE_OUT VARCHAR(10) NOT NULL)
AS
DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL;
BEGIN
PS_SQL = 'SELECT DISTINCT';
PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG';
PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM
ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(DAY FROM ML_CREATE_DATE)
AS VARCHAR(2)))' || '/';
PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(YEAR FROM ML_CREATE_DATE)
AS VARCHAR(4 = ' || :PS_DATE_IN;
FOR
EXECUTE STATEMENT (PS_SQL) INTO :PS_DATE_OUT
DO
SUSPEND;
END;
<<<
>>>
>>> SELECT Statement
>>>
SELECT * FROM SP_GET_MSGLOG_RECS ('10/17/2017')
<<<
Nonetheless, would you suggest that I put the "SUSPEND" statement within the
FOR-DO construct?
Thank you...
Steve Naidamast
Sr. Software Engineer
blackfalconsoftw...@outlook.com
[cid:96714d12-5078-4b55-a660-b7d6d4c1b734]
From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> on
behalf of Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]
<firebird-support@yahoogroups.com>
Sent: Thursday, May 3, 2018 5:17 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Problem returning multiple rows from a
CTE-recursive procedure
03.05.2018 23:04, blackfalconsoftw...@outlook.com [firebird-support] wrote:
> However, I though only the issuance one "SUSPEND" was enough to flush the
> entire buffer of
> all records...
No. One SUSPEND - one record in result set. Two SUSPENDs - two records and
so on.
Watch this in isql:
SQL> set term GO;
SQL> EXECUTE BLOCK RETURNS (A INTEGER)
CON> AS
CON> begin
CON> a = 1;
CON> SUSPEND; -- return a record containing 1
CON> a = 2;
CON> SUSPEND; -- return a record containing 2
CON> SUSPEND; -- return another record containing 2
CON> end
CON> GO
A
1
2
2
--
WBR, SD.
++
Visit http://www.firebirdsql.org and click the Documentation item
Firebird: The true open source database for Windows, Linux
...<http://www.firebirdsql.org/>
www.firebirdsql.org
Firebird SQL: The true open-source relational database
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
Yahoo Groups Links
[Non-text portions of this message have been removed]
RE: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
András... Thank you very much for your assistance. Your suggestion seemed to work like a charm. However, I though only the issuance one "SUSPEND" was enough to flush the entire buffer of all records... Steve
[firebird-support] Problem returning multiple rows from a CTE-recursive procedure
Hello... I have been in the process of converting my application's SQL Server T-SQL code to Firebird PSQL procedures. So far so good until I got to the one where I built a Firebird procedure with a recursive CTE in it. The following code is an exact match to my original SQL Server T-SQL code... >>> WITH RECURSIVE HIERARCHY_TABLE AS ( SELECT RCN1.CN_KEY, RCN1.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN1 WHERE RCN1.CN_KEY = 3 UNION ALL SELECT RCN2.CN_KEY, RCN2.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN2 JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY ) SELECT * FROM HIERARCHY_TABLE; <<< When run in my Firebird DB Manager within a query script-screen against the two records in the table, it returns both records as it should. The records are setup as follows... >>> record #1 CN_KEY = 3, CN_PARENT_KEY = 0 (a parent key of 0 means that this is the top-most record in the hierarchy) record #2 CN_KEY = 4, CN_PARENT_KEY = 3 (child record to to record #1) <<< The result then from this test is that the Firebird PSQL code produces the same exact result as my SQL Server's T-SQL code when run. The problem I am finding however, is that when I run my Firebird PSQL code above within a procedure, it only returns record #2, the child record, instead of both records. My Firebird PSQL procedure is as follows... >>> CREATE PROCEDURE SP_GET_CAT_CHILD_NODES_BYKEY( PI_KEY_IN BIGINT NOT NULL) RETURNS( PI_KEY_OUT BIGINT, PI_PARENT_KEY_OUT BIGINT) AS BEGIN FOR WITH RECURSIVE HIERARCHY_TABLE AS ( SELECT RCN1.CN_KEY, RCN1.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN1 WHERE RCN1.CN_KEY = :PI_KEY_IN UNION ALL SELECT RCN2.CN_KEY, RCN2.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN2 JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY ) SELECT CN_KEY, CN_PARENT_KEY FROM HIERARCHY_TABLE INTO :PI_KEY_OUT, :PI_PARENT_KEY_OUT DO IF (PI_KEY_IN = 0) THEN EXCEPTION ROOT_CAT_NODE_DELETE; SUSPEND; END; <<< Can anyone explain why my procedure is not returning the expected number of records? Thank you...
Re: [firebird-support] Dynamic PSQL (Execute Statement) in Stored Procedure With "SELECT / INTO"
Thank you very much for your help, Karol... As soon as I corrected my procedure based on your information, it worked as expected. Thank you, again... Sincerely, Steve Naidamast
[firebird-support] Dynamic PSQL (Execute Statement) in Stored Procedure With "SELECT / INTO"
Hello...
I have been doing fairly well at converting a project's database to Firebird
Embedded, transposing all of the inline SQL to Firebird stored procedures.
However, with the following procedure I am creating a slightly
complex SELECT statement based upon two incoming parameters.
It seems that no matter how I try to implement the "INTO" clause against the
return variable, when I run the procedure from my DB-Manager it yields an
error...
>>>
>>> procedure sql
>>>
CREATE PROCEDURE SP_GET_MSGLOG_REC_COUNT(
PS_DATE VARCHAR(10),
PS_MSG_TYPE CHAR(1))
RETURNS(
PI_CNT INTEGER NOT NULL)
AS
DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL;
BEGIN
PI_CNT = 0;
PS_SQL = 'SELECT COUNT(*)';
PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG';
IF (CHAR_LENGTH(TRIM(:PS_DATE)) > 0) THEN
BEGIN
PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM
ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(DAY FROM
ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(YEAR FROM
ML_CREATE_DATE) AS VARCHAR(4 = ' || :PS_DATE;
END
IF (CHAR_LENGTH(TRIM(:PS_MSG_TYPE)) > 0) THEN
BEGIN
If (POSITION('WHERE' IN PS_SQL) > 0) THEN
PS_SQL = PS_SQL || ' AND ML_MESSAGE_TYPE = ' || :PS_MSG_TYPE;
ELSE
PS_SQL = PS_SQL || ' WHERE ML_MESSAGE_TYPE = ' || :PS_MSG_TYPE;
END
--PS_SQL = PS_SQL || ' INTO ' || :PI_CNT;
PS_SQL = PS_SQL || ' INTO :PI_CNT';
EXECUTE STATEMENT (:PS_SQL);
SUSPEND;
END;
<<<
If someone could take a look at this code and give me an idea as to where I am
going wrong it would very much appreciated...
Thank you...
Steve Naidamast
Re: [firebird-support] Book on T-SQL
Mark... One of the newer and nice error trapping features in T-SQL is the implementation of the "Try-Catch" block, which I used quite a bit when working on developing corporate databases. Another was the use of "select" to to test out functionality. for example, with T-SQL in a database manager I can write the following... select 'trailing ''la''' -> which returns trailing 'la' when run. You can do the same with the following Firebird-SQL statement... select 'trailing ''la''' from RDB$DATABASE However, it took me a while to find this capability and I only did so by accident. This is where a book on "Learning Firebird SQL" would of great assistance in moving the Firebird up the popularity rankings. In fact, such books are far more helpful than the language references since these latter books assume that you have a working knowledge of SQL and do little to promote the Firebird database beyond the core community. As it regards the above example, if I remember correctly, I believe there is a similar statement construct in Oracle. However, the T-SQL implementation is the most elegant and the easiest to use and learn. Cursors in T-SQL have also gotten very powerful and even more efficient with the later releases of SQL Server. As a result, their complexity is far easier to learn and use. As Oracle relies on cursors in a way that SQL Server does not, it is with this database engine that the power of the cursor is its most extensive. These are just some examples off the top of my head so to speak...
Re: [firebird-support] Book on T-SQL
I have to disagree with your observation. It is clearly stated in the Firebird SQL Reference Manual that the development of Firebird-SQL is with a strict implementation of the SQL-92 Standard. This is not to say that Firebird-SQL does not go beyond the SQL-92 Standard but it is somewhat limited in this respect when compared to the other major SQL implementation for which I have used them all with the exception of of IBM's DB2...
Re: [firebird-support] Book on T-SQL
I just wanted to follow up on this thread about learning Firebird-SQL since I have extensive experience with using SQL with a variety of database engines over a very long career. That being said, the gentleman who stated that you cannot use T-SQL with Firebird is primarily correct and learning T-SQL in of itself would hinder one trying to convert their knowledge to Firebird-SQL. Both T-SQL and Firebird-SQL support the SQL-92 Standard. However, T-SQL, like SQL from other major database vendors add so much to their SQL dialects that it is difficult to determine where the SQL-92 Standard begins and where it ends. Firebird's implementation of SQL is a very strict implementation of the SQL-92 Standard. As a result, this makes Firebird-SQL not only very limited in what it can do when compared to other SQL dialects but extremely frustrating to those who come to use Firebird with the expectation that their current knowledge of SQL will suffice. Since I retired in 2014 and began development on projects that did not use SQL as heavily as I was used to, I became somewhat rusty in my knowledge of the more advanced techniques with SQL. As a result, to learn Firebird, I decided to base my initial referencing on the Firebird SQL Language Reference PDF. For the most part, it is a superb guide but somewhat lacking in more extensive examples demonstrating some of the more advanced techniques in certain areas. Such discrepancies for example, can be found in the use of string and date-time functions. However, this should not deter anyone from basing their learning or referencing from this manual. Ms. Helen Borrie, one of the co-development leads on the Firebird Database Engine project, also has a multi-volume set of books available for purchase, which detail all of the necessary details one would need for learning to work with Firebird's implementation of SQL. However, her books are a little pricey, probably the result of the lower distribution capabilities when compared to the other database engines. An excellent learning guide and refresher book is "Learning SQL" by Alan Beaulieu from O'Reilly Books. Mr Beauleu provides a very good insight into using SQL based upon various implementations for SQL Server, MySQL, and Oracle with an emphasis on MySQL. As a result, I would recommend anyone wanting to learn Firebird-SQL to obtain this book and use it as an additional reference when working with the Firebird SQL Language Reference Guide. Such use will allow the developer to ascertain what Firebird supports and what it doesn't simply by making comparisons between the two against the SQL code that the developer is attempting too create. To aid in this area, I am also developing an entire set of SQL code in stored procedure format that I will eventually post on my own technical blog for anyone that wants example scripts to go by. Finally, whether you have experience with databases or not, approach the learning of Firebird-SQl with an open mind. It's strict adherence to the SQL-92 standard can become very frustrating to work with; especially for those who have prior database experience. But stick with it and you will master the necessary essentials. PostgreSQL has similar issues with its SQL implementation but due to the extensive and in-depth manual that comes with it, one is more easily able to adjust to this powerful database's SQL implementation. If Firebird were to become more popularized, I am quite sure that the development team would accommodate such increased interest by providing more in-depth written material. And surprisingly, in a recent poll for the rankings of database usage around the world, the European use of Firebird has actually pushed this excellent database up a notch in the rankings from #30 to #29. As a result, Firebird appears to be looking at becoming a mainstay database engine if the trend continues as slow as it may be... Steve Naidamast Sr. Software Engineer
Re: [firebird-support] Re: Firebird Embedded - Will a 3.0 version be released???
Helen... Thank you for your quick reply I must be working on too many software projects as I completely forgot about the earlier Firebird 3.0 release. It may have been the way the site was presenting its current news that lead me to believe that the 3.0 version was new. In any event, I'll take a look at those release notes to get a better understanding of how to work with the updated Embedded Edition. Thank you again... Steve blackfalconsoftw...@outlook.com
[firebird-support] Re: Firebird Embedded - Will a 3.0 version be released???
I am currently using Firebird Embedded edition for a military simulation I have been working on. I have seen recently that Firebird 3.0 has just been released with a brand new User-Guide. Along with this, I noticed that Firebird 4.0 is now on the books for development. However, in the interim will the Firebird Team be releasing a 3.0 version of their embedded database engine to match the internals of their recent release for their server engine? Or has Firebird Embedded Edition reached a level of maturity that it requires no further development at this time? Thank you... Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com
