System.Data.SQLite
Check-in [2cd9814a44]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add GetAllAsText connection flag to force all column values to be returned as text. Pursuant to [e06c4caff3].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2cd9814a44ddf86bfa155c92b7fb75131c5abbed
User & Date: mistachkin 2013-03-22 23:40:47
References
2013-03-22
23:45 Ticket [e06c4caff3] NAN storage problem in columns of type 'double' status still Open with 3 other changes artifact: 4e4d8b7ab5 user: mistachkin
Context
2013-03-23
10:49
Update Eagle script library in externals to the latest trunk. check-in: aa16dda5d9 user: mistachkin tags: trunk
2013-03-22
23:40
Add GetAllAsText connection flag to force all column values to be returned as text. Pursuant to [e06c4caff3]. check-in: 2cd9814a44 user: mistachkin tags: trunk
2013-03-20
23:00
In the interop assembly, use the sqlite3_prepare_v2() function when available. In the managed assembly, use the sqlite3_prepare_v2() function when enabled. Sync up the list of define constants used by the test suite. check-in: 160e7ea1f6 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Doc/Extra/version.html.

    55     55         <li>Add HexPassword connection string property to work around the inability to include a literal semicolon in a connection string property value. Pursuant to <a href="http://system.data.sqlite.org/index.html/info/1c456ae75f">[1c456ae75f]</a>.</li>
    56     56         <li>Add static Execute method to the SQLiteCommand class.</li>
    57     57         <li>Support custom connection pool implementations by adding the ISQLiteConnectionPool interface, the static SQLiteConnection.ConnectionPool property, and the static CreateHandle method in addition to modifying the SQLiteConnectionPool class. Pursuant to <a href="http://system.data.sqlite.org/index.html/info/393d954be0">[393d954be0]</a>.</li>
    58     58         <li>Add public constructor to the SQLiteDataAdapter class that allows passing the parseViaFramework parameter to the SQLiteConnection constructor.</li>
    59     59         <li>When built with the CHECK_STATE compile-time option, skip throwing exceptions from the SQLiteDataReader class when the object is being disposed.</li>
    60     60         <li>Support automatic value conversions for columns with a declared type of BIGUINT, INTEGER8, INTEGER16, INTEGER32, INTEGER64, SMALLUINT, TINYSINT, UNSIGNEDINTEGER, UNSIGNEDINTEGER8, UNSIGNEDINTEGER16, UNSIGNEDINTEGER32, UNSIGNEDINTEGER64, INT8, INT16, INT32, INT64, UINT, UINT8, UINT16, UINT32, UINT64, or ULONG.</li>
    61     61         <li>Add BindUInt32AsInt64 connection flag to force binding of UInt32 values as Int64 instead. Pursuant to <a href="http://system.data.sqlite.org/index.html/info/c010fa6584">[c010fa6584]</a>.</li>
    62         -      <li>Add BindAllAsText connection flag to force binding of all values as text.</li>
           62  +      <li>Add BindAllAsText and GetAllAsText connection flags to force binding and returning of all values as text.</li>
    63     63         <li>Remove AUTOINCREMENT from the column type name map.&nbsp;<b>** Potentially Incompatible Change **</b></li>
    64     64         <li>Avoid throwing overflow exceptions from the SQLite3.GetValue method for integral column types. Partial fix for <a href="http://system.data.sqlite.org/index.html/info/c010fa6584">[c010fa6584]</a>.&nbsp;<b>** Potentially Incompatible Change **</b></li>
    65     65         <li>Use the legacy connection closing algorithm when built with the INTEROP_LEGACY_CLOSE compile-time option.</li>
    66     66         <li>Support using the directory containing the primary managed-only assembly as the basis for native library pre-loading.</li>
    67     67         <li>Still further enhancements to the build and test automation.</li>
    68     68       </ul>
    69     69       <p><b>1.0.84.0 - January 9, 2013</b></p>

Changes to System.Data.SQLite/SQLite3.cs.

  1913   1913           }
  1914   1914       }
  1915   1915   
  1916   1916       /// <summary>
  1917   1917       /// Helper function to retrieve a column of data from an active statement.
  1918   1918       /// </summary>
  1919   1919       /// <param name="stmt">The statement being step()'d through</param>
         1920  +    /// <param name="flags">The flags associated with the connection.</param>
  1920   1921       /// <param name="index">The column index to retrieve</param>
  1921   1922       /// <param name="typ">The type of data contained in the column.  If Uninitialized, this function will retrieve the datatype information.</param>
  1922   1923       /// <returns>Returns the data in the column</returns>
  1923         -    internal override object GetValue(SQLiteStatement stmt, int index, SQLiteType typ)
         1924  +    internal override object GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, int index, SQLiteType typ)
  1924   1925       {
  1925   1926         if (IsNull(stmt, index)) return DBNull.Value;
  1926   1927         TypeAffinity aff = typ.Affinity;
  1927   1928         Type t = null;
  1928   1929   
  1929   1930         if (typ.Type != DbType.Object)
  1930   1931         {
  1931   1932           t = SQLiteConvert.SQLiteTypeToType(typ);
  1932   1933           aff = TypeToAffinity(t);
  1933   1934         }
         1935  +
         1936  +      if ((flags & SQLiteConnectionFlags.GetAllAsText) == SQLiteConnectionFlags.GetAllAsText)
         1937  +          return GetText(stmt, index);
  1934   1938   
  1935   1939         switch (aff)
  1936   1940         {
  1937   1941           case TypeAffinity.Blob:
  1938   1942             if (typ.Type == DbType.Guid && typ.Affinity == TypeAffinity.Text)
  1939   1943               return new Guid(GetText(stmt, index));
  1940   1944   

Changes to System.Data.SQLite/SQLiteBase.cs.

   267    267       /// zero otherwise.
   268    268       /// </returns>
   269    269       internal abstract bool IsInitialized();
   270    270   
   271    271       internal abstract int GetCursorForTable(SQLiteStatement stmt, int database, int rootPage);
   272    272       internal abstract long GetRowIdForCursor(SQLiteStatement stmt, int cursor);
   273    273   
   274         -    internal abstract object GetValue(SQLiteStatement stmt, int index, SQLiteType typ);
          274  +    internal abstract object GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, int index, SQLiteType typ);
   275    275   
   276    276       internal abstract bool AutoCommit
   277    277       {
   278    278         get;
   279    279       }
   280    280   
   281    281       internal abstract SQLiteErrorCode FileControl(string zDbName, int op, IntPtr pArg);
................................................................................
   794    794         /// <summary>
   795    795         /// When binding parameter values, always bind them as though they were
   796    796         /// plain text (i.e. no numeric, date/time, or other conversions should
   797    797         /// be attempted).
   798    798         /// </summary>
   799    799         BindAllAsText = 0x80,
   800    800   
          801  +      /// <summary>
          802  +      /// When returning column values, always return them as though they were
          803  +      /// plain text (i.e. no numeric, date/time, or other conversions should
          804  +      /// be attempted).
          805  +      /// </summary>
          806  +      GetAllAsText = 0x100,
          807  +
          808  +      /// <summary>
          809  +      /// When binding and returning column values, always treat them as though
          810  +      /// they were plain text (i.e. no numeric, date/time, or other conversions
          811  +      /// should be attempted).
          812  +      /// </summary>
          813  +      BindAndGetAllAsText = BindAllAsText | GetAllAsText,
          814  +
   801    815         /// <summary>
   802    816         /// Enable all logging.
   803    817         /// </summary>
   804    818         LogAll = LogPrepare | LogPreBind | LogBind |
   805    819                  LogCallbackException | LogBackup,
   806    820   
   807    821         /// <summary>

Changes to System.Data.SQLite/SQLiteCommand.cs.

   230    230           }
   231    231           finally
   232    232           {
   233    233               base.Dispose(disposing);
   234    234           }
   235    235       }
   236    236       #endregion
          237  +
          238  +    ///////////////////////////////////////////////////////////////////////////////////////////////
          239  +
          240  +    /// <summary>
          241  +    /// This method attempts to query the flags associated with the database
          242  +    /// connection in use.  If the database connection is disposed or any other
          243  +    /// error occurs, the default flags will be returned.
          244  +    /// </summary>
          245  +    /// <param name="command">
          246  +    /// The command containing the databse connection to query the flags from.
          247  +    /// </param>
          248  +    /// <returns>
          249  +    /// The connection flags value.
          250  +    /// </returns>
          251  +    internal static SQLiteConnectionFlags GetFlags(
          252  +        SQLiteCommand command
          253  +        )
          254  +    {
          255  +        try
          256  +        {
          257  +            if (command != null)
          258  +            {
          259  +                SQLiteConnection cnn = command._cnn;
          260  +
          261  +                if (cnn != null)
          262  +                    return cnn.Flags;
          263  +            }
          264  +        }
          265  +        catch (ObjectDisposedException)
          266  +        {
          267  +            // do nothing.
          268  +        }
          269  +
          270  +        return SQLiteConnectionFlags.Default;
          271  +    }
   237    272   
   238    273       ///////////////////////////////////////////////////////////////////////////////////////////////
   239    274   
   240    275       /// <summary>
   241    276       /// Clears and destroys all statements currently prepared
   242    277       /// </summary>
   243    278       internal void ClearCommands()

Changes to System.Data.SQLite/SQLiteDataReader.cs.

  1120   1120         CheckValidRow();
  1121   1121   
  1122   1122         if (i >= VisibleFieldCount && _keyInfo != null)
  1123   1123           return _keyInfo.GetValue(i - VisibleFieldCount);
  1124   1124   
  1125   1125         SQLiteType typ = GetSQLiteType(i);
  1126   1126   
  1127         -      return _activeStatement._sql.GetValue(_activeStatement, i, typ);
         1127  +      return _activeStatement._sql.GetValue(
         1128  +          _activeStatement, SQLiteCommand.GetFlags(_command), i, typ);
  1128   1129       }
  1129   1130   
  1130   1131       /// <summary>
  1131   1132       /// Retreives the values of multiple columns, up to the size of the supplied array
  1132   1133       /// </summary>
  1133   1134       /// <param name="values">The array to fill with values from the columns in the current resultset</param>
  1134   1135       /// <returns>The number of columns retrieved</returns>

Added Tests/tkt-e06c4caff3.eagle.

            1  +###############################################################################
            2  +#
            3  +# tkt-e06c4caff3.eagle --
            4  +#
            5  +# Written by Joe Mistachkin.
            6  +# Released to the public domain, use at your own risk!
            7  +#
            8  +###############################################################################
            9  +
           10  +package require Eagle
           11  +package require Eagle.Library
           12  +package require Eagle.Test
           13  +
           14  +runTestPrologue
           15  +
           16  +###############################################################################
           17  +
           18  +package require System.Data.SQLite.Test
           19  +runSQLiteTestPrologue
           20  +
           21  +###############################################################################
           22  +
           23  +runTest {test tkt-e06c4caff3-1.1 {NaN to NULL constraint failure} -setup {
           24  +  setupDb [set fileName tkt-e06c4caff3-1.1.db]
           25  +} -body {
           26  +  sql execute $db "CREATE TABLE t1(x REAL NOT NULL);"
           27  +
           28  +  sql execute $db "INSERT INTO t1 (x) VALUES(?);" \
           29  +      [list param1 Double [set NaN [object invoke Double NaN]]]
           30  +} -cleanup {
           31  +  cleanupDb $fileName
           32  +
           33  +  unset -nocomplain NaN db fileName
           34  +} -constraints \
           35  +{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
           36  +-returnCodes 1 -match regexp -result [string map [list \n \r\n]\
           37  +{^System\.Data\.SQLite\.SQLiteException \(0x80004005\): constraint failed
           38  +t1\.x may not be NULL
           39  +.*$}]}
           40  +
           41  +###############################################################################
           42  +
           43  +runTest {test tkt-e06c4caff3-1.2 {NaN to NULL} -setup {
           44  +  setupDb [set fileName tkt-e06c4caff3-1.2.db]
           45  +} -body {
           46  +  sql execute $db "CREATE TABLE t1(x REAL);"
           47  +
           48  +  sql execute $db "INSERT INTO t1 (x) VALUES(?);" \
           49  +      [list param1 Double [set NaN [object invoke Double NaN]]]
           50  +
           51  +  sql execute -execute reader -format list $db "SELECT x FROM t1;"
           52  +} -cleanup {
           53  +  cleanupDb $fileName
           54  +
           55  +  unset -nocomplain NaN db fileName
           56  +} -constraints \
           57  +{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
           58  +-result {}}
           59  +
           60  +###############################################################################
           61  +
           62  +runTest {test tkt-e06c4caff3-1.3 {NaN w/BindAllAsText} -setup {
           63  +  setupDb [set fileName tkt-e06c4caff3-1.3.db] "" "" "" BindAllAsText
           64  +} -body {
           65  +  sql execute $db "CREATE TABLE t1(x REAL NOT NULL);"
           66  +
           67  +  list [sql execute $db "INSERT INTO t1 (x) VALUES(?);" \
           68  +      [list param1 Double [set NaN [object invoke Double NaN]]]] \
           69  +      [sql execute -execute reader -format list $db "SELECT x FROM t1;"]
           70  +} -cleanup {
           71  +  cleanupDb $fileName
           72  +
           73  +  unset -nocomplain NaN db fileName
           74  +} -constraints \
           75  +{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
           76  +{1 0}}
           77  +
           78  +###############################################################################
           79  +
           80  +runTest {test tkt-e06c4caff3-1.4 {NaN w/BindAllAsText & GetAllAsText} -setup {
           81  +  setupDb [set fileName tkt-e06c4caff3-1.4.db] "" "" "" BindAndGetAllAsText
           82  +} -body {
           83  +  sql execute $db "CREATE TABLE t1(x REAL NOT NULL);"
           84  +
           85  +  list [sql execute $db "INSERT INTO t1 (x) VALUES(?);" \
           86  +      [list param1 Double [set NaN [object invoke Double NaN]]]] \
           87  +      [sql execute -execute reader -format list $db "SELECT x FROM t1;"]
           88  +} -cleanup {
           89  +  cleanupDb $fileName
           90  +
           91  +  unset -nocomplain NaN db fileName
           92  +} -constraints \
           93  +{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
           94  +{1 NaN}}
           95  +
           96  +###############################################################################
           97  +
           98  +runSQLiteTestEpilogue
           99  +runTestEpilogue

Changes to readme.htm.

   200    200       <li>Add HexPassword connection string property to work around the inability to include a literal semicolon in a connection string property value. Pursuant to [1c456ae75f].</li>
   201    201       <li>Add static Execute method to the SQLiteCommand class.</li>
   202    202       <li>Support custom connection pool implementations by adding the ISQLiteConnectionPool interface, the static SQLiteConnection.ConnectionPool property, and the static CreateHandle method in addition to modifying the SQLiteConnectionPool class. Pursuant to [393d954be0].</li>
   203    203       <li>Add public constructor to the SQLiteDataAdapter class that allows passing the parseViaFramework parameter to the SQLiteConnection constructor.</li>
   204    204       <li>When built with the CHECK_STATE compile-time option, skip throwing exceptions from the SQLiteDataReader class when the object is being disposed.</li>
   205    205       <li>Support automatic value conversions for columns with a declared type of BIGUINT, INTEGER8, INTEGER16, INTEGER32, INTEGER64, SMALLUINT, TINYSINT, UNSIGNEDINTEGER, UNSIGNEDINTEGER8, UNSIGNEDINTEGER16, UNSIGNEDINTEGER32, UNSIGNEDINTEGER64, INT8, INT16, INT32, INT64, UINT, UINT8, UINT16, UINT32, UINT64, or ULONG.</li>
   206    206       <li>Add BindUInt32AsInt64 connection flag to force binding of UInt32 values as Int64 instead. Pursuant to [c010fa6584].</li>
   207         -    <li>Add BindAllAsText connection flag to force binding of all values as text.</li>
          207  +    <li>Add BindAllAsText and GetAllAsText connection flags to force binding and returning of all values as text.</li>
   208    208       <li>Remove AUTOINCREMENT from the column type name map.&nbsp;<b>** Potentially Incompatible Change **</b></li>
   209    209       <li>Avoid throwing overflow exceptions from the SQLite3.GetValue method for integral column types. Partial fix for [c010fa6584].&nbsp;<b>** Potentially Incompatible Change **</b></li>
   210    210       <li>Use the legacy connection closing algorithm when built with the INTEROP_LEGACY_CLOSE compile-time option.</li>
   211    211       <li>Support using the directory containing the primary managed-only assembly as the basis for native library pre-loading.</li>
   212    212       <li>Still further enhancements to the build and test automation.</li>
   213    213   </ul>
   214    214   <p>

Changes to www/news.wiki.

    16     16       <li>Add HexPassword connection string property to work around the inability to include a literal semicolon in a connection string property value. Pursuant to [1c456ae75f].</li>
    17     17       <li>Add static Execute method to the SQLiteCommand class.</li>
    18     18       <li>Support custom connection pool implementations by adding the ISQLiteConnectionPool interface, the static SQLiteConnection.ConnectionPool property, and the static CreateHandle method in addition to modifying the SQLiteConnectionPool class. Pursuant to [393d954be0].</li>
    19     19       <li>Add public constructor to the SQLiteDataAdapter class that allows passing the parseViaFramework parameter to the SQLiteConnection constructor.</li>
    20     20       <li>When built with the CHECK_STATE compile-time option, skip throwing exceptions from the SQLiteDataReader class when the object is being disposed.</li>
    21     21       <li>Support automatic value conversions for columns with a declared type of BIGUINT, INTEGER8, INTEGER16, INTEGER32, INTEGER64, SMALLUINT, TINYSINT, UNSIGNEDINTEGER, UNSIGNEDINTEGER8, UNSIGNEDINTEGER16, UNSIGNEDINTEGER32, UNSIGNEDINTEGER64, INT8, INT16, INT32, INT64, UINT, UINT8, UINT16, UINT32, UINT64, or ULONG.</li>
    22     22       <li>Add BindUInt32AsInt64 connection flag to force binding of UInt32 values as Int64 instead. Pursuant to [c010fa6584].</li>
    23         -    <li>Add BindAllAsText connection flag to force binding of all values as text.</li>
           23  +    <li>Add BindAllAsText and GetAllAsText connection flags to force binding and returning of all values as text.</li>
    24     24       <li>Remove AUTOINCREMENT from the column type name map.&nbsp;<b>** Potentially Incompatible Change **</b></li>
    25     25       <li>Avoid throwing overflow exceptions from the SQLite3.GetValue method for integral column types. Partial fix for [c010fa6584].&nbsp;<b>** Potentially Incompatible Change **</b></li>
    26     26       <li>Use the legacy connection closing algorithm when built with the INTEROP_LEGACY_CLOSE compile-time option.</li>
    27     27       <li>Support using the directory containing the primary managed-only assembly as the basis for native library pre-loading.</li>
    28     28       <li>Still further enhancements to the build and test automation.</li>
    29     29   </ul>
    30     30   <p>