[firebird-support] Re: Unicode text in Exception?
29.01.2015 19:15, Martijn Tonies wrote: 2) Create SP with EXCEPTION ERROR 'some Cyrillic string'; Done. In the source, I see the following: begin exception test _utf8 X'31D0A2D095D0A1D0A220D09DD09020D091D0AAD09BD093D090D0A0D0A1D09AD098'; end You don't need the _utf8 prefix with HEX string if you create SP in UTF8 connection. When I tried with _utf8, I got some weird results too. Without _utf8, everything worked fine. But I have a Cyrillic keyboard to write the error text properly ;-) Dmitry
[firebird-support] nesting - weight calculation
Hello, there is a table with all Material (stock), each one has a number. Now, in a second table there is listed the material that is a part of the Material. For example: Material ABC Material BCD Material XXX Material OMV Material KOM Material OMV can be a Part of XXX and XXX a Part of ABC, of course the BCD can assigned to XXX, OMV, ABC. Now I would like to know what is the total weight of the main material, (total weight from abc + amount of bcd + amount of xxx ABC has 2 XXX, XXX has 2 OMV Now we have 1 X ABC, 2 times XXX and 4 X OMV, OMV weighs 0,5kg and so on The structure Table Mainmaterial PartNo Description Table Material PartNo of Mainmaterial PartNoA = assigned Material Amount (of PartNoA) Is there a simpler statement possible than my stored procedure (recursive) Thank you.
RE: [firebird-support] nesting - weight calculation
there is a table with all Material (stock), each one has a number. Now, in a second table there is listed the material that is a part of the Material. For example: Material ABC Material BCD Material XXX Material OMV Material KOM Material OMV can be a Part of XXX and XXX a Part of ABC, of course the BCD can assigned to XXX, OMV, ABC. Now I would like to know what is the total weight of the main material, (total weight from abc + amount of bcd + amount of xxx ABC has 2 XXX, XXX has 2 OMV Now we have 1 X ABC, 2 times XXX and 4 X OMV, OMV weighs 0,5kg and so on The structure Table Mainmaterial PartNo Description Table Material PartNo of Mainmaterial PartNoA = assigned Material Amount (of PartNoA) Is there a simpler statement possible than my stored procedure (recursive) While, I don't see any reference to a weight field for the material... my answer would be: I don't see how you can avoid the SP, just minimize the number of times you use it. Store the weight in new Part_Weight table, trigger to tag TableMaterial changes (Changed = True) when change any of the sub-parts are made, and then a SP (which would be called before you run any analysis/report, and/or run on a scheduled basis) to recalculate the weights of any Part that have changed sub-parts. In this way you have the weights and only do the recursive SP when absolutely necessary. Sean
[firebird-support] Re: Unicode text in Exception?
27.01.2015 14:48, Martijn Tonies wrote: btw, in Firebird 3, the RDB$EXCEPTIONS.RDB$MESSAGE is still NONE. Would it make sense to encode the message according to the given connection character set? Do you mean changing RDB$EXCEPTIONS.RDB$MESSAGE to UTF8? It was discussed but ended nowhere. Now, I tried the following in the procedure to work around the above: exception test _utf8 'unicode string here'; Any idea why this won't work either? Or at least, not in my test? I tried without the _utf8 prefix and it worked: 1) Connect as UTF8 charset 2) Create SP with EXCEPTION ERROR 'some Cyrillic string'; 3) Validate that RDB$PROCEDURES.RDB$PROCEDURE_BLR (not RDB$PROCEDURE_SOURCE!) really contains a Unicode string 4) Connect as WIN1251, execute SP - error text is Cyrillic 5) Connect as WIN1250, execute SP - cannot transliterate character between character sets Dmitry
Re: [firebird-support] Re: Unicode text in Exception?
Hello Dmitry, btw, in Firebird 3, the RDB$EXCEPTIONS.RDB$MESSAGE is still NONE. Would it make sense to encode the message according to the given connection character set? Do you mean changing RDB$EXCEPTIONS.RDB$MESSAGE to UTF8? It was discussed but ended nowhere. No, I meant a runtime encoding when sending the text to the client. Or perhaps storing it as UNICODE_FSS, same as $SOURCE or $DESCRIPTIONs are stored? Now, I tried the following in the procedure to work around the above: exception test _utf8 'unicode string here'; Any idea why this won't work either? Or at least, not in my test? I tried without the _utf8 prefix and it worked: I'm trying this in Database Workbench, this works: select 'ТЕСТ НА БЪЛГАРСКИ' from rdb$database 1) Connect as UTF8 charset Yep. 2) Create SP with EXCEPTION ERROR 'some Cyrillic string'; Done. In the source, I see the following: begin exception test _utf8 X'31D0A2D095D0A1D0A220D09DD09020D091D0AAD09BD093D090D0A0D0A1D09AD098'; end 3) Validate that RDB$PROCEDURES.RDB$PROCEDURE_BLR (not RDB$PROCEDURE_SOURCE!) really contains a Unicode string In the BLR, I see: 0502040101000700029B110002028006 // 0454455354150F0400210031D0A2D095 // TEST ... ! then the same as above in hex D0A1D0A220D09DD09020D091D0AAD0 9BD093D090D0A0D0A1D09AD098 FF0E01020115070019014C 4) Connect as WIN1251, execute SP - error text is Cyrillic 5) Connect as WIN1250, execute SP - cannot transliterate character between character sets When executing the procedure, UTF8/WIN1251/WIN1250 connected, I get: exception 1 TEST 1ТЕСТ ÐРБЪЛГÐРСКИ At procedure 'TEST' line: 4, col: 3 This -could- be my error, but how do I verify? When I try in isql, I get the same. 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] nesting - weight calculation
There is at least a good alternative, Frank Ingermann showed how to do something very similar when calculating the amount of ingredients required when baking a marble cake at a Firebird conference a few years ago. He used a recursive CTE. Whether a recursive CTE is simpler than a recursive procedure or not depends partly on implementation, partly on what you mean by simple and partly on who you ask. I liked Franks solution. Set __ Hello, there is a table with all Material (stock), each one has a number. Now, in a second table there is listed the material that is a part of the Material. For example: Material ABC Material BCD Material XXX Material OMV Material KOM Material OMV can be a Part of XXX and XXX a Part of ABC, of course the BCD can assigned to XXX, OMV, ABC. Now I would like to know what is the total weight of the main material, (total weight from abc + amount of bcd + amount of xxx ABC has 2 XXX, XXX has 2 OMV Now we have 1 X ABC, 2 times XXX and 4 X OMV, OMV weighs 0,5kg and so on The structure Table Mainmaterial PartNo Description Table Material PartNo of Mainmaterial PartNoA = assigned Material Amount (of PartNoA) Is there a simpler statement possible than my stored procedure (recursive) Thank you.
Re: [firebird-support] Re: Unicode text in Exception?
Hello Dmitry, I'm still puzzled. 29.01.2015 19:15, Martijn Tonies wrote: 2) Create SP with EXCEPTION ERROR 'some Cyrillic string'; Done. In the source, I see the following: begin exception test _utf8 X'31D0A2D095D0A1D0A220D09DD09020D091D0AAD09BD093D090D0A0D0A1D09AD098'; end You don't need the _utf8 prefix with HEX string if you create SP in UTF8 The hex string is generated automagically when I connect with utf8, enter a string value with Cyrillic character and save the procedure. Upon reloading it from the database, it has this hex string. Without the prefix, the text is stored normal. connection. When I tried with _utf8, I got some weird results too. Without _utf8, everything worked fine. But I have a Cyrillic keyboard to write the error text properly ;-) I removed the _utf8 prefix so I only have the next string, connected with utf8/win1251/win1250 but I always get the same garbage returned. I'm doing this using DevArt IBDAC, but I get the same using isql. No clue why I can't get the correct text. 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!
