PC Toledo

Código: Selecionar todos

#require "hbsqlit3"

#define TRACE
#define TABLE_SQL "CREATE TABLE t1( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER )"

PROCEDURE Main()

   ? sqlite3_libversion()
   sqlite3_sleep( 3000 )

   IF sqlite3_libversion_number() < 3005001
      RETURN
   ENDIF

   t1()
   t2()

   RETURN

/*
*/
PROCEDURE t1()

   LOCAL lCreateIfNotExist := .F.
   LOCAL db := sqlite3_open( "new.s3db", lCreateIfNotExist )

   IF ! Empty( db )
      sqlite3_exec( db, "DROP TABLE t1" )
   ENDIF

   RETURN

/*
*/
PROCEDURE t2()

   LOCAL lCreateIfNotExist := .T.
   LOCAL db := sqlite3_open( "new.s3db", lCreateIfNotExist )
   LOCAL stmt
   LOCAL nCCount, nCType, nI, nJ
   LOCAL aCType :=  { "SQLITE_INTEGER", "SQLITE_FLOAT", "SQLITE_TEXT", "SQLITE_BLOB", "SQLITE_NULL" }
   LOCAL aTable

   IF ! Empty( db )

#ifdef TRACE
      sqlite3_profile( db, .T. )
      sqlite3_trace( db, .T. )
