ソース掲示板




すべてから検索

キーワード   条件 表示 現行ログ 過去ログ トピックス 名前 本文
Oracle : PHPでWEB 上にあるライブラリを使って簡単にOracleにアクセス / 【Windows環境】
日時: 2013/05/10 10:43
名前: lightbox



※ 6番接続を使用するには、OO4O がインストールされている必要があります
※ ローカルの Windows PC 上で Windows 用の PHP で実行します
※ DSN は、Microsoft の ODBC ドライバで作成して下さい( 参考 )
※ 少なくとも Oracle のクライアントソフトがインストールされ、DSN が有効である必要があります
※ Oracle は通常 SHIFT_JIS でインストールされるので、ライブラリ内でキャラクタセットを変換します(少し遅くなります)
※ 返された1行ぶんの配列の中には、キャラクタセット変換される前のコードが含まれています
<?
header( "Content-Type: text/html; Charset=EUC-JP" );
header( "Expires: Wed, 31 May 2000 14:59:58 GMT" );

require_once( "http://homepage2.nifty.com/lightbox/gen/db.php");
$conf_db_type = 5;		// ADO + ODBC 接続
$conf_client_charset = 'euc-jp';
$conf_db_charset = 'shift_jis';
$SQL = new DB( "データソース名","","ユーザー","パスワード" );

#$conf_db_type = 6;		// OO4O 接続
#$SQL = new DB( "PC名/Oracleサービス名","","ユーザー","パスワード" );

$SQL->Debug = true;

$Query = "select * from 社員マスタ";
$Column = $SQL->QueryEx( $Query );

print "<pre>";
while( $Column ) {

	print "<HR>";
	print_r($Column);

	$Column = $SQL->QueryEx( );

}
print "</pre>";

$SQL->Close();
?>
↓使用ライブラリ http://homepage2.nifty.com/lightbox/gen/db.php
メンテナンス

使用ライブラリのコード ( No.1 )
日時: 2013/05/10 10:37
名前: lightbox


日時: 2013/05/10 10:37
名前: lightbox
<?
# **********************************************************
# database class ( 2007/04/10 )
# **********************************************************
class DB {
 
	var $Connect;
	var $Result;

	var $nField;
	var $nRow;

	var $Debug;
	var $Error;
	var $Work;

	var $Oracle;
	var $Cn;
	var $Rs;
	var $ConnectionString;

# **********************************************************
# 
# **********************************************************
	function DB( $Server='default', $DbName='default', $User='default', $Password='default' ) {

		$Server = $Server == 'default' ? $GLOBALS['conf_db_host'] : $Server;
		$DbName = $DbName == 'default' ? $GLOBALS['conf_db_db'] : $DbName;
		$User = $User == 'default' ? $GLOBALS['conf_db_user'] : $User;
		$Password = $Password == 'default' ? $GLOBALS['conf_db_pass'] : $Password;

		switch( $GLOBALS['conf_db_type'] ) {

			// MySQL
			case 1:
				$Server = $Server == '' ? 'localhost' : $Server;
				$DbName = $DbName == '' ? 'lightbox' : $DbName;
				$User = $User == '' ? 'root' : $User;

				$this->Connect = @mysql_connect( $Server, $User, $Password );
				if ( !$this->Connect ) {
					$this->Error = "version --> " . phpversion() . "<br />";
					$this->Error .= "<b>mysql_connect</b><br />";
					$this->Error .= "<B>" . mysql_error() . "</B><BR>";
					$this->Error .= __FILE__ . "<br />";
					$this->Error .= "FUNCTION -->" . __FUNCTION__ . "<br />";
					$this->Error .= "CLASS --> " .__CLASS__ . "<br />";
					if ( substr( phpversion(), 0, 1 )+0 > 4 ) {
						$this->Error .= "METHOD --> " . __METHOD__ . "<br />";
					}
					else {
						$this->Error .= "METHOD --> ? : use php5<br />";
					}
					return;
				}
				mysql_select_db( $DbName, $this->Connect );
				if ( $GLOBALS['conf_db_connect_action'] != '' ) {
					mysql_query( $GLOBALS['conf_db_connect_action'], $this->Connect );
				}
				break;

			// SQLServer
			case 2:
				$Server = $Server == '' ? '127.0.0.1' : $Server;
				$DbName = $DbName == '' ? 'lightbox' : $DbName;
				$User = $User == '' ? 'sa' : $User;

				if ( !extension_loaded( "mssql" ) ) {
					dl("php_mssql.dll");
				}
				$this->Connect = mssql_connect( $Server, $User, $Password );
				mssql_select_db( $DbName, $this->Connect );
				if ( $GLOBALS['conf_db_connect_action'] != '' ) {
					 mssql_query( $GLOBALS['conf_db_connect_action'], $this->Connect );
				}
				break;

			// PostgreSQL
			case 3:
				$Server = $Server == '' ? 'localhost' : $Server;
				$DbName = $DbName == '' ? 'lightbox' : $DbName;
				$User = $User == '' ? 'lightbox' : $User;

				if ( !extension_loaded( "pgsql" ) ) {
					dl("php_pgsql.dll");
				}
				$this->Connect = pg_connect(
					"host=$Server" .
					" port=5432" .
					" dbname=$DbName" .
					" user=$User" .
					" password=$Password"
				);
				if ( $GLOBALS['conf_db_connect_action'] != '' ) {
					pg_query( $this->Connect, $GLOBALS['conf_db_connect_action'] );
				}
				break;

			// use COM for MDB
			case 4:
				$this->Cn = new COM( "ADODB.Connection" );
				$this->Cn->CursorLocation = 3;
				$this->Rs = new COM( "ADODB.Recordset" );
				
				$Server = $Server == '' ? realpath( "hanbaib.mdb" ) : $Server;
				$ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;";
				$ConnectionString .= "Data Source=$Server;";

				$this->Cn->Open( $ConnectionString );
				if ( $GLOBALS['conf_db_connect_action'] != '' ) {
					$this->Cn->Execute( $GLOBALS['conf_db_connect_action'] );
				}
				break;

			// use COM for Oracle (it depends on ODBC driver)
			case 5:
				$this->Cn = new COM( "ADODB.Connection" );
				$this->Cn->CursorLocation = 3;
				$this->Rs = new COM( "ADODB.Recordset" );
				
				$ConnectionString = "Provider=MSDASQL;";
				$ConnectionString .= "DSN=$Server;";
				$ConnectionString .= "UID=$User;";
				$ConnectionString .= "PWD=$Password;";

				$this->Cn->Open( $ConnectionString );
				if ( $GLOBALS['conf_db_connect_action'] != '' ) {
					$this->Cn->Execute( $GLOBALS['conf_db_connect_action'] );
				}
				break;

			// Oracle OO4O
			case 6:
				$this->Oracle = new COM( "OracleInProcServer.XOraSession" );
				$this->Cn = $this->Oracle->OpenDatabase($Server, "$User/$Password", 0);
#				OraDatabase.CreateDynaset(SqlQuery,2)
				
				if ( $GLOBALS['conf_db_connect_action'] != '' ) {
					$this->Cn->ExecuteSQL( $GLOBALS['conf_db_connect_action'] );
				}
				break;

		}
		$this->Debug = FALSE;
	}
 
# **********************************************************
# 
# **********************************************************
	function Close( ) {
		switch( $GLOBALS['conf_db_type'] ) {
			case 1:
				@mysql_close( $this->Connect );
				break;
			case 2:
				mssql_close( $this->Connect );
				break;
			case 3:
				pg_close( $this->Connect );
				break;
			case 4:
			case 5:
#				@$this->Rs->Close();
				@$this->Cn->Close();
				break;
		}
	}
 
# **********************************************************
# 
# **********************************************************
	function Query( $SqlQuery ) {

		if ( $GLOBALS['conf_db_charset'] != '' ) {
			$SqlQuery = mb_convert_encoding( $SqlQuery, $GLOBALS['conf_db_charset'], $GLOBALS['conf_client_charset'] );
		}

		switch( $GLOBALS['conf_db_type'] ) {
			case 1:
				$ret = mysql_query( $SqlQuery,$this->Connect );
				if ( $this->Debug ) {
					if ( mysql_errno() != 0 ) {
						print "<B>" . mysql_error() . "</B><BR>";
					}
				}
				break;
			case 2:
				$ret = mssql_query( $SqlQuery,$this->Connect );
				break;
			case 3:
				$ret = pg_query( $this->Connect, $SqlQuery );
				break;
			case 4:
				$this->Rs->Open( $SqlQuery, $this->Cn );
				$ret = !$this->Rs->EOF;
				break;

			case 5:
				if ( $this->Rs->State >= 1 ) {
					$this->Rs->Close();
				}
				$this->Rs->Open( $SqlQuery, $this->Cn );
				$ret = !$this->Rs->EOF;
				break;
			case 6:
				$this->Rs = $this->Cn->CreateDynaset($SqlQuery,2);
				$ret = !$this->Rs->EOF;
				break;
		}

		return $ret;
	}
 
# **********************************************************
# 
# **********************************************************
	function Fetch( $Result ) {
		switch( $GLOBALS['conf_db_type'] ) {
			case 1:
				$ret = mysql_fetch_array( $Result );
				break;
			case 2:
				$ret = mssql_fetch_array( $Result );
				break;
			case 3:
				$ret = pg_fetch_array( $Result );
				break;
			case 4:
			case 5:
			case 6:
				$ret = array();
				for( $i = 0; $i < $this->Rs->Fields->count; $i++ ) {
					if ( $this->Rs->Fields[$i]->type == 7 ) {
						if ( substr(phpversion(),0,1) == '4' ) {
							$test = date( "H:i:s", $this->Rs->Fields[$i]->value );
							if ( $test == "00:00:00" ) {
								$ret[$i] = date( "Y/m/d", $this->Rs->Fields[$i]->value );
								$ret[$this->Rs->Fields[$i]->name] = date( "Y/m/d", $this->Rs->Fields[$i]->value );
							}
							else {
								$ret[$i] = date( "Y/m/d H:i:s", $this->Rs->Fields[$i]->value );
								$ret[$this->Rs->Fields[$i]->name] = date( "Y/m/d H:i:s", $this->Rs->Fields[$i]->value );
							}
						}
						else {
							$ret[$i] = $this->Rs->Fields[$i]->value;
							$ret[$this->Rs->Fields[$i]->name] = $this->Rs->Fields[$i]->value;
						}
					}
					else {
						$ret[$i] = $this->Rs->Fields[$i]->value;
						$ret[$this->Rs->Fields[$i]->name] = $this->Rs->Fields[$i]->value;
					}
				}
				break;
		}
		if ( $GLOBALS['conf_db_charset'] != '' ) {

			if ( $ret ) {
				$ret2 = array();
				while (list($Key, $Value) = @each($ret)) {
					$ret2[$Key] = mb_convert_encoding( $Value, $GLOBALS['conf_client_charset'], $GLOBALS['conf_db_charset'] );
					$Key2 = mb_convert_encoding( $Key, $GLOBALS['conf_client_charset'], $GLOBALS['conf_db_charset'] );
					$ret2[$Key2] = mb_convert_encoding( $Value, $GLOBALS['conf_client_charset'], $GLOBALS['conf_db_charset'] );
				}
			}

		}
		else {
			$ret2 = $ret;
		}

		return $ret2;
	}
 
# **********************************************************
# 
# **********************************************************
	function FieldName( $idx ) {
		switch( $GLOBALS['conf_db_type'] ) {
			case 1:
				$ret = mysql_field_name ( $this->Result, $idx );
				break;
			case 2:
				$ret = mssql_field_name ( $this->Result, $idx );
				break;
			case 3:
				$ret = pg_field_name( $this->Result, $idx );
				break;
			case 4:
			case 5:
			case 6:
				$ret = $this->Rs->Fields[$idx]->name;
				break;
		}
		if ( $GLOBALS['conf_db_charset'] != '' ) {
			$ret = mb_convert_encoding( $ret, $GLOBALS['conf_client_charset'], $GLOBALS['conf_db_charset'] );
		}
		return $ret;
	}

# **********************************************************
# 
# **********************************************************
	function QueryEx( $SqlQuery='' ) {
 
		if ( $SqlQuery != '' ) {
			if ( $this->Debug ) {
				print "<TABLE border=0 cellpadding=5>";
				print "<TH align=left bgcolor=skyblue><pre>" . $this->Arrange($SqlQuery) . "</pre></TD>";
				print "</TABLE>";
			}

			if ( $GLOBALS['conf_db_type'] == 4 || $GLOBALS['conf_db_type'] == 5 ) {
				if ( substr( $SqlQuery, 0, 7 ) == "COLUMNS" ) {
					$table_name = Explode( " ", $SqlQuery );
					$this->Rs = $this->Cn->OpenSchema( 4 );
					if ( $GLOBALS['conf_db_charset'] != '' ) {
						$tbl = mb_convert_encoding( $table_name[1], $GLOBALS['conf_db_charset'], $GLOBALS['conf_client_charset'] );
					}
					else {
						$tbl = $table_name[1];
					}
					$this->Rs->Filter = "TABLE_NAME = '" . $tbl . "'";
					$this->Rs->Sort = "ORDINAL_POSITION";
				}
				else {
					$this->Result = $this->Query( $SqlQuery );
					if ( !$this->Result ) {
						return FALSE;
					}
				}
			}
			else {
				$this->Result = $this->Query( $SqlQuery );
				if ( !$this->Result ) {
					return FALSE;
				}
			}

			switch( $GLOBALS['conf_db_type'] ) {
				case 1:
					$this->nField = mysql_num_fields( $this->Result );
					$this->nRow = mysql_num_rows ( $this->Result );
					break;
				case 2:
					$this->nField = mssql_num_fields( $this->Result );
					$this->nRow = mssql_num_rows ( $this->Result );
					break;
				case 3:
					$this->nField = pg_num_fields( $this->Result );
					$this->nRow = pg_num_rows( $this->Result );
					break;
				case 4:
				case 5:
				case 6:
					$this->nField = $this->Rs->Fields->count;
					$this->nRow = $this->Rs->RecordCount;
					break;
			}

			return $this->Fetch ( $this->Result );
		}
		else {
			if ( $GLOBALS['conf_db_type'] == 4 || $GLOBALS['conf_db_type'] == 5 ||  $GLOBALS['conf_db_type'] == 6 ) {
				$this->Rs->MoveNext();
				if ( $this->Rs->EOF ) {
					return FALSE;
				}
			}
			return $this->Fetch ( $this->Result );
		}
 
	}
 
# **********************************************************
# 
# **********************************************************
	function Execute( $SqlExec ) {
		switch( $GLOBALS['conf_db_type'] ) {
			case 1:
				if ( $this->Debug ) {
					print "<TABLE border=0 cellpadding=5>";
					print "<TH align=left bgcolor=skyblue><pre>" . $this->Arrange($SqlExec) . "</pre></TD>";
					print "</TABLE>";
					if ( mysql_errno() != 0 ) {
						print "<B>" . mysql_error() . "</B><BR>";
					}
				}

				if ( $GLOBALS['conf_db_charset'] != '' ) {
					$SqlExec = mb_convert_encoding( $SqlExec, $GLOBALS['conf_db_charset'], $GLOBALS['conf_client_charset'] );
				}

				$ret = mysql_query( $SqlExec,$this->Connect );
				break;
			case 2:
				if ( $this->Debug ) {
					print "<TABLE border=0 cellpadding=5>";
					print "<TH align=left bgcolor=skyblue><pre>" . $this->Arrange($SqlExec) . "</pre></TD>";
					print "</TABLE>";
				}

				if ( $GLOBALS['conf_db_charset'] != '' ) {
					$SqlExec = mb_convert_encoding( $SqlExec, $GLOBALS['conf_db_charset'], $GLOBALS['conf_client_charset'] );
				}

				$ret = mssql_query( $SqlExec,$this->Connect );
				break;
			case 3:
				if ( $this->Debug ) {
					print "<TABLE border=0 cellpadding=5>";
					print "<TH align=left bgcolor=skyblue><pre>" . $this->Arrange($SqlExec) . "</pre></TD>";
					print "</TABLE>";
				}

				if ( $GLOBALS['conf_db_charset'] != '' ) {
					$SqlExec = mb_convert_encoding( $SqlExec, $GLOBALS['conf_db_charset'], $GLOBALS['conf_client_charset'] );
				}

				$ret = pg_query( $this->Connect, $SqlExec );
				break;
			case 4:
			case 5:
			case 6:
				if ( $this->Debug ) {
					print "<TABLE border=0 cellpadding=5>";
					print "<TH align=left bgcolor=skyblue><pre>" . $this->Arrange($SqlExec) . "</pre></TD>";
					print "</TABLE>";
				}

				if ( $GLOBALS['conf_db_charset'] != '' ) {
					$SqlExec = mb_convert_encoding( $SqlExec, $GLOBALS['conf_db_charset'], $GLOBALS['conf_client_charset'] );
				}

				if ( $GLOBALS['conf_db_type'] == 6 ) {
					$this->Cn->ExecuteSQL( $SqlExec );
				}
				else {
					$this->Cn->Execute( $SqlExec );
				}
				$ret = TRUE;
				break;
		}
		return $ret;
	}
 
# **********************************************************
# 
# **********************************************************
	function Version( ) {
		switch( $GLOBALS['conf_db_type'] ) {
			case 1:
				$Field = $this->QueryEx( "show variables like 'version'" );
				$ret = $Field[1];
				break;
			case 2:
				$Field = $this->QueryEx( "sp_server_info @attribute_id = 2" );
				$ret = $Field["attribute_value"];
				break;
			case 3:
				$Field = $this->QueryEx( "select version()" );
				$ret = $Field["version"];
				break;
		}
		return $ret;
	}
 
# **********************************************************
# 
# **********************************************************
	function Arrange( $target ) {
		$ret = str_replace( ',', "\n\t,", $target );
		$ret = str_replace( 'set ', "set\n\t", $ret );
		$ret = str_replace( 'where ', "\nwhere ", $ret );
		return "\n$ret";
	}
}
?>
このアーティクルの参照用URLをクリップボードにコピー メンテナンス