「ほっ」と。キャンペーン
タグ:SQL ( 24 ) タグの人気記事
(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(ブログ)
[PR]
by Jehoshaphat | 2012-11-30 23:22 | SQL | Trackback | Comments(0)
(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 クエリの発行
[PR]
by jehoshaphat | 2011-07-03 00:25 | PHP開発 | Trackback(5) | Comments(0)
(MySQL)ログイン時のパスワード指定と外部SQLファイルの実行
余り普段使わない MySQL ではログインでも四苦八苦する時があります。

(MySQL)スクリプトファイルからクエリを実行する方法では、MySQLクライアントにログインしてから外部ファイルを読み込んでますが、今回はコマンドラインから直接外部ファイルを読み込むための方法です。

こんな感じでOKみたいです。

>mysql -h ホスト名 -u ユーザ -pパスワード DB名 < hogehoge.sql


ハマったのは -p の後に空白入れずにパスワードを入れる部分ですね。(空白を入れてしまっていました)
外部ファイルは入力リダイレクト(<)でファイルを指定するだけでいけます。
[PR]
by jehoshaphat | 2009-08-21 10:57 | SQL | Trackback | Comments(0)
(SQL)SQL Server 2005 でDBログインユーザとデータベースユーザの一覧取得
SQL Server のデータベースログインユーザと、各データベースのユーザをSQLで列挙する方法です。

ログインユーザは master データベースの syslogins テーブルに定義されているようです。
データベースユーザは各データベースの sysusers テーブル定義されているようです。

下記のようなSQLで確認できます。

/*ログインユーザ列挙*/
USE master;
SELECT * FROM syslogins;
 
/*データベースユーザ列挙*/
USE DB名;
SELECT * FROM sysusers;
 
/*おまけ。データベースの一覧列挙*/
USE master;
SELECT name FROM sysdatabases;


参考:
KYO'S ROOM 【SQL Server】:
[PR]
by jehoshaphat | 2009-08-10 18:36 | SQL | Trackback | Comments(0)
OLEDB JETでクエリを投げると、式が複雑すぎますと怒られた

ADO.Net を使って MDBへ のクエリ実行時に「式が複雑すぎます。」という例外で怒られました。

確かに、WHERE句に相当数の条件を入れています。
例えばこんな感じ。(パラメータクエリを使ってます。詳しくは(ADO.Net)OLEDB経由でのMDBへの副問い合わせにパラメータクエリを使うとおかしくなることがある)

PARAMETERS
pCondBirthday1 Char ,
pCondBirthday2 Char ,
pCondBirthday3 Char ,
pCondPref1 Text ,
pCondPref2 Text ,
pCondPref3 Text ;
SELECT *
FROM Customer
WHERE
(
Birthday = pCondBirthday1 OR
Birthday = pCondBirthday2 OR
Birthday = pCondBirthday3 OR
[省略]
) AND (
Pref = pCondPref1 OR
Pref = pCondPref2 OR
Pref = pCondPref3 OR
[省略]
)

どうやら WHERE句に条件大量にあるとダメみたいです。具体的のどれくらいの数を超えたらNGかというのは見つかりませんでした。

で、上記のSQLの場合、特に一つのフィールドに対して複数の OR 条件が大量の設定してます。
これは IN句を使うべきと同僚に言われてました。
言われてみれば、そんなのもあったなと。。今まで、副問い合わせで複数値が返るときにか使ってませんでした。。

ということで、下記のように OR 条件を IN句に置き換えたらエラーはでなくなりましたとさ。
(パラメータ定義部分は省略)

SELECT *
FROM Customer
WHERE
Birthday IN ( pCondBirthday1 ,
pCondBirthday2 ,
pCondBirthday3 ,
[省略]
)
AND
Pref IN ( pCondPref1 ,
pCondPref2 ,
pCondPref3 ,
[省略]
)

ま、結局は3流PGのSQLの知識不足ということでした。

参考:
「式が複雑すぎます」の具体的な条件
[PR]
by jehoshaphat | 2009-08-02 01:13 | SQL | Trackback | Comments(0)
AccessとOLEDB JET経由でLIKE文のワイルドカードが変わる

Access で LIKE 演算子で0文字以上の文字を表す時は * を使いますよね。
ところが、JETエンジン使って mdb にアクセスする OLEDB経由の ADO.Net だと % という純粋なSQLに近い形しないとダメなようです。

/*Accessだとヒットする。 OLEDB経由だとヒットしない*/
SELECT *
FROM Customer
WHERE Address LIKE '*京都*'
 
/*Accessだとヒットしない。 OLEDB経由だとヒットする*/
SELECT *
FROM Customer
WHERE Address LIKE '%京都%'


Access でテスト用のクエリを書いて、.Net のコードに移植してたんですが、これに気付かずちょっとハマってしまいした。
Visual Studio 上のサーバエクスプローラから mdf ファイルを指定し、クエリたたけば OLEDB 経由になるので、これからはこっち使ったほうが安全ですね。

この仕様については、Microsoft Jet SQL と ANSI SQL との比較で解説されてます。
下記の引用部分が、その仕様について言ってるところですね。
ANSI SQL のワイルドカード文字を使用できるのは、Jet 4.x と Microsoft OLE DB Provider for Jet を組み合わせて使う場合だけです。Microsoft Access または DAO を介して ANSI SQL のワイルドカード文字を使うと、リテラルとして解釈されます。Microsoft OLE DB Provider for Jet と Jet 4.x を使用しているときは、Jet SQL のワイルドカード文字がリテラルとして解釈されます。
[PR]
by jehoshaphat | 2009-07-31 12:39 | SQL | Trackback | Comments(0)
(ADO.Net)OLEDB経由でのMDBへの副問い合わせにパラメータクエリを使うとおかしくなることがある

OleDbCommand , OleDbDataAdapter オブジェクトを使って、SQLクエリを書いたんですが、どうも挙動がおかしいのです。
SQLには WHERE,FROM 句の中で副問い合わせ(サブクエリ)を多数使い、サブクエリ内で OleDbParameter を使いパラメータで抽出条件となる値を設定してます。

で、サブクエリを書いた位置によって、「抽出条件でデータ型が一致しません。」とかいうエラーがでたり、明らかに条件に一致するデータがあるのに抽出できなかったりとするのです。

どうやら何かの原因でパラメータの順序が入れ替わっているようです。
(もともと OLEDB のパラメータクエリは、プレースホルダを ? で表現し、OleDbCommand.Parameters に追加した順番で適用されます。)

いろいろ検索してると、MSサポート:OLE DB Provider for Jet 4.0 または Office 12.0 Access Database Engine OLE DB Provider を使用時にサブ クエリを持つパラメータ クエリを実行すると、予期しない結果を返す場合があるというのを見つけました。

どうやら、この問題は JET のバグっぽいですね。

対策としては、上記のページにあるようにメインのSQLクエリの前に
PARAMETERS p1 Text,p2 Long; SLECT ....
という感じで PARAMETERS を使い、ここで宣言したパラメータ変数(?)の順番で、パラメータを設定すればいいようです。


この原因探るのに数時間かかりました。この現象気付く人も結構少ないんじゃないでしょうか。。
バグってのはほんと勘弁してほしいですよね。
[PR]
by jehoshaphat | 2009-07-28 22:46 | .Net開発 | Trackback | Comments(0)
(SQL)現在の日時を数値(整数)で落としたい

SQL Server で現在日時を取得して、整数の文字列として保存する方法です。
あんまり需要は無いかもしれませんが、今回保存する汎用テーブル構造が キー:nvarchar(50) , 値:nvarchar(50) なので、こういう方法で。。。

下記のように convert 関数を使って、いったん timestamp 型に変えた後、bigint に変える方法にしてみました。
bigint の値から nvarchar 型のフィールドへは暗黙的キャストで入れれるので、特に何もしてません。

convert(bigint, convert(timestamp,GETDATE(),121 ),2)


参考:
TechNet:CAST および CONVERT (Transact-SQL)
TechNet:データ型 (Transact-SQL)
[PR]
by jehoshaphat | 2009-07-18 00:19 | SQL | Trackback | Comments(0)
(SQL Server)mdfファイルを別SQL Serverに移行した時のDBユーザの問題
例えば、SQL Server で "testDB" というデータベースを運用しているとします。
この SQL Server には "userA" という SQL ユーザがおり、SQL 認証でのログインが可能で、"testDB" に対して db_owner のロールを持っているとします。
e0091163_15592288.jpg


この "testDB" データベースをデタッチして、新たに SQL Server をインストールした別のサーバに "testDB" の mdf,ldf ファイルを移してアタッチします。

この時、どうやら mdf ファイルに前のサーバで設定した データベースユーザの情報が残っているらしく、Management Studio でみると、"testDB" のセキュリティに "userA" が表示されます。
ただ、SQL Server 全体のセキュリティには表示されません。
ちょうど下記のような状態です。(SQL Server からユーザ削除した時と同じ現象ですね。)
これは "testDB" の "userA" のプロパティです。ログインができなくなってます。
e0091163_16124921.jpg


この状態で、下記のように "userA" を作っても、うまく "testDB" とのマッピングができません。

USE [master]
GO
CREATE LOGIN userA WITH PASSWORD=N'1234', DEFAULT_DATABASE=testDB, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE testDB
GO
CREATE USER userA FOR LOGIN userA
GO
USE testDB
GO
EXEC sp_addrolemember N'db_owner', N'userA'
GO

このように、マッピングされていない状態となってしまいます。
e0091163_16132227.jpg


それで、上記のコマンドの前にいったん "testDB" に関連付けられてる "userA" を削除する下記 SQL を実行したら、上記コマンド実行後にちゃんと "testDB" と "userA" がマッピングされるようになりました。

USE [testDB]
GO
DROP USER [userA]
GO


まあ、Management Studio から手動でマッピングしてもいいんですが、パッケージ製品として、インストール時にバッチファイル等で初期設定するときとかは SQL コマンドでやったほうが楽ですね。
[PR]
by jehoshaphat | 2009-07-08 16:15 | サーバがらみ | Trackback | Comments(0)
(.Net)SQL Server でバックアップ時(復元時)の進行状況を取得しプログレスバーに表示する(ソース編)
(.Net)SQL Server でバックアップ時(復元時)の進行状況を取得しプログレスバーに表示する(説明編)の続きとういより、文字数オーバーで載せれなかったコードです。
(なお、プログレスバーを貼り付けているフォームクラスのコードは面倒なので掲載してません。
クラス名は Progress で、プログレスバーコントロール名は ProgressBar です。)


Public Class TestBackup
 
''' <summary>
''' SQL Server のバックアップを行う(バックアップファイル名は日時.bak)
''' エラー時、成功時ともに、イベントビュアーにログを残す
''' </summary>
''' <returns>True:正常 False:異常</returns>
Public Function BackupNow() As Boolean
'コネクションオブジェクト作成
Dim conn As SqlConnection = Me.GetDbConnection()
'プログレスバー表示クラス生成
prg = New ProgressBarControl()
 
Try
conn.Open()
'コマンドオブジェクト生成
Dim cmd As New SqlCommand()
cmd.Connection = conn
'STATS = 1 なので1秒おきに進捗率を通知
cmd.CommandText = "BACKUP DATABASE [testdatabase] TO DISK = N'C:\test.bak' WITH NOFORMAT, INIT, NAME = N'テスト', SKIP, NOREWIND, NOUNLOAD, STATS = 1"
'復元時だとこういうコードになります。
'cmd.CommandText = "RESTORE DATABASE [testdatabase] FROM DISK = N'C:\test.bak' WITH FILE = 1, NOUNLOAD, REPLACE , STATS = 1"
'これをTrueしないとInfoMessageがまとめて発行されてしまう(ただし、TrueにするとSQLクエリ実行中に例外発生してもスローしないので注意!)
conn.FireInfoMessageEventOnUserErrors = True
'進捗情報取得のためのイベントハンドラを追加
AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf SqlConnect_InfoMassage)
'プログレスバー表示(別スレッド)
prg.Show()
'バックアップクエリ実行
cmd.ExecuteNonQuery()
Catch ex As Exception
'エラー処理のコードがここに入る(ログに残すとかメッセージボックスとか)
Return False
Finally
conn.Close()
If prg IsNot Nothing Then
'プログレスバー表示用別スレッドを停止。プレブレスバー表示フォームを閉じる。
prg.Close()
End If
End Try
Return True
End Function
 
 
''' <summary>
''' 進行状況ダイアログのプログレスバーに進捗を表示する。
''' SqlConnection.InfoMessageのイベントハンドラ。
''' </summary>
Private Sub SqlConnect_InfoMassage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)
Dim conn As SqlConnection = DirectCast(sender, SqlConnection)
For Each sqlErr As SqlError In e.Errors
'エラーの重要度が10より大きいなら例外発生させ処理停止
If sqlErr.Class > 10 Then
'Falseにすることで、次回より例外がスローされるようになる
conn.FireInfoMessageEventOnUserErrors = False
'エラーを ApplicationExceptionとして例外生成(しかし、この例外はスローされないっぽい。この次の例外からスローされる)
Dim ex As New ApplicationException(sqlErr.ToString())
'この下のThrowでは実際にはThrowされないようなので、ここでイベントビュアーにエラー出力
'エラー処理のコードがここに入る(ログに残すとかメッセージボックスとか)
Throw ex
End If
Next

'メッセージを分解し、intに変換
Dim idx As Integer = e.Message.LastIndexOf(" パーセント処理されました。")
If idx < 1 Then
Return
End If
'パーセント
Dim intPer As Integer
Try
intPer = CType(e.Message.Substring(0, idx), Integer)
Catch ex As Exception
Return
End Try
'プログレスバー表示更新
If prg IsNot Nothing Then
prg.ValuePercent = intPer
End If
End Sub
End Class
 
 
''' <summary>
''' プログレスバーフォームを別スレッドで表示さすためのラッパクラス
''' </summary>
Public Class ProgressBarControl
 
'進行状況表示クラス(このクラスのコードは省略。ただフォームにプログレスバーを乗せてるだけ)
Private m_statusForm As Progress
'現在のプログレスバーの値
Private m_ValuePercent As Integer
 
''' <summary>
''' プログレスバーの値を設定、取得するプロパティ
''' </summary>
Public Property ValuePercent() As Integer
Get
Return m_ValuePercent
End Get
Set(ByVal value As Integer)
m_ValuePercent = value
If m_statusForm IsNot Nothing Then
'm_statusFormのインスタンスを持っているスレッドで、SetProgressValuePercentメソッドを処理させる
m_statusForm.Invoke(New MethodInvoker(AddressOf SetProgressValuePercent))
End If
End Set
End Property
 
''' <summary>
''' プログレスバーを閉じる。(と同時にプログレスバー表示スレッドも自然と消える)
''' </summary>
Public Sub Close()
If m_statusForm IsNot Nothing Then
'm_statusFormのインスタンスを持っているスレッドで、ProgressCloseメソッドを処理させる
m_statusForm.Invoke(New MethodInvoker(AddressOf ProgressClose))
End If
End Sub
 
 
''' <summary>
''' プログレスバーを表示する。プログレスバー表示クラスを別スレッドで作成し、表示。
''' </summary>
Public Sub Show()
Dim thread As Threading.Thread = New Threading.Thread(AddressOf Run)
thread.Start()
End Sub
 
''' <summary>
''' 別スレッドで動くメソッド。プログレスバー表示クラスのインスタンスを作成し、ShowDialog する。
''' </summary>
Private Sub Run()
m_statusForm = New Progress()
m_statusForm.ShowDialog()
m_statusForm.Dispose()
End Sub
 
 
''' <summary>
''' プログレスバーの値を更新する(MethodInvoker デリゲートから呼ばれる)
''' </summary>
Private Sub SetProgressValuePercent()
m_statusForm.ProgressBar.Value = m_ValuePercent
End Sub
 
''' <summary>
''' プログレスバーを閉じる(MethodInvoker デリゲートから呼ばれる)
''' </summary>
Private Sub ProgressClose()
m_statusForm.Close()
End Sub
 
End Class

[PR]
by jehoshaphat | 2009-04-28 01:36 | .Net開発 | Trackback | Comments(0)