#endif
      sqlite3_exec( db, "PRAGMA auto_vacuum=0" )
      sqlite3_exec( db, "PRAGMA page_size=4096" )

      IF sqlite3_exec( db, TABLE_SQL ) == SQLITE_OK
         ? "CREATE TABLE t1 - Ok"
      ENDIF

      sqlite3_exec( db, ;
         "BEGIN TRANSACTION;" + ;
         "INSERT INTO t1( name, age ) VALUES( 'Bob', 52 );" + ;
         "INSERT INTO t1( name, age ) VALUES( 'Fred', 40 );" + ;
         "INSERT INTO t1( name, age ) VALUES( 'Sasha', 25 );" + ;
         "INSERT INTO t1( name, age ) VALUES( 'Ivet', 28 );" + ;
         "COMMIT;" )

      ? "BEGIN TRANSACTION"
      ? "INSERT INTO t1( name, age ) VALUES( 'Bob', 52 )"
      ? "INSERT INTO t1( name, age ) VALUES( 'Fred', 40 )"
      ? "INSERT INTO t1( name, age ) VALUES( 'Sasha', 25 )"
      ? "INSERT INTO t1( name, age ) VALUES( 'Ivet', 28 )"
      ? "COMMIT"

      ? "The number of database rows that were changed: " + hb_ntos( sqlite3_changes( db ) )
      ? "Total changes: " + hb_ntos( sqlite3_total_changes( db ) )

      sqlite3_sleep( 3000 )

      stmt := sqlite3_prepare( db, "INSERT INTO t1( name, age ) VALUES( :name, :age )" )
      IF ! Empty( stmt )
         IF sqlite3_bind_text( stmt, 1, "Andy" ) == SQLITE_OK .AND. ;
            sqlite3_bind_int( stmt, 2, 17 ) == SQLITE_OK
            IF sqlite3_step( stmt ) == SQLITE_DONE
               ? "INSERT INTO t1( name, age ) VALUES( 'Andy', 17 ) - Done"
            ENDIF
         ENDIF
         sqlite3_reset( stmt )

         IF sqlite3_bind_text( stmt, 1, "Mary" ) == SQLITE_OK .AND. ;
            sqlite3_bind_int( stmt, 2, 19 ) == SQLITE_OK
            IF sqlite3_step( stmt ) == SQLITE_DONE
               ? "INSERT INTO t1( name, age ) VALUES( 'Mary', 19 ) - Done"
            ENDIF
         ENDIF
         sqlite3_clear_bindings( stmt )
         sqlite3_finalize( stmt )
      ENDIF

      ? "The number of database rows that were changed: " + hb_ntos( sqlite3_changes( db ) )
      ? "Total changes: " + hb_ntos( sqlite3_total_changes( db ) )
      ? "Last _ROWID_: " + Str( sqlite3_last_insert_rowid( db ) )
      ? ""

      stmt := sqlite3_prepare( db, "SELECT * FROM t1 WHERE name == :name " )
      sqlite3_bind_text( stmt, 1, "Andy" )

      ?
      ? "SELECT * FROM t1 WHERE name == 'Andy'"
      nJ := 0

      DO WHILE sqlite3_step( stmt ) == SQLITE_ROW
         nCCount := sqlite3_column_count( stmt )
         ++nJ
         ? "Record # " + Str( nJ )

         IF nCCount > 0
            FOR nI := 0 TO nCCount - 1
               nCType := sqlite3_column_type( stmt, nI )
               ? "Column name : " + sqlite3_column_name( stmt, nI )
               ? "Column type : " + aCType[ nCType ]
               ? "Column value: "

               SWITCH nCType
               CASE SQLITE_BLOB
                  ?? "BLOB" // sqlite3_column_blob( stmt, nI )
                  EXIT

               CASE SQLITE_INTEGER
                  ?? Str( sqlite3_column_int( stmt, nI ) )
                  EXIT

               CASE SQLITE_NULL
                  ?? "NULL"
                  EXIT

               CASE SQLITE_TEXT
                  ?? sqlite3_column_text( stmt, nI )
                  EXIT
               ENDSWITCH

            NEXT
         ENDIF
      ENDDO
      ? "Total records - " + Str( nJ )

      sqlite3_clear_bindings( stmt )
      sqlite3_finalize( stmt )

      sqlite3_sleep( 3000 )

      stmt := sqlite3_prepare( db, "SELECT * FROM t1 WHERE age >= ?5" )
      sqlite3_bind_int( stmt, 5, 40 )

      ?
      ? "SELECT * FROM t1 WHERE age >= 40 "
      nJ := 0
      DO WHILE sqlite3_step( stmt ) == SQLITE_ROW
         nCCount := sqlite3_column_count( stmt )
         ++nJ
         ? "Record # " + Str( nJ )

         IF nCCount > 0
            FOR nI := 1 TO nCCount
               nCType := sqlite3_column_type( stmt, nI )
               ? "Column name : " + sqlite3_column_name( stmt, nI )
               ? "Column type : " + aCType[ nCType ]
               ? "Column value: "

               SWITCH nCType
               CASE SQLITE_BLOB
                  ?? "BLOB" // sqlite3_column_blob( stmt, nI )
                  EXIT

               CASE SQLITE_INTEGER
                  ?? Str( sqlite3_column_int( stmt, nI ) )
                  EXIT

               CASE SQLITE_NULL
                  ?? "NULL"
                  EXIT

               CASE SQLITE_TEXT
                  ?? sqlite3_column_text( stmt, nI )
                  EXIT
               ENDSWITCH

            NEXT
         ENDIF
      ENDDO
      ? "Total records - " + Str( nJ )
      sqlite3_clear_bindings( stmt )
      sqlite3_finalize( stmt )

      sqlite3_sleep( 3000 )

      ?
      ? "SELECT id, name, age + 5 FROM t1"
      stmt := sqlite3_prepare( db, "SELECT id, name, age + 5 FROM t1" )

      ? sqlite3_column_name( stmt, 1 )
      ? sqlite3_column_name( stmt, 2 )
      ? sqlite3_column_name( stmt, 3 )

      ? aCType[ sqlite3_column_type( stmt, 1 ) ]
      ? aCType[ sqlite3_column_type( stmt, 2 ) ]
      ? aCType[ sqlite3_column_type( stmt, 3 ) ]

      ? sqlite3_column_decltype( stmt, 1 )
      ? sqlite3_column_decltype( stmt, 2 )
      ? sqlite3_column_decltype( stmt, 3 )

      sqlite3_finalize( stmt )

      sqlite3_sleep( 3000 )

      ?
      ? "sqlite3_get_table"
      ?
      aTable := sqlite3_get_table( db, "SELECT name, age  FROM t1 WHERE age BETWEEN 10 AND 20" )
      FOR nI := 1 TO Len( aTable )
         FOR nJ := 1 TO Len( aTable[ nI ] )
            ?? aTable[ nI ][ nJ ], " "
         NEXT
         ?
      NEXT

      sqlite3_sleep( 3000 )
   ENDIF

   RETURN

Código: Selecionar todos

2019-02-11 13:09 UTC+0100 Aleksander Czajczynski (hb fki.pl)
  * contrib/sddsqlt3/core.c
    ! fix DBUSEAREA() operation with SQLITE3 SDD to return empty result
      when query conditions are false or the source table has no rows.

      Previously an logically correct example caused RTE:
      DBUSEAREA(,, "SELECT * FROM existing_table WHERE FALSE")

    ! fix double-free error in sqLite3Disconnect(), looks like the
      sqlite3_close() return value checking was reverted, SQLITE_OK is 0

    * use CDP API to get UTF8 string length

    * use new sqlite3_prepare_v3() when built against
      sqlite 3.20.0 or upper (change borrowed from Viktor's 3.4 fork)

    + add HB_SQLT3_MAP_DECLARED_EMULATED define (not yet enabled by default)
      which make this SDD additionally parse SQLite column declarations.
      Right now it can make HB_FT_DATE fields working using standard
      ISO 8601 "yyyy-mm-dd" syntax. Also declarations not significant for
      SQLite, but useful in xBase-style programming - SQL numeric(len,dec)
      columns are detected in this mode and will be reflected in dbStruct().

    + add support for alternative StoD() like syntax for HB_FT_DATE columns

    + add support for ISO 8601 "YYYY-MM-DD HH:MM:SS.FFF" timestamp declared
      columns, SQLite stored strings are converted to proper HB_FT_TIMESTAMP
      fields

    + added HB_SQLT3_FIELDNAME_STRICT define, which enables shortening
      of field to "name" if SQLite returns "table.name". Such fields are
      not completly usable in xBase code - WA->T.FIELD syntax is not
      valid, but FieldPos("t.field") is OK. I think it should be default
      behaviour or some runtime setting should be introduced for convenience
      when working with specific SQL queries.

Código: Selecionar todos

PROCEDURE PTESSQLITE

   LOCAL cnSQL := SQLiteConnection( "D:\TOOLS\MediaMonkey\MM.DB" )
   LOCAL oTBrowse, oRs

   WITH OBJECT oRs := cnSQL:Execute( "SELECT Songs.ID, SongTitle, playcounter, FileLength, SongLength, Rating," + ;
         " IDAlbum, Artists.Artist, Albums.Album" + ;
         " FROM Songs" + ;
         " LEFT JOIN Albums ON Albums.ID = Songs.IDAlbum" + ;
         " LEFT JOIN ArtistsSongs ON ArtistsSongs.IDSong = Songs.ID" + ;
         " LEFT JOIN Artists ON Artists.ID = ArtistsSongs.IDArtist" + ;
         " WHERE EXTENSION = 'MP3'" + ;
         " group by songs.id" + ;
         " ORDER BY playcounter DESC" + ;
         " LIMIT 300" )
       DO WHILE ! :Eof()
           ? :Fields( "ID" ):Value, ;
         :Fields( "Artist" ):Value, ;
         :Fields( "SongTitle" ):Value, ;
         :Fields( "PlayCounter" ):Value, ;
         :Fields( "Album" ):Value, ;
         :Fields( "FileLength" ):Value, ;
         :Fields( "SongLength" ):Value
      :MoveNext()
      ENDDO
      :Close()
   ENDWITH
   cnSQL:Close()

   RETURN

FUNCTION SQLiteConnection( cFileName )

   LOCAL oConexao := win_OleCreateObject( "ADODB.Connection" )

   oConexao:ConnectionString := "Provider=MSDASQL;" + ;
      "Driver={SQLite3 ODBC Driver};Database=" + cFileName + ";"
   oConexao:CursorLocation := 3
   oConexao:CommandTimeOut := 20

   RETURN oConexao