VB.NETで、SQLiteへupsert文を実行したい

開発環境:

Visual Studio Express 2017
.NET Framework 4.6.1
DB browser for SQLite
解決したいこと

VB.netにて、windowsフォームアプリを開発中。
ボタンフォーム押下で実行する関数内で、
SQLCON.ExecuteQuery() コマンドで、sqliteのテーブルのデータをupsertしたい。
SQLiteのテーブル構造

delivery_date text(yyyy-mm-dd) PK
product_type  text             PK
stock_A       integer
stock_B       integer
stock_C       integer
stock_D       integer
stock_E       integer

実際のコード(VB)

Dim DT As DataTable
Dim StrSQLString As String = String.Empty 
StrSQLString = "INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E)" +
               "VALUES('2020-08-03','aaa',0,0,0,0,0)" +
               "on CONFLICT(delivery_date,product_type) " +
               "DO UPDATE SET stock_A = 0, stock_B = 0, stock_C = 0, stock_D = 0, stock_E = 1"
DT = SQLCON.ExecuteQuery(StrSQLString)

VisualStudioのデバッグモードで、上記コードを実行したときのダイアログ

SQL logic error near "on":syntax error

確認したこと

ほかのSQL文をstrSQLStringに入れて実行し、問題なく動作した。(insert文、select文など)
DB browser for SQLiteのコンソール(SQL実行タブ)で上記strSQLstring内のupsert文を実行し、問題なく処理された。
(実際には、DB browserで通ったSQL文を、VBのコードで文字列として再度記述しています。
視認性をよくするために改行を行っている以外は完全にコピペです。)
デバッガで見た、strSQLStringの中身

StrSQLString = INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E) VALUES('2020-08-03','aaa',0,0,0,0,0) on CONFLICT(delivery_date,product_type) DO UPDATE SET stock_A = 0, stock_B = 0, stock_C = 0, stock_D = 0, stock_E = 1
SQLite error (1): near "on": syntax error
※VALUES前とon前のスペースは、空いている・空いていない版の両方を試しましたが、結果は同じでした。

ご存じの方、ご指導のほど、よろしくお願いいたします。

YAmaGNZ

2020/08/27 10:59

「DB browser for SQLiteのコンソール(SQL実行タブ)で上記strSQLstring内のupsert文を実行し、問題なく処理された。」とのことですが、これはデバッガ等でstrSQLstringの内容を出力して、それをコピペして実行されたのでしょうか？

Kyohei-Kondo

2020/08/27 11:09 編集

コメントありがとうございます。この業界に入って日が浅いので、あまり語彙もなく、うまく説明出来ているかわかりませんが、ご容赦ください。実際には逆の順序で行っております。 ①DB browser for SQLite 内のSQL実行タブで、upsert文を作成し、動作を確認 ②①で実行したSQL文を、VBのコード内で文字列変数として記述し、SQLCON.ExecuteQuery()の引数として使用する(ここがコピペです。) この②実行時にsyntax errorが発生したため、「DB browser側では通ったのに！」と困っている、という具合です。ほかのSQL文も同じ具合でVB側に実装していますが、問題なく動作しており、insertやselect文の結果をwindowsフォーム上に表示できているため、引数の渡し方や型が違っているとも思えず…

YAmaGNZ

2020/08/27 11:23

では、デバッガなどでstrSQLstringのSQLを出力するなりして確認してみてください。スペースが無いためにエラーになっていたり等考えられませんか？他のツールで実行確認したSQLをプログラムに実装する手順はいいのですが、動作しない場合はプログラムで実際に実行しているSQLが本当に動作するものなのかを確認する必要があります。

Kyohei-Kondo

2020/08/27 11:35

ありがとうございます。本文に、デバッガで出力したstrSQLStringの文面を追記いたしました。ちなみに、これ(デバッガ文面)を再度DB browser側にコピペして実行しましたが、通りました。不思議です…

YAmaGNZ

2020/08/27 11:46 編集

