Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts

    Using PHP and Oracle

    I have php and apache installed on one server (windows 2000). We have a shared oracle database on another server (unix). I can't figure out how to query the database and just get some test data to reply back.

    Any tips? For starters, I don't understand the difference between the modules php_oci8.dll and php_oracle.dll so I don't know which one to use. I tried both of them at separate times and still couldn't get a response.

  • #2
    New to the CF scene
    Join Date
    Aug 2002
    Location
    Brussels
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If i'm not mistaking myself, there's a whole section on PHP-functionality with Oracle in the PHP Manual...
    Zeon

  • #3
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts

    Arrow

    I learn by example and the combination of php and oracle is not as common as things like mysql and php or asp and access. So it was quite difficult to track down any code. I think the complete lack of responses on this board proves that quite well (unless everyone thought I was just lazy due to your comment)

    I eventually found some help and ended up with a very basic query like this. Hopefully someone else finds it useful.

    <html>
    <body>
    <?
    $db = "servicename";
    $conn = OCILogon("fakeuser","fakepass", $db);

    $stmt = OCIParse($conn,"select id, name from test order by id");


    OCIDefinebyname($stmt,"ID",$yar);
    OCIDefinebyname($stmt,"NAME", $buh);

    OCIExecute($stmt);

    while (OCIFetch($stmt)) {
    echo "ID:".$yar."\n<br>";
    echo "Name:".$buh."\n<br><br>";
    }

    OCIFreestatement($stmt);
    OCILogoff($conn);
    ?>

    </body>
    </html>

  • #4
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    Thanks, I haven't used Oracle w php yet, but I know I will in the future. A copmany I work for is migrating from Unix / PERL / DB2 to Windows / ASP or JSP or PHP / SQL Server or Oracle.

    So, since I have no interest in learning ASP, I'll probably go with the PHP / SQL Server or PHP / Oracle combo.

    I have used PERL with oracle, see code below, it may help.

    Code:
    $authVar=0;
    my $uname="";
    my $pword="";
    my $setCookie="no";
    sub checkUser( $$ ) {
    	$un = shift;
    	$pwd = shift;
    	$setCookie = shift;
    	$dbh = DBI->connect("DBI:Oracle:host=$dbHost;sid=$dbSID;port=$dbPort","$dbUserName","$dbPwd", { RaiseError => 0, AutoCommit => 0}) or die (print "Location: http://localhost/loginErrDB.html\n\n");
    	$sql = "select * from users where USERNAME=\'$un\'";
    	$cursor = $dbh->prepare ("$sql");
    	$cursor->execute();
    	my $totalRes=0;
    	my $passWord;
    ###Verify that there is only one user name match
    	while ( @row = $cursor->fetchrow ) {
    		$totalRes++;
    		$passWord=$row[1];
    	}
    ###If only one match, check to make sure the password is valid.
    	if($totalRes==1) {
    ###Check password
    ###May need to add chomp on UNIX!!!
    		if($pwd eq $passWord) {
    ###update lastlog
    			$now = localtime();
    			$month =substr($now,4,3);
    			$day = substr($now,8,2);
    			$year = substr($now,20,4);
    			$oracleDate="$day-$month-$year";
    			$logSQL="update users set lastlog=SYSDATE where username=\'".$un."\'";
    			$cursor = $dbh->prepare ("$logSQL");
    			$cursor->execute();
    	
    ### SET USER NAME AND PASSWORD COOKIE
    			my $val = $un . ':' . $passWord;
    			my $c = new CGI::Cookie(-name=>"usr",-value=>"$val",-expires=>"+20s");
    			print "Set-Cookie: ",$c->as_string,"\n";
    			print "Content-type: text/html\n";
    			print "\n";	
    			$authVar=1;
    			
    		}
    		else {
    			$authVar=0;
    ### GO TO DESTROY COOKIE
    		}
    	}
    	else {
    		$authVar=0;
    	}
    	$cursor->finish;
    	$dbh->disconnect;
    }
    sub getCookie() {
    	#my $query = new CGI;
    	#my $cookie_in = $query->cookie("usr");
    	#if($cookie_in)
    	%cookies = fetch CGI::Cookie;
    	$cookie = $cookies{'usr'}->value;
    	($uname, $pword) = split(/:/, $cookie, 2);
    	&checkUser( $uname, $pword, 'no' );
    }
    sub verifyUser() {
    	if ($ENV{'REQUEST_METHOD'} eq 'POST') {
    		&Parse_Form();
    		$uname=$formdata{'userName'};
    		$pword=$formdata{'p'};
    		&checkUser( $uname, $pword, 'yes');
    	}
    	elsif ($ENV{'HTTP_COOKIE'}) {
    		&getCookie();
    
    	}
    	else {
    		$authVar=0;
    	}
    }
    1;
    Or maybe not. PERL w/ Oracle is not too common either.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •