Re: [firebird-support] Working with SQL (might be using loop)
On 4-10-2018 10:34, Vishal Tiwari vishuals...@yahoo.co.in
[firebird-support] wrote:
> I am trying to get below stuff using SQL only.
>
> 1. If I have any statement like "World is good enough to enjoy..." then
> in I need to get SQL out put as
> "WorlD IS GooD EnougH TO EnjoY..." that is first and last character of
> each word should be capital letter and rest should be in small letters.
These things are generally easier (and more efficient) to do in 'normal'
programming languages. Consider your options carefully before resorting
to doing this in SQL.
You probably can't do this with only SQL (or at least, I can't think of
an option that isn't at least extremely painful to write and hard to
understand, and I'm not sure that approach will even work).
Instead, you will need to use a Firebird 3 function (or a (selectable)
stored procedure, or a native UDF or UDR) to do this.
With a PSQL function, you'd be able to do something like:
create function capitalize_first_last_of_word(inputval varchar(100))
returns varchar(100)
as
declare outputval varchar(100) = '';
declare previousChar char(1) = ' ';
declare currentChar char(1);
declare nextChar char(1);
declare nextPosition integer;
declare stringLength integer;
declare function charAt(string varchar(100), charPos integer)
returns char(1)
as
begin
return substring(string from charPos for 1);
end
begin
stringLength = char_length(inputval);
if (stringLength is null or stringLength = 0) then
begin
-- preserve null or empty string
return inputval;
end
currentChar = charAt(inputVal, 1);
nextPosition = 2;
while (nextPosition <= stringLength) do
begin
nextChar = charAt(inputVal, nextposition);
outputVal = outputVal || case
when previousChar = ' ' or nextChar in (' ', '.') then
upper(currentChar)
else lower(currentChar)
end;
previousChar = currentChar;
currentChar = nextChar;
nextPosition = nextPosition + 1;
end
outputVal = outputVal || upper(currentChar);
return outputVal;
end
> 2. If I give any number like 007, 10002, 5645 then if the first digit of
> the integer value is 0 or 1 then a minus sign should be prefixed and if
> it is not 0 or 1 then it should prefix + sign. We can enter integer
> value in string format as well if needed. like -007, -10002, +5645...
Integers don't have prefixed zeroes, so these need to be strings.
Ignoring cases where you are passed a non-integer value, you can do
something like:
case
when left(numbervalue, 1) in ('0', '1') then '-'
else '+'
end || numbervalue
> 3. Also, I need to make odd position character in capital letter in a
> given statement like "World is good enough to enjoy..." should be
> output as "WoRlD Is GoOd EnUuGh To EnJoY..."
Similar as point 1, this would be pretty hard to do with pure SQL.
With a function, you can do something like:
create function capitalize_odd(inputval varchar(100))
returns varchar(100)
as
declare outputval varchar(100) = '';
declare currentChar char(1);
declare currentPosition integer = 1;
declare stringLength integer;
declare function charAt(string varchar(100), charPos integer) returns
char(1)
as
begin
return substring(string from charPos for 1);
end
begin
stringLength = char_length(inputval);
if (stringLength is null or stringLength = 0) then
begin
-- preserve null or empty string
return inputval;
end
while (currentPosition <= stringLength) do
begin
currentChar = charAt(inputval, currentPosition);
outputVal = outputVal || case mod(currentPosition, 2)
when 1 then upper(currentChar)
else lower(currentChar)
end;
currentPosition = currentPosition + 1;
end
return outputVal;
end
Note: I have not exhaustively tested these.
--
Mark Rotteveel
Re: [firebird-support] How many record can I fill in a table
Thank you very much to all
Re: [firebird-support] Privileges for generators in Firebird 3
> select gen_id(GEN_AGENDA_NRO, 287390 - gen_id(GEN_AGENDA_NRO , 0)) from > rdb$database An ugly hack, I agree, but works. >This does make you wonder if maybe RESTART WITH shouldn't fall under >USAGE instead of the ALTER privilege (or alternatively if using gen_id >with anything other than 0 or 1 shouldn't fall under the ALTER privilege). I agree also: a bit strange. Thanks. Aldo Caruso
Re: [firebird-support] How many record can I fill in a table
On 5-10-2018 18:42, m_brahi...@yahoo.fr [firebird-support] wrote: > I need to know please, what is the limit of record in a table with more > than a hundred fields in FB 3 without reaching the 64 KB maximum row > size. How many records my table must contains ? https://www.firebirdsql.org/en/firebird-technical-specifications/ says: Maximum size of one table ~18 TB Maximum number of rows per table> 2^40 Maximum row size64 KB So 18 TB / 64 KB => 18 * 1024^4 bytes / 64 * 1024 bytes => 18 * 1024^3 / 64 => 18 * 1024 * 1024^2 / 64 => 18 * 16 * 1024 * 1024 = 301,989,888 So, depending on the actual row size, somewhere between 300 million and 2^40 (1,099,511,627,776). Take this calculation with a grain of salt, there may be some additional overhead which reduces this to a lower number. Mark -- Mark Rotteveel
[firebird-support] Re: How many record can I fill in a table
Hi, from http://www.ibphoenix.com/resources/documents/general/doc_323 Maximum row size for user tables - 64KB.Count bytes. BLOB and ARRAY columns each cost 8 bytes to store the ID; VARCHARs, byte length + 2;CHARs, byte-length; SMALLINT, 2; INTEGER, FLOAT, DATE and TIME, 4; BIGINT, DOUBLE PRECISION and TIMESTAMP, 8; NUMERIC and DECIMAL, 4 or 8, depending on precision. Regards Pablo Sánchez On 10/5/18 1:42 PM, m_brahi...@yahoo.fr [firebird-support] wrote: > Hello, > > I need to know please, what is the limit of record in a table with more > than a hundred fields in FB 3 without reaching the 64 KB maximum row > size. How many records my table must contains ? > > Sorry if my question seems lazy. > > Thanks > > > >
[firebird-support] How many record can I fill in a table
Hello, I need to know please, what is the limit of record in a table with more than a hundred fields in FB 3 without reaching the 64 KB maximum row size. How many records my table must contains ? Sorry if my question seems lazy. Thanks
Re: [firebird-support] Privileges for generators in Firebird 3
On 5-10-2018 15:13, Aldo Caruso aldo.car...@argencasas.com [firebird-support] wrote: > Hello, > > I'm migrating from Firebird 2 to Firebird 3 > > When setting a generator, I get the following error: > > SET GENERATOR GEN_AGENDA_NRO to 287390; > > unsuccessful metadata update > SET GENERATOR GEN_AGENDA_NRO failed > no permission for ALTER access to GENERATOR GEN_AGENDA_NRO > > The same happens if I use the new syntax: > > ALTER SEQUENCE GEN_AGENDA_NRO RESTART WITH 287390; > > Reading documentation I found that there is a new SQL command for > granting privileges to metadata objects like sequences > > GRANT ALTER ANY > TO [USER | ROLE] | [WITH GRANT OPTION]; > > I'm a bit confused about how to specify the sequence > > Should I use > > GRANT ALTER ANY SEQUENCE TO ROLE > > or > > GRANT ALTER SEQUENCE TO ROLE From the release notes: """ - If the ANY option is used, the user will be able to perform any operation on any object - If the ANY option is absent, the user will be able to perform operations on the object only if he owns it """ So, the first will allow the user/role granted that privilege to alter **any** sequence, the second will allow the user/role to only alter sequences he owns. > Why isn't a way to define a specific sequence ? i.e. > > GRANT ALTER SEQUENCE TO ROLE Because that wasn't considered when this was implemented; consider filing an improvement request. There is a bit of an ugly hack that still allows you to do it while only having the USAGE privilege on the specific sequence: select gen_id(GEN_AGENDA_NRO, 287390 - gen_id(GEN_AGENDA_NRO , 0)) from rdb$database This does make you wonder if maybe RESTART WITH shouldn't fall under USAGE instead of the ALTER privilege (or alternatively if using gen_id with anything other than 0 or 1 shouldn't fall under the ALTER privilege). Mark -- Mark Rotteveel
[firebird-support] Privileges for generators in Firebird 3
Hello, I'm migrating from Firebird 2 to Firebird 3 When setting a generator, I get the following error: SET GENERATOR GEN_AGENDA_NRO to 287390; unsuccessful metadata update SET GENERATOR GEN_AGENDA_NRO failed no permission for ALTER access to GENERATOR GEN_AGENDA_NRO The same happens if I use the new syntax: ALTER SEQUENCE GEN_AGENDA_NRO RESTART WITH 287390; Reading documentation I found that there is a new SQL command for granting privileges to metadata objects like sequences GRANT ALTER ANY TO [USER | ROLE] | [WITH GRANT OPTION]; I'm a bit confused about how to specify the sequence Should I use GRANT ALTER ANY SEQUENCE TO ROLE or GRANT ALTER SEQUENCE TO ROLE Why isn't a way to define a specific sequence ? i.e. GRANT ALTER SEQUENCE TO ROLE Thanks in advance for any answer. Aldo Caruso ++ 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] Working with SQL (might be using loop)
It's not homework assignment. I always try to play with SQL and just got an
idea to deal with such tasks, as there are always new requirements come for
displaying report with different formats.
Mostly I try to get the output from SQL execution part itself rather than
stored procedure or from UI side.
It was just learning purpose i asked.
Thanks anyways.
With Best Regards.
Vishal
On Thursday, 4 October, 2018, 7:02:35 PM IST,
blackfalconsoftw...@outlook.com [firebird-support]
wrote:
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 NaidamastSr. Software Engineer
#yiv8023000987 #yiv8023000987 -- #yiv8023000987ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8023000987
#yiv8023000987ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8023000987
#yiv8023000987ygrp-mkp #yiv8023000987hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv8023000987 #yiv8023000987ygrp-mkp #yiv8023000987ads
{margin-bottom:10px;}#yiv8023000987 #yiv8023000987ygrp-mkp .yiv8023000987ad
{padding:0 0;}#yiv8023000987 #yiv8023000987ygrp-mkp .yiv8023000987ad p
{margin:0;}#yiv8023000987 #yiv8023000987ygrp-mkp .yiv8023000987ad a
{color:#ff;text-decoration:none;}#yiv8023000987 #yiv8023000987ygrp-sponsor
#yiv8023000987ygrp-lc {font-family:Arial;}#yiv8023000987
#yiv8023000987ygrp-sponsor #yiv8023000987ygrp-lc #yiv8023000987hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8023000987
#yiv8023000987ygrp-sponsor #yiv8023000987ygrp-lc .yiv8023000987ad
{margin-bottom:10px;padding:0 0;}#yiv8023000987 #yiv8023000987actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8023000987
#yiv8023000987activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8023000987
#yiv8023000987activity span {font-weight:700;}#yiv8023000987
#yiv8023000987activity span:first-child
{text-transform:uppercase;}#yiv8023000987 #yiv8023000987activity span a
{color:#5085b6;text-decoration:none;}#yiv8023000987 #yiv8023000987activity span
span {color:#ff7900;}#yiv8023000987 #yiv8023000987activity span
.yiv8023000987underline {text-decoration:underline;}#yiv8023000987
.yiv8023000987attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv8023000987 .yiv8023000987attach div a
{text-decoration:none;}#yiv8023000987 .yiv8023000987attach img
{border:none;padding-right:5px;}#yiv8023000987 .yiv8023000987attach label
{display:block;margin-bottom:5px;}#yiv8023000987 .yiv8023000987attach label a
{text-decoration:none;}#yiv8023000987 blockquote {margin:0 0 0
4px;}#yiv8023000987 .yiv8023000987bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8023000987
.yiv8023000987bold a {text-decoration:none;}#yiv8023000987 dd.yiv8023000987last
p a {font-family:Verdana;font-weight:700;}#yiv8023000987 dd.yiv8023000987last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8023000987
dd.yiv8023000987last p span.yiv8023000987yshortcuts
{margin-right:0;}#yiv8023000987 div.yiv8023000987attach-table div div a
{text-decoration:none;}#yiv8023000987 div.yiv8023000987attach-table
{width:400px;}#yiv8023000987 div.yiv8023000987file-title a, #yiv8023000987
div.yiv8023000987file-title a:active, #yiv8023000987
div.yiv8023000987file-title a:hover, #yiv8023000987 div.yiv8023000987file-title
a:visited {text-decoration:none;}#yiv8023000987 div.yiv8023000987photo-title a,
#yiv8023000987 div.yiv8023000987photo-title a:active, #yiv8023000987
div.yiv8023000987photo-title a:hover, #yiv8023000987
div.yiv8023000987photo-title a:visited {text-decoration:none;}#yiv8023000987
div#yiv8023000987ygrp-mlmsg #yiv8023000987ygrp-msg p a
span.yiv8023000987yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8023000987
.yiv8023000987green {color:#628c2a;}#yiv8023000987 .yiv8023000987MsoNormal
{margin:0 0 0 0;}#yiv8023000987 o {font-size:0;}#yiv8023000987
#yiv8023000987photos div {float:left;width:72px;}#yiv8023000987
#yiv8023000987photos div div {border:1px solid
#66;min-height:62px;overflow:hidden;width:62px;}#yiv8023000987
#yiv8023000987photos div label
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8023000987
#yiv8023000987reco-category {font-size:77%;}#yiv8023000987
#yiv8023000987reco-desc {font-size:77%;}#yiv8023000987 .yiv8023000987replbq
{margin:4px;}#yiv8023000987 #yiv8023000987ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv8023000987 #yiv8023000987ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8023000987