どうも掲載されているソースとSQLを出力した時のソースが異なるようです。掲載されたソースをそのまま実行して得られるSQLは INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E)VALUES('2020-08-03','aaa',0,0,0,0,0)on CONFLICT(delivery_date,product_type) DO UPDATE SET stock_A = 0, stock_B = 0, stock_C = 0, stock_D = 0, stock_E = 1 でVALUES句部分やonの部分のスペースが違います。

Kyohei-Kondo

2020/08/27 12:08 編集

すみません。スペースのあるなしも色々試行してみたので、若干バージョンが違いました。 valueやonの前にスペースがあるかないかの違いと思いますが、あってもなくても結果は同じようです。（VBではエラー、DB browser側では通る）一応ダメ元で、valuesとon前の２か所に対し、スペースのあるなしを変えて４パターン行いましたが、結果は変わりませんでした。

行動規範の内容に同意します

回答1件

ベストアンサー

VBNET
1Using conn As New SQLiteConnection("Data Source=d:\test\test.db")
2
3    ' データベースオープン
4    conn.Open()
5
6    Using command = conn.CreateCommand()
7        Dim StrSQLString As String = String.Empty
8        StrSQLString = "INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E)" +
9        "VALUES('2020-08-03','aaa',0,0,0,0,0)" +
10        "on CONFLICT(delivery_date,product_type) " +
11        "DO UPDATE SET stock_A = 0, stock_B = 0, stock_C = 0, stock_D = 0, stock_E = 1"
12
13        command.CommandText = StrSQLString
14        command.ExecuteNonQuery()
15
16    End Using
17
18    ' データベースクローズ
19    conn.Close()
20
21End Using
22

上記ソースでエラーは発生しませんでした。

実行できるようなソースは提示できませんか？

投稿2020/08/27 12:39

YAmaGNZ

総合スコア10702

Kyohei-Kondo

2020/08/27 15:46 編集

色々とありがとうございます。長くて恐縮ですが、本プログラムから、実行できる範囲で関係ありそうな部分だけを抜き出すと以下の通りです。 Form1.vb ``` Imports Microsoft.Office.Interop Imports System.Runtime.InteropServices Public Class Form1 Dim DataBaseName As String = "test.sqlite3" 'ファイル名(拡張子はdb,sqlite,sqlite3何でもOK) Dim StrDatabasePath1 As String = SetFolderNameAddYen(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) & DataBaseName Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load TextBox1.Text = StrDatabasePath1.ToString End Sub Private Shared Function SetFolderNameAddYen(ByVal _StrFolderName As String) As String '引数が空文字の場合処理を抜ける If _StrFolderName = String.Empty Then Return _StrFolderName 'フォルダ名の末尾に "\" が無い場合、フォルダ名の末尾に "\" を付ける。 If Not _StrFolderName.EndsWith("\") Then _StrFolderName += "\" End If Return _StrFolderName End Function Private Sub MakeGrid2() Dim DT As DataTable Dim StrSQLString As String = String.Empty Try Dim SQLCON = New SQLite_NET(StrDatabasePath1) 'StrSQLString = "SELECT MIN(delivery_date),MAX(delivery_date) FROM test_aggregate WHERE product_type ='aaa'" 'StrSQLString = "INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E)" + ' " VALUES('2020-08-04','aaa',0,0,0,0,0)" StrSQLString = "INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E) ON CONFLICT(delivery_date,product_type) DO UPDATE SET stock_A = 0, stock_B = 0, stock_C = 0, stock_D = 0, stock_E =0" Debug.WriteLine("StrSQLString = " + StrSQLString) SQLCON.Open() 'データベースに接続 DT = SQLCON.ExecuteQuery(StrSQLString) 'SQL文実行 SQLCON.Close() 'データベースを切断 If Not DT.Rows.Count = 0 Then DataGridView1.DataSource = DT 'カラムの編集を不可にする For i As Integer = 0 To DataGridView1.Columns.Count - 1 DataGridView1.Columns(i).ReadOnly = True Next End If Catch ex As Exception MessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click MakeGrid2() End Sub End Class ``` SQLite_NET.vb ``` Imports System.Data.SQLite Imports System.Data.SQLite.Linq ''' <summary> ''' SQLite処理用クラス ''' 参照：System.Data.SQLite.dll ''' 参照：System.Data.SQLite.Linq.dll ''' 注意：SQLite.Interop.dllは手動で実行フォルダにコピーしておくこと ''' </summary> Public Class SQLite_NET Private _conn As New SQLiteConnection 'SQliteコネクション用 Private _cmd As New SQLiteCommand 'SQliteコマンド用 Private _successCount As Integer 'DataProcessing内で実行したSQL(INSERT/UPDATE/DELETE)の成功総数 Private _failureCount As Integer 'DataProcessing内で実行したSQL(INSERT/UPDATE/DELETE)の失敗総数 '' DataProcessing内で実行したSQL(INSERT/UPDATE/DELETE)の失敗総数 Public ReadOnly Property FailureCount() As Integer Get Return _failureCount End Get End Property ''' DataProcessing内で実行したSQL(INSERT/UPDATE/DELETE)の成功総数 Public ReadOnly Property SuccessCount() As Integer Get Return _successCount End Get End Property ''' <summary> ''' 初期化（データベースファイルの指定） ''' </summary> ''' <param name="dataSource">データベースファイルのフルパス</param> Public Sub New(dataSource As String) '接続文字を作成するコンストラクタ _conn.ConnectionString = "Data Source=" + dataSource End Sub ''' <summary> ''' DBファイルに接続 ''' </summary> ''' <returns>True:成功　False:失敗</returns> Public Function Open() As Boolean Try _cmd = _conn.CreateCommand() _conn.Open() Return True Catch sqlex As SQLiteException System.Diagnostics.Debug.WriteLine(sqlex.Message) Catch ex As Exception System.Diagnostics.Debug.WriteLine(ex.Message) End Try Return False End Function ''' <summary> ''' DBファイルの切断 ''' </summary> ''' <returns>True:成功　False:失敗</returns> Public Function Close() As Boolean Try If _conn.State = ConnectionState.Open Then 'データベースが開いているか確認 'データベースが開いていれば閉じる _conn.Close() End If Return True Catch sqlex As SQLiteException System.Diagnostics.Debug.WriteLine(sqlex.Message) Catch ex As Exception System.Diagnostics.Debug.WriteLine(ex.Message) End Try Return False End Function ''' <summary> ''' SQL（SELECT文）の実行 ''' </summary> ''' <param name="strSQL">SQL文</param> ''' <returns>取得したデータを格納するDataTable</returns> Public Function ExecuteQuery(strSQL As String) As DataTable Dim adapter = New SQLiteDataAdapter() Dim dtTbl As New DataTable() Try 'データがオープンしていない場合には、なにもないdtTblを返す If _conn.State <> ConnectionState.Open Then Return dtTbl adapter = New SQLiteDataAdapter(strSQL, _conn) 'SQLを実行 'SQLを実行して、結果をdtTblに保存 adapter.Fill(dtTbl) '成功件数をカウント _successCount += dtTbl.Rows.Count Catch sqlex As SQLiteException Throw New Exception(sqlex.Message) Catch ex As Exception Throw New Exception(ex.Message) End Try Return dtTbl End Function ''' <summary> ''' SQL（INSERT・UPDATE・DELETE）の実行 ''' </summary> ''' <param name="strSQL">SQL文</param> ''' <returns>処理件数</returns> Public Function ExecuteNonQuery(strSQL As String) As Integer Dim count As Integer = -1 Try 'データがオープンされていない場合には、「-1」を返す If _conn.State <> ConnectionState.Open Then Return count _cmd.CommandText = strSQL count = _cmd.ExecuteNonQuery() '成功件数をカウント _successCount += count Catch sqlex As SQLiteException '失敗件数を絶対値でカウント _failureCount += Math.Abs(count) Throw New Exception(sqlex.Message) Catch ex As Exception Throw New Exception(ex.Message) End Try Return count End Function ''' <summary> ''' トランザクション開始 ''' </summary> ''' <returns>True:成功　False:失敗</returns> Public Function BeginTransaction() As Boolean Try 'データベースをオープンする。 'オープンに失敗した場合には処理を抜ける If Not Open() Then Return False 'データベースがクローズ状態は処理終了 If _conn.State = ConnectionState.Closed Then Return False 'トランザクション処理 _cmd.Transaction = _conn.BeginTransaction() Return True Catch sqlex As SQLiteException System.Diagnostics.Debug.WriteLine(sqlex.Message) Catch ex As Exception System.Diagnostics.Debug.WriteLine(ex.Message) End Try Return False End Function ''' <summary> ''' ロールバック ''' </summary> ''' <returns>True:成功　False:失敗</returns> Public Function Rollback() As Boolean Try ' ロールバック実施 _cmd.Transaction.Rollback() Return True Catch sqlex As SQLiteException System.Diagnostics.Debug.WriteLine(sqlex.Message) Catch ex As Exception System.Diagnostics.Debug.WriteLine(ex.Message) End Try Return False End Function ''' <summary> ''' コミット ''' </summary> ''' <returns>True:成功　False:失敗</returns> Public Function EndTransaction() As Boolean Try 'コミット処理 _cmd.Transaction.Commit() Return True Catch sqlex As SQLiteException System.Diagnostics.Debug.WriteLine(sqlex.Message) Catch ex As Exception System.Diagnostics.Debug.WriteLine(ex.Message) End Try Return False End Function End Class ```

