Re: [firebird-support] Re: Rolling back from 2.5 to 2.0
On Wed, 09 Jan 2013 15:01:32 +1300, Helen Borrie hele...@iinet.net.au wrote: You said originally that you had installed Fb 2.5 on Win7 X64. However, if you installed the 64-bit Firebird and instructed the installer to install fbclient.dll into the system directory on 64-bit Windows, then it will be the 64-bit client library, which the installer may have been unable to delete because it was loaded. Your app is probably 32-bit and, if you are trying to run it locally, it's possibly not finding the 32-bit library of the same name. It could be worth checking. Actually, the Windows 64 bit installer will install (and remove when uninstalled) both the 32 bit and 64 bit fbclient.dll when that option is enabled in the installer. Mark
[firebird-support] unavailable database
In case somebody's just reading by and has a clue, don't hold back :-) For the first time since working with Firebird I'm probably about losing data. Here's what I did to successfully break my precious: - shutdown -f while 1 one user who told he's not working was in fact still working with the database - starting a backup which was expected to run some 7 minutes - terminated the process after some 30 minutes (no CPU usage of gBak.exe for more than 20 minutes) Now an ODBC connection test works fine. Connecting via Jiri's .net provider times out (CPU 50% for 2++ minutes) Connecting via IBExpert times out (CPU 50% for 2++ minutes) From 6 databases on that server, only 1 is making trouble. I made a file copy of the offending file to C:\Current.fdb. gfix -v -n C:\Current.fdb results in unavailable database (userpw supplied...) gfix -v -n localhost:C:\Current.fdb results in a new child process fb_inet_server.exe at 50% for a short time (~2 seconds), a new blank line in the command window. gbak -b -v C:\Current.fdb C:\Current.fbk results in unavailable database gbak -b -v localhost:C:\Current.fdb C:\Current.fbk a new process gbak.exe with constantly 0%, a new file C:\Current.fbk with 0 bytes, a new child process fb_inet_server.exe at 50% for a loong time, some update to the database, because c:\current.fdb gets a new file-timestamp. ouput says: readied database... creating file ... starting transaction nothing more happens. How would I best go about trying to fix this? I have a good backup which is 4 hours old. There are worse things in the world than having to revert back to that, but if possible I'd try to restore this one. FB 1.5 Classic Server on W2K3 TIA André ++ 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/
[firebird-support] Need a little help with EXECUTE STATEMENT
Greetings All,
Using Firebird 1.5.3
D.CREATE_DATE is defined as a TimeStamp in the database.
V_END_DATE is an input parameter defined as Date
V_END_DATETIME is an input parameter defined as TimeStamp
I have a stored procedure with the following execute statment
EXECUTE STATEMENT 'SELECT COALESCE(COUNT(D.DEBT_NO),0) AS TOTAL_COUNT,
COALESCE(SUM(D.ORIG_PRINCIPAL +
D.ADJ_PRINCIPAL),0) AS TOTAL_GROSS,
(SUM(D.LIST_DATE -
D.SERVICE_DATE)/COALESCE(COUNT(D.DEBT_NO),1)) AS AVG_AGE
FROM DEBT D
JOIN ACCT_CASE AC ON AC.ACCT_ID = D.ACCT_ID AND
AC.CASE_ID = D.CASE_ID
WHERE AC.CLT_ID = ' || V_CLT_ID ||
'AND NOT AC.CLT_SITE_ID IN (' ||
V_CLT_SITE_ID_LIST || ')
AND EXTRACT(MONTH FROM D.LIST_DATE) = ' ||
iBegMonth ||
'AND EXTRACT(YEAR FROM D.LIST_DATE) = ' ||
iBegYear ||
'AND CAST(D.CREATE_DATE AS DATE) = ' ||
V_END_DATE
INTO :R_TOTAL_COUNT, :R_TOTAL_GROSS_AMT,
:R_AVG_AGE;
When I run the stored procedure I receive the follow error:
ISC ERROR CODE: 335544606
ISC ERROR MESSAGE:
expression evaluation not supported
I tracked it down to the CAST(D.CREATE_DATE AS DATE) line.
I guess I have never tried to use a CAST() inside of a execute statement
before. Is this correct?
So, as a work around I thought I would modify it as follows:
EXECUTE STATEMENT 'SELECT COALESCE(COUNT(D.DEBT_NO),0) AS TOTAL_COUNT,
COALESCE(SUM(D.ORIG_PRINCIPAL +
D.ADJ_PRINCIPAL),0) AS TOTAL_GROSS,
(SUM(D.LIST_DATE -
D.SERVICE_DATE)/COALESCE(COUNT(D.DEBT_NO),1)) AS AVG_AGE
FROM DEBT D
JOIN ACCT_CASE AC ON AC.ACCT_ID = D.ACCT_ID AND
AC.CASE_ID = D.CASE_ID
WHERE AC.CLT_ID = ' || V_CLT_ID ||
'AND NOT AC.CLT_SITE_ID IN (' ||
V_CLT_SITE_ID_LIST || ')
AND EXTRACT(MONTH FROM D.LIST_DATE) = ' ||
iBegMonth ||
'AND EXTRACT(YEAR FROM D.LIST_DATE) = ' ||
iBegYear ||
'AND D.CREATE_DATE = ' || V_END_DATE || '
23:59:59'
INTO :R_TOTAL_COUNT, :R_TOTAL_GROSS_AMT,
:R_AVG_AGE;
ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23
So then I thought I would try this:
DECLARE VARIABLE tsEndDate TimeStamp;
begin
tsEndDate = :V_END_DATE || ' 23:59:59';
EXECUTE STATEMENT 'SELECT COALESCE(COUNT(D.DEBT_NO),0) AS TOTAL_COUNT,
COALESCE(SUM(D.ORIG_PRINCIPAL +
D.ADJ_PRINCIPAL),0) AS TOTAL_GROSS,
(SUM(D.LIST_DATE -
D.SERVICE_DATE)/COALESCE(COUNT(D.DEBT_NO),1)) AS AVG_AGE
FROM DEBT D
JOIN ACCT_CASE AC ON AC.ACCT_ID = D.ACCT_ID AND
AC.CASE_ID = D.CASE_ID
WHERE AC.CLT_ID = ' || V_CLT_ID ||
'AND NOT AC.CLT_SITE_ID IN (' ||
V_CLT_SITE_ID_LIST || ')
AND EXTRACT(MONTH FROM D.LIST_DATE) = ' ||
iBegMonth ||
'AND EXTRACT(YEAR FROM D.LIST_DATE) = ' ||
iBegYear ||
'AND D.CREATE_DATE = ' || tsEndDate
INTO :R_TOTAL_COUNT, :R_TOTAL_GROSS_AMT,
:R_AVG_AGE;
Same Error:
ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23
Then I thought I would just try something simple.
EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE
FROM DEBT D
WHERE D.CREATE_DATE = ' || :V_END_DATETIME
INTO :FIRST_CREATE_DATE;
or
DECLARE VARIABLE tsEndDate TimeStamp;
begin
tsEndDate = :V_END_DATE || ' 23:59:59';
EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE
FROM DEBT D
WHERE D.CREATE_DATE = ' || tsEndDate
INTO :FIRST_CREATE_DATE;
both caused this error.
ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23
What am I doing wrong? Any work arounds?
Any help appreciated.
Thanks,
Mike
Re: [firebird-support] Need a little help with EXECUTE STATEMENT
Hello Mike, DECLARE VARIABLE tsEndDate TimeStamp; begin tsEndDate = :V_END_DATE || ' 23:59:59'; EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE FROM DEBT D WHERE D.CREATE_DATE = ' || tsEndDate INTO :FIRST_CREATE_DATE; both caused this error. ISC ERROR CODE: 335544569 ISC ERROR MESSAGE: Dynamic SQL Error SQL error code = -104 Token unknown - line 7, char 141 23 What am I doing wrong? Any work arounds? This ends up being (for V_END_DATE = 2013/1/09) WHERE D.CREATE_DATE = 9/1/2013 23:59:59 Do you see the problem? It becomes un-quoted... Try: WHERE D.CREATE_DATE = ''' || tsEndDate || INTO ... Where ''' = ' ' ' (with no spaces) and = ' ' ' ' (with no spaces) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird!
Re: [firebird-support] unavailable database
How would I best go about trying to fix this? I forgot some things: - of course I brought the database online again after backup first failed. - users are logging in with explicit usernames and roles. nobody and no software is using SYSDBA besides me - database only has 1 file (no limbo problems) gfix -v -full reports Number of record level errors:1 so does gfix -m ... , gfix -m -i ..., gfix -m -full -i ... after executing all possible combinations of gfix -m, gfix -v still reports Number of record level errors: 1, and gbak -b -g -i -v ... still does not get over starting transaction. ++ 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] Need a little help with EXECUTE STATEMENT
On Wed, 9 Jan 2013 08:08:10 -0600, SoftTech mi...@softtechks.com wrote: tsEndDate = :V_END_DATE || ' 23:59:59'; EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE FROM DEBT D WHERE D.CREATE_DATE = ' || tsEndDate INTO :FIRST_CREATE_DATE; both caused this error. ISC ERROR CODE: 335544569 ISC ERROR MESSAGE: Dynamic SQL Error SQL error code = -104 Token unknown - line 7, char 141 23 What am I doing wrong? Any work arounds? You are adding it unquoted, so the resulting query is something like D.CREATE_DATE = 23-JUN-2012 23:59:59 (I guess I keep forgetting if Firebird will output in this format or in -mm-dd when doing string conversion), While you actually need D.CREATE_DATE = '23-JUN-2012 23:59:59' (or better yet to be explicit about the type D.CREATE_DATE = TIMESTAMP '2012-06-23 23:59:59') So tsEndDate = :V_END_DATE || ' 23:59:59'; actually needs to be: tsEndDate = || :V_END_DATE || ' 23:59:59''' (or add the additional quotes inside the query where you add tsEndDate) This will probably also remove the need to do this separately, and might even fix the original error you started with. Mark
Re: [firebird-support] unavailable database
On Wed, 9 Jan 2013 15:19:43 +0100, André Knappstein knappst...@beta-eigenheim.de wrote: - database only has 1 file (no limbo problems) A single database file can have limbo transactions, I assume you are referring to shadow problems instead. Mark
Re: [firebird-support] unavailable database
Oh, I expressed that wrong, I think. What I really meant was (so, no problems with distributed files) and also non with limbo transactions. I am not using shadows nor 2-phase transactions. On Wed, 9 Jan 2013 15:19:43 +0100, André Knappstein knappst...@beta-eigenheim.de wrote: - database only has 1 file (no limbo problems) A single database file can have limbo transactions, I assume you are referring to shadow problems instead. Mark ++ 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 mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] unavailable database
after executing all possible combinations of gfix -m, gfix -v still reports Number of record level errors: 1, and gbak -b -g -i -v ... still does not get over starting transaction. I don't have any idea what happened, but suddenly the backup *did* work. This completely toils my understanding of why - after a clean reboot of the server and all users disconnected - a command would at first not work, but then suddenly would when just trying often enough. So, I made a backup, restored that to a new database, and this one seems to work so far. I hope there are no hidden surprises. ++ 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] unavailable database
On Wed, Jan 9, 2013 at 9:19 AM, André Knappstein knappst...@beta-eigenheim.de wrote: - database only has 1 file (no limbo problems) Having nothing to do with your problem - which I'm thinking about - but to clarify a misunderstanding. Limbo transactions are the result of an incomplete two phase commit and have nothing to do with multi-file or even shadowed databases. The application has to initiate a two phase commit with a call to PREPARE. Normally that's done to coordinate updates in different databases, but it can also be done to coordinate a database commit with some other activity that does not have the ability to rollback. Good luck, Ann [Non-text portions of this message have been removed]
[firebird-support] database over internet
Hello! I use Delphi XE2, Firebird and ClientDatasets. So far, all my applications run over a local network. Know I have a customer with two selling points (in diferent cityes) and I need to share the data between them. Nowdays, this is a very used scenario, but unfortunatelly I never came across whith such a task before. May I ask for some advise from people who is already doing it? I was told that most of the people (don't know if it's true!) uses Microsoft's Remote Desktop, so what travels is just the screen. This way is pretty fast, but I need to use the local serial port, and somehow it takes about 40 seconds between I send something to print to a serial printer and it gets printed. So I wonder if there's another way... I've made some very simple tests, conecting to the database like the_remote_server:C\databases\data.fdb It works, but it's slow... Thanks for any advice!! sergio
Re: [firebird-support] database over internet
Sergio wrote: I've made some very simple tests, conecting to the database like the_remote_server:C\databases\data.fdb It works, but it's slow... Of course it works, and how slow it is depends on how many data you are transferring. I'm using FB this way, too (via a VPN because otherwise passwords would go unencyrpted through the Internet), and while it isn't as fast as at home, I can easily work with it. The point is that you must already have WAN connections in mind at database design time (and when constructing SQL queries). Many programmers just make queries like SELECT * FROM table; and choose the needed rows and columns within their client software because this way it's easy to code and (at home!) fast enough. What they don't keep in mind is data minimization. If you just need the first name of customer #137, the above query show read SELECT firstname FROM customers WHERE id=137; and nothing else. So, only the needed data goes over the WAN which makes it pretty fast. And, I don't use any of the Delphi-supplied data components because I never know exactly what they are doing. The only components which I'm using are (from the Interbase tab) 1 IBDatabase, 1 IBTransaction and 1 or more IBSQL. (Granted, that stuff is old, but still works perfetcly with FB 2.5.1.) This way, I have full control over the data which is transmitted between client and server. It may be a bit more programming work to transport the FB data manually from and to a visual component like a textbox, a memo or something like that (no automagic!), but it's well worth the speed gain when you're working over a WAN! On the other side, if your client really *needs* each and every row and colum, you just can look for a faster Internet connection because AFAIK there's no possible data compression between FB clients and server. But this case should happen very rarely... -Matt
Re: [firebird-support] unavailable database
Em 9/1/2013 13:22, André Knappstein escreveu: after executing all possible combinations of gfix -m, gfix -v still reports Number of record level errors: 1, and gbak -b -g -i -v ... still does not get over starting transaction. I don't have any idea what happened, but suddenly the backup *did* work. This completely toils my understanding of why - after a clean reboot of the server and all users disconnected - a command would at first not work, but then suddenly would when just trying often enough. So, I made a backup, restored that to a new database, and this one seems to work so far. I hope there are no hidden surprises. Garbage Collection ? When a database is broken, the first thing to try to make a back-up is to use -g option, to avoid garbage collection, sometimes the problems resides on versions that are not needed to complete the back-up. see you ! ++ 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] database over internet
Em 9/1/2013 17:45, Matthias Hanft escreveu: On the other side, if your client really *needs* each and every row and colum, you just can look for a faster Internet connection because AFAIK there's no possible data compression between FB clients and server. But this case should happen very rarely... -Matt One can use Zebedee (there is an article written by Artur Anjos on how to configure it with Firebird), it's pretty easy to set up. SSH tunnel has compression too see you !
[firebird-support] Re: unavailable database
André wrote: In case somebody's just reading by and has a clue, don't hold back :-) I'm reading, but have no clue! Anyway, did you consider this: http://www.ib-aid.com/downloads/ Best of luck Aage J.
