人気ブログランキング |
タグ:SQLite ( 5 ) タグの人気記事
(SQL)テーブル名にハイフン入ってる時はクォートする必要あり
SQLiteでついハイフン(-)入のテーブルを作ってしまい、以下の用なクエリを実行したらエラーになりました。
SELECT * FROM hoge-test WHERE name='moe';

テーブル名にハイフンが入っていると、ダメなようです。
これは、SQLiteに限らず他のDBMS(MySQLやSQL Server)も同様みたいです。

解決策としては、ハイフンを付けないか、以下のようにクォートしてやるかです。
SELECT * FROM 'hoge-test' WHERE name='moe';


しばらくSQLやDB触ってないとこうなっちゃいますね。

参考:
PHP+SQLの覚え書き: テーブル名にハイフンはご法度
龍の記憶:SQLServer SQL のテーブル名に - (半角ハイフン) - livedoor Blog(ブログ)
by Jehoshaphat | 2012-11-30 23:22 | SQL
(PHP)PDO使用時はPDOStatementをした方がよい
PDOを使って、選択クエリ発効後、挿入クエリをするスクリプトを書いています。

選択クエリは、fetch メソッドで先頭行のみ取得しており、挿入クエリでは、トランザクションを使っています。

しかし、挿入クエリの、beginTransaction() で下記のようなエラーが。。。

Fatal error: Maximum execution time of 30 seconds exceeded in D:\eclipe\testl\util.php on line 100

原因として選択クエリ後に、PDOStatement を解放してなかったのがまずいようです。

PHPリファレンス:PDO::queryには、「PDO::query() を次にコールする前に 結果セット内の全てのデータを取得しない場合、そのコールは失敗します。 PDOStatement::closeCursor() をコールし、 次に PDO::query() をコールする前に PDOStatement オブジェクトに関連付けられたリソースを解放してください。」とあります。

fetch 後、PDOStatement::closeCursor() を実行することで、ちゃんと動くようになりました。

下記のような感じです。(ADD関数は、DB追加時に汎用性を持たすようにしたものです。詳しくは、(PHP)PDOでforeachを使ってbindParamでパラメータを設定してた時の注意点を参照。)

$employeeCode = '015640';
$departmentCode = '201';
try {
// DBに接続する
$db = new PDO( 'sqlite2:./test.sqlite', '', '' );
 
//件名カテゴリ取得
$stmt = $db->prepare( 'SELECT * FROM employee WHERE employee_code = :code' );
$res = $stmt->execute(array(':code' => $employeeCode ) );
$res = $stmt->fetch(PDO::FETCH_BOTH);
$employeeId = $subject . $res['id'];
//↓これが必要。これで他の SQL ステートメントを発行できるようにサーバへの接続を解放。
$stmt->closeCursor();

//件名カテゴリ取得
$stmt = $db->prepare( 'SELECT * FROM department WHERE department_code= :code' );
$res = $stmt->execute(array(':code' => $departmentCode ) );
$res = $stmt->fetch(PDO::FETCH_BOTH);
$departmentId = $subject . $res['id'];
//↓これが必要。これで他の SQL ステートメントを発行できるようにサーバへの接続を解放。
$stmt->closeCursor();
 
//DBにデータ追加
Add('project', array('employee_id','department_id'),array($employeeId , $departmentId) );
 
// DBから切断する ( $db = null; も可能)
unset( $db );
} catch (PDOException $e) {
// DBアクセスができなかったとき
'アクセスできません : ' . $ex->getMessage();
unset( $db );
die();
}
 

/* DBにデータを追加するSQL文
* $tbl:テーブル名
* $clm_ary:列名の配列
* $value_ary:値の配列($clm_aryの要素Noにあったデータを入れること)
*/