Kyohei-Kondo

2020/08/27 16:07

後学のためにはエラー原因も追究したいところですが、やり方に拘りがあるわけではないので、ご教示くださったやり方を試してみたのですが、残念ながら、同様のエラーが発生しました。（もともとのForm1の中で、頂いたUsing文を関数として実行しました） System.Data.SQLite.SQLiteException: 'SQL logic error near "on": syntax error' 明日、頂いたコードをもう少しシンプルな環境で実行してみて通るか確認いたします。

YAmaGNZ

2020/08/27 22:24

提示されたソースの Private Sub MakeGrid2() にて実行しているINSERTで合っていますか？もしそうなのだとしたら、質問で提示されたSQLと違うのですがコメントにて今回提示されたSQLを私が書いたコードで実行した場合、エラーとなります。本当に実行しているのはどちらのSQLなのですか？

YAmaGNZ

2020/08/27 22:26

ちなみに、質問で提示されたSQLをコメントで提示されたSQLiteを扱うクラスで実行した場合は正常終了しました。

YAmaGNZ

2020/08/27 22:28

質問で提示されたSQL INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E)VALUES('2020-08-03','aaa',0,0,0,0,0)on CONFLICT(delivery_date,product_type) DO UPDATE SET stock_A = 0, stock_B = 0, stock_C = 0, stock_D = 0, stock_E = 1 今回のコメントで提示されたSQL INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E) ON CONFLICT(delivery_date,product_type) DO UPDATE SET stock_A = 0, stock_B = 0, stock_C = 0, stock_D = 0, stock_E =0

Kyohei-Kondo

2020/08/27 22:37

すみません。コピペによる影響(文字コードか何か？)があるのかもと思い、再度手打ちでVB側のSQLを打ち直していたのですが、そこでVALUE句を忘れるミスを犯しました。本来実行したかったSQLは前者ですので、そちらに修正して再度実行します。

YAmaGNZ

2020/08/27 22:49

質問などする場合、ソースを手打ちする意味はまずありません。というか、今回のようにミスしたりと害悪しかないと言っていいかと思います。実際に動作確認したものをコピペするようにしてください。１．質問したい現象を確認する２．そのソースを質問として提示するです。１と２の間に自分でいろいろやってみて現象が修正できるか試したくなるのは分かりますが、それは１の前にやるべきこととなります。また、質問した後に修正する場合は、２で提示したソースはそのままコメントアウトするなりして残しておきましょう。コメントのやりとりしている時に質問時のソースとは違うものを実行していては話がかみ合わなくなります。今回のコメントでのSQLを実行した時のエラーが「SQL logic error near "on":syntax error」でした。

Kyohei-Kondo

2020/08/27 23:10 編集

大変お手数おかけしました。以後、齟齬なきよう気を付けます。一応、以下の通り修正して改めて実行しました。 (VALUES句の抜けを修正、VALUESとONの前のスペースはあり) StrSQLString = "INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E) VALUES('2020-08-03','aaa',0,0,0,0,0) ON CONFLICT(delivery_date,product_type) DO UPDATE SET stock_A = 0, stock_B = 0, stock_C = 0, stock_D = 0, stock_E =0" デバッガで確認したstrSQLStringの中身 INSERT INTO test_aggregate(delivery_date,product_type,stock_A,stock_B,stock_C,stock_D,stock_E) VALUES('2020-08-03','aaa',0,0,0,0,0) ON CONFLICT(delivery_date,product_type) DO UPDATE SET stock_A = 0, stock_B = 0, stock_C = 0, stock_D = 0, stock_E =0 エラーログは同様でした。図らずも、私がVALUES句を忘れたSQLを実行していただいたときに、同様のエラーログが出たのが気になります。本当に実行されているSQL文が違う(実際にはVALUES句か何かが抜けた文が発行されている)という可能性はあるのでしょうか。一応、宣言文直後と使用直前にDebug句を置いて、問題ないことを確認しているのですが…

YAmaGNZ

2020/08/28 00:21

もし実際に実行されているSQLを確認されるのであれば、SQLite_NETクラスのExecuteQueryメソッドやExecuteNonQueryメソッドにてログを出力されるほうがよろしいかと思います。 INSERTを実行するのにExecuteQueryメソッドを呼ぶのではなくExecuteNonQueryメソッドを使いましょう。 INSERT文なのでDataTableを返す必要はないはずです。

Kyohei-Kondo

2020/08/28 06:39

ご回答遅くなりました。・ログ出力にはSQLite_NETクラスを使用　＞ありがとうございます。そのように致しました。　　幸い、ここで問題が生じてはいませんでした。・INSERT実行時にExecuteNonQueryを使用　＞仰る通り、DataTableを返す必要はありませんでしたので、変更しました。　　ただ、SQLite_NETクラスを使用すると、エラーログが「Unique制約に抵触する」という内容に変化しました。　　・回答欄でご教示いただいたコードで、SQL文の部分にText.StringBuilderを使用したところ、実行できました。 (参考)https://www.doraxdora.com/blog/2017/12/21/post-3519/ ●Form1.vb Imports System.Data.SQLite Public Class Form1 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click ' ボタンがクリックされたらデータを追加・更新 Using conn As New SQLiteConnection("Data Source=C:\Users(ユーザ名)\Desktop\test.sqlite3") ' データベースオープン conn.Open() ' テーブルが存在しなければ作成 Using command = conn.CreateCommand() Dim sb As New Text.StringBuilder() sb.Append("INSERT INTO test_aggregate(") sb.Append("delivery_date,product_type,stock_A,") sb.Append("stock_B,stock_C,") sb.Append("stock_D,stock_E)") sb.Append(" VALUES('2020-08-03','aaa',0,0,0,0,0)") sb.Append(" ON CONFLICT(delivery_date,product_type)") sb.Append(" DO UPDATE SET stock_A=0, stock_B=0,") sb.Append("stock_C=0, stock_D=0, stock_E=5") command.CommandText = sb.ToString() command.ExecuteNonQuery() End Using ' データベースクローズ conn.Close() End Using 'メッセージボックスを表示する MessageBox.Show("データが追加・更新されました。", "情報", MessageBoxButtons.OK, MessageBoxIcon.Asterisk) End Sub End Class 「最初のやり方ではなぜ出来なかったのか」が分からないままですが、このままずるずると付き合わせてしまうのも心苦しく、ご教示いただいた内容で解決したため、勝手とは思いますが、本件はこれでいったん終了とさせていただきたく思います。私の質問の仕方が拙いなか、丁寧にご対応いただきました、YAmaGNZ様には重ね重ねお礼を申し上げます。今後とも、よろしくお願いいたします。

行動規範の内容に同意します