table_info('','','','%') cannot return any types
I was recently confirming table_info special cases and discovered the case for getting table_types cannot work.
table_info('','','','%')
should return a list of table types but it returns a list of empty strings instead:
my @types = $h->tables('', '', '', '%');
print "all types:\n", join("xxx\n", @types), "\n";
# should output something like:
# "dbo"
# "INFORMATION_SCHEMA"
# "sys"
# and actually outputs:
xxx
xxx
It seems to be down to the following in DBI.pm:
sub tables {
my ($dbh, @args) = @_;
my $sth = $dbh->table_info(@args[0,1,2,3,4]) or return;
my $tables = $sth->fetchall_arrayref or return;
my @tables;
if ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR
# problem is missing 3 in the slice below
@tables = map { $dbh->quote_identifier( @{$_}[0,1,2] ) } @$tables;
}
My test case missed this because currently it is returning 3 values but they are all ''.
Adding 3 to the slice fixes the issue but unfortunately changes the data returned from the deprecated tables method which now returns values like this:
"master"."dbo"."DBD_ODBC_LOB_TEST"."TABLE"
instead of (before)
"master"."dbo"."DBD_ODBC_LOB_TEST"
table_info is ok because it returns a result set and not a set of values pushed through quote_identifier.
Any comments?
BTW, all examples were done with DBD::ODBC.
Martin
|
0 |
|
bohica
6/23/2015 8:12:16 AM
perl.dbi.dev
1960 articles. 
0 followers.
3 Replies
2190 Views
On Tue, Jun 23, 2015 at 09:12:16AM +0100, Martin J. Evans wrote:
> I was recently confirming table_info special cases and discovered the case for getting table_types cannot work.
>
> table_info('','','','%')
>
> should return a list of table types but it returns a list of empty strings instead:
>
> my @types = $h->tables('', '', '', '%');
> print "all types:\n", join("xxx\n", @types), "\n";
> # should output something like:
> # "dbo"
> # "INFORMATION_SCHEMA"
> # "sys"
> # and actually outputs:
> xxx
> xxx
>
> It seems to be down to the following in DBI.pm:
>
> sub tables {
> my ($dbh, @args) = @_;
> my $sth = $dbh->table_info(@args[0,1,2,3,4]) or return;
> my $tables = $sth->fetchall_arrayref or return;
> my @tables;
> if ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR
> # problem is missing 3 in the slice below
> @tables = map { $dbh->quote_identifier( @{$_}[0,1,2] ) } @$tables;
> }
>
> My test case missed this because currently it is returning 3 values but they are all ''.
>
> Adding 3 to the slice fixes the issue but unfortunately changes the data returned from the deprecated tables method which now returns values like this:
>
> "master"."dbo"."DBD_ODBC_LOB_TEST"."TABLE"
>
> instead of (before)
>
> "master"."dbo"."DBD_ODBC_LOB_TEST"
>
> table_info is ok because it returns a result set and not a set of values pushed through quote_identifier.
Thanks for the great analysis Martin.
> Any comments?
The tables('', '', '', '%') call is a special case so it seems
reasonable to handle it as a special case in the code.
Tim.
|
|
0 |
|
Tim
6/24/2015 12:24:11 PM
On 24/06/15 13:24, Tim Bunce wrote:
> On Tue, Jun 23, 2015 at 09:12:16AM +0100, Martin J. Evans wrote:
>> I was recently confirming table_info special cases and discovered the case for getting table_types cannot work.
>>
>> table_info('','','','%')
>>
>> should return a list of table types but it returns a list of empty strings instead:
>>
>> my @types = $h->tables('', '', '', '%');
>> print "all types:\n", join("xxx\n", @types), "\n";
>> # should output something like:
>> # "dbo"
>> # "INFORMATION_SCHEMA"
>> # "sys"
>> # and actually outputs:
>> xxx
>> xxx
>>
>> It seems to be down to the following in DBI.pm:
>>
>> sub tables {
>> my ($dbh, @args) = @_;
>> my $sth = $dbh->table_info(@args[0,1,2,3,4]) or return;
>> my $tables = $sth->fetchall_arrayref or return;
>> my @tables;
>> if ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR
>> # problem is missing 3 in the slice below
>> @tables = map { $dbh->quote_identifier( @{$_}[0,1,2] ) } @$tables;
>> }
>>
>> My test case missed this because currently it is returning 3 values but they are all ''.
>>
>> Adding 3 to the slice fixes the issue but unfortunately changes the data returned from the deprecated tables method which now returns values like this:
>>
>> "master"."dbo"."DBD_ODBC_LOB_TEST"."TABLE"
>>
>> instead of (before)
>>
>> "master"."dbo"."DBD_ODBC_LOB_TEST"
>>
>> table_info is ok because it returns a result set and not a set of values pushed through quote_identifier.
>
> Thanks for the great analysis Martin.
>
>> Any comments?
>
> The tables('', '', '', '%') call is a special case so it seems
> reasonable to handle it as a special case in the code.
>
> Tim.
>
diff --git a/DBI.pm b/DBI.pm
index a23bed8..6e0e592 100644
--- a/DBI.pm
+++ b/DBI.pm
@@ -1761,7 +1761,10 @@ sub _new_sth { # called by DBD::<drivername>::db::prepare)
my $sth = $dbh->table_info(@args[0,1,2,3,4]) or return;
my $tables = $sth->fetchall_arrayref or return;
my @tables;
- if ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR
+ if (defined($args[3]) && $args[3] eq '%' && # special case for tables('','','','%')
+ grep {defined($_) && $_ eq ''} @args[0,1,2]) {
+ @tables = map {$_->[3]} @$tables;
+ } elsif ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR
@tables = map { $dbh->quote_identifier( @{$_}[0,1,2] ) } @$tables;
}
else { # temporary old style hack (yeach)
adds a special case, passes DBI tests for me and passes my testing. There is no need to quote the type as the DB should accept what it passed out.
tables('','','','%') now returns (for my ODBC DB):
SYSTEM TABLE
TABLE
VIEW
and no other calls to tables should be affected.
Martin
|
|
0 |
|
bohica
6/25/2015 10:08:14 AM
On Thu, Jun 25, 2015 at 11:08:14AM +0100, Martin J. Evans wrote:
> On 24/06/15 13:24, Tim Bunce wrote:
> >
> >The tables('', '', '', '%') call is a special case so it seems
> >reasonable to handle it as a special case in the code.
>
> diff --git a/DBI.pm b/DBI.pm
> [...]
> adds a special case, passes DBI tests for me and passes my testing. There is no need to quote the type as the DB should accept what it passed out.
>
> tables('','','','%') now returns (for my ODBC DB):
> SYSTEM TABLE
> TABLE
> VIEW
>
> and no other calls to tables should be affected.
Great. Thanks Martin.
Tim.
|
|
0 |
|
Tim
6/25/2015 3:38:32 PM
Similar Artilces:
''''''''''''''''''''
Name: haznen Email: haznenatyahoodotcom Product: Gran Paradiso Alpha 8 Summary: '''''''''''''''''''' Comments: '''''''''''''''''''''''''''''''''''' Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 UGES/1.7.2.0 GranParadiso/3.0a1 From URL: http://www.mozilla.org/projects/granparadiso/ Note to readers: Hendrix gives...
'''''
Name: mario Email: ramar17atfastwebnetdotit Product: Gran Paradiso Alpha 2 Summary: ''''' Comments: K: Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a2) Gecko/20070206 GranParadiso/3.0a2 ...
Cast from type ''''DBNull'''' to type ''''String'''' is not valid.
How do I avoid getting this error: Cast from type ''''DBNull'''' to type ''''String'''' is not valid. I am trying to get values from the database into a form for updating using a Datareader, SQL is set to allow nulls for certain fields as they are not required but the only way I can get the form to display is by adding a space in SQL. Is there another way around this? Check if it is equal to DBNull.Value first and if it is, don't do the cast.Stanley Tan theSpoke Blog Where in the code does it need to go? I am using the following and it's the profile that isn't always requi...
'do' won't 'do' if '/'
Greetings to All from Au, Have a NetWare Perl 5.8.4 and wanted to tweak File\Spec\NW.pm to try and standardise on '/' separators. If I run a test script (t/uni/lower.t) with an unmodified NW.pm, it calls t/uni/case.pl, that, in case.pl, (when the path separators are '\'), the $file is '..\lib\unicore\To\Lower.pl' and the following code portion works, with $simple getting a returned table: sub casetest { my ($base, $spec, $func) = @_; my $file = File::Spec->catfile(File::Spec->catdir(File::Spec->updir, "lib", &qu...
'''
Name: L Lachowsky Email: e2brutus_10atyahoodotcom Product: Firefox Summary: ''' Comments: why duddn this surprise me... I change default server from IE to Mozilla, and then i get error messages. well, shud I decide to not get on my computer with Mozilla..or shud I risk security breeches with IE....hmmm at least i can get on the internet with IE Browser Details: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.5) Gecko/2008120122 Firefox/3.0.5 From URL: http://hendrix.mozilla.org/ Note to readers: Hendrix gives no expectation of a response to this fee...
'IN' Clause or 'OR'
Hello, [1]: select * from TABLEA where COL1 IN('value1','value2''valu3'....) [2]:select * from TABLEA where COL1 = 'value1' OR COL1= 'value2' OR COL1='valu3'. TABLEA is a huge table and it has non-clustered index on COL1. Among the above 2 queries, which query will give me the better performance or fast response and WHY? What is the difference between 'IN' and 'OR' clauses as for as Sybase Optimization is concerned. Which is the better one to be used on huge tables. Thanks. Mac An IN list is treated ...
'or' or 'union'
Hello I was just wondering, in general what is better to use, an 'or' clause in a select or a 'union' to join two selects together. Do both statements create work tables? Many thanks Alex I think OR will be better than union. because suppose u have 3 tables and using OR u can join table a and table b and table c so each table will have only one read. but using union you will join table a and table b and in another query of union u will use table (a or b) and table c so ur one read is more in union . Ramdas Alex Cheung wrote: > Hello > >...
'b'..'a'
Hello. I'm using defferent 5.6.0's for Win32. I wonder wheter following behaviour is intentional or not: d:\>perl -e "print 'b'..'c'" bc d:\>perl -e "print 'b'..'a'" bcdefghijklmnopqrstuvwxyz I expected empty list in latter case, like in perl -e "print 'bb'..'a'" <!ENTITY Vadim REALLIFE "Vadim V.Konovalov, St.Petersburg, Russia"> &Vadim; On Wed, Sep 20, 2000 at 03:14:41PM +0400, Konovalov, Vadim wrote: > Hello. > > I'm using defferent 5.6.0'...
'To', 'CC', & 'BCC'
Name: Dick Tracy Email: philipdottracyatoptusnetdotcomdotau Product: Thunderbird Summary: 'To', 'CC', & 'BCC' Comments: I have just started using Thunderbird and while I find it quite excellent- I suggest having a button to add addressees to 'BCC' as well. Currently each 'BCC' addressee has to be selected manually. I send e-mail to quite a number of people at a time and I do not wish to readily spread others addresses easily. Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.6) Gecko/20070725 Firefox/2.0....
'Value' should be between 'minimum' and 'maximum'.
I'm attempting to invoke with parameters: Dim thing2 As New mydelsubPBStep(AddressOf pbStep) Me.Invoke(thing2, "setMax", dt.Rows.Count) <---- this is where the error occurs the delegate looks like this: Private Delegate Sub mydelsubPBStep(ByVal mode As String, ByVal value As Integer) the function it's calling looks like this:Private Sub pbStep(ByVal mode As String, ByVal value As Integer) Select Case mode.ToLower Case "clear" ProgressBar1.Value = 0 Case "step" ProgressBar1.PerformStep() L...
EXEC sp_msforeachtable 'sp_spaceused ''?'''
What does the following SQL code mean? EXEC sp_msforeachtable 'sp_spaceused ''?'''Johan TheunissenMCPD, MCSE, MCTS BizTalk 2006==============================Please mark the most helpful reply/replies as "Answer". JohanNL:sp_msforeachtable This is the name of the SP that resides in the master database. This SP executes one or more commands for a table. JohanNL:sp_spaceused This is the command that you want to run for each of the tables residing in your current database. As you might have understood so far, that this is again a...
'returns' vs 'of'
I don't like the assignments of 'returns' and 'of'. I think it is easily confused. I've written foo (Int $x) returns Int in examples and nobody noticed. As formal documentation, that scans right as the outer perceived return value type. But no, it's supposed to be foo (Int $x) of Int instead. The returns keyword affects the implementation of the function, not the external contract. Yet, if returns is missing it uses the of value by default, but if the of is missing it does not give static type checking to the caller, rather than using the sam...
What should ''.split('') return?
Hi, what should ''.split('') return? The empty list, or a list with one null string? Moritz -- Moritz Lenz http://moritz.faui2k3.org/ | http://perl-6.de/ On Fri, Sep 19, 2008 at 05:58:59PM +0200, Moritz Lenz wrote: : Hi, : : what should ''.split('') return? The empty list, or a list with one null : string? Empty list would make more sense as a degenerate case. In 'a'.split('') we don't return the null strings before or after 'a', just ('a'). Larry Larry Wall wrote: > On Fri, Sep 19...
Operator '=' is not defined for type 'Char' and type 'Boolean'.
Hello, In my application when i click the button i'm getting this " Operator '=' is not defined for type 'Char' and type 'Boolean'. " But when i test it locally there is no problem at all!!!! Please tell me why this error is occuring...Thanks SwapnaPlease click "mark as answer" if this post helped you. swapnasamsonvarkey: Hello, In my application when i click the button i'm getting this " Operator '=' is not defined for type 'Char' and type 'Boolean'. " But when i test it lo...
Name: haznen Email: haznenatyahoodotcom Product: Gran Paradiso Alpha 8 Summary: '''''''''''''''''''' Comments: '''''''''''''''''''''''''''''''''''' Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 UGES/1.7.2.0 GranParadiso/3.0a1 From URL: http://www.mozilla.org/projects/granparadiso/ Note to readers: Hendrix gives...
'''''
Name: mario Email: ramar17atfastwebnetdotit Product: Gran Paradiso Alpha 2 Summary: ''''' Comments: K: Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a2) Gecko/20070206 GranParadiso/3.0a2 ...
Cast from type ''''DBNull'''' to type ''''String'''' is not valid.
How do I avoid getting this error: Cast from type ''''DBNull'''' to type ''''String'''' is not valid. I am trying to get values from the database into a form for updating using a Datareader, SQL is set to allow nulls for certain fields as they are not required but the only way I can get the form to display is by adding a space in SQL. Is there another way around this? Check if it is equal to DBNull.Value first and if it is, don't do the cast.Stanley Tan theSpoke Blog Where in the code does it need to go? I am using the following and it's the profile that isn't always requi...
'do' won't 'do' if '/'
Greetings to All from Au, Have a NetWare Perl 5.8.4 and wanted to tweak File\Spec\NW.pm to try and standardise on '/' separators. If I run a test script (t/uni/lower.t) with an unmodified NW.pm, it calls t/uni/case.pl, that, in case.pl, (when the path separators are '\'), the $file is '..\lib\unicore\To\Lower.pl' and the following code portion works, with $simple getting a returned table: sub casetest { my ($base, $spec, $func) = @_; my $file = File::Spec->catfile(File::Spec->catdir(File::Spec->updir, "lib", &qu...
'''
Name: L Lachowsky Email: e2brutus_10atyahoodotcom Product: Firefox Summary: ''' Comments: why duddn this surprise me... I change default server from IE to Mozilla, and then i get error messages. well, shud I decide to not get on my computer with Mozilla..or shud I risk security breeches with IE....hmmm at least i can get on the internet with IE Browser Details: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.5) Gecko/2008120122 Firefox/3.0.5 From URL: http://hendrix.mozilla.org/ Note to readers: Hendrix gives no expectation of a response to this fee...
'IN' Clause or 'OR'
Hello, [1]: select * from TABLEA where COL1 IN('value1','value2''valu3'....) [2]:select * from TABLEA where COL1 = 'value1' OR COL1= 'value2' OR COL1='valu3'. TABLEA is a huge table and it has non-clustered index on COL1. Among the above 2 queries, which query will give me the better performance or fast response and WHY? What is the difference between 'IN' and 'OR' clauses as for as Sybase Optimization is concerned. Which is the better one to be used on huge tables. Thanks. Mac An IN list is treated ...
'or' or 'union'
Hello I was just wondering, in general what is better to use, an 'or' clause in a select or a 'union' to join two selects together. Do both statements create work tables? Many thanks Alex I think OR will be better than union. because suppose u have 3 tables and using OR u can join table a and table b and table c so each table will have only one read. but using union you will join table a and table b and in another query of union u will use table (a or b) and table c so ur one read is more in union . Ramdas Alex Cheung wrote: > Hello > >...
'b'..'a'
Hello. I'm using defferent 5.6.0's for Win32. I wonder wheter following behaviour is intentional or not: d:\>perl -e "print 'b'..'c'" bc d:\>perl -e "print 'b'..'a'" bcdefghijklmnopqrstuvwxyz I expected empty list in latter case, like in perl -e "print 'bb'..'a'" <!ENTITY Vadim REALLIFE "Vadim V.Konovalov, St.Petersburg, Russia"> &Vadim; On Wed, Sep 20, 2000 at 03:14:41PM +0400, Konovalov, Vadim wrote: > Hello. > > I'm using defferent 5.6.0'...
'To', 'CC', & 'BCC'
Name: Dick Tracy Email: philipdottracyatoptusnetdotcomdotau Product: Thunderbird Summary: 'To', 'CC', & 'BCC' Comments: I have just started using Thunderbird and while I find it quite excellent- I suggest having a button to add addressees to 'BCC' as well. Currently each 'BCC' addressee has to be selected manually. I send e-mail to quite a number of people at a time and I do not wish to readily spread others addresses easily. Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.6) Gecko/20070725 Firefox/2.0....
'Value' should be between 'minimum' and 'maximum'.
I'm attempting to invoke with parameters: Dim thing2 As New mydelsubPBStep(AddressOf pbStep) Me.Invoke(thing2, "setMax", dt.Rows.Count) <---- this is where the error occurs the delegate looks like this: Private Delegate Sub mydelsubPBStep(ByVal mode As String, ByVal value As Integer) the function it's calling looks like this:Private Sub pbStep(ByVal mode As String, ByVal value As Integer) Select Case mode.ToLower Case "clear" ProgressBar1.Value = 0 Case "step" ProgressBar1.PerformStep() L...
EXEC sp_msforeachtable 'sp_spaceused ''?'''
What does the following SQL code mean? EXEC sp_msforeachtable 'sp_spaceused ''?'''Johan TheunissenMCPD, MCSE, MCTS BizTalk 2006==============================Please mark the most helpful reply/replies as "Answer". JohanNL:sp_msforeachtable This is the name of the SP that resides in the master database. This SP executes one or more commands for a table. JohanNL:sp_spaceused This is the command that you want to run for each of the tables residing in your current database. As you might have understood so far, that this is again a...
'returns' vs 'of'
I don't like the assignments of 'returns' and 'of'. I think it is easily confused. I've written foo (Int $x) returns Int in examples and nobody noticed. As formal documentation, that scans right as the outer perceived return value type. But no, it's supposed to be foo (Int $x) of Int instead. The returns keyword affects the implementation of the function, not the external contract. Yet, if returns is missing it uses the of value by default, but if the of is missing it does not give static type checking to the caller, rather than using the sam...
What should ''.split('') return?
Hi, what should ''.split('') return? The empty list, or a list with one null string? Moritz -- Moritz Lenz http://moritz.faui2k3.org/ | http://perl-6.de/ On Fri, Sep 19, 2008 at 05:58:59PM +0200, Moritz Lenz wrote: : Hi, : : what should ''.split('') return? The empty list, or a list with one null : string? Empty list would make more sense as a degenerate case. In 'a'.split('') we don't return the null strings before or after 'a', just ('a'). Larry Larry Wall wrote: > On Fri, Sep 19...
Operator '=' is not defined for type 'Char' and type 'Boolean'.
Hello, In my application when i click the button i'm getting this " Operator '=' is not defined for type 'Char' and type 'Boolean'. " But when i test it locally there is no problem at all!!!! Please tell me why this error is occuring...Thanks SwapnaPlease click "mark as answer" if this post helped you. swapnasamsonvarkey: Hello, In my application when i click the button i'm getting this " Operator '=' is not defined for type 'Char' and type 'Boolean'. " But when i test it lo...
Web resources about - table_info('','','','%') cannot return any types - perl.dbi.devResources last updated: 1/19/2016 12:49:48 PM