SQLiteDataReader has already read all rows in first result set

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLiteDataReader has already read all rows in first result set

Guy St-Denis
New SQLite user... trying to understand the following issue.
(Spent several hours on Google, Stack Overflow, SQLite mailing list archive... found nothing satisfactory.)

[ISSUE]
As far as I can tell, after calling ExecuteReader(), I cannot Read() any rows in the *first* result set because the returned DataReader has *already* Read() the rows.
Calling NextResult() moves to the next result set, and then Read() allows me to step through the rows in the *second* (and subsequent) result sets as expected.

[CONFIGURATION:SQLITE]
sqlite-netFx46-binary-x64-2015-1.0.111.0\System.Data.SQLite.dll
SQLiteSourceId=2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
SQLiteVersion=3.28.0
InteropVersion=1.0.111.0
ProviderSourceId=767b97f17029698d929f3fd9be563f51942b1805 2019-05-16 03:23:41 UTC
ProviderVersion=1.0.111.0

[CONFIGURATION:OTHER]
Windows 10 Pro
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 58 Stepping 9, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=3a09
.NET Framework=4.7.2
CLRVersion=4.0.30319.42000
PSVersion=5.1.17134.858
PSBuildVersion=10.0.17134.858

[STEPS TO REPRODUCE]
### SETUP ###
PS D:\temp> Add-Type -Path 'D:\temp\sqlite-netFx46-binary-x64-2015-1.0.111.0\System.Data.SQLite.dll'
PS D:\temp> $dbConnection = New-Object -TypeName System.Data.SQLite.SQLiteConnection
PS D:\temp> $dbConnection.ConnectionString = 'Data Source=:memory:'
PS D:\temp> $dbConnection.Open()
PS D:\temp> $dbCommand = New-Object -TypeName System.Data.SQLite.SQLiteCommand
PS D:\temp> $dbCommand.Connection = $dbConnection
PS D:\temp> $dbCommand.CommandText = 'CREATE TABLE T1(C1,C2);'
PS D:\temp> $dbNonQuery = $dbCommand.ExecuteNonQuery()
PS D:\temp> $dbCommand.CommandText = 'INSERT INTO T1 VALUES (101,102),(201,202),(301,302);'
PS D:\temp> $dbNonQuery = $dbCommand.ExecuteNonQuery()
PS D:\temp> $dbCommand.CommandText = 'SELECT C1 AS X1,C2 AS X2 FROM T1;SELECT C1 AS Y1,C2 AS Y2 FROM T1;'
PS D:\temp> $dataReader = $dbCommand.ExecuteReader()

### ACTUAL RESULTS: *FIRST* RESULT SET ###
PS D:\temp> $dataReader.GetName(0)
X1
PS D:\temp> $dataReader.GetName(1)
X2
PS D:\temp> $dataReader.StepCount
3
PS D:\temp> $dataReader.HasRows
False
PS D:\temp> $dataReader.Read()
False
PS D:\temp> $dataReader.GetValue(0)
Exception calling "GetValue" with "1" argument(s): "No current row"
(...)
PS D:\temp> $dataReader.GetValue(1)
Exception calling "GetValue" with "1" argument(s): "No current row"
(...)
### ACTUAL RESULTS: *SECOND* RESULT SET ###
PS D:\temp> $dataReader.NextResult()
True
PS D:\temp> $dataReader.StepCount
4
PS D:\temp> $dataReader.GetName(0)
Y1
PS D:\temp> $dataReader.GetName(1)
Y2
PS D:\temp> $dataReader.HasRows
True
PS D:\temp> $dataReader.GetValue(0)
Exception calling "GetValue" with "1" argument(s): "No current row"
(...)
PS D:\temp> $dataReader.Read()
True
PS D:\temp> $dataReader.GetValue(0)
101
PS D:\temp> $dataReader.GetValue(1)
102
PS D:\temp> $dataReader.HasRows
True
PS D:\temp> $dataReader.Read()
True
PS D:\temp> $dataReader.GetValue(0)
201
PS D:\temp> $dataReader.GetValue(1)
202
PS D:\temp> $dataReader.HasRows
True
PS D:\temp> $dataReader.Read()
True
PS D:\temp> $dataReader.GetValue(0)
301
PS D:\temp> $dataReader.GetValue(1)
302
PS D:\temp> $dataReader.Read()
False
PS D:\temp> $dataReader.HasRows
False
PS D:\temp> $dataReader.GetValue(0)
Exception calling "GetValue" with "1" argument(s): "No current row"
(...)

--
Guy St-Denis
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLiteDataReader has already read all rows in first result set

Guy St-Denis
Issue is RESOLVED.

After more experimentation, I realized that I was causing the Read() operations in the first resultset by doing this after the ExecuteReader() call:
PS D:\temp> $dataReader
FieldCount
----------
         2
         2
         2

It would seem that asking PowerShell to 'output' an SQLiteDataReader instance forces it to Read() through the current resultset.

This seems to be what is observed here after moving to the second resultset.
PS D:\temp> $dataReader.NextResult()
True
PS D:\temp> $dataReader.HasRows
True
PS D:\temp> $dataReader
FieldCount
----------
         2
         2
         2

PS D:\temp> $dataReader.HasRows
False

--
Guy St-Denis

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Monday, July 15, 2019 12:34 PM, Guy St-Denis <[hidden email]> wrote:

> New SQLite user... trying to understand the following issue.
> (Spent several hours on Google, Stack Overflow, SQLite mailing list archive... found nothing satisfactory.)
>
> [ISSUE]
> As far as I can tell, after calling ExecuteReader(), I cannot Read() any rows in the *first* result set because the returned DataReader has *already* Read() the rows.
> Calling NextResult() moves to the next result set, and then Read() allows me to step through the rows in the *second* (and subsequent) result sets as expected.
>
> [CONFIGURATION:SQLITE]
> sqlite-netFx46-binary-x64-2015-1.0.111.0\System.Data.SQLite.dll
> SQLiteSourceId=2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
> SQLiteVersion=3.28.0
> InteropVersion=1.0.111.0
> ProviderSourceId=767b97f17029698d929f3fd9be563f51942b1805 2019-05-16 03:23:41 UTC
> ProviderVersion=1.0.111.0
>
> [CONFIGURATION:OTHER]
> Windows 10 Pro
> PROCESSOR_ARCHITECTURE=AMD64
> PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 58 Stepping 9, GenuineIntel
> PROCESSOR_LEVEL=6
> PROCESSOR_REVISION=3a09
> .NET Framework=4.7.2
> CLRVersion=4.0.30319.42000
> PSVersion=5.1.17134.858
> PSBuildVersion=10.0.17134.858
>
> [STEPS TO REPRODUCE]
> ### SETUP ###
> PS D:\temp> Add-Type -Path 'D:\temp\sqlite-netFx46-binary-x64-2015-1.0.111.0\System.Data.SQLite.dll'
> PS D:\temp> $dbConnection = New-Object -TypeName System.Data.SQLite.SQLiteConnection
> PS D:\temp> $dbConnection.ConnectionString = 'Data Source=:memory:'
> PS D:\temp> $dbConnection.Open()
> PS D:\temp> $dbCommand = New-Object -TypeName System.Data.SQLite.SQLiteCommand
> PS D:\temp> $dbCommand.Connection = $dbConnection
> PS D:\temp> $dbCommand.CommandText = 'CREATE TABLE T1(C1,C2);'
> PS D:\temp> $dbNonQuery = $dbCommand.ExecuteNonQuery()
> PS D:\temp> $dbCommand.CommandText = 'INSERT INTO T1 VALUES (101,102),(201,202),(301,302);'
> PS D:\temp> $dbNonQuery = $dbCommand.ExecuteNonQuery()
> PS D:\temp> $dbCommand.CommandText = 'SELECT C1 AS X1,C2 AS X2 FROM T1;SELECT C1 AS Y1,C2 AS Y2 FROM T1;'
> PS D:\temp> $dataReader = $dbCommand.ExecuteReader()
>
> ### ACTUAL RESULTS: *FIRST* RESULT SET ###
> PS D:\temp> $dataReader.GetName(0)
> X1
> PS D:\temp> $dataReader.GetName(1)
> X2
> PS D:\temp> $dataReader.StepCount
> 3
> PS D:\temp> $dataReader.HasRows
> False
> PS D:\temp> $dataReader.Read()
> False
> PS D:\temp> $dataReader.GetValue(0)
> Exception calling "GetValue" with "1" argument(s): "No current row"
> (...)
> PS D:\temp> $dataReader.GetValue(1)
> Exception calling "GetValue" with "1" argument(s): "No current row"
> (...)
> ### ACTUAL RESULTS: *SECOND* RESULT SET ###
> PS D:\temp> $dataReader.NextResult()
> True
> PS D:\temp> $dataReader.StepCount
> 4
> PS D:\temp> $dataReader.GetName(0)
> Y1
> PS D:\temp> $dataReader.GetName(1)
> Y2
> PS D:\temp> $dataReader.HasRows
> True
> PS D:\temp> $dataReader.GetValue(0)
> Exception calling "GetValue" with "1" argument(s): "No current row"
> (...)
> PS D:\temp> $dataReader.Read()
> True
> PS D:\temp> $dataReader.GetValue(0)
> 101
> PS D:\temp> $dataReader.GetValue(1)
> 102
> PS D:\temp> $dataReader.HasRows
> True
> PS D:\temp> $dataReader.Read()
> True
> PS D:\temp> $dataReader.GetValue(0)
> 201
> PS D:\temp> $dataReader.GetValue(1)
> 202
> PS D:\temp> $dataReader.HasRows
> True
> PS D:\temp> $dataReader.Read()
> True
> PS D:\temp> $dataReader.GetValue(0)
> 301
> PS D:\temp> $dataReader.GetValue(1)
> 302
> PS D:\temp> $dataReader.Read()
> False
> PS D:\temp> $dataReader.HasRows
> False
> PS D:\temp> $dataReader.GetValue(0)
> Exception calling "GetValue" with "1" argument(s): "No current row"
> (...)
>
> --
> Guy St-Denis
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users