using DBIx::Class with native SQL query??

[easy]

I am a beginner to DBI in perl.

I would like some feature from DBIx::Class,
but also want to use native SQL query.

After reading some manual,
now I know only how to use
$schema->resultset('....')->search(....)
but the code is too hard to write and to read(understand).

for example, using mysql.
I would prefer to use
############################
my $rs = $some_obj->some_sub(
"SELECT p.id, p.name, c.id, c.name
FROM people p, company c
WHERE p.work_at = c.id");
my $obj = $rs->next();
print $obj->name;
#######################################################
is it possible?
or any other good module which can help?

tnaks a lot

 

[John]

I am a beginner to DBI in perl.

I would like some feature from DBIx::Class,
but also want to use native SQL query.

After reading some manual,
now I know only how to use
$schema->resultset('....')->search(....)
but the code is too hard to write and to read(understand).

for example, using mysql.
I would prefer to use
############################
my $rs = $some_obj->some_sub(
"SELECT p.id, p.name, c.id, c.name
FROM people p, company c
WHERE p.work_at = c.id");
my $obj = $rs->next();
print $obj->name;
#######################################################
is it possible?
or any other good module which can help?

tnaks a lot

Hi

I always begin with the following module
useBI;

The following is a routine I plucked from my site.

sub print_clients {

# send the obligatory Content-Type (2 LF's) and print the heading
print "Content-type: text/html\n\n";
print $heading;

my $table="clients";

my $dbh=MySQL_connection ();

my $sql = "SELECT ip,username,password,surname,city FROM $table";
$sth = $dbh->prepare($sql);
$sth->execute or die "Unable to execute query: $dbh->errstr\n";

print '<table border="1">';
my $row;
while ($row = $sth->fetchrow_hashref) {
my $ip=$row->{'ip'}; my $username=$row->{'username'}; my
$password=$row->{'password'};
my $surname=$row->{'surname'}; my $city=$row->{'city'};
print "<tr>";
print "<td>$ip</td> <td>$username</td> <td>$password</td>";
print "<td>$surname</td> <td>$city</td>";
print "</tr>";
}
print "</table>";

$sth->finish();
$dbh->disconnect();
print $lf . $hr;
return;
}

I think the above is as close to standard MySQL as you can get it and easy
to read.

Regards
John

 

[Robert Sedlacek]

I would like some feature from DBIx::Class,
but also want to use native SQL query.

IIRC there's no way to specify the whole SQL at the moment, but you can
use search_literal() to define the WHERE clause[1].

After reading some manual,
now I know only how to use
$schema->resultset('....')->search(....)
but the code is too hard to write and to read(understand).

It isn't that hard, though it might seem so at the beginning. A simple
explanation:

# connect your schema to the database
my $schema = Schema::Class->connect( $dsn, $user, $pass, ...);

# find your wanted resultset. a resultset represents a set of rows in
# the database.
my $resultset = $schema->resultset( 'Person' );

# search for users named Bob, this returns a new resultset. there is
# no query done unless we actually access the rows.
my $bobs = $resultset->search({ name => 'Bob' });

# here we print all bobs
print Dumper( { $u->get_columns } ) while my $u = $bobs->next;

If you need help, DBIC has a very active developer/user-community and
you can find specific help also on the DBIC mailinglist or on
#dbix-class on irc.perl.org.

hth, p

[1]
http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/ResultSet.pm#search_literal