WSL(Ubuntu)-PHPからSQLServerへの道のり

「CentOSからWindows上のSQLServerへの道のり」のつづきです。

LinuxからWindows上の SQLServerへのアクセス方法については、世間的にはあまり需要がないのでしょう。まぁ、Linuxとかのunix系のOSでは、SQLServerなんて採用しないし、逆も然り。。。困ったもんだ。。。

さて、Linuxの各ディストリビューション用のMicrosoft製のODBCドライバが提供されていて手順を踏むと、LinuxからSQLServerへODBC経由でアクセスすることができました。

今度は LAMP環境、PHPから文字化けさせずにCRUDすることができるか、ウェブアプリケーションをちゃんと実装できるか、確認です。とりあえずLinux各ディストリビューション用にマイクロソフト製のODBCドライバをインストールしていることが前提です。

PHPでたとえば、以下のようなコードでテーブルを作成し、SQLServer Management Studioとかで確認すると、日本語が全部文字化けします。
sqlcmdコマンドで日本語交じりのSQL文を発行すると化けないので、おそらく、PHPの問題でしょう。DSNで odbc: を使うのがだめなんでしょうね。odbcドライバをインストールしたんだから、当然odbcプレフィックスを使うものだと勘違いしてまして、これが罠でした・・・。日本語関係の処理が全くされない。。。この辺、よくわからん・・・。知識が圧倒的に足りない。SQLServerのODBCドライバとsqlsvr(PHP SQLServer用のドライバ?)の関係が全然わからない。PHPのマニュアル見てもよくわからない・・・。

// これが間違い
$pdo = new PDO('odbc:Driver={ODBC Driver 13 for SQL Server};Server=localhost;Database=Sample','dbuser','dbpass');
$pdo->exec('CREATE TABLE tbl_hoge(hoge_id int NOT NULL,hoge_txt nvarchar(100))');
$pdo->exec("INSERT INTO tbl_hoge values(1,N'日本語で挿入・・・なんかエロいな')");

接続文字列でエンコーディング関係のパラメータがあるのかなと、調べてみたけどないっぽい。さらにネットでの情報がほとんどなく。。。ヒットするのはFreeTDSを使ったものばかりで、多くはPHP5の情報ばかりで、PHP7以降のものは出てこず。。。

