php使用PDO扩展连接PostgreSQL对象关系数据库

php使用PDO扩展连接PostgreSQL对象关系数据库

$pdo = NULL;
		if(version_compare(PHP_VERSION, '5.3.6', '<')){
			$pdo = new \PDO('pgsql:host=127.0.0.1;port=5432;dbname=postgredb1','postgres',"123456",array(\PDO::MYSQL_ATTR_INIT_COMMAND=>'SET NAMES \'UTF8\'' ));
		}
		else{
			$pdo = new \PDO('pgsql:host=127.0.0.1;port=5432;dbname=postgredb1','postgres',"123456");
		}
		try {
			$pdo->beginTransaction();
			$tableName = 'user';
			
			if($fetch = true){
				$myPDOStatement = $pdo->prepare("SELECT * FROM " . $tableName . " WHERE id=:id ");
				if(!$myPDOStatement) {
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				$id = 1;
				$myPDOStatement->bindParam(":id",$id);
				$myPDOStatement->execute();
				if($myPDOStatement->errorCode()>0){
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				$item = $myPDOStatement->fetch();
				print_r($item);
			}
			
			$insertedId = 0;
			if($insert = true){
				$myPDOStatement = $pdo->prepare("INSERT INTO " . $tableName . "(username,password,status)	VALUES(:username,:password,:status)");
				if(!$myPDOStatement) {
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				$timestamp = time();
				$data = array(
					'username' =>'usernamex',
					'password' =>'passwordx',
					'status' =>'1',
				);
				$myPDOStatement->bindParam(":username",$data['username']);
				$myPDOStatement->bindParam(":password",$data['password']);
				$myPDOStatement->bindParam(":status",$data['status']);
				$myPDOStatement->execute();
				if($myPDOStatement->errorCode()>0){
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				$affectRowCount = $myPDOStatement->rowCount();
				if($affectRowCount>0){
					$insertedId = $pdo->lastInsertId();
				}
				print_r('$insertedId = '.$insertedId);//PostgreSQL不支持
				print_r('$affectRowCount = '.$affectRowCount);
			}
			
			if($update = true){
				$myPDOStatement = $pdo->prepare("UPDATE " . $tableName . " SET  username=:username, status=:status WHERE id=:id");
				if(!$myPDOStatement) {
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				$id = 1;
				$username = 'username update';
				$status = 0;
				$myPDOStatement->bindParam(":id",$id);
				$myPDOStatement->bindParam(":username",$username);
				$myPDOStatement->bindParam(":status",$status);
				$myPDOStatement->execute();
				if($myPDOStatement->errorCode()>0){
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				$affectRowCount = $myPDOStatement->rowCount();
				print_r('$affectRowCount = '.$affectRowCount);
			}
			
			if($fetchAll = true){
				$myPDOStatement = $pdo->prepare("SELECT * FROM " . $tableName ." WHERE id > :id");
				if(!$myPDOStatement) {
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				$id = 0;
				$myPDOStatement->bindParam(":id",$id);
				$myPDOStatement->execute();
				if($myPDOStatement->errorCode()>0){
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				$list = $myPDOStatement->fetchAll();
				print_r($list);
			}
			
			if($update = true){
				$myPDOStatement = $pdo->prepare("DELETE FROM " . $tableName . " WHERE id=:id");
				if(!$myPDOStatement) {
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				//$insertedId = 10;
				$myPDOStatement->bindParam(":id",$insertedId);
				$myPDOStatement->execute();
				if($myPDOStatement->errorCode()>0){
					$errorInfo = $myPDOStatement->errorInfo();
					throw new \Exception($errorInfo[0].'###'.$errorInfo[1].'###'.$errorInfo[2]);
				}
				$affectRowCount = $myPDOStatement->rowCount();
				print_r('$affectRowCount = '.$affectRowCount);
			}
			$pdo->commit();
		} catch (\Exception $e) {
			$pdo->rollBack();
// 			print_r($e);
		}
		$pdo = null;