[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
Your problem is still solvable. Simply add another table. Let one table have
huge field lengths (CHAR/VARCHAR or BLOB SUBTYPE TEXT) and use this only for
imports, then use AFTER INSERT triggers to copy the first few characters of
field(s) to the other table (which you in turn use for querying). Variations
of this solution includes adding another field rather than table or even
another database ('another database' would probably be an overkill and more
difficult to implement in triggers).
HTH,
Set
Do you really think that he should do this?
i do not know what do that system
but why you need to truncate data provided by your customer?
Think abut this system:
1. Customer can put an order for product
and write in order info text:
I need 200 red boots .. but only if you can send it to me in two days
2. your system store truncated data
I need 200 red boots
3. and you send a request by week
4. what do customer - say i do not need this now, get it back
regards,
Karol Bieniaszewski
Re: [firebird-support] Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
On Wed, 16 Jan 2013 04:36:36 -, fabianchocron fabia...@hotmail.com wrote: Hi All, We are trying to resolve an issue caused by the Application inserting a string larger than the database field size. The planned solution was to add a trigger before insert as follows: new.string = substring(new.string from 1 for 100); We did not modify the application because we don't have at this stage the ability to recompile it without a massive effort. For some reason that we do not understand the trigger is not working as desired, and the insert fails with an arithmetic overflow or string truncation error. Is is possible that Firebird 2.1 64 bits has a bug and we may need to upgrade to resolve this issue? Or are we doing something wrong? This is intentional behaviour (I believe it is even specified in the SQL specification, but not 100% sure on that). If you submit data longer than can be stored, it will result in an exception because otherwise it would result in loss of data. Only the client program would know how to deal with this. A workaround would be to use an updatable view and handle the transformation in the view trigger, however you can still in run into problems when you exceed the maximum CHAR or VARCHAR lengths. Mark
[firebird-support] Isql copy command - anyone know how it works?
I'm doing some more documentation work on the isql manual. The current version mentions a copy command in isql which copies a table structure to another table, or database. I tried the following, in the employee database, seeing as the help copy command simply barfs: SQL copy ; Either source or destination tables are missing SQL copy country ; Either source or destination tables are missing SQL copy country my_country; ** * unixODBC - isql* ** * Syntax * ** * isql DSN [UID [PWD]] [options]* ** * Options* ** * -b batch.(no prompting etc)* * -dxdelimit columns with x * * -x0xXX delimit columns with XX, where * *x is in hex, ie 0x09 is tab * * -w wrap results in an HTML table * * -c column names on first row. * *(only used when -d) * * -mnlimit column display width to n * * -v verbose.* * -lxset locale to x * * -q wrap char fields in dquotes * * -3 Use ODBC 3 calls* * -n Use new line processing * * --version version * ** * Commands * ** * help - list tables * * help table - list columns in table * * help help - list all help options * ** * Examples * ** * isql WebDB MyID MyPWD -w My.sql * ** * Each line in My.sql must contain * * exactly 1 SQL command except for the * * last line which must be blank (unless * * -n option specified). * ** * Please visit; * ** * http://www.unixodbc.org * * phar...@codebydesign.com * * n...@easysoft.com * ** Errors occurred (possibly duplicate domains) in creating MY_COUNTRY in employee. So, it appears that isql knows about the command and that it should have a source and destination table, but I've no idea what the block of text at the end is on about! I was suspicious of the mention of duplicate domains, so I created a simple test table with a single integer column, added a couple of rows, and tried to copy that. Same result. This is on Linux Mint 13, KDE version, isql is called isql-fb and my Firebird version is: SQL show version; ISQL Version: LI-V2.5.1.26351 Firebird 2.5 Server version: Firebird/linux AMD64 (access method), version LI-V2.5.1.26351 Firebird 2.5 Firebird/linux AMD64 (remote server), version LI-V2.5.1.26351 Firebird 2.5/tcp (hubble)/P12 Firebird/linux AMD64 (remote interface), version LI-V2.5.1.26351 Firebird 2.5/tcp (hubble)/P12 on disk structure version 11.2 Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
Re: [firebird-support] Is it possible to do this with FK Constraints?
Chuck Belanger schrieb am 08.12.2012 um 13:09 (-0800): During a DB update that I apply to my users' desktop application, I need to Drop all FK constraints then add them back when all the updates of the various tables are complete. I have been issuing explicit DSQL statements to do this, but would like to go through the RDB$ tables and create the DROP statements semi-automatically. So, when I DROP a constraint does its presence in the system tables cease to exist? If so, I was thinking of storing the FK constraints into a table before DROPping them then use that table to ADD the constraints back. Sounds like three hacks in a row to me, but maybe there's no way around what you're proposing to do here. I don't have a good answer, but see here (if still interested): How can I temporarily disable all constraints in a Table in Firebird 2.1? http://stackoverflow.com/a/2757433/269126 Michael
Re: [firebird-support] Isql copy command - anyone know how it works?
Morning Mark, ** * unixODBC - isql* ** Are you sure you are using the right ISQL? It looks like you are using the one from unixODBC instead of the Firebird one. Well, yes, I am using the right one, on my Linux Mint setup, it's called isql-fb. But here's another test, to be absolutely sure: SQL show database; Database: employee Owner: SYSDBA ... ODS = 11.2 Default Character set: NONE SQL show table test; A INTEGER Nullable SQL copy test my_test; ** * unixODBC - isql* ** ... I'm wondering if somehow the copy command shells out to start another isql session and it is that which is attempting to run the isql for mysql rather than isql-fb. Quick test, rename /usr/lib/isql to isql-my, then try again: SQL copy test my_test; sh: 1: isql: not found Errors occurred (possibly duplicate domains) in creating MY_TEST in employee AHA My gut feeling was right, it is shelling out. This is a nuisance then, as it means that people on systems where the isql app has been renamed, cannot use the copy command. H. Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
Re: [firebird-support] Isql copy command - anyone know how it works?
On 17/01/13 10:18, Mark Rotteveel wrote: Sounds like a major bug to me that it depends on 1) name of the executable and 2) the executable being on the path. Could be. Sounds like one anyway. Even worse, when I sym-linked isql-fb to isql, and tried again: SQL copy test my_test; Statement failed, SQLSTATE = 28000 Your user name and password are not defined. Ask your database administrator to set up a Firebird login. After line 0 in file /tmp/fb_query_C8w4Oc Errors occurred (possibly duplicate domains) in creating MY_TEST in employee The temp file, is of course, blank when I try to view it to see what it was doing. I have not been able to get a setup where I can supply a user name and password. I expect that maybe defining ISC_USER and ISC_PASSWORD before I run isql might work. Yes indeed, that does work! But there has to be a way to pass username and password, after all, you wouldn't necessarily want to define those ever so slightly insecure variables just to use a copy command? I wouldn't! Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
[firebird-support] Re: How to connect using Hamachi?
--- In firebird-support@yahoogroups.com, W O wrote: Hello everybody Is it possible to connect to a database using Hamachi? Of course, the database is in another computer. In ISQL, which would be the connection string? yes you can , you need to put the [hamachi ip]:[database path] ahh also open the 3050 port to the hamachi ips http://www.linkedin.com/groupItem?view=gid=2078215type=memberitem=194307967commentID=108261345
Re: [firebird-support] Re: How to connect using Hamachi?
I *hate* Hamachi :) - Go to your Hamachi window - Select the customer and/or branch you want - Right click - Select Copy IPv4 address - Use that as your IP for connection. A On Thu, Jan 17, 2013 at 10:34 AM, mariuz mar...@mariuz.android-dev.rowrote: --- In firebird-support@yahoogroups.com, W O wrote: Hello everybody Is it possible to connect to a database using Hamachi? Of course, the database is in another computer. In ISQL, which would be the connection string? yes you can , you need to put the [hamachi ip]:[database path] ahh also open the 3050 port to the hamachi ips http://www.linkedin.com/groupItem?view=gid=2078215type=memberitem=194307967commentID=108261345 ++ 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 -- Mr. Andrea Raimondi Senior Software AnalystDeveloper [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
On 17-1-2013 4:35, fabianchocron wrote: What we are trying to achieve is to have a trigger that reads the size of each field and trims all insert sentences to the size of the field being inserted, and the reason is because the application receives the strings to insert on database from many different systems across the world and it is not aware of the current size of each field, the application's job is to receive data via internet and post it to the DB via insert command. So the App is doing what it was designed to do. The DB's fields size cannot be increased every time a new system across the world is connected to the application, and we cannot just increase field sizes to the maximum because it will slowdown the system's response. So we planned to use triggers to trim the input but it seems impossible. The alternative would be to let the App know the DB schema upfront, and deal with the issue at the App level, but we were under the impression the problem could be sorted via triggers, it appears it is not possible. Why do you need to let the app know the DB schema? Can't the application query the DB schema tables (RDB$*) itself to find out field lengths - e.g. once when the application starts?
Re: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
On 17-1-2013 9:14, karolbieniaszewski wrote: i do not know what do that system but why you need to truncate data provided by your customer? Think abut this system: 1. Customer can put an order for product and write in order info text: I need 200 red boots .. but only if you can send it to me in two days 2. your system store truncated data I need 200 red boots 3. and you send a request by week 4. what do customer - say i do not need this now, get it back Based on the thread so far I suspect this could indeed be a problem. However, in theory, the OP could have some error feedback/exception reporting mechanism in his application that rolls back the transaction on errors and reports back to the originating application if truncation is unacceptable... Going by the OP's posts, he thinks truncating data actually makes sense given his business needs - no idea if all data may be truncated or only some, etc.
[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
Hi Set,
The system we have is a protocol to connect and transfer data across multiple
different systems, and as each system has a different DB schema we planed the
solution reading from source DB and writing into the other DB without
restrictions, and left the trimming for the DB on each target system to handle.
It appears the idea was BAD, but using the intermediary table with triggers
after insert could end up being the solution we are after, so THANK YOU for
thinking outside the box and sharing it. I would have preferred to see a
trigger before insert handling the issue, but I understand why the DB currently
allocates the new and old fields based on the DB schema, and why it would be
too hard to try to write a smarted method to sort out this issue.
Regards
Fabian
--- In firebird-support@yahoogroups.com, Svein Erling Tysvær wrote:
Thank you, the problem is our plan was to have an App that DOES NOT need to
know the DB schema to work,
so the insert statements were planned to just post data to the DB and let
the DB handle the trimming.
That way the App is generic and works regardless of DB changes.
My idea of recompiling the FB client was to capture any insert statement
on the customers side, and
replace it with a trimmed insert statement. I don't see an issue with that.
I am not sure why you think
it is not possible. Alternatively we could capture the insert statements on
the server side, and
replace the original insert with a trimmed sentence using the DB schema to
know the size to use for
each field. I was hopping to be able to resolve it without a re-compile of
the DB engine, but it seems
I was wrong.
Regarding the DB doing it's job in checking and protecting against
arithmetic overflow, I still think
the way it is done is a Bug as it is actually checking the size of a field
that is NOT what is being
inserted because the trigger has actually changed already the size of the
field to be inserted, so the
DB is actually checking something that is irrelevant. I don't know if it was
designed to be that way
for a reason, or if it was something that could be re-designed to compare
the field actually being
inserted after all triggers have being applied. Under the current model,
what would happened if we have
a trigger before insert to update the field in a way that it does not feet
any-more into the target
table? The checking and validation was done before the trigger, so I guess
it did not pick up the issue
and who know what was actually inserted?
Hi Fabian!
I don't think telling the user that the field is too long to handle can be
considered a bug. Sure, it would be nice if Firebird had infinitely sized
buffers and didn't check the size until after the BEFORE INSERT trigger had
finished, but it doesn't. First, Firebird receives data, then BEFORE triggers
are executed, then the data is stored in the database. The error that you see
occurs before the BEFORE INSERT trigger.
Your problem is still solvable. Simply add another table. Let one table have
huge field lengths (CHAR/VARCHAR or BLOB SUBTYPE TEXT) and use this only for
imports, then use AFTER INSERT triggers to copy the first few characters of
field(s) to the other table (which you in turn use for querying). Variations
of this solution includes adding another field rather than table or even
another database ('another database' would probably be an overkill and more
difficult to implement in triggers).
HTH,
Set
Re: [firebird-support] Re: How to connect using Hamachi?
Thank you very much, that was my problem, the firewall was blocking the port 3051 (the one I was using there) Now, it works perfectly, thanks again. Greetings. Walter. On Thu, Jan 17, 2013 at 6:34 AM, mariuz mar...@mariuz.android-dev.rowrote: ** --- In firebird-support@yahoogroups.com, W O wrote: Hello everybody Is it possible to connect to a database using Hamachi? Of course, the database is in another computer. In ISQL, which would be the connection string? yes you can , you need to put the [hamachi ip]:[database path] ahh also open the 3050 port to the hamachi ips http://www.linkedin.com/groupItem?view=gid=2078215type=memberitem=194307967commentID=108261345 [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-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: How to connect using Hamachi?
Andrea, why you hate Hamachi? This is a good program, I think. The problem with the connection was the port blocked for the firewall. Greetings. Walter. On Thu, Jan 17, 2013 at 6:59 AM, Andrea Raimondi andrea.raimo...@gmail.comwrote: ** I *hate* Hamachi :) - Go to your Hamachi window - Select the customer and/or branch you want - Right click - Select Copy IPv4 address - Use that as your IP for connection. A On Thu, Jan 17, 2013 at 10:34 AM, mariuz mar...@mariuz.android-dev.ro wrote: --- In firebird-support@yahoogroups.com, W O wrote: Hello everybody Is it possible to connect to a database using Hamachi? Of course, the database is in another computer. In ISQL, which would be the connection string? yes you can , you need to put the [hamachi ip]:[database path] ahh also open the 3050 port to the hamachi ips http://www.linkedin.com/groupItem?view=gid=2078215type=memberitem=194307967commentID=108261345 ++ 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 -- Mr. Andrea Raimondi Senior Software AnalystDeveloper [Non-text portions of this message have been removed] [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-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
The application is actually a protocol and it does not have permission to read the db schema. Think it as an object, the DB schema is private, not accessible to the App. And the App should not need to know the DB Schema as it is a generic protocol, it transfers data across from sender to receiver, without knowing the size of each field on the recipient's DB. Furthermore, the App does not even know if there is a DB on the other side, all it knows is that it is agreed between sender and receiver to use SQL language, insert sentence, to send data to the receiver inside the insert statement. I know everybody assumed there are a lot of normal things that should apply, such as the developer knowing the DB schema, but unfortunately this is a very generic application, used across the world by thousands of different companies, with different languages, platforms, DB engines, etc. Some recipient would just have a plain text file to save the data, but use the SQL format to communicate, even when there is not SQL engine there, just a program parsing the insert statement and getting the strings and other data types into a disk file. The sad thing is that we could achieve the results if we bypass Firebird and just save the data into txt files on disk, but we are not going to go that way thanks to the brilliant idea of using a temporary table with BLOBS and transmitting back to the real table via trigger after insert. Cheers, Fabian --- In firebird-support@yahoogroups.com, Reinier Olislagers wrote: On 17-1-2013 4:35, fabianchocron wrote: What we are trying to achieve is to have a trigger that reads the size of each field and trims all insert sentences to the size of the field being inserted, and the reason is because the application receives the strings to insert on database from many different systems across the world and it is not aware of the current size of each field, the application's job is to receive data via internet and post it to the DB via insert command. So the App is doing what it was designed to do. The DB's fields size cannot be increased every time a new system across the world is connected to the application, and we cannot just increase field sizes to the maximum because it will slowdown the system's response. So we planned to use triggers to trim the input but it seems impossible. The alternative would be to let the App know the DB schema upfront, and deal with the issue at the App level, but we were under the impression the problem could be sorted via triggers, it appears it is not possible. Why do you need to let the app know the DB schema? Can't the application query the DB schema tables (RDB$*) itself to find out field lengths - e.g. once when the application starts?
Re: [firebird-support] Re: How to connect using Hamachi?
Because every time I need to use it, my computer(quad core, W7) slows down horribly. The program works well as far as I can tell, but I can't help it, I loathe having to turn connections to customers on just because of the slowness I experience. Most probably, this has to do with the many things running on my machine, I am not putting the blame for it on the software, but really... can't help it :) On Thu, Jan 17, 2013 at 11:56 AM, W O sistemas2000profesio...@gmail.comwrote: Andrea, why you hate Hamachi? This is a good program, I think. The problem with the connection was the port blocked for the firewall. Greetings. Walter. On Thu, Jan 17, 2013 at 6:59 AM, Andrea Raimondi andrea.raimo...@gmail.comwrote: ** I *hate* Hamachi :) - Go to your Hamachi window - Select the customer and/or branch you want - Right click - Select Copy IPv4 address - Use that as your IP for connection. A On Thu, Jan 17, 2013 at 10:34 AM, mariuz mar...@mariuz.android-dev.ro wrote: --- In firebird-support@yahoogroups.com, W O wrote: Hello everybody Is it possible to connect to a database using Hamachi? Of course, the database is in another computer. In ISQL, which would be the connection string? yes you can , you need to put the [hamachi ip]:[database path] ahh also open the 3050 port to the hamachi ips http://www.linkedin.com/groupItem?view=gid=2078215type=memberitem=194307967commentID=108261345 ++ 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 -- Mr. Andrea Raimondi Senior Software AnalystDeveloper [Non-text portions of this message have been removed] [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 -- Mr. Andrea Raimondi Senior Software AnalystDeveloper [Non-text portions of this message have been removed]
[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
Mark Thank you, I understand the need for the DB to return to the requester the exception but you have to admit from the high level perspective it sounds ridiculous to report an error on data that has not yet being prepared by the triggers. After all what's the logic in validating raw data when we know there are triggers waiting to massage the data before posting it to the engine? Even if the raw data is valid, the triggers could then modify it and make it non-compliant so from the high level point of view it seems the validation process is not efficient, it is effective but is checks things before there are ready. Now from the Firebird's developer perspective, I do understand 100% they need to allocate the memory for a variable and that variable needs to be inherited from somewhere. Perhaps in the future this issue will come to a head in some developers meeting and a solution would be found. If you think it breaking the problem by level, it is easier to see the issue, let's think it this way: 1) Level Requester, where the insert statement is created, or App. 2) Level Massaging where the triggers occur before being ready to pass to the data saving process. 3) Level Archiving, where the DB validates and either saves or returns error to requester. The key is understanding that in 1) and 2) there is still change occurring while 3) is a black or white outcome, where either the request can be inserted or it is illegal and fails. At the moment the DB is returning an error on a request that has being posted by 1) but not being processed by 2), so it seems inefficient to report on an issue before allowing 2) to take care of it. Cheers Fabian --- In firebird-support@yahoogroups.com, Mark Rotteveel wrote: On Wed, 16 Jan 2013 04:36:36 -, fabianchocron wrote: Hi All, We are trying to resolve an issue caused by the Application inserting a string larger than the database field size. The planned solution was to add a trigger before insert as follows: new.string = substring(new.string from 1 for 100); We did not modify the application because we don't have at this stage the ability to recompile it without a massive effort. For some reason that we do not understand the trigger is not working as desired, and the insert fails with an arithmetic overflow or string truncation error. Is is possible that Firebird 2.1 64 bits has a bug and we may need to upgrade to resolve this issue? Or are we doing something wrong? This is intentional behaviour (I believe it is even specified in the SQL specification, but not 100% sure on that). If you submit data longer than can be stored, it will result in an exception because otherwise it would result in loss of data. Only the client program would know how to deal with this. A workaround would be to use an updatable view and handle the transformation in the view trigger, however you can still in run into problems when you exceed the maximum CHAR or VARCHAR lengths. Mark
[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
--- In firebird-support@yahoogroups.com, fabianchocron wrote: Mark Thank you, I understand the need for the DB to return to the requester the exception but you have to admit from the high level perspective it sounds ridiculous to report an error on data that has not yet being prepared by the triggers. After all what's the logic in validating raw data when we know there are triggers waiting to massage the data before posting it to the engine? Even if the raw data is valid, the triggers could then modify it and make it non-compliant so from the high level point of view it seems the validation process is not efficient, it is effective but is checks things before there are ready. Now from the Firebird's developer perspective, I do understand 100% they need to allocate the memory for a variable and that variable needs to be inherited from somewhere. Perhaps in the future this issue will come to a head in some developers meeting and a solution would be found. If you think it breaking the problem by level, it is easier to see the issue, let's think it this way: 1) Level Requester, where the insert statement is created, or App. 2) Level Massaging where the triggers occur before being ready to pass to the data saving process. 3) Level Archiving, where the DB validates and either saves or returns error to requester. The key is understanding that in 1) and 2) there is still change occurring while 3) is a black or white outcome, where either the request can be inserted or it is illegal and fails. At the moment the DB is returning an error on a request that has being posted by 1) but not being processed by 2), so it seems inefficient to report on an issue before allowing 2) to take care of it. Cheers Fabian I have a good mood today so here is a solution to your problem ;-) CREATE TABLE REAL_TABLE ( REAL_FIELD VARCHAR(60) ); CREATE TABLE AAA ( XXX BLOB SUB_TYPE TEXT ); CREATE TRIGGER TRIG_BI_AAA FOR AAA ACTIVE BEFORE INSERT POSITION 0 AS BEGIN INSERT INTO REAL_TABLE(REAL_FIELD) VALUES(SUBSTRING(NEW.XXX FROM 1 FOR 60)); NEW.BLOB = NULL; DELETE FROM AAA; END; Regards, Karol Bieniaszewski
RE: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
Mark Thank you, I understand the need for the DB to return to the requester the exception but you have to admit from the high level perspective it sounds ridiculous to report an error on data that has not yet being prepared by the triggers. After all what's the logic in validating raw data when we know there are triggers waiting to massage the data before posting it to the engine? Even if the raw data is valid, the triggers could then modify it and make it non-compliant so from the high level point of view it seems the validation process is not efficient, it is effective but is checks things before there are ready. Now from the Firebird's developer perspective, I do understand 100% they need to allocate the memory for a variable and that variable needs to be inherited from somewhere. Perhaps in the future this issue will come to a head in some developers meeting and a solution would be found. If you think it breaking the problem by level, it is easier to see the issue, let's think it this way: 1) Level Requester, where the insert statement is created, or App. 2) Level Massaging where the triggers occur before being ready to pass to the data saving process. 3) Level Archiving, where the DB validates and either saves or returns error to requester. The key is understanding that in 1) and 2) there is still change occurring while 3) is a black or white outcome, where either the request can be inserted or it is illegal and fails. At the moment the DB is returning an error on a request that has being posted by 1) but not being processed by 2), so it seems inefficient to report on an issue before allowing 2) to take care of it. Cheers Fabian Hi Fabian I would go with stored procedure with input parameter 32K. Inside stored procedure, you can do whatever you want. Or am i missing something ? Regards Bogdan [Non-text portions of this message have been removed]
[firebird-support] Re: Isql copy command - anyone know how it works?
--- In firebird-support@yahoogroups.com, Mark Rotteveel wrote: On Thu, 17 Jan 2013 10:15:16 +, Norman Dunbar wrote: AHA My gut feeling was right, it is shelling out. This is a nuisance then, as it means that people on systems where the isql app has been renamed, cannot use the copy command. H. Sounds like a major bug to me that it depends on 1) name of the executable and 2) the executable being on the path. A proposal on Firebird-devel has been made i think is time some of our tools change it's names and enter the fb-* prefix namespace fb-sql, fb-dump, fb-security, fb-fixup, fb-stat, fb-backup, fb-qli, fb-preprocess, fb-split, fb-qli http://firebird.1100200.n4.nabble.com/Firebird-3-time-to-rename-conflict-names-td4632263.html
[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
--- In firebird-support@yahoogroups.com, Bogdan wrote: Mark Thank you, I understand the need for the DB to return to the requester the exception but you have to admit from the high level perspective it sounds ridiculous to report an error on data that has not yet being prepared by the triggers. After all what's the logic in validating raw data when we know there are triggers waiting to massage the data before posting it to the engine? Even if the raw data is valid, the triggers could then modify it and make it non-compliant so from the high level point of view it seems the validation process is not efficient, it is effective but is checks things before there are ready. Now from the Firebird's developer perspective, I do understand 100% they need to allocate the memory for a variable and that variable needs to be inherited from somewhere. Perhaps in the future this issue will come to a head in some developers meeting and a solution would be found. If you think it breaking the problem by level, it is easier to see the issue, let's think it this way: 1) Level Requester, where the insert statement is created, or App. 2) Level Massaging where the triggers occur before being ready to pass to the data saving process. 3) Level Archiving, where the DB validates and either saves or returns error to requester. The key is understanding that in 1) and 2) there is still change occurring while 3) is a black or white outcome, where either the request can be inserted or it is illegal and fails. At the moment the DB is returning an error on a request that has being posted by 1) but not being processed by 2), so it seems inefficient to report on an issue before allowing 2) to take care of it. Cheers Fabian Hi Fabian I would go with stored procedure with input parameter 32K. Inside stored procedure, you can do whatever you want. Or am i missing something ? Regards Bogdan Hi, If he can use stored procedure then i believe better is to change client side logic i understand him previously that customer provide him whole insert statement and changing something now in application logic is problematic with helper table and trigger (as i previously posted) this is simple to fix on db side Regards, Karol Bieniaszewski
Re: [firebird-support] Re: How to connect using Hamachi?
Ok, good point. I had not see slowness in the computers but maybe when there are too many connected that happens. Greetings. Walter. On Thu, Jan 17, 2013 at 8:01 AM, Andrea Raimondi andrea.raimo...@gmail.comwrote: ** Because every time I need to use it, my computer(quad core, W7) slows down horribly. The program works well as far as I can tell, but I can't help it, I loathe having to turn connections to customers on just because of the slowness I experience. Most probably, this has to do with the many things running on my machine, I am not putting the blame for it on the software, but really... can't help it :) On Thu, Jan 17, 2013 at 11:56 AM, W O sistemas2000profesio...@gmail.com wrote: Andrea, why you hate Hamachi? This is a good program, I think. The problem with the connection was the port blocked for the firewall. Greetings. Walter. On Thu, Jan 17, 2013 at 6:59 AM, Andrea Raimondi andrea.raimo...@gmail.comwrote: ** I *hate* Hamachi :) - Go to your Hamachi window - Select the customer and/or branch you want - Right click - Select Copy IPv4 address - Use that as your IP for connection. A On Thu, Jan 17, 2013 at 10:34 AM, mariuz mar...@mariuz.android-dev.ro wrote: --- In firebird-support@yahoogroups.com, W O wrote: Hello everybody Is it possible to connect to a database using Hamachi? Of course, the database is in another computer. In ISQL, which would be the connection string? yes you can , you need to put the [hamachi ip]:[database path] ahh also open the 3050 port to the hamachi ips http://www.linkedin.com/groupItem?view=gid=2078215type=memberitem=194307967commentID=108261345 ++ 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 -- Mr. Andrea Raimondi Senior Software AnalystDeveloper [Non-text portions of this message have been removed] [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 -- Mr. Andrea Raimondi Senior Software AnalystDeveloper [Non-text portions of this message have been removed] [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-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Re: How to connect using Hamachi?
On Thu, 17 Jan 2013 12:01:26 +, Andrea Raimondi andrea.raimo...@gmail.com wrote: Because every time I need to use it, my computer(quad core, W7) slows down horribly. I have never had that experience, but another reason for not liking Hamachi would be, that they are using non-private IP range, that is actually in use at Internet. It took me some time to figure out, why I could not open www.thinkwiki.org website. -- Virgo Pärna virgo.pa...@mail.ee ++ 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] value exceeds the range for valid timestamps
Hello, today I have created some records in a table. Everything works fine. Now, I have open the table again and now I get some error messages while open the records. Timestamps until 2007xxx works fine, bit values after works not. The same error I get with gbak. Now I have deleted the timestamps of my last records, no chance, the same error. If I select all records without this column, it works fine. I use firebird 2.5 cs. Has anyone an idea? Best regards. Olaf [Non-text portions of this message have been removed]
[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
Hi Karol THANK YOU VERY VERY MUCH - Muchas Gracias Obrigado! We are going to apply this solution you suggested, we need to do so for every field in the protocol so it will be a bit more generic and complex, but the concept is what matters, and this also shows that thinking outside the box is the key. Cheers, Fabian --- In firebird-support@yahoogroups.com, karolbieniaszewski wrote: --- In firebird-support@yahoogroups.com, fabianchocron wrote: Mark Thank you, I understand the need for the DB to return to the requester the exception but you have to admit from the high level perspective it sounds ridiculous to report an error on data that has not yet being prepared by the triggers. After all what's the logic in validating raw data when we know there are triggers waiting to massage the data before posting it to the engine? Even if the raw data is valid, the triggers could then modify it and make it non-compliant so from the high level point of view it seems the validation process is not efficient, it is effective but is checks things before there are ready. Now from the Firebird's developer perspective, I do understand 100% they need to allocate the memory for a variable and that variable needs to be inherited from somewhere. Perhaps in the future this issue will come to a head in some developers meeting and a solution would be found. If you think it breaking the problem by level, it is easier to see the issue, let's think it this way: 1) Level Requester, where the insert statement is created, or App. 2) Level Massaging where the triggers occur before being ready to pass to the data saving process. 3) Level Archiving, where the DB validates and either saves or returns error to requester. The key is understanding that in 1) and 2) there is still change occurring while 3) is a black or white outcome, where either the request can be inserted or it is illegal and fails. At the moment the DB is returning an error on a request that has being posted by 1) but not being processed by 2), so it seems inefficient to report on an issue before allowing 2) to take care of it. Cheers Fabian I have a good mood today so here is a solution to your problem ;-) CREATE TABLE REAL_TABLE ( REAL_FIELD VARCHAR(60) ); CREATE TABLE AAA ( XXX BLOB SUB_TYPE TEXT ); CREATE TRIGGER TRIG_BI_AAA FOR AAA ACTIVE BEFORE INSERT POSITION 0 AS BEGIN INSERT INTO REAL_TABLE(REAL_FIELD) VALUES(SUBSTRING(NEW.XXX FROM 1 FOR 60)); NEW.BLOB = NULL; DELETE FROM AAA; END; Regards, Karol Bieniaszewski
Re: [firebird-support] Interbase/Firebird interaction?
On 1/17/2013 3:45 PM, Leyne, Sean wrote: We have the following report from a user, who owns a copy of our AuctionTracker software: Background: AT uses IBObjects 4.8.7, and is built running against Firebird V2.2.1.17910. The normal installation requires a Firbird installation on the workstation. Our development environment is Delphi7. There is no such release as Firebird v2.2.x. The 2.x Firebird releases as 2.1.x or 2.5.x Sean Typo on my part. Meant FB2.1.1.17910. [Non-text portions of this message have been removed]
Re: [firebird-support] Interbase/Firebird interaction?
Em 17/1/2013 21:40, lcampbell escreveu: We have the following report from a user, who owns a copy of our AuctionTracker software: Background: AT uses IBObjects 4.8.7, and is built running against Firebird V2.2.1.17910. The normal installation requires a Firbird installation on the workstation. Our development environment is Delphi7. This user had an old DB app. that runs against Interbase; with an IB6.5 installation on his workstation. He installed AT on the workstation WITHOUT a Firebird Server installation. Apparently, AT starts, runs, finds its data and executes several processes with no problems in this environment. We're using the IBObjects TIBODatabase to connect, passing it the location of the .FDB database files, and calling its Connect method. Apparently, the underlying IBO code doesn't care if it's running against an IB server or an FB Server. We are working to verify this. This raises a couple of questions: 1) Are there any adverse conflicts between IB6.5 and Firebird in a situation like this ... i.e., would running AT against an IB6.5 engine ultimately damage the data? 2) Is there some way we can determine at startup time whether there is a Firbird engine running or an IB engine? We might want to ensure that AT is running in an environment served by Firebird only. Anybody feel free to jump in with info/advice Lane Campbell NW Software Well.. I know I am not answering your questions But.. I would install FB on a diferent port and run against it. I think it will save you any kind of trouble... The IB server could not understand the FB ODS, will not provide all the features, but, in the general case de cliente API is very similar. see you !
Re: [firebird-support] Interbase/Firebird interaction?
This raises a couple of questions: 1) Are there any adverse conflicts between IB6.5 and Firebird in a situation like this ... i.e., would running AT against an IB6.5 engine ultimately damage the data? I had some significant issues if I used FB to access an IB database file. It was imperative to backup the IB database and restore it as FB database. This might or might not be an issue for you if you distribute a prepared database file (with all tables, procedures, triggers etc. created); if you instead create the database file from scratch, then you should be OK. Also, some statements behave differently under IB and FB (e.g. raise an error in one and work normally in the other - some CASTs, I think, and perhaps AS used in certain situations). 2) Is there some way we can determine at startup time whether there is a Firbird engine running or an IB engine? We might want to ensure that AT is running in an environment served by Firebird only. It is definitely possible to detect a version of Firebird by querying various system tables (e.g. if RDB$GENERATORS has a RDB$DESCRIPTION field, then the database is at least FB 2.0). I suppose you could do the same for IB (check whether a field or table exists, which only exists in one of the engines). Josef ++ 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/
AW: [firebird-support] value exceeds the range for valid timestamps
Hi Sean, Olaf, today I have created some records in a table. Everything works fine. Now, I have open the table again and now I get some error messages while open the records. Timestamps until 2007xxx works fine, bit values after works not. Have you recently altered the datatype of the problem field/column or the related Domain? Sean No. But I have found two records in the middle with changed wrong timestamp. After I deleted the wrong entries, it works again. I was wondering why? I had just closed the database, what might have gone wrong? 200 Records, Record 80 and record 81 after closing with invalid timestamp. This records were not opened last session. Have a nice weekend. Best regards. Olaf [Non-text portions of this message have been removed]
Re: [firebird-support] Interbase/Firebird interaction?
Mi mandi il cell che la ringrazio Codebue Fabio P-Soft Il giorno 18/gen/2013, alle ore 07:35, Josef Kokeš j.ko...@apatykaservis.cz ha scritto: This raises a couple of questions: 1) Are there any adverse conflicts between IB6.5 and Firebird in a situation like this ... i.e., would running AT against an IB6.5 engine ultimately damage the data? I had some significant issues if I used FB to access an IB database file. It was imperative to backup the IB database and restore it as FB database. This might or might not be an issue for you if you distribute a prepared database file (with all tables, procedures, triggers etc. created); if you instead create the database file from scratch, then you should be OK. Also, some statements behave differently under IB and FB (e.g. raise an error in one and work normally in the other - some CASTs, I think, and perhaps AS used in certain situations). 2) Is there some way we can determine at startup time whether there is a Firbird engine running or an IB engine? We might want to ensure that AT is running in an environment served by Firebird only. It is definitely possible to detect a version of Firebird by querying various system tables (e.g. if RDB$GENERATORS has a RDB$DESCRIPTION field, then the database is at least FB 2.0). I suppose you could do the same for IB (check whether a field or table exists, which only exists in one of the engines). Josef ++ 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