で、困ったときはグーグルで検索・・・ではなく、まずマイクロソフトの公式のドキュメントを漁りましょうってことですね(^^;
ググらなくても最近のマイクロソフトはちゃんとドキュメントを用意してくれてます。(MSも変わったな。。。)

Build an app using SQL Server


上記サイトの PHP ⇒ UBUNTU をクリックすると、Linux版 SQL Serverのセットアップから詳細な手順が書かれています。英語ですが、簡単な英文なので読みましょう!

今までSQLServer用のODBCドライバとPHPでのSQLServer用のドライバ、それぞれが別々に説明がされてて、一体何がどうなってってるのか理解できなかったけど、上記サイトで Ubuntu + PHP では、この手順、Redhat系では、この手順、という風にまとめて手順が説明されているので、これで悩まなくて済むようになった。PHP7以降限定ですけどね。 まぁPHP5系はこの先消えていく(deprecated)のでまぁいいか、という感じ。

要するに ODBCドライバに加えて、peclでsqlsvr/pdo_sqlsvrをインストールすれば完了。やっと 非Windows環境でも労せずSQLServerへフツーにアクセスできるようになりました。

上記マイクロソフトのサイトには、Linux用のSQL Serverのセットアップから説明されていますが・・・SQLServerは Windows用のものをインストールしましょう。というか、開発マシンがWindowsの場合は、素直にWindows用のSQLServer Developper Editionをインストールした方がいいです。開発目的ならライセンスフリーですし。(サーバー環境で運用はできないけど)
開発マシンがMacやLinuxの方は・・・そもそもSQLServerに接続するような仕事してないでしょ?w

WSL(Ubuntu)でPHPをインストールしていない場合、aptで適当にphp7系をインストールしてください。その際、php-dev パッケージは必須です。
また、最新のsqlsrv,pdo-sqlsrvドライバはphp7.0をサポートしないので、最新に拘る人はphp7.1以上にするべし?php7.0をサポートするMicrosoftのsqlsrv/pdoドライバのバージョンは5.3です。最新は確か5.6だったかな?
システム要件:https://docs.microsoft.com/ja-jp/sql/connect/php/system-requirements-for-the-php-sql-driver

$ sudo apt install php php-cli php-dev php-... (必要なパッケージ)
# apacheで動作するには必要
$ sudo apt install libapache2-mod-php

1, 先に マイクロソフトのサイトからODBC Driver for SQLServer をインストールしておく(上述)

2, sqlsrv/pdo_sqlsrv のインストール (ubuntu 1604 の場合)

$ sudo apt install php-dev
$ sudo pecl install sqlsrv
$ sudo pecl install pdo_sqlsrv
$ sudo echo "extension=pdo_sqlsrv.so" > /etc/php/7.0/mods-available/pdo_sqlsrv.ini
$ sudo echo "extension=sqlsrv.so" > /etc/php/7.0/mods-available/sqlsrv.ini
$ sudo phpenmod -v 7.0 -s ALL  pdo_sqlsrv sqlsrv

apache2な人は再起動するべし。
参考リンク:the installation instructions on Microsoft Docs.

3, PHPで確認
PDOで、DSNのプレフィックスをodbc: ではなく sqlsrv: で。

$pdo = new PDO('sqlsrv:Server=localhost;Database=Sample','dbuser','dbpass');
$pdo->exec('CREATE TABLE tbl_サンプル(hoge_id int NOT NULL,hoge_txt nvarchar(100))');
$pdo->exec(sprintf('INSERT INTO tbl_hoge values(1,N%s)',$pdo->quote('日本語で挿入・・・なんかエロいな'));

参考リンク: https://github.com/Microsoft/msphpsql/tree/master/sample

文字列リテラルをNプレフィックスをつけずにINSERTとすると絵文字が化ける。文字列リテラルの場合は Nプレフィックスを忘れずに。。。
ただ、プリペアドステートメントを利用する場合、自動的にNプレフィックスはつけてくれるみたい。SQLServer Data Profilerで実際実行されたSQL文を確認すると、PDO::execでは自動的にNプレフィックスはつけてくれないみたい。まぁ当たり前といえば、そうなんだけど。。。

あと、東京オリンピックまでには、apt install php-sqsrv / yum install php-sqlsrv とか標準のリポジトリでインストールできるようにしてほしい。。。

\(^o^)/

PHPでのExcel吐き出しCSVファイルの処理

何度となくハマったPHPでのCSV処理・・・もういやだ。
最初は、fgetcsv で setlocaleし忘れでハマり、改行を含むセルでSplFileObjectを知り、SJIS-WIN でハマり・・・もういやだ。

もうCSVで涙目になるのは嫌なので、一個クラスを作る。

要はCP932エンコードされたCSVファイル用の SplFileObject が欲しい!ってことなんですけどね。
マルチバイト用のSplFileObject、mb_SplFileObjectみたいなもの標準で入れてくんないのかな・・・。

ってなわけで?、
SplFileObjectから派生したクラスを定義。ついでに、read / readAll / each メソッドを追加。これは自己満。
コンストラクタでCP932なCSVファイルを引き受けて、UTF-8に変換した作業用ファイルを作成して、デストラクタで消去。
はじめは file_get_contentsで一気に変換しようかと思ったが、巨大サイズのファイルを渡されるとmemory_limitに引っかかるので・・・(^^;
/usr/bin/nkf とか /usr/bin/iconv とかに丸投げしようかと思ったけど、Windows環境だとメンドーだし。

とりあえず、 jQuery のeachみたいな感じのものが欲しかったので・・・

<?php
//エクセルから排出したCSVは・・・
$csv = new Csv($csvpath); 

//CSVがUTF-8なら文字コード変換はスルーする
// というか、CSVファイルがUTF-8の場合は、SplFileObjectをそのまま使えばいいじゃん! ってことなんですけどね。
$csv = new Csv($utf8_csvpath,array('encoding' => 'UTF-8'));

//テスト出力
$csv->each(function($num,$i,$row) { printf("%03d : %03d : %s\n",$num,$i,$row[1]); });

クラス作るほどのものじゃないんだけどなー・・・・

<?php 
/*
  あんまテストしてない。動けばいいや的な。
*/
class Csv extends SplFileObject
{
  private static $DEFAULT_OPTIONS = array( 'remove' => false,
                                           'encoding' => 'SJIS-WIN',
                                           'mode'   => 'r');

  protected static function prepare(&$filepath,&$options)
  {
    $path = $filepath . '.utf8';
    $fout = new SplFileObject($path,'w');
    $fin = new SplFileObject($filepath);
    $fin->rewind();
    foreach($fin as $line)
      $fout->fwrite(mb_convert_encoding($line,'UTF-8',$options['encoding']));

    $fout->fflush();
    unset($fin,$fout);

    $filepath = $path;
    $options['remove'] = true;
  }
  
  protected $path;
  protected $options;

  // constructor & destructor
  public function __construct($csvpath,$options = array())
  {
    if(!is_array($options))
      throw new Exception("second argument is invalid type");
    $this->options = array_merge(self::$DEFAULT_OPTIONS,$options);
    $this->path = $csvpath;
    if(strlen($this->path) == 0)
      throw new Exception('CSV file path is required.');

    // change encoding...
    if(!preg_match('/utf-?8/i',$this->options['encoding']) && file_exists($this->path))
      self::prepare($this->path,$this->options);

    parent::__construct($this->path,$this->options['mode']);
    $this->setFlags(SplFileObject::READ_CSV);
  }
  public function __destruct()
  {
    if($this->options['remove'])
      unlink($this->path);
  }

  /**************************************************************************
    * read all and returns array of rows ( helper method )
  **************************************************************************/
  public function readAll($ignore_first = false)
  {
    return $this->read($ignore_first ? 1 : 0,-1);
  }

  /*********************************************************************
   * read and call $callable with CSV row.
   * $callable must be function with 3 arguments.
   * first argument is line number,
   * second argument is index number of loop,
   * third argument is array of row.
   *    placefolder:  function callable($linenumber,index,$row); 
   *  and if $callable returns -1, loop process is stop immediately.
  *********************************************************************/
  public function each($callable, $offset = 0)
  {
    return $this->read($offset,-1,$callable);
  }


  /***********************************************************************
   * read csv 
    if $length is -1, returns all. 
    if $callable is set, call $callable and return value num of calls
  ***********************************************************************/
  public function read($offset = 0,$length = 0,$callable = null)
  {
    $rv = false;
    if($length)
      {
        $is_call = $callable && is_callable($callable);
        $count = 0;
        $num = $offset;
        $ite = new LimitIterator($this, $offset, $length);
        foreach($ite as $row)
        {
          $num++;
          if(is_null($row[0]))
            continue;

          if($is_call)
            {
              if(!is_int($rv))
                $rv = 0;

              $result = call_user_func_array($callable,array($num,$count++,$row));
              $rv++;
              if(intval($result) < 0)
                break;
            }
          else
            {
              if(!is_array($rv))
                $rv = array();

              $rv[] = $row;
            }
        }
      }

    return $rv;
  }
}

PHP配列の悪夢、再び

おれって、相当アホ!!! ってな備忘録。

こんなコードを書いた。(実際に書いたコードはもっと複雑ですが・・・)

&lt;?php
/******************************

 配列の中身を出力する。だけ。

******************************/
function array_wrong($files)
{
  $num = count($files);
  for($i=0;$i&lt;$num;$i++)
    {
      printf(&quot;%d = %sn&quot;,$i,@$files[$i]);
    }
}

もうこの時点で、「お前の書くコードなんて一切使わない」宣言されるだろう。
はい、そうですね。僕もそう思います。(T-T)

こんな基本的な間違いを平気で犯す自分に自己嫌悪して数時間立ち直れませんでした・・・。

たぶん、次のような引数を渡すと期待するように動作するでしょう。

&lt;?php
$files = array('abc.txt',
               'xyz.pdf',
               'def.jpg');

//たぶん上手くいく。オール、オッケー
array_wrong($files);

しかし・・・次のようにしたら・・・

&lt;?php
$files = array('abc.txt',
               'xyz.pdf',
               'def.jpg');

unset($files[1]);

$files[10] = 'zzzz.gif';
$files[7] = 'bbbbb.ai';

array_wrong($files);

僕が期待した出力には当然なりません。とほほ。

こういうバカ・コードが原因だと気づくのに、半日要してしまったよ・・・・。
PHPの配列とcount関数の挙動が全然理解してない証拠っすね。。。恥ずかしい。。。

原因が分かれば・・・foreachもしくは、count関数を使わず・・・ループすればいい。

&lt;?php
function array_right($files)
{
  ksort($files);
  foreach($files as $i =&gt; $file)
    {
      printf(&quot;%d = %sn&quot;,$i,@$files[$i]);
    }
}

//もしくは・・・count()関数でループ回数を決定するのではなく配列キーの最大値を利用する。
function array_right($files)
{
  ksort($files);
  $num = max(array_keys($files));
  for($i=0;$i&lt;$num;$i++)
    {
      if(isset($files[$i]))
        printf(&quot;%d = %sn&quot;,$i,@$files[$i]);
    }
}

はぁ・・・。いつになったら使える人間になれるんだろう・・・。

MySQL + UPDATE + PDOStatement::rowCount の罠

MySQLのセットアップはテーブル作成とかも含めて、非常に面倒なので、作成途中(開発中)はSQLiteで作って動作確認して、作成大詰めの段階でMySQLに切り替え完成・・・という工程はわりかし一般的?だと思う。ファイル一個でバックアップ・リストアも簡単で開発効率も上がります、僕は。

で、SQLite + PDO で何の問題もなくある程度コーディングが終わり、MySQLに移行して検証していると、おかしな挙動の解決に半日かかってしまった備忘録のエントリです。

データを空更新、特定の行を取得して、編集画面表示、その後、内容を変えずに同じデータで更新すると、update文は成功するのに、作用した行数が0を返す・・・。だいたい下のような感じ。

&lt;?php
/*************************************************************

  あらかじめ下記mysqlクライアントで実行

  &gt;&gt; CREATE TABLE test_table(id INTEGER,data CHAR(255));
  &gt;&gt; INSERT INTO test_table values(1,'Kenji Nakagawa');

**************************************************************/
$pdo = new PDO('sqlite:log.sqlite');

if(modify_name(1,'Kenji Nakagawa'))
{
  header('location: list.php');
}

function modify_name($id,$name)
{
  global $pdo;

  $rv = false;
  $sql = 'update test_table set name = ? where id = ?';

  if(false !== ($stmt = $pdo-&gt;prepare($sql)))
    {
      if(false !== ($result = $stmt-&gt;execute(array($name,$id))))
        {
          $rv = $stmt-&gt;rowCount();
        }
    }

  return $rv;
}

この一連のコーディングでの最大の失敗は、rowCount()メソッドが返す行数で、update文の成功・失敗を判断したところ。分かってしまえば、何でもないことだけど、はまってしまった。

分かったことは、

MySQL + UPDATE文の実行では、PDOStatement::rowCount() は「実際に変更した(あった?)行数」(←ここ重要)を返す

・・・・ということ。

元々のレコードと同じデータをupdateすると、update文の実行はfalseは返さない(成功する)が、rowCount()は1ではなく、0を返す・・・。PHPサイトのドキュメントを検索したらちゃんと書いてありました・・・・とほほ(T-T)

UPDATE を使用する場合、MySQL では新旧の値が同じときには更新処理を行いません。 このことから、必ずしも mysql_affected_rows() の返す値が マッチする行の数と一致するとは限りません。返す値は実際に更新処理が行われた 行の数です。

そんな・・・・僕が勉強したときにちょろっと読んだMySQL入門書には書いてない!(笑)

ってわけで、解説書なんかで勉強するときは、いかに良い本に巡り会うことの重要性を改めて痛感しました。。。

SJISで組まれたPHPスクリプトをUTF-8で出力する。

【追記 2015年2月9日】
文字化け対策で、mb_convert_encoding関数に渡す漢字コードは’SJIS’ ではなく、’SJIS-WIN’ ( ‘CP932’) を使えとのお達し。ごめんなさい(m_m)
【追記ここまで】

年初からなんやかんやと忙しく更新する時間が取れないので、放置状態でした(ーー;;
ここ一ヶ月、年度末でなかなかの作業量。

備忘録のメモです。

外注している、SJISで組まれたPHPスクリプトをヘッダとフッターを変えUTF-8にする、という簡単な仕事。時間が迫っているので、外注先に投げる時間がない。ヘッダとフッタを変えてスクリプトファイルの文字コードをUTF8にしただけでは、文字化けしてしまう。そりゃ当然、 データーベースに保存されているデータはSJISのままなんだから・・・。

しかもデーターベースには手が出せない。それに外注先のスクリプトをこちらで勝手にいじると、まずい。何かと問題がある。

というわけで・・・

元のPHPスクリプトファイルを XXXX-sjis.phpのようにして、XXXX.phpから以下のようにしてXXXX-sjis.phpを読み込んでエンコード変換して出力することで、一応解決。

&lt;?php
//XXXX-sjis.php
echo 'これはSJISファイルスクリプト';
?&gt;

&lt;?php
// XXXX.php
$sjis_contents = file_get_contents('http://host/XXXX-sjis.php');
echo mb_convert_encoding($sjis_contents,'UTF-8','SJIS');

本当は以下のように、元のファイルに出力バッファリング・ハンドラを追加すれば解決するんですが・・・元のファイルはあまりいじりたくないので・・・(^^;;;;

&lt;?php
function output_handler($buf)
{
  return mb_convert_encoding($buf,'UTF-8','SJIS');
}

ob_start('output_handler');
//XXXX-sjis.php
echo 'これはSJISファイルスクリプトですが、出力はUTF-8になります。

ob_end_flush();
?&gt;

とりあえずの応急処置・・・でなんとか切り抜けた(^^ゞ

でも・・・わざわざfile_get_contents関数なんて使わないで、requireすれば良かったのか????

&lt;?php
// XXXX.php
function output_handler($buf)
{
  return mb_convert_encoding($buf,'UTF-8','SJIS');
}

ob_start('output_handler');

require 'XXXX-sjis.php';

ob_end_flush();

まぁ、いいや。