Another sql problem...

[meccaboy858]

Writing a CGI script that'll access my sqlplus database when I ask for a specific name. So I got it to work for the default table emp and I'm able to grab the info off of that table. But when I try to access my own table I get no love.

So I can get this part to work...

my $sth = $dbh->prepare("

select * from emp where upper(ENAME) = upper('$ename')

" );

$sth->execute()|| die "Oracle connection failed!";

while (my $search = $sth->fetchrow_hashref())
{
print "<TR>";
print "<TD>".$search->{EMPNO};
print "<TD>".$search->{ENAME};
print "<TD>".$search->{JOB};
print "<TD>".$search->{HIREDATE};
print "<TD>".$search->{SAL};
print "\n";
}

But when I change it up to this part it doesnt...

my $sth = $dbh->prepare("

select * from shelter where upper(sname) = upper('$sname')

" );

$sth->execute()|| die "Oracle connection failed!";


while (my $search = $sth->fetchrow_hashref())
{
print "<TR>";
print "<TD>".$search->{sname};
print "<TD>".$search->{city};
print "<TD>".$search->{pnumber};
print "\n";
}

I did a quick print at the top of my page to make sure that I'm passing the correct data and I have the right name's going through. Any suggestions? Thanks in advance..

 

[MrChad]

A few things to check.

1. Oracle is case-sensitive, so make sure your table and column name references are using the correct capitalization.
2. Double-check your connection string and make sure that you are connecting to the correct schema in your database.

 

[meccaboy858]

Originally posted by: MrChad
A few things to check.

1. Oracle is case-sensitive, so make sure your table and column name references are using the correct capitalization.
2. Double-check your connection string and make sure that you are connecting to the correct schema in your database.

I think I took care of the first with the upper(Column Name). But the second, sorry to sound like a newb, but whats a schema?

 

[MrChad]

UPPER(column name) does not take care of the case-sensitivity issue that I am describing. UPPER(column name) converts the all the values of the column name specified to upper case, in this case for comparison purposes in the WHERE clause. However, if your column name is SNAME and you try to check UPPER(sname), you will get an invalid column reference (since "sname" is not a valid column, but "SNAME" is).

Schemas are collections of objects in an Oracle database. Each schema has a user associated with it, and if you want to access the tables within a particular schema you must connect with the correct username and password. There's a decent explanation of schemas and how to query against them here. This article lists the default users/schemas that Oracle creates.

 

[meccaboy858]

Thanks!