Sử dụng SQLite với DB Browser.

[NhanSu]

Chào các bạn. Trong chủ đề này mình xin chia sẻ cách sử dụng DB Browser để làm việc với SQLite. Hiện đã có topic về SQLite

Bạn cần đăng nhập để thấy link

nhưng sử dụng giao diện dòng lệnh còn chủ đề này mình giới thiệu cách sử dụng ứng dụng GUI (graphic user interface - giao diện đồ họa). Mình chỉ giới thiệu các chức năng thường dùng thôi cho nhanh.
DB Browser là ứng dụng miễn phí, các bạn có thể tải về tại

Bạn cần đăng nhập để thấy link

chọn phiên bản phù hơp với hệ điều hành về cài, có cả bản portable. Đây là giao diện của ứng dụng:

Bạn cần đăng nhập để thấy đính kèm

SQLite lưu dữ liệu vào file database thường có phần mở rộng là .db, trong file này chứa các table. Đầu tiên nếu chưa có file db, các bạn có thể tạo bằng cách bấm nút New database, chọn đường dẫn, nhập tên rồi bấm Save để tạo file. Để tạo table, các bạn có thể dùng lệnh CREATE TABLE (vào tab Execute SQL để nhập) hoặc đơn giản nhất là dùng chức năng Import table from csv file trong menu File, quá trình import diễn ra rất nhanh. Nếu có nhiều file CSV có cấu trúc giống nhau, ta có thể chọn tất cả rồi import 1 lượt vào 1 table. File CSV có thể tạo ra bằng cách dùng Excel save as thành CSV UTF8. Chú ý các dữ liệu số cần chuyển về General (chọn cột rồi bấm Ctrl-Shift-~) trong Excel trước khi saveas.

Bạn cần đăng nhập để thấy đính kèm

 

[NhanSu]

Bạn cần đăng nhập để thấy đính kèm

Đây là tab Browse data, chúng ta thấy bảng tương tự Excel, có thể lọc bằng cách nhập vào ô Filter ở trên cột hoặc bấm chuột phải để chọn cách lọc, cũng có thể chỉnh sửa trực tiếp trên ô.

Bạn cần đăng nhập để thấy đính kèm

Tab Database structure có thể dùng chỉnh sửa cấu trúc table, thay đổi kiểu của cột, export ra file csv hoặc xóa table.

 

[NhanSu]

Tab Excecute SQL là nơi chạy lệnh SQL, các bạn nhập lệnh như bình thường, xuống dòng bằng cách bấm Enter, để chạy lệnh thì bấm Ctrl-Enter (nút Enter ở giữa, không phải ở khung số) hoặc F5 hoặc Ctrl-R. Ta có thể đánh dấu chọn một đoạn trong cửa sổ SQL rồi chạy riêng phần này. Có thể tạo nhiều tab SQL để lưu các lệnh, các tab này có thể rename (hỗ trợ unicode) để dễ nhớ tác dụng của lệnh SQL. DB Browser cho phép lưu các lệnh đã dùng vào file Project có phần mở rộng sqbpro, lần sau ta có thể mở file project này, file db sẽ tự động mở và các lệnh cũ vẫn còn nguyên. Kết quả truy vấn có thể copy paste ra Excel nhưng nếu dữ liệu nhiều thì việc copy sẽ chậm, ta có thể export ra CSV bằng cách bấm vào nút Save result view (nút thứ 4 từ phải sang, trên các tab lệnh SQL).

Bạn cần đăng nhập để thấy đính kèm

 

[NhanSu]

Mình tìm được

Bạn cần đăng nhập để thấy link

cho phép kết nối SQLite trong Excel mà không cần cài gì cả do file winsqlite3.dll đi kèm Win10. Ở bài viết này, ngoài phương pháp của tác giả (code ở dưới) thì bài viết cũng cung cấp giải pháp của Govert Van Drimmelen (tác giả của Excel DNA quen thuộc).
Dưới đây là phương pháp của tác giả bài viết:

Bạn cần đăng nhập để thấy link

(bấm vào nút code màu xanh, chọn download zip), giải nén có 2 file bas, import hoặc copy vào module là được, tuy vậy code phức tạp quá không thích lắm.
Code dưới và code của Govert mình chưa test vì trong chủ đề này mục đích mình muốn giới thiệu sử dụng DB Browser chứ không phải SQLite for Excel. Do thấy mọi người quan tâm nên mình mới dẫn link.

option explicit

public const SQLITE_OK      =    0  ' Successful result
public const SQLITE_ERROR   =    1  ' Generic Error
public const SQLITE_BUSY    =    5  ' The database file is locked
public const SQLITE_TOOBIG  =   18  ' String or BLOB exceeds size limit
public const SQLITE_MISUSE  =   21  ' Library used incorrectly
public const SQLITE_ROW     =  100  ' sqlite3_step() has another row ready
public const SQLITE_DONE    =  101  ' sqlite3_step() has finished executing

' Constants that identify SQLite data types ' {
'
' Returned by sqlite3_column_type()
'
public const SQLITE_INTEGER =  1
public const SQLITE_FLOAT   =  2
public const SQLITE_TEXT    =  3  ' TODO: Should it be named SQLITE3_TEXT?
public const SQLITE_BLOB    =  4
public const SQLITE_NULL    =  5
' }

' { WinAPI constants
private const CP_UTF8        = 65001
' }

' sqlite3_open {
declare ptrSafe function sqlite3_open        lib "winsqlite3.dll" (  _
     byVal    zFilename        as string , _
     byRef    ppDB             as longPtr  _
) as longPtr ' }

' sqlite3_close {
declare ptrSafe function sqlite3_close       lib "winsqlite3.dll" ( _
     byVal    db               as longPtr  _
) as longPtr ' }

' sqlite3_exec ' {
'
'   TODO: how can/should errmsg be handled in case of an error
'
declare ptrSafe function sqlite3_exec        lib "winsqlite3.dll" ( _
     byVal    db               as any    , _
     byVal    sql              as string , _
     byVal    callback         as longPtr, _
     byVal    argument_1       as longPtr, _
     byRef    errmsg           as string   _
) as longPtr ' }

' sqlite3_prepare_v2 {
'
'   TODO pzTail is actually a char**
'
declare ptrSafe function sqlite3_prepare_v2  lib "winsqlite3.dll" ( _
     byVal    db               as any    , _
     byVal    zSql             as string , _
     byVal    nByte            as longPtr, _
     byRef    ppStatement      as longPtr, _
     byRef    pzTail           as any      _
) as longPtr ' }

' sqlite3_finalize {
'
'
declare ptrSafe function sqlite3_finalize    lib "winsqlite3.dll" ( _
     byVal    stmt             as longPtr  _
) as longPtr ' }

' sqlite3_bind_* ' {

' sqlite3_bind_int {
declare ptrSafe function sqlite3_bind_int    lib "winsqlite3.dll" ( _
     byVal    stmt            as longPtr , _
     byVal    pos             as long    , _
     byVal    val             as long      _
) as long ' }

' sqlite3_bind_text {
'
'     TODO: what is the «whatIsThis» parameter used for?
'
declare ptrSafe function sqlite3_bind_text_  lib "winsqlite3.dll" alias "sqlite3_bind_text" ( _
     byVal    stmt            as longPtr , _
     byVal    pos             as long    , _
     byVal    val             as longPtr , _
     byVal    len_            as integer , _
     byVal    whatIsThis      as longPtr   _
) as long ' }

' sqlite3_bind_null {
declare ptrSafe function sqlite3_bind_null   lib "winsqlite3.dll" ( _
     byVal    stmt            as longPtr , _
     byVal    pos             as long      _
) as long
' }

' }

' sqlite3_step ' {
declare ptrSafe function sqlite3_step          lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr   _
) as long ' }


' sqlite3_reset ' {
declare ptrSafe function sqlite3_reset         lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr   _
) as long ' }


' sqlite3_column_* {

' sqlite3_column_double ' {
declare ptrSafe function sqlite3_column_double lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as double ' }

' sqlite3_column_int ' {
declare ptrSafe function sqlite3_column_int    lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as integer ' }

' sqlite3_column_text ' {
'
' The string returned from SQLite needs to be converted to
' a wide character string in VBA. The following declaration (whose
' name ends in an underscore) first gets the pointer to the (ASCII or UTF8) string.
' Further below, another function is declared, sqlite3_column_text, that
' takes the pointer and converts it into a wide character string, suitable
' for a VBA string.
'

declare ptrSafe function sqlite3_column_text_  lib "winsqlite3.dll" alias "sqlite3_column_text" ( _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as longPtr ' }

' }

' sqlite3_column_type ' {
'
' Returns one of the five data types (SQLITE_INTEGER, …) of
' a selected column.
'
declare ptrSafe function sqlite3_column_type   lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as integer ' }

' MultiByteToWideChar {
private declare ptrSafe function MultiByteToWideChar lib "kernel32" ( _
   byVal CodePage       as long   , _
   byVal dwFlags        as long   , _
   byVal lpMultiByteStr as longPtr, _
   byVal cbMultiByte    as long   , _
   byVal lpWideCharStr  as longPtr, _
   byVal cchWideChar    as long     _
) as long ' }

' WideCharToMultiByte {
private declare ptrSafe function WideCharToMultiByte lib "kernel32" ( _
  byVal CodePage          as long   , _
  byVal dwFlags           as long   , _
  byVal lpWideCharStr     as longPtr, _
  byVal cchWideChar       as long   , _
  byVal lpMultiByteStr    as longPtr, _
  byVal cchMultiByte      as long   , _
  byVal lpDefaultChar     as longPtr, _
  byVal lpUsedDefaultChar as longPtr  _
) as long ' }

function utf8ptrToString(byVal pUtf8String as longPtr) as string ' {
'
' Found @ https://github.com/govert/SQLiteForExcel/blob/master/Source/SQLite3VBAModules/Sqlite3_64.bas
'
    dim buf     as string
    dim cSize   as long
    dim retVal  as long

  ' cSize includes the terminating null character
    cSize = MultiByteToWideChar(CP_UTF8, 0, pUtf8String, -1, 0, 0)

    if cSize <= 1 then ' {
        Utf8ptrToString = ""
        exit function
    end if ' }

    Utf8ptrToString = string(cSize - 1, "*") ' and a termintating null char.

    retVal = MultiByteToWideChar(CP_UTF8, 0, pUtf8String, -1, strPtr(Utf8ptrToString), cSize)
    if retVal = 0 then ' {
       err.raise 1000, "utf8ptrToString", "Utf8ptrToString error: " & err.lastDllError
       exit function
    end if ' }

end function ' }

function stringToUtf8bytes(byVal txt as string) as byte() ' {

    dim bSize  as long
    dim retVal as long
    dim buf()  as byte

    bSize = WideCharToMultiByte(CP_UTF8, 0, strPtr(txt), -1, 0, 0, 0, 0)

    if bSize = 0 then ' {
        exit function
    end if ' }

    ReDim buf(bSize)

    retVal = WideCharToMultiByte(CP_UTF8, 0, strPtr(txt), -1, varPtr(buf(0)), bSize, 0, 0)

    if retVal = 0 then
        err.raise 1000, "stringToUtf8bytes", "stringToUtf8bytes error: " & err.lastDllError
        exit function
    end if

    stringToUtf8bytes = buf

end function ' }

' { sqlite3_bind_text

function sqlite3_bind_text  ( _
     byVal    stmt            as longPtr , _
     byVal    pos             as long    , _
     byVal    val             as string  , _
     byVal    len_            as integer , _
     byVal    whatIsThis      as longPtr   _
) as long ' }

  dim arrayVariant as variant
  arrayVariant = stringToUtf8bytes(val)

' dim x() as byte
' x = stringToUtf8bytes(val)

  sqlite3_bind_text = sqlite3_bind_text_(stmt, pos, varPtr(arrayVariant          ), len_, whatIsThis)
' sqlite3_bind_text = sqlite3_bind_text_(stmt, pos, varPtr(stringToUtf8bytes(val)), len_, whatIsThis)
' sqlite3_bind_text = sqlite3_bind_text_(stmt, pos,        stringToUtf8bytes(val ), len_, whatIsThis)

end function ' }

' { sqlite3_column_text
function sqlite3_column_text (             _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as string

    sqlite3_column_text = utf8ptrToString(sqlite3_column_text_(stmt, iCol))

end function ' }

option explicit


sub main() ' {

    dim db as longPtr

    db = openDB(environ("temp") & "\test.db")

    execSQL db, "create table tab(foo, bar, baz)"

    execSQL db, "insert into tab values(1, 'one', null);"
    execSQL db, "insert into tab values(2,  2.2 ,'two');"

    dim stmt as longPtr
    stmt = prepareStmt(db, "insert into tab values(?, ?, ?)")

    checkBindRetval(sqlite3_bind_int (stmt, 1, 3              ))
    checkBindRetval(sqlite3_bind_text(stmt, 2,"three", -1, 0  ))
    checkBindRetval(sqlite3_bind_int (stmt, 3, 333            ))
    checkStepRetval(sqlite3_step     (stmt))

  ' sqlite3_reset(stmt) still seems necesssary although the documentation says
  ' that in version after 3.6.something, it should not be necessary anymore...
  '
  ' TODO: or should sqlite3_clear_bindings be used?
  '
    sqlite3_reset(stmt) ' Or sqlite3_clear_bindings() ?

    checkBindRetval(sqlite3_bind_int (stmt, 1, 55             ))
    checkBindRetval(sqlite3_bind_text(stmt, 2,"four" , -1, 0  ))
    checkBindRetval(sqlite3_bind_null(stmt, 3                 ))
    checkStepRetval(sqlite3_step     (stmt))
    sqlite3_reset(stmt) ' Or sqlite3_clear_bindings() ?

    checkBindRetval(sqlite3_bind_int (stmt, 1, 42                 ))
    checkBindRetval(sqlite3_bind_text(stmt, 2,"Umlauts"   , -1, 0 ))
    checkBindRetval(sqlite3_bind_text(stmt, 3,"äöü ÄÖÜ éÉ", -1, 0 ))
    checkStepRetval(sqlite3_step     (stmt))
'   sqlite3_reset(stmt) ' Or sqlite3_clear_bindings() ?

    sqlite3_finalize  stmt

    selectFromTab(db)

    closeDB(db)

end sub ' }

function openDB(fileName as string) as longPtr ' {

    dim res as longPtr

    res = sqlite3_open(fileName, openDB)
    if res <> SQLITE_OK then
       err.raise 1000, "openDB", "sqlite_open failed, res = " & res
    end if

    debug.print("SQLite db opened, db = " & openDB)

end function ' }

sub closeDB(db as longPtr) ' {

    dim res as longPtr

    res = sqlite3_close(db)
    if res <> SQLITE_OK then
       err.raise 1000, "closeDB", "sqlite_open failed, res = " & res
    end if

end sub ' }

sub checkBindRetval(retVal as long) ' {

    if retVal = SQLITE_OK then
       exit sub
    end if

    if retVal = SQLITE_TOOBIG then
       err.raise 1000, "checkBindRetval", "bind failed: String or BLOB exceeds size limit"
    end if

    if retVal = SQLITE_MISUSE then
       err.raise 1000, "checkBindRetval", "bind failed: Library used incorrectly"
    end if

    err.raise 1000, "checkBindRetval", "bind failed, retVal = " & retVal

end sub ' }

sub checkStepRetval(retVal as long) ' {

    if retVal = SQLITE_DONE then
       exit sub
    end if

    err.raise 1000, "checkStepRetval", "step failed, retVal = " & retVal

end sub ' }

sub execSQL(db as longPtr, sql as string) ' {

    dim res    as longPtr
    dim errmsg as string

    res = sqlite3_exec(db, sql, 0, 0, errmsg)
    if res <> SQLITE_OK then
       err.raise 1000, "execSQL", "sqlite3_exec failed, res = " & res
    end if

end sub ' }

function prepareStmt(db as longPtr, sql as string) as longPtr ' {

    dim res    as longPtr

    res = sqlite3_prepare_v2(db, sql, -1, prepareStmt, 0)
    if res <> SQLITE_OK then
       err.raise 1000, "prepareStmt", "sqlite3_prepare failed, res = " & res
    end if

    debug.print("stmt = " & prepareStmt)

end function ' }

sub selectFromTab(db as longPtr) ' {

    dim stmt as longPtr
    stmt = prepareStmt(db, "select * from tab where foo > ? order by foo")

    sqlite3_bind_int stmt, 1, 2

    dim rowNo as long

    while sqlite3_step(stmt) <> SQLITE_DONE ' {

      rowNo = rowNo + 1

      dim colNo as long
      colNo = 0
      while colNo <= 2 ' {

         if     sqlite3_column_type(stmt, colNo) = SQLITE_INTEGER then

                cells(rowNo, colNo + 1) = sqlite3_column_int(stmt, colNo)

         elseIf sqlite3_column_type(stmt, colNo) = SQLITE_FLOAT   then

                cells(rowNo, colNo + 1) = sqlite3_column_double(stmt, colNo)

         elseIf sqlite3_column_type(stmt, colNo) = SQLITE_TEXT    then

                cells(rowNo, colNo + 1) = sqlite3_column_text(stmt, colNo)

         elseIf sqlite3_column_type(stmt, colNo) = SQLITE_NULL    then

                cells(rowNo, colNo + 1) ="n/a"

         else

                cells(rowNo, colNo + 1) ="?"

         end if

         colNo = colNo + 1

      wend ' }

    wend ' }

    sqlite3_finalize stmt

end sub ' }

 

[NhanSu]

Tiếp nối bài 3 ở trên, bài này mình xin chia sẻ kinh nghiệm của mình về cách xuất dữ liệu kết quả query ra Excel.

Bạn cần đăng nhập để thấy đính kèm

Xuất dữ liệu bằng cách copy paste ra Excel sẽ rất chậm với dữ liệu lớn, vì thế ta sẽ dùng chức năng Save result view thành file CSV (nút thứ 4 từ phải sang có hình cái đĩa mềm). Tuy nhiên file CSV tạo ra khi đọc bằng Excel sẽ bị lỗi:

Bạn cần đăng nhập để thấy đính kèm

Tuy nhiên nếu mở file này bằng Notepad thì lại bình thường:

Bạn cần đăng nhập để thấy đính kèm

Nguyên nhân của lỗi này là do file CSV lưu các ký tự theo UTF8 nhưng Excel lại không biết do file này không có các ký tự nhận diện ở đầu file. Các ký tự này được gọi là BOM, đối với UTF8 là 3 bytes có giá trị hexa là EF BB BF. Vì thế để Excel nhận diện đúng ta có thể dùng các cách:
- Dùng Notepad Save as dưới dạng UTF8 with BOM;
- Tạo 1 file gồm 3 byte BOM ở trên, có thể dùng Notepad tạo file trống tên là bom.txt rồi save dạng UTF8 with BOM. Trong cửa sổ cmd, gõ lệnh

Copy/B bom.txt + kq.csv kq1.csv

Sau khi sử dụng 1 trong 2 cách trên thì Excel nhận diện được UTF8 và hiển thị bình thường. Đây cũng là bài kết thúc về sử dụng DB Browser cho SQLite, bản thân mình sử dụng SQLite thấy tốc độ rất tốt (với dữ liệu khoảng 1 triệu dòng, mình chưa dùng dữ liệu lớn hơn). Xin mời các bạn đóng góp ý kiến và thảo luận để chủ đề hoàn thiện.

 

[tuhocvba]

Thành thực xin lỗi bạn @Lương Tuấn Ba . Theo nội dung topic này thì SMod @NhanSu đã thông báo kết thúc và mọi người có thể thảo luận bình thường. Rất xin lỗi bạn.