[firebird-support] why failed to insert into a table from a storeprocedure?
hi, all, in a store procedure, i try to insert into a table some records from an other store procedure, it seems like this: create procedure test1 as begin insert into table1(f1, f2, f3) select fa, fb, fc from test2(param1, param2); end test2 is a store procedure defined in the same database. it fetchs records from some tables and an other store procedure. but nothing have done without any error. if execute alone the store procedure test2, lots of records can be founded. why? thanks.
[firebird-support] Re: DDL updates in 2.5
--- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote: At 08:12 AM 22/11/2011, Aage Johansen wrote: Martin Agren wrote: ... Data output from my stored proc isnt reflecting the changes I have made. I make changes in DDL, check the new dataset (Flamerobin or my client app) and am not getting the desired results as data is unchanged. I make another change, still no effect. Then, when disconnectiong and restarting my client side apps all the changes suddenly appear. Will your stored procedure need re-compiling before changes will show up in the results? I don't think the SP is recompiled until the database is closed and reopened - it could be difficult to recompile for you and keeping the old one for other users. OTOH - I could be mistaken. No, Aage, you're not mistaken. The compiled BLR goes into the cache and stays there for re-use until the database is left with no further connections (or is shut down purposely). Superserver's cache is shared between all connections so the Flamerobin client will still execute the old BLR. On Classic and Superclassic, each connection has its own cache. If Martin was doing his testing in that environment, his FR client would still be working with the old BLR, while new connections would work with the new BLR. ./heLen Well, here we have it then. I can tell you that this will drive you crazy from time to time. Beeing used to changes reflecting in data output immediately, you tend to question your sanity when you dont get desired result from changing metadata. I strongly suggest a configuration flag to make it possible to disable this cache in development. /M
[firebird-support] Re: DDL updates in 2.5
--- In firebird-support@yahoogroups.com, Thomas Steinmaurer ts@... wrote: Helen, Martin Agren wrote: ... Data output from my stored proc isnt reflecting the changes I have made. I make changes in DDL, check the new dataset (Flamerobin or my client app) and am not getting the desired results as data is unchanged. I make another change, still no effect. Then, when disconnectiong and restarting my client side apps all the changes suddenly appear. Will your stored procedure need re-compiling before changes will show up in the results? I don't think the SP is recompiled until the database is closed and reopened - it could be difficult to recompile for you and keeping the old one for other users. OTOH - I could be mistaken. No, Aage, you're not mistaken. The compiled BLR goes into the cache and stays there for re-use until the database is left with no further connections (or is shut down purposely). Superserver's cache is shared between all connections so the Flamerobin client will still execute the old BLR. On Classic and Superclassic, each connection has its own cache. If Martin was doing his testing in that environment, his FR client would still be working with the old BLR, while new connections would work with the new BLR. In 2.5, this isn't the case anymore. http://tracker.firebirdsql.org/browse/CORE-2052 Don't think so Thomas. Seems to be exactly what I am experiencing.. /Martin
Re: [firebird-support] Re: why failed to insert into a table from a storeprocedure?
A bit basic but is the transaction being committed? Sent from my BlackBerry® wireless device -Original Message- From: Tomasz Tyrakowski t.tyrakow...@sol-system.pl Sender: firebird-support@yahoogroups.com Date: Tue, 22 Nov 2011 11:55:00 To: firebird-support@yahoogroups.com Reply-To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: why failed to insert into a table from a storeprocedure? I still claim insert...select.. from stored proc works as expected. Executing this: -- create table t1( f1 integer not null, primary key(f1) ); create table t2( f2 integer not null, primary key(f2) ); insert into t2(f2) values (1); insert into t2(f2) values (2); insert into t2(f2) values (3); insert into t2(f2) values (4); insert into t2(f2) values (5); set term ^; create procedure p2( param integer ) returns( res integer ) as begin for select f2 from t2 where f2 = :param into :res do suspend; end ^ create procedure p1( param integer ) as begin insert into t1(f1) select res from p2(:param); end ^ set term ;^ execute procedure p1(4); select * from t1; -- leaves t1 with two records (f1=4 and f1=5), exactly as expected. Are you sure you posted the real source of your procedure test1? (of course not ;) ). If you don't prefix param1 and param2 with colons , the line select fa, fb, fc from test2(param1, param2); actually contains a syntax error and you can't create such procedure. IMO there's no point in further looking for a bug in FB in this case - everything works as it ought to. Double check your procedures and you'll be fine. regards Tomasz W dniu 2011-11-22 11:35, ibmcom2011 pisze: Tomasz, thank you. in fact, the test1 have the same parameters as test2. it's my negligence in describing my problem. create procedure test1( param1 varchar(20), param2 varchar(20) ) as begin insert into table1(f1, f2, f3) select fa, fb, fc from test2(param1, param2); end i want to save the result data set which got from a complex store procedure (test2) into a temp table (table1) so that i can use it to build an new query. as you say, a invoke b, if b has been changed, the a would be changed. but the calling shouldn't be omitted, i think. in the structure insert into t2 (..) select .. from t1 , can the t1 be a store procedure ? --- In firebird-support@yahoogroups.com, Tomasz Tyrakowskit.tyrakowski@... wrote: Hi, At the first glance, it should work as expected. If you run by hand select fa, fb, fc from test2(param1,param2) and it does return a data set, then I'd take a closer look at the params you pass to test2 inside test1. Make sure they're really what you think they are. Also, if you altered test2 in the database after creating/altering test1, alter test1 again to itself (all procedures depending on X should be altered, that is, byte-compiled, every time X is altered). If that doesn't help, send more details. regards Tomasz On 2011-11-22 09:15, ibmcom2011 wrote: hi, all, in a store procedure, i try to insert into a table some records from an other store procedure, it seems like this: create procedure test1 as begin insert into table1(f1, f2, f3) select fa, fb, fc from test2(param1, param2); end test2 is a store procedure defined in the same database. it fetchs records from some tables and an other store procedure. but nothing have done without any error. if execute alone the store procedure test2, lots of records can be founded. why? thanks. -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__ -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__ [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ 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] Re: why failed to insert into a table from a storeprocedure?
Tomasz, i'm sorry. i always use ibexpert to manage my database and test my store procedure and sql. now i think i have found the reason of failing to inserting. just i execute the procedure again, and clear all the history in the parameters history, it can work correctly. thank you again. --- In firebird-support@yahoogroups.com, Tomasz Tyrakowski t.tyrakowski@... wrote: Hi, At the first glance, it should work as expected. If you run by hand select fa, fb, fc from test2(param1,param2) and it does return a data set, then I'd take a closer look at the params you pass to test2 inside test1. Make sure they're really what you think they are. Also, if you altered test2 in the database after creating/altering test1, alter test1 again to itself (all procedures depending on X should be altered, that is, byte-compiled, every time X is altered). If that doesn't help, send more details. regards Tomasz On 2011-11-22 09:15, ibmcom2011 wrote: hi, all, in a store procedure, i try to insert into a table some records from an other store procedure, it seems like this: create procedure test1 as begin insert into table1(f1, f2, f3) select fa, fb, fc from test2(param1, param2); end test2 is a store procedure defined in the same database. it fetchs records from some tables and an other store procedure. but nothing have done without any error. if execute alone the store procedure test2, lots of records can be founded. why? thanks. -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] XML Data type
Mahesh Pratihari wrote: Could you please let me know the best fit data type in XML in firebird, as firebird doesn't support the xml data type? I need the alternative data type of it. If you later need to query this data, then you should really store it in tables instead of a single blob column. A useful tool to help you determine the structure is XMLWizard. It will suggest the table(s), columns and datatypes you should use: http://www.guacosoft.com/xmlwizard HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
[firebird-support] Replacing embedded Firebird?
Hi, I use a proprietary application that embeds a Firebird database. I would like to be able to access its data via network, the application does not provide an interface. Since the database file is locked (http://www.firebirdfaq.org/faq230/) only one server may control it. Is there a way to setup a regular local Firebird server to which the application can connect directly (via DLL), so that other connections are possible as well? Or can the embedded server be configured to act as relay to a regular Firebird DB? Any help appreciated, Anonymous
[firebird-support] Re: Firebird 2.5.1 on Mac OS X 10.7.2 problems
Yes, I've done that several times. No success. I've tried all the different configurations of Firebird 2.5.1. Still no success. Any other ideas? Thanks!
Re: RES: [firebird-support] When I need to do a full backup/restore?
Hello, Also check transaction count limit (2^32-1) in your databases. If Next transaction is close to it, make backup/restore. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) There are no problems (like corruption) if the server was power down during backup? Since backup is an regular read-operation id should not cause corruption when an power-fail does happen while backup. The backup file itself is junk in case of an power-fail. When I restore the backup at a different location how I validate the restored backup? With gfix? The gbak application should report an error if backup is not restorable. There is a “formula” to indentify that a Database NEED a backup/restore? I mean: If “A” is 50,000 bigger than “B” I need to backup/restore. Usually there should be no reason for restore. Restore is only needed when your database gets lost or damaged. Elmar ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Replacing embedded Firebird?
At 04:38 AM 23/11/2011, random64785 wrote: Hi, I use a proprietary application that embeds a Firebird database. I would like to be able to access its data via network, the application does not provide an interface. Since the database file is locked (http://www.firebirdfaq.org/faq230/) only one server may control it. Is there a way to setup a regular local Firebird server to which the application can connect directly (via DLL), so that other connections are possible as well? Yes, of course. Install a full server and replace the fbembed.dll on the local machine with the fbclient.dll remote client. But unless you have the source code for your proprietary software, you won't be able to do this yourself - ask your vendor to give you a client/server version. Or can the embedded server be configured to act as relay to a regular Firebird DB? The client embedded in fbembed.dll can be a *remote* client to a full server installation (if that's what you're asking). Your proprietary application code could connect as a local client to a full server installation, i.e., on the same machine as the application code, if you moved fbembed.dll out of harm's way (hide it somewhere) and replaced it with fbclient.dll. Be aware that application developers often rename fbembed.dll to fbclient.dll or gds32.dll for compatibility with old application code. You should compare what you have with the fbclient.dll that you can extract from the version-matching zip kit from the Firebird downloads page. The real fbclient.dll is about one-fifth of the size of fbembed.dll. If you are going to play around with your installation, I strongly recommend installing everything on another machine as a trial. Better advice would be to consult the vendors: anything we could suggest doing would be based on guesswork about how the software is set up. Any help appreciated, Anonymous When you are asking for free help on a peer support list, signing messages with a pseudonym is regarded as impolite. In this case, whether justified or not, it leads to suspicion about your motives for wanting to get inside the database. It is strongly against our list policies to help people to hack proprietary software. Please reassure us. ^ heLen (List moderator)
