Re: Column names when using selectall_arrayref
Hi Bill,
"Bill Moseley" <[EMAIL PROTECTED]> wrote:
How do I get the column names as a list to match the order of the
rows returned when using select/fetchall_arrayref [...] I'm not
having luck finding it in the docs.
See the "Statement Handle Attributes" section of the DBI docs:
http://search.cpan.org/~timb/DBI-1.50/DBI.pm#Statement_Handle_Attributes
Although I'm not sure if this could work for $dbh->selectall_arrayref(),
if you didn't mind preparing and executing the statement handle in
advance (and your backend database's DBD driver supports it -- MySQL
does), then you can use the NAME attribute of the statement handle to
get back the actual column names that will be returned in the query
result *before* you actually fetch any rows (but after you execute the
query) like this:
# assuming a little test db like this:
create table test (
id int not null,
name text,
age int,
primary key (id)
);
insert into test
(1, 'david', 39),
(2, 'goliath', 1764)
;
You can prepare your query and execute it but not fetch anything yet:
my $sth=$dbh->prepare("select * from test");
$sth->execute or die $sth->errstr;
and now the statement handle can tell you its ->{NAME}'s:
print Dumper($sth->{NAME});
as an arrayref of column names, in the order that they'll be returned
(once you fetch them):
$VAR1 = [
'id',
'name',
'age'
];
and here comes the fetched data:
print Dumper($sth->fetchall_arrayref)'
$VAR1 = [
'id',
'name',
'age'
];
$VAR1 = [
['39', 'david'], ['1764', 'goliath']
];
...and using an ARRAY slice?
d'oh! You like throwing those curve balls don't you?
I don't know the column names ahead of time -- I'm passed a query and
want to return the data in the column order specified in the query.
And also return the list of column names.
Okay well, now that you know the names in the query *before* you sliced
it up at fetch-time with an arrayref, so now you just need to slice the
NAME's arrayref the same way. If, for instance, you passed an arrayref
slice to get back the third and second columns, in that order:
my @names = @{$sth->{NAME}};
my @slice = (2,1);
print Dumper (@[EMAIL PROTECTED]);
$VAR1 = 'age';
$VAR2 = 'name';
the same way the fetched data got sliced:
print Dumper($sth->fetchall_arrayref([EMAIL PROTECTED]))'
$VAR1 = [
['39', 'david'],
['1764', 'goliath']
];
Hope this helps!
-dave
Re: Column names when using selectall_arrayref
On 5/3/06, Bill Moseley <[EMAIL PROTECTED]> wrote:
How do I get the column names as a list to match the order of the
rows returned when using select/fetchall_arrayref and using an ARRAY
slice? I'm not having luck finding it in the docs.
I don't know the column names ahead of time -- I'm passed a query and
want to return the data in the column order specified in the query.
And also return the list of column names.
Doesn't fetchall_arrayref return you a reference to an array of rows, each
row of which is itself an array. So, you fall back on $sth->{NAMES} for the
list of column names.
Your subject line only mentions selectall_arrayref - so maybe you're really
planning to use that. If so, you need to read the fine print about if
selectall_arrayref being passed a prepared statement handle - and then use
that and $sth->{NAMES} again.
--
Jonathan Leffler <[EMAIL PROTECTED]> #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Re: Column names have spaces in them!!!
Alexander Foken wrote: You could slowly migrate your system, a first step would be to make sure all table and column names match /^[A-Za-z][A-Za-z0-9_]+$/. Next, move the tables and the business logic to a "real" database. Then finally, get rid of ODBC drivers and Access on the clients and switch to a web frontend using a native driver like DBD::Oracle or DBD::Pg. That is what I am going to do. The guy that built the original had one foot out the door and the more I look at what he did the harder it is to keep from barfing. I am going to redo the schema a bit and just migrate the actual data over. We have an ent. license for Oracle so I may as well use it. Robert
Re: Column names have spaces in them!!!
On Wed, 1 Feb 2006 15:46:31 -0800, Darren Duncan wrote: Hi Darren > It would be quite natural for such users to make identifiers like > 'Person' and 'Home Address' and 'Home Telephone' and 'Work > Telephone' and so on; it isn't natural for them to say > 'Home_Telephone' and such. I agree. My document may not spell it out, but it's simply a statement of what I choose to limit myself to when I have control over the names. -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html
Re: Column names have spaces in them!!!
At 9:30 AM +1100 2/2/06, Ron Savage wrote: On Wed, 01 Feb 2006 13:36:41 +0100, Alexander Foken wrote: Right. But using a restrictive set of characters for table and column names makes things easier. "The column for the number of Just as I raved about back in 2003 :-): Now, I understand the arguments for limiting names to certain characters that don't need delimiting, and that's fine for some contexts. But I also wanted to make clear that there are some contexts where such limiting is counter productive, and so it is good for database engines to be flexible, regardless of how users decide to design databases. I considered it important to be able to support spaces and other arbitrary characters, because I see this support as making things easier to use from a non-programming user's point of view. Say we're making a consumer database application that is of the point-and-click GUI variety and has little to no structural abstraction (such as with Filemaker Pro or MS Access or perhaps Oracle Forms), where users can define their own tables and columns and such in the same ways they can define category names or folder or file names, where they just type the name into a text input box. These users would never see anything resembling programming code, such as SQL, but just know they're making a database. It would be quite natural for such users to make identifiers like 'Person' and 'Home Address' and 'Home Telephone' and 'Work Telephone' and so on; it isn't natural for them to say 'Home_Telephone' and such. So if we're making this consumer application and it is implemented on top of a generic SQL database, and assuming there is very little abstraction involved, we would probably be generating SQL code that lines up the names the users chose with identical table and column and such names, including the spaces. There would be no translation necessary since the identifiers in the database are the same as what the users see. Sure, we could impose naming restrictions on the users, so we can generate SQL without delimited identifiers, but then that would be making things more difficult for the users to make it easier for ourselves. And it isn't even that much extra work to support this, or it may be less work. From users' point of view, I see allowing this flexability to be like allowing them to name their file system files anything they want, and often they like to put spaces in file names. The users aren't creating programming code, there creating boxes to put their data in, conceptually not far off from folders and files. I'm also not suggesting that identifiers have to be arbitrarily long, like whole sentences; rather, just about the same lengths as are valid for folder or file names; just they should be allowed to contain spaces and such. -- Darren Duncan
Re: Column names have spaces in them!!!
On Wed, 01 Feb 2006 13:36:41 +0100, Alexander Foken wrote: Hi Alexander > Right. But using a restrictive set of characters for table and > column names makes things easier. "The column for the number of Just as I raved about back in 2003 :-): http://savage.net.au/Ron/html/naming-database-objects.html Now I'm moving towards Rose::DB, I think I'll be adopting plurals for table names, but apart from that... -- Cheers Ron Savage, [EMAIL PROTECTED] on 2/02/2006 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
Re: Re: Column names have spaces in them!!!
On Wed, Feb 01, 2006 at 02:08:32AM +0100, [EMAIL PROTECTED] wrote:
> use backticks instead of double quotes:
>
> my $sth=$dbh->prepare('select * from taskhours_per_date where `employee
> name`=?');
That's not portable.
The DBI has a $dbh->quote_identifier method to abstract this and
do-the-right-thing for the database being used.
Tim.
> Regards,
> Renee
>
> Am 31.01.2006 um 23:58 Uhr haben Sie geschrieben:
> > Alexander Foken wrote:
> > > You need to pass the quotes to the SQL engine. And by the way, you
> > > should either use parameters or the quote function for values:
> > >
> > > my $sth=$dbh->prepare('select * from taskhours_per_date where
> "employee
> > > name"=?');
> > > $sth->execute('NAME HERE');
> > >
> > > Maybe MS Acesss has other ways to do this, especially old Access
> > > versions have some very strange behaviours.
> > >
> > > Alexander
> >
> > Could it be that DBD::ODBC just cannot handle it? I tried it that way,
> I
> > tried it with brackets, backticks, double quotes, etc. and no go.
> >
> > I was hoping just to slap a small web frontend to it but I guess I
> will
> > go the heavier route and move it over into Oracle using my own schema.
> >
> > Thanks for the replies.
> >
> > Robert
> >
> >
> >
>
>
Re: Column names have spaces in them!!!
Darren Duncan wrote: At 9:44 AM +0100 2/1/06, Alexander Foken wrote: You could slowly migrate your system, a first step would be to make sure all table and column names match /^[A-Za-z][A-Za-z0-9_]+$/. Next, move the tables and the business logic to a "real" database. Then finally, get rid of ODBC drivers and Access on the clients and switch to a web frontend using a native driver like DBD::Oracle or DBD::Pg. Any "real" database can use all of the same identifiers that Access can, and in fact you can use any character at all in one. You just bound them in double-quotes everywhere they are referenced, at least with databases conforming to the SQL standard of delimited identifiers; some products may use alternate delimiters. Right. But using a restrictive set of characters for table and column names makes things easier. "The column for the number of hours worked for that day and that client, including phone support, but without travel time" may be a perfectly valid and very speaking column name, but "Hours_Worked" is shorter, easier to type, does not run easily into name length limits, and has no need for quotes. I do not propose to use stupid abbreviations like "hsw", but to use reasonably short and readable names (two to three words), using only letters and digits and the underscore instead of the space. In the original example, "EMPLOYEE_NAME" instead of "EMPLOYEE NAME" Chopping out the spaces and stuff is only crippling yourself. -- Darren Duncan I don't want to remove spaces, I want to replace them with a character that does not need quoting. Column and table names are identifiers for the database and the programmer, not pretty and nice names for the end user. Nice names and providing help is the job of the front end, not the job of the database. Alexander -- Alexander Foken mailto:[EMAIL PROTECTED] http://www.foken.de/alexander/
Re: Column names have spaces in them!!!
At 9:44 AM +0100 2/1/06, Alexander Foken wrote: You could slowly migrate your system, a first step would be to make sure all table and column names match /^[A-Za-z][A-Za-z0-9_]+$/. Next, move the tables and the business logic to a "real" database. Then finally, get rid of ODBC drivers and Access on the clients and switch to a web frontend using a native driver like DBD::Oracle or DBD::Pg. Any "real" database can use all of the same identifiers that Access can, and in fact you can use any character at all in one. You just bound them in double-quotes everywhere they are referenced, at least with databases conforming to the SQL standard of delimited identifiers; some products may use alternate delimiters. Chopping out the spaces and stuff is only crippling yourself. -- Darren Duncan
Re: Column names have spaces in them!!!
Robert Hicks wrote:
Alexander Foken wrote:
You need to pass the quotes to the SQL engine. And by the way, you
should either use parameters or the quote function for values:
my $sth=$dbh->prepare('select * from taskhours_per_date where
"employee name"=?');
$sth->execute('NAME HERE');
Maybe MS Acesss has other ways to do this, especially old Access
versions have some very strange behaviours.
Alexander
Could it be that DBD::ODBC just cannot handle it? I tried it that way,
I tried it with brackets, backticks, double quotes, etc. and no go.
I think the main problem here is MS Access. I've worked a lot with
DBD::ODBC, and except with Unicode data, I never had problems. You did
not tell us wich version of Access you are using, there are a lot of
differences between the various versions, older versions (like 2.0 or
95) are just plain pain, newer versions (like 2000) slowly evolved to
something comparable to an SQL database. Don't get me wrong, Access is a
nice frontend for a database, but its own "database" is nothing I would
use for more than 100 records in one or two tables.
I was hoping just to slap a small web frontend to it but I guess I
will go the heavier route and move it over into Oracle using my own
schema.
You can use Oracle and Access, just install the Oracle ODBC drivers onto
each client and use Access just for the forms. DON'T let Access handle
the business logic, this is something you definitly want to do on the
(Oracle) server. If you don't have an Oracle License, you could also use
the free (as in beer) MSDE, or the free (as in beer and speech) PostgreSQL.
You could slowly migrate your system, a first step would be to make sure
all table and column names match /^[A-Za-z][A-Za-z0-9_]+$/. Next, move
the tables and the business logic to a "real" database. Then finally,
get rid of ODBC drivers and Access on the clients and switch to a web
frontend using a native driver like DBD::Oracle or DBD::Pg.
Alexander
--
Alexander Foken
mailto:[EMAIL PROTECTED] http://www.foken.de/alexander/
Re: Re: Column names have spaces in them!!!
use backticks instead of double quotes:
my $sth=$dbh->prepare('select * from taskhours_per_date where `employee
name`=?');
Regards,
Renee
Am 31.01.2006 um 23:58 Uhr haben Sie geschrieben:
> Alexander Foken wrote:
> > You need to pass the quotes to the SQL engine. And by the way, you
> > should either use parameters or the quote function for values:
> >
> > my $sth=$dbh->prepare('select * from taskhours_per_date where
"employee
> > name"=?');
> > $sth->execute('NAME HERE');
> >
> > Maybe MS Acesss has other ways to do this, especially old Access
> > versions have some very strange behaviours.
> >
> > Alexander
>
> Could it be that DBD::ODBC just cannot handle it? I tried it that way,
I
> tried it with brackets, backticks, double quotes, etc. and no go.
>
> I was hoping just to slap a small web frontend to it but I guess I
will
> go the heavier route and move it over into Oracle using my own schema.
>
> Thanks for the replies.
>
> Robert
>
>
>
Re: Column names have spaces in them!!!
Alexander Foken wrote:
You need to pass the quotes to the SQL engine. And by the way, you
should either use parameters or the quote function for values:
my $sth=$dbh->prepare('select * from taskhours_per_date where "employee
name"=?');
$sth->execute('NAME HERE');
Maybe MS Acesss has other ways to do this, especially old Access
versions have some very strange behaviours.
Alexander
Could it be that DBD::ODBC just cannot handle it? I tried it that way, I
tried it with brackets, backticks, double quotes, etc. and no go.
I was hoping just to slap a small web frontend to it but I guess I will
go the heavier route and move it over into Oracle using my own schema.
Thanks for the replies.
Robert
Re: Column names have spaces in them!!!
Ugg!!
first coice is to rebuild the table
second choice
I found that wrapping the offending field name in [ ] worked with ODBC and
OLE but I am not sure how this will workd with DBI?
something like this
SELECT * FROM taskhours_per_date WHERE [EMPLOYEE NAME] = 'NAME HERE'
might work
option three
Create a number of select querries on the access DB without the the spaces,
Been years since I did this but I remember it was a very messy solution and
slow.
- Original Message -
From: "Robert Hicks" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, January 31, 2006 11:27 AM
Subject: Column names have spaces in them!!!
> I am using the ODBC module to talk to an Access database. In that
> database some of the column names have spaces in them (something like
> EMPLOYEE NAME).
>
> I have tried a bunch of stuff (backticking, brackets, braces, variables)
> but I cannot seem to do a select on that table.
>
> my $sth = $dbh->prepare("SELECT * FROM taskhours_per_date WHERE
> `EMPLOYEE NAME` = 'NAME HERE'" );
>
> How do I use a column name with spaces in it?!
>
> I cannot change the table name itself.
>
> Robert
>
Re: Column names have spaces in them!!!
You need to pass the quotes to the SQL engine. And by the way, you
should either use parameters or the quote function for values:
my $sth=$dbh->prepare('select * from taskhours_per_date where "employee
name"=?');
$sth->execute('NAME HERE');
Maybe MS Acesss has other ways to do this, especially old Access
versions have some very strange behaviours.
Alexander
Robert Hicks wrote:
I am using the ODBC module to talk to an Access database. In that
database some of the column names have spaces in them (something like
EMPLOYEE NAME).
I have tried a bunch of stuff (backticking, brackets, braces,
variables) but I cannot seem to do a select on that table.
my $sth = $dbh->prepare("SELECT * FROM taskhours_per_date WHERE
`EMPLOYEE NAME` = 'NAME HERE'" );
How do I use a column name with spaces in it?!
I cannot change the table name itself.
Robert
--
Alexander Foken
mailto:[EMAIL PROTECTED] http://www.foken.de/alexander/
Re: Column names have spaces in them!!!
I believe you want square-brackets for Access:
my $sth = $dbh->prepare("SELECT * FROM taskhours_per_date WHERE [EMPLOYEE NAME]
= ?");
Paul
Quoting Robert Hicks <[EMAIL PROTECTED]>:
> I am using the ODBC module to talk to an Access database. In that
> database some of the column names have spaces in them (something like
> EMPLOYEE NAME).
>
> I have tried a bunch of stuff (backticking, brackets, braces, variables)
> but I cannot seem to do a select on that table.
>
> my $sth = $dbh->prepare("SELECT * FROM taskhours_per_date WHERE
> `EMPLOYEE NAME` = 'NAME HERE'" );
>
> How do I use a column name with spaces in it?!
>
> I cannot change the table name itself.
>
> Robert
>
Re: Column Names for Oracle Cursor
On Mon, 4 Nov 2002 19:08:27 +0100 Georg Botorog <[EMAIL PROTECTED]> wrote: > Is there a way to get the column names for a cursor call instead of > ":B1", ":B2", etc? Those names are usually associated with bind variables, not columns. Some example code might make it easier for someone here to figure out what you are after. Most of the statement handle attributes (e.g. NAME, NUM_OF_FIELDS, and NUM_OF_PARAMS) should be available for cursors. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Column names and etc..
On Fri, 11 Jan 2002 09:07:15 -0500, Terrence Brannon wrote:
>> You can do (very portable):
>>
>> my $sth = $dbh->prepare("SELECT * FROM $table");
>
>its a shame that it is not just as portable to limit the result
>set to 1 row...
How about 0 rows?
my $sth = $dbh->prepare("SELECT * FROM $table where 1=0");
You still have to finish(), though, or otherwise flag DBI/the database
that there are no more records, like trying to retrieve a record.
--
Bart.
Re: Column names and etc..
Bart Lateur writes:
> [beginners list snipped]
>
> On Fri, 11 Jan 2002 11:40:03 +0100, Marius Keraitis wrote:
>
> >Second step is to show every table contents (by clicking on link with table
> >name).
> >To do this I need to know how many columns is in table, and all names of columns
> >in table.
>
> You can do (very portable):
>
> my $sth = $dbh->prepare("SELECT * FROM $table");
I would add WHERE 1=0 there to make sure that no actual rows are
fetched (although that may not work on all database servers it's a
common idiom).
Michael
--
Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler
[EMAIL PROTECTED] - [EMAIL PROTECTED]
International Sybase User Group - http://www.isug.com
Re: Column names and etc..
On 11 Jan 2002, at 9:07, Terrence Brannon wrote:
>
> On Friday, January 11, 2002, at 07:09 AM, Bart Lateur wrote:
>
> > [beginners list snipped]
> >
> > On Fri, 11 Jan 2002 11:40:03 +0100, Marius Keraitis wrote:
> >
> >> Second step is to show every table contents (by clicking on link
> >> with table name). To do this I need to know how many columns is in
> >> table, and all names of columns in table.
> >
> > You can do (very portable):
> >
> > my $sth = $dbh->prepare("SELECT * FROM $table");
>
> its a shame that it is not just as portable to limit the result
> set to 1 row...
I don't know how portable this one is, but doesn't
my $sth = $dbh->prepare("SELECT * FROM $table where 1=2");
set all the column metadata in $sth without returning any rows?
Bodo
Re: Column names and etc..
On Friday, January 11, 2002, at 07:09 AM, Bart Lateur wrote:
> [beginners list snipped]
>
> On Fri, 11 Jan 2002 11:40:03 +0100, Marius Keraitis wrote:
>
>> Second step is to show every table contents (by clicking on link
>> with table
>> name).
>> To do this I need to know how many columns is in table, and all
>> names of columns
>> in table.
>
> You can do (very portable):
>
> my $sth = $dbh->prepare("SELECT * FROM $table");
its a shame that it is not just as portable to limit the result
set to 1 row...
> $sth->execute;
> my @fieldnames = @{$sth->{NAME}};
> $sth->finish;
>
> Check the contents of @fieldnames.
>
> Check the other "statement handle attributes" in the DBI docs, such as
> "TYPE" and "NULLABLE" (instead of "NAME"), for more interesting
> properties.
>
> --
> Bart.
>
>
Re: Column names and etc..
[beginners list snipped]
On Fri, 11 Jan 2002 11:40:03 +0100, Marius Keraitis wrote:
>Second step is to show every table contents (by clicking on link with table
>name).
>To do this I need to know how many columns is in table, and all names of columns
>in table.
You can do (very portable):
my $sth = $dbh->prepare("SELECT * FROM $table");
$sth->execute;
my @fieldnames = @{$sth->{NAME}};
$sth->finish;
Check the contents of @fieldnames.
Check the other "statement handle attributes" in the DBI docs, such as
"TYPE" and "NULLABLE" (instead of "NAME"), for more interesting
properties.
--
Bart.
RE: Column names and etc..
Not a real Perl/Oracle hacker, but try if the select clause below produces the data you need: === select OWNER, TABLE_NAME, COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE from all_tab_columns order by OWNER, TABLE_NAME, COLUMN_ID = Pekka Siiskonen > -Original Message- > From: Marius Keraitis [mailto:[EMAIL PROTECTED]] > Sent: 11. tammikuuta 2002 12:40 > To: DBI Perl; Begginers Perl > Subject: Column names and etc.. > > > Hi, > > I have a problem with getting colunm count and their names. > > I have to get the list of all tables in the users account (Oracle). > This I do by selecting * from cat. > > Second step is to show every table contents (by clicking on > link with table > name). > To do this I need to know how many columns is in table, and > all names of columns > in table. > > If in Perl I could use the DESCRIBE commands there was no > problem, but this is > an Invalid statement..:( > > One idea is to use cursors procedure, but it's all I know > about this mistical > procedure..:) > > Can anyone help me? I'll be very gladfull.. > > MarioDaProgLamer. >
RE: Column Names
my %db;
$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
while ($sth->fetch)
{
#... and no worries about which order the columns get returned in
#... since you access them via the $db{ColumnName} method :)
Right, no worries, but a good point to make is that the hash keys are in an
array - so the order in which they are returned while doing that is always
in the order they returned by the query, so it is perfectly safe to do
something like this when the two tables have the same columns:
my $select = qq{SELECT * FROM Sometable};
my $selecth = $dbh1->prepare($select) || die "Can't
prepare\n$select\n$DBI::errstr\n"
$selecth->execute() || die "Can't execute\n$select\n$DBI::errstr\n";
$selecth->bind_columns(undef, \(@col{ @{$selecth->{NAME}}}));
my $insert = qq{INSERT INTO SomeOtherTable ($columnlist) VALUES (}
. '?' . '?' x $#col{ @{$selecth->{NAME}}} . ')';
my $inserth = $dbh2->prepare($insert) || die "Can't
prepare\n$insert\n$DBI::errstr";
while ($selecth->fetch) {
#do some manipulation if necessary
$inserth->execute(@col{ @{$selecth->{NAME}}}) || die "Can't execute
$insert: $DBI::errstr\n";
}
# or if you are making a pipe delimited file instead of inserting elsewhere:
while ($selecth->fetch) {
#do some manipulation if necessary
print outfile join('|', @{$selecth->{NAME}}}) . "\n";
}
Syntax untested in that example, but I use the principle sometimes. It gives
the advantage of the speed of bind_columns instead of fetchrow_hashref, and
the ability to access the columns by name, and it keeps all the columns in
order for the use in execute or print or whatever else might be useful. I
find it very slick when I need column names.
Steve H.
-Original Message-
From: Scott R. Godin [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 02, 2001 6:28 AM
To: [EMAIL PROTECTED]
Subject: Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Bart Lateur) wrote:
> On Thu, 01 Nov 2001 18:56:18 -0800, Venkataramana Mokkapati wrote:
>
> >How do I get column names and order of column names
> >for a "select * from ..." query.
>
> If you have
>
> $sth = $dbh->prepare("select * from ...");
>
> then try
>
> @column names = @{$sth->{NAME}};
>
> You may have to do an "execute" first, for this to return anything of
> value.
>
> It's in the DBI docs under the heading "Statement Handle Attributes", in
> the DBI POD formatted as text around line 2284.
the absolute neatest trick I've seen with this, that is so totally
perlish it defies description.. you stare at it for a bit and suddenly
all becomes clear.
$sth->execute
or die("Cannot Execute SQL Statement: ", $sth->errstr(), "\n");
my $rows = $sth->rows;
# only expecting one row for a unique ID . this should NEVER happen.
safe_error("invalid number of rows returned from database ($rows) for
ID $id")
if $rows > 1;
# although this might...
safe_error("no match in database for ID $id")
if $rows < 1;
my %db;
$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
while ($sth->fetch)
{
#... and no worries about which order the columns get returned in
#... since you access them via the $db{ColumnName} method :)
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: Column Names
On Fri, Nov 02, 2001 at 02:41:05PM -0500, Scott R. Godin wrote:
> In article <[EMAIL PROTECTED]>,
> [EMAIL PROTECTED] (Tim Bunce) wrote:
>
> > On Fri, Nov 02, 2001 at 02:18:15PM +0100, Bart Lateur wrote:
> > > On Fri, 02 Nov 2001 07:27:49 -0500, Scott R. Godin wrote:
> > >
> > > >my %db;
> > > >$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
> > > >
> > > >while ($sth->fetch)
> > > >{
> > > >#... and no worries about which order the columns get returned in
> > > >#... since you access them via the $db{ColumnName} method :)
> > >
> > > What's the advantage of this approach over
> > >
> > > while(my $db = fetchrow_hashref) {
> > > ...
> > > }
> > >
> > > and accessing the datae through $db->{ColumnName}?
> >
> > Speed! It's many times faster (assuming the loop is empty :)
> >
> > (But use $sth->{NAME_lc} or $sth->{NAME_uc} for portability.
> >
> > Tim.
>
> with the exception of my case where neither mod_perl nor Apache::DBI is
> compiled in..
The performance of bind_columns + fetchrow_arrayref vs fetchrow_hashref
is not related to mod_perl or Apache::DBI.
> in the php vs perl thread earlier this (last?) month, I posted some
> "benchmarks" done by the site admin on a search of 5100 rows for "c" by
> their ph script and my perl script.. the results were staggeringly
> different, even with the help of this (see the script I posted in that
> thread for details on what I was doing) the thread issues are posted
> here: <[EMAIL PROTECTED]>
Can you give me a url?
Tim.
Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Michael Peppler) wrote:
> > my $rows = $sth->rows;
> > # only expecting one row for a unique ID . this should NEVER happen.
> >safe_error("invalid number of rows returned from database ($rows) for
> > ID $id")
> > if $rows > 1;
> > # although this might...
> > safe_error("no match in database for ID $id")
> > if $rows < 1;
>
> Be careful here!
>
> Most DBI drivers will return -1 for $sth->rows() for a SELECT query.
=:o
> In the case of DBD::Sybase $h->rows() will return the correct number
> of rows only *after* all the rows have been fetched.
I guess I'm fortunate that DBD::'s CSV, AnyData, and mysql all work this
way.
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Tim Bunce) wrote:
> On Fri, Nov 02, 2001 at 02:18:15PM +0100, Bart Lateur wrote:
> > On Fri, 02 Nov 2001 07:27:49 -0500, Scott R. Godin wrote:
> >
> > >my %db;
> > >$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
> > >
> > >while ($sth->fetch)
> > >{
> > >#... and no worries about which order the columns get returned in
> > >#... since you access them via the $db{ColumnName} method :)
> >
> > What's the advantage of this approach over
> >
> > while(my $db = fetchrow_hashref) {
> > ...
> > }
> >
> > and accessing the datae through $db->{ColumnName}?
>
> Speed! It's many times faster (assuming the loop is empty :)
>
> (But use $sth->{NAME_lc} or $sth->{NAME_uc} for portability.
>
> Tim.
with the exception of my case where neither mod_perl nor Apache::DBI is
compiled in..
in the php vs perl thread earlier this (last?) month, I posted some
"benchmarks" done by the site admin on a search of 5100 rows for "c" by
their ph script and my perl script.. the results were staggeringly
different, even with the help of this (see the script I posted in that
thread for details on what I was doing) the thread issues are posted
here: <[EMAIL PROTECTED]>
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: Column Names
On Fri, Nov 02, 2001 at 02:18:15PM +0100, Bart Lateur wrote:
> On Fri, 02 Nov 2001 07:27:49 -0500, Scott R. Godin wrote:
>
> >my %db;
> >$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
> >
> >while ($sth->fetch)
> >{
> >#... and no worries about which order the columns get returned in
> >#... since you access them via the $db{ColumnName} method :)
>
> What's the advantage of this approach over
>
> while(my $db = fetchrow_hashref) {
> ...
> }
>
> and accessing the datae through $db->{ColumnName}?
Speed! It's many times faster (assuming the loop is empty :)
(But use $sth->{NAME_lc} or $sth->{NAME_uc} for portability.
Tim.
Re: Column Names
Scott R. Godin writes:
> the absolute neatest trick I've seen with this, that is so totally
> perlish it defies description.. you stare at it for a bit and suddenly
> all becomes clear.
>
>$sth->execute
> or die("Cannot Execute SQL Statement: ", $sth->errstr(), "\n");
>
> my $rows = $sth->rows;
> # only expecting one row for a unique ID . this should NEVER happen.
>safe_error("invalid number of rows returned from database ($rows) for
> ID $id")
> if $rows > 1;
> # although this might...
> safe_error("no match in database for ID $id")
> if $rows < 1;
Be careful here!
Most DBI drivers will return -1 for $sth->rows() for a SELECT query.
In the case of DBD::Sybase $h->rows() will return the correct number
of rows only *after* all the rows have been fetched.
See the DBI docs for details.
Michael
--
Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler
[EMAIL PROTECTED] - [EMAIL PROTECTED]
International Sybase User Group - http://www.isug.com
Re: Column Names
On Fri, 02 Nov 2001 07:27:49 -0500, Scott R. Godin wrote:
>my %db;
>$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
>
>while ($sth->fetch)
>{
>#... and no worries about which order the columns get returned in
>#... since you access them via the $db{ColumnName} method :)
What's the advantage of this approach over
while(my $db = fetchrow_hashref) {
...
}
and accessing the datae through $db->{ColumnName}?
I can see nothing but an alternative, but highly equivalent method.
--
Bart.
Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Bart Lateur) wrote:
> On Thu, 01 Nov 2001 18:56:18 -0800, Venkataramana Mokkapati wrote:
>
> >How do I get column names and order of column names
> >for a "select * from ..." query.
>
> If you have
>
> $sth = $dbh->prepare("select * from ...");
>
> then try
>
> @column names = @{$sth->{NAME}};
>
> You may have to do an "execute" first, for this to return anything of
> value.
>
> It's in the DBI docs under the heading "Statement Handle Attributes", in
> the DBI POD formatted as text around line 2284.
the absolute neatest trick I've seen with this, that is so totally
perlish it defies description.. you stare at it for a bit and suddenly
all becomes clear.
$sth->execute
or die("Cannot Execute SQL Statement: ", $sth->errstr(), "\n");
my $rows = $sth->rows;
# only expecting one row for a unique ID . this should NEVER happen.
safe_error("invalid number of rows returned from database ($rows) for
ID $id")
if $rows > 1;
# although this might...
safe_error("no match in database for ID $id")
if $rows < 1;
my %db;
$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
while ($sth->fetch)
{
#... and no worries about which order the columns get returned in
#... since you access them via the $db{ColumnName} method :)
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: Column Names
On Thu, 01 Nov 2001 18:56:18 -0800, Venkataramana Mokkapati wrote:
>How do I get column names and order of column names
>for a "select * from ..." query.
If you have
$sth = $dbh->prepare("select * from ...");
then try
@column_names = @{$sth->{NAME}};
You may have to do an "execute" first, for this to return anything of
value.
It's in the DBI docs under the heading "Statement Handle Attributes", in
the DBI POD formatted as text around line 2284.
--
Bart.
Re: Column Names
$sth->{NAME} returns a reference to an array of field names for each
column
e.g.
foreach my $column (@{ $sth->{NAME} }) {
print $column;
}
--
Simon Oliver