function Add($tbl,$clm_ary,$value_ary){
try {
//sql文組立て
$sql_part1 = '';
for ($i = 0; $i < count($clm_ary); $i++) {
$sql_part1 = $sql_part1 . ' ? ';
if( $i < count($clm_ary)-1) {
$sql_part1 = $sql_part1 . ",";
}
}
$sql_part2 ='';
for ($i = 0; $i < count($value_ary); $i++) {
$sql_part2 = $sql_part2 . ' ? ';
if( $i < count($value_ary)-1) {
$sql_part2 = $sql_part2 . ",";
}
}
$sql = 'INSERT INTO ?( ' . $sql_part1 . ') VALUES (' . $sql_part2 . ')';
// DBに接続する
$db = new PDO( 'sqlite2:./test.sqlite', '', '' );
//SQL警告出すようにする
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//トランザクション開始。 closeCursor 無いとここでエラー。
$stmt=$db->beginTransaction();
$stmt = $db->prepare( $sql );
//パラメータ指定
$i = 1;
$stmt->bindParam($i, $tbl);
foreach($clm_ary as $key => &$clm) {
$i++;
$stmt->bindParam($i, $clm);
}
unset($clm);
foreach($value_ary as $key => &$value) {
$i++;
$stmt->bindParam($i, $value);
}
unset($value);

//クエリ実行
$res = $stmt->execute();
//コミット
$stmt= $db->commit();
$db=null;
}catch( PDOException $ex ) {
// DBアクセスができなかったとき
print 'DBにデータ追加失敗。 : ' . $ex->getMessage();
unset( $db );
die();

}
}



参考:
【SQLite】PDOStatementは開放しないとだめなこともある
by jehoshaphat | 2011-07-10 21:26 | PHP開発
(PHP)PDOでforeachを使ってbindParamでパラメータを設定してた時の注意点
PDO経由でSQliteを使おうと思ってます。

Insert文で使う予定なんですが、汎用性を出すために、列名、値それぞれを配列に格納し、名前無ププレースホルダのSQLを生成して、それにbindParamメソッドでパラメータをセットしていきます。

それで当初下記のようにしていました。

/* DBにデータを追加するSQL文
* $tbl:テーブル名
* $clm_ary:列名の配列
* $value_ary:値の配列($clm_aryの要素Noにあったデータを入れること)
*/

function Add($tbl,$clm_ary,$value_ary){
try {
//sql文組立て
$sql_part1 = '';
for ($i = 0; $i < count($clm_ary); $i++) {
$sql_part1 = $sql_part1 . ' ? ';
if( $i < count($clm_ary)-1) {
$sql_part1 = $sql_part1 . ",";
}
}
$sql_part2 ='';
for ($i = 0; $i < count($value_ary); $i++) {
$sql_part2 = $sql_part2 . ' ? ';
if( $i < count($value_ary)-1) {
$sql_part2 = $sql_part2 . ",";
}
}
$sql = 'INSERT INTO ?( ' . $sql_part1 . ') VALUES (' . $sql_part2 . ')';
// DBに接続する
$db = new PDO( 'sqlite2:./test.sqlite', '', '' );
//SQL警告出すようにする
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$stmt = $db->prepare( $sql );
//パラメータ指定(列名)
$i = 1;
$stmt->bindParam($i, $tbl);
//列名のパラメータを設定
foreach($clm_ary as $key => $clm) {
$i++;
$stmt->bindParam($i, $clm);
}
//値のパラメータを設定
foreach($value_ary as $key => $value) {
$i++;
$stmt->bindParam($i, $value);
}
//クエリ実行
$res = $stmt->execute();
$db=null;
}catch( PDOException $ex ) {

// DBアクセスができなかったとき
print 'DBにデータ追加失敗。 : ' . $ex->getMessage();
unset( $db );
die();
}
}
x


しかし、これだと下記のエラーとなってしまいました。
DBにデータ追加失敗。 : SQLSTATE[HY000]: General error: 1 SQL logic error or missing database

かなり悩んだんですが、PDOStatement->bindParamの罠で解決の糸口が見えてきました。

たしかに、PDOStatement->bindParaを見てみると、変数は参照としてバインドされ、PDOStatement::execute() がコールされたときのみ評価されます。とあります。

で、foreach 文は、foreachphp の foreach で参照渡しをするにはにあるように、配列のコピーに対して値渡しで処理をするようです。

なので下記のように、&を付けて参照渡しをすることで、ちゃんとSQLが実行できるようになりました。

//列名のパラメータを設定
foreach($clm_ary as $key => &$clm) {
$i++;
$stmt->bindParam($i, $clm);
}
unset($clm);
//値のパラメータを設定
foreach($value_ary as $key => &$value) {
$i++;
$stmt->bindParam($i, $value);
}
unset($value);

しかし、foreachで参照渡しにしたときは、foreachの$valueを参照で受けると思わぬバグを引き起こすにあるような罠があるので、使い終わったら参照を解除してるべきのようです。

(ちなみに、foreach で配列内の値を変更するときも参照渡しにしてやらないといけないようですね。。。記憶が全く飛んでました。)


これ解決するのに相当時間がかかってしまいました。。。
by jehoshaphat | 2011-07-03 00:28 | PHP開発
(PHP)PDOで、SQLiteにアクセスしてみた
PHPで多分初めてSQLiteを使おうと思ってます。

SQLiteドライバから直接クエリを投げてもいいんですが、PDO(PHP Data Object)というDBアクセスの抽象化レイヤがあるみたいで、これを使うとどのデータベースを使っているかを気にすることなく使え、DBを移行した際にもさしてソースに変更加えなくていいようです。
(ただし、O/Rマッピングをしてくれるわけではないので、完全な抽象化をしたければCakePHPのようなフレームワークを使った方がいいかもしれません。)

DBファイルやテーブルの作成自体は、SQLiteManager を使いました。
なお、PDOを使うにはDBごとにPDOのモジュールがインストールし、php.iniで使うように設定しなければなりません。(まぁ大抵のレンタルサーバはsqlite2のPDOモジュールは入ってると思いますが。。。)


まず接続は PDO クラスのインスタンスを作成するだけでいいようです。
作成した PDO インスタンスの変数に null を入れたり、unset(変数)を実行するだけで切断となります。
PDO->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); としておくと、エラー発生時は PDOException が発生するので、try catch で囲むようにしておけばOKです。

SQLをすぐに実行する場合は、下記のようにPDOインスタンスのqueryメソッドを実行し、引数にクエリ文を入れたらいいようです。結果は PDOStatement オブジェクトとして返ってきます。 PDOStatement オブジェクトの fetchAll メソッドを使うと、結果を全て配列にしてくれるので、PHPでは操作しやすいですね。

try {
// DBに接続する
$db = new PDO( 'sqlite2:./test.sqlite', '', '' );
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
 
/* ..... DBアクセス ..... */
//データを取得し、それを表示
foreach( $db->query( 'SELECT * FROM testtbl ORDER BY id' ) as $row ) {
print 'id = ' . $row[ 'id' ] . ', value = ' . $row[ 'value' ] . "\n";
}
 
//データを取得し、全て配列化する
$dbRes_subject_cate = $db->query( 'SELECT * FROM testtbl ORDER BY id' )->fetchAll();
 
echo "<pre>";
print_r( $dbRes_subject_cate );
echo "</pre>";
 
// DBから切断する ( $db = null; も可能)
unset( $db );
 
} catch( PDOException $ex ) {

// DBアクセス時にエラーとなった時
print 'DBエラー : ' . $ex->getMessage();
unset( $db );
die();
}



パラメータ(プレースホルダ)を使ってSQLクエリを投げることもできます。(プリペアドステートメント)
パラメータは名前付きパラメータと名前無パラメータが使えるようです。
パラメータは配列で指定することもできますし、PDOStatement オブジェクトの bindParam メソッドでも指定できます。
下記のような感じです。

try {
// DBに接続する
$db = new PDO( 'sqlite2:./test.sqlite', '', '' );
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
 
//******名前付きプレースホルダ(値配列指定)
$stmt = $db->prepare( 'INSERT INTO test( id , value ) VALUES ( :id , :value )' );
//配列で指定するときは実行メソッド(execute)の引数にパラメータの値配列を指定
$res = $stmt->execute(array(':id' => 1 , ':value' => 'test') );
echo "結果行数:" .$res . "<br>";
 
//******名前付きプレースホルダ(bindParamメソッドにて値指定)
$stmt = $db->prepare( 'INSERT INTO test( id , value ) VALUES ( :id , :value )' );
$value = "test";
//bindParamメソッドでは値のリテラルを引数に指定してはいけないので注意。
$stmt->bindParam( ':value', $value );
$id=1;
$stmt->bindParam( ':id', $id );
//bindParamメソッドの後からexecuteを実行する前なら値の変更可能
$id=2;
//クエリ実行
$res = $stmt->execute();
echo "結果行数:" .$res . "<br>";
 
//******名前無プレースホルダ(値配列指定)
$stmt = $db->prepare( 'INSERT INTO test( id , value ) VALUES ( ? , ? )' );
//配列で指定するときは実行メソッド(execute)の引数にパラメータの値配列を指定
$res = $stmt->execute(array( 5 , 'test5') );
echo "結果行数:" .$res . "<br>";
 
//******名前無プレースホルダ(bindParamメソッドにて値指定)
$stmt = $db->prepare( 'INSERT INTO test( id , value ) VALUES ( ? , ? )' );
$id=6;
$value = "test";
$stmt->bindParam( 1, $id ); //1番目のパラメータ指定
$stmt->bindParam( 2, $value ); //2番目のパラメータ指定
//クエリ実行
$res = $stmt->execute();
echo "結果行数:" .$res . "<br>";

$db=null;
}catch( PDOException $ex ) {

// DBアクセスができなかったとき
print 'DBにデータ追加失敗。 : ' . $ex->getMessage();
unset( $db );
die();
}


また、トランザクションを有効にするには、下記のようにしてやるといいようです。

// DBに接続する
$db = new PDO( 'sqlite2:./test.sqlite', '', '' );
//トランザクション開始
$stmt=$db->beginTransaction();
//クエリを実行
$db->query('INSERT INTO test( id , value ) VALUES ( 10 , testetst )' );
//コミット
$stmt=$db->commit();



参考:
PDOでサクサクDB開発(1/9):CodeZine
今からスタート! PHP:第11回 データベースへのアクセス [PDO編]|gihyo.jp
PHP: PDO - Manual:リファレンス
PDO クエリの発行
by jehoshaphat | 2011-07-03 00:25 | PHP開発
SQLiteManagerでUTF-8でデータを保存したい
SQLite内のデータベースを容易に管理するためにPHP上で動く SQLiteManager を入れてみたんですが、どうやら日本語に設定するとEUC-JPで動くらしく、UTF-8のデータをみると文字化けします。


ということで SQLiteManager でUTF-8のデータを扱う方法ですが、languageファイルの文字コードと定義を変更すればいいようです。

まず下記ファイルをテキストエディタで開きます。
lang\japanese.inc.php

文字コード定義部分を下記のように修正します。

/*
$charset = 'euc-jp';
$langSuffix = 'ja-euc';
↓*/

$charset = 'utf-8';
$langSuffix = 'ja-utf-8';

後は、languageファイルをUTF-8で保存します。
これでUTF-8を扱えるようになりました。

参考:
MAMP に入っている SQLiteManager の文字コードを UTF-8 に変更する - 転校生@Hatena
by jehoshaphat | 2011-07-01 01:56 | SQL