convert editorgadget text to utf8 string

[juror]

Transitioning from pb 5.45 to 6.3 and having unicode/utf8 issues.
I have a sqlite db with text stored as UTF8
I display the text in an editorgadget.
I need to get the possible edited, text from the editor gadget into a utf8 string I can use to update the database.
I always end up updating the db with unicode text.
for example

Code: Select all

            uText$="12345" ;unicode default
             utextadd=@uText$ ;utext$ is unicode
             *text8=AllocateMemory(Len(uText$)) ; membuffer to use for UTF8
             PokeS(*text8,utext$,-1,#PB_UTF8)   ;poke unicode to UTF8 in memory works when viewed in memory
             Text8$= PeekS(*text8,-1,#PB_UTF8)  ; Peek to obtain utf8 string for db

text8$ is unicode not utf8. How can I get a uft8 string to insert in the database? New to PB6 and this is baffling.

Thanks for any assistance.
cheers

[RASHAD]

Hi
Try using

Code: Select all

	*Buffer = UTF8(String$)

[juror]

Hi Rashad. Always nice to hear from a true expert. I've used your tips many times.

I am able to get the utf8 into a memory buffer - the PokeS(*text8,utext$,-1,#PB_UTF8) ;poke unicode to UTF8 in memory works ok.

I am unable so far to get the membuffer *text8 into a string I can insert into the database.

I thought Text8$= PeekS(*text8,-1,#PB_UTF8) would give me a utf8 string, but when I insert it into the sqlite db, it is Unicode, not utf8. I have tried inserting it as text or even as blob, either way it is unicode.
If I get the memory address of @Text8 and view it, it is unicode.

I've been at this for a while, but still perplexed.

Thanks for looking.

[skywalk]

If your sqlite db pragma is set for UTF-8 and you use DatabaseString to send, doesn't it get stored as UTF-8?

[juror]

Thanks. The pragma is UTF8. I am currently using setdatabaseblob or "UPDATE cTbl SET "+fieldName+"=Text8$ - neither of which work since I am unable to get the utf8 memory address into a string I can insert.

I have not used DataBaseString, however it looks like it might take a string, which so far, I have not been able to create

I image it's my total unfamiliarity with unicode but after spending the better part of the day on it, I decided to throw in the towel and ask for assistance. Probably something simple i'm overlooking. How to get utf8 from a memory buffer into a utf8 string? Sounds simple, but I've been unable to so far.

I use the exact same PeekS(*pData,-1,#PB_UTF8) to get the original data from the clipboard to build the database, which works fine. I know I'm just overlooking something in the code above and need some fresh eyes.

[RASHAD]

Hi
Try

Code: Select all

#CP_UTF8 = 65001
String$ = "Hélé"

nBytes = WideCharToMultiByte_(#CP_UTF8, 0, @String$, -1, 0, 0, 0, 0)
Dim Buffer(nBytes - 2)
nBytes = WideCharToMultiByte_(#CP_UTF8, 0, @String$, -1, @Buffer(0), nBytes - 1, 0, 0)
Debug PeekS(@buffer())


*Buffer = UTF8(String$)
Debug PeekS(*Buffer, -1)

[juror]

Nice code. Works well.

As I mentioned, I am able to create a memory buffer which contains the utf8 string. I have been unable to extract a string from that buffer that i can use to insert into my sqlite database. My attempt via - Text8$= PeekS(*text8,-1,#PB_UTF8) results in a string, which when inserted into the database is always unicode.

Guess I need to revisit my sqlite process, however it is almost identical to the original insertion process I use to populate the database when it is created or a record is added. I will attempt to create an example which demonstrates the entire creation, update process, but that will take some time. I was hoping someone would observe some obvious error in what I submitted.

Back to the drawing board.

[skywalk]

I will look at my SQLite code and get back to you.
It was not overly complicated since PB does have a UTF8() function.
And you stuff blobs into the database.
You can read them back as blobs also.

[juror]

Thanks skywalk. I have followed you for quite some time too.

My original sqlite codebase circa 2010 used ascii string for the sqlite database, however when PB switched to "pure" 128 bit ascii (not sure of the date), my "text" no longer displayed correctly, so I switched to storing the text as blobs. I retired the development with pb 5.45.

I recently have been attempting to convert the app to PB 6.3. Has taken some effort, however I'm currently stonewalled.

I collect data from the clipboard (text, image, html, rtf) and store it in the same sqlite database I've used since 2010(?). Works fine and I'm able to update it from clipboard captures, retrieve it and access it fine. I display the text in an editorgadget, it displays fine.

The problem occurs when I attempt to retrieve text from the editorgadget via getgadgettext and update the database. The text retrieved from the editorgadget is unicode e.g. utext$. I can convert it to a UTF8 memory buffer via a PokeS(*text8,utext$,-1,#PB_UTF8) .

However, when I attempt to create a UTF8 string via a Text8$= PeekS(*text8,-1,#PB_UTF8) - to insert into the database, the insertion is always unicode. I have attempted to insert Text8$ as a blob, but it is always unicode.

Therein lies my problem.

[mk-soft]

I think you're completely confused here

Strings in PureBasic are always UNICODE.
DataBaseString takes care of the correct coding with the database

If you want to enter a text or image as a blob in the database, you must work with pointer

Modify basis code with blob data

Code: Select all

;-TOP

; Comment : ShowDatabaseRows
; Author  : mk-soft
; Version : v1.04.1
; Create  : 12.02.2017
; Update  : 29.03.2025
; Link    : https://www.purebasic.fr/english/viewtopic.php?t=75072

EnableExplicit

; ***************************************************************************************

Procedure.s ColumnTypeString(Type)
  Protected r1.s
  Select Type
    Case #PB_Database_Long
      r1 = "Long"
    Case #PB_Database_Quad
      r1 = "Quad"
    Case #PB_Database_Float
      r1 = "Float"
    Case #PB_Database_Double
      r1 = "Double"
    Case #PB_Database_String
      r1 = "String"
    Case #PB_Database_Blob
      r1 = "Blob"
  EndSelect
  ProcedureReturn r1
EndProcedure

; ----

Procedure GetTextWidth(Text.s, FontID.i = 0)
  Static image
  Protected result
  
  If Not image
    image = CreateImage(#PB_Any, 1, 1)
  EndIf
  
  If image And StartDrawing(ImageOutput(image))
    If FontID
      DrawingFont(FontID)
    EndIf
    result = TextWidth(Text)
    StopDrawing()
  EndIf
  ProcedureReturn result
EndProcedure

; ----

Procedure ClearGadgetColumns(Gadget)
  CompilerIf #PB_Compiler_Version <= 551
    ClearGadgetItems(Gadget)
    While GetGadgetItemText(Gadget, -1, 0)
      RemoveGadgetColumn(Gadget, 0)
    Wend
  CompilerElse
    RemoveGadgetColumn(Gadget, #PB_All)
  CompilerEndIf
EndProcedure

; ----

Procedure CountGadgetColumns(Gadget)
  Protected result
  CompilerIf #PB_Compiler_Version <= 551
    While GetGadgetItemText(Gadget, -1, result)
      result + 1
    Wend
  CompilerElse
    result = GetGadgetAttribute(Gadget, #PB_ListIcon_ColumnCount)
  CompilerEndIf
  ProcedureReturn result
EndProcedure

; ----

Procedure FitColumnWidth(Gadget, Column = #PB_All)
  Protected columns, rows, col, row, width, max, dx
  Static help_gadget
  
  CompilerIf #PB_Compiler_OS = #PB_OS_Windows
    If Column = #PB_All
      columns = CountGadgetColumns(Gadget) - 1
      For col = 0 To columns
        SendMessage_(GadgetID(Gadget), #LVM_SETCOLUMNWIDTH, col, #LVSCW_AUTOSIZE_USEHEADER)
      Next
    Else
      SendMessage_(GadgetID(Gadget), #LVM_SETCOLUMNWIDTH, Column, #LVSCW_AUTOSIZE_USEHEADER)
    EndIf
    
  CompilerElse
    rows = CountGadgetItems(Gadget) - 1
    dx = GetTextWidth("X", GetGadgetFont(Gadget))
    If Column = #PB_All
      columns = CountGadgetColumns(Gadget) - 1
      For col = 0 To columns
        For row = -1 To rows
          width = Len(GetGadgetItemText(Gadget, row, col))
          If width > max
            max = width
          EndIf
        Next
        max + 1
        width = max * dx
        SetGadgetItemAttribute(Gadget, 0, #PB_ListIcon_ColumnWidth, width, col)
        width = 0
        max = 0
      Next
    Else
      For row = -1 To rows
        width = Len(GetGadgetItemText(Gadget, row, Column))
        If width > max
          max = width
        EndIf
      Next
      max + 1
      width = max * dx
      SetGadgetItemAttribute(Gadget, 0, #PB_ListIcon_ColumnWidth, width, Column)
    EndIf
    
  CompilerEndIf
  
EndProcedure

; ----

Procedure ShowDatabaseRows(Gadget, DataBase, Limit = 1000, NbDecimals = 2, Hide = #False)
  Protected columns.i, index.i, count, size.i, text.s, fltVal.f, dblVal.d, *buffer
  
  Repeat ; Do
    If GadgetType(Gadget) <> #PB_GadgetType_ListIcon
      Break
    EndIf
    If Not IsDatabase(DataBase)
      Break
    EndIf
    HideGadget(Gadget, Hide)
    ClearGadgetItems(Gadget)
    ClearGadgetColumns(Gadget)
    columns = DatabaseColumns(DataBase) - 1
    Dim ColumnType(columns)
    For index = 0 To columns
      ColumnType(index) = DatabaseColumnType(DataBase, index)
      text = DatabaseColumnName(DataBase, index)
      size = GetTextWidth(text) + 12
      AddGadgetColumn(Gadget, index, text, size)
    Next
    While NextDatabaseRow(DataBase)
      count + 1
      If count > Limit
        MessageRequester("ShowDatabaseRows", "More than " + limit + " rows. Output limited.", #PB_MessageRequester_Warning)
        Break
      EndIf
      text = ""
      For index = 0 To columns
        Select ColumnType(index)
          Case #PB_Database_Float
            fltVal = GetDatabaseFloat(DataBase, index)
            text + FormatNumber(fltVal, NbDecimals) + #LF$
          Case #PB_Database_Double
            dblVal = GetDatabaseDouble(DataBase, index) ; <- Bug Linux
            text + FormatNumber(dblVal, NbDecimals) + #LF$
          Case #PB_Database_Blob
            size = DatabaseColumnSize(DataBase, index)
            If size > 0
              *buffer = AllocateMemory(size)
              If GetDatabaseBlob(DataBase, index, *buffer, size)
                text + "BlobData: "
                ;TODO DataType of Blob
                text + PeekS(*buffer, size, #PB_UTF8) + #LF$
              Else
                text + "BlobData: "
              EndIf
            Else
              text + "Blobdata Empty"
            EndIf
          Default
            text + GetDatabaseString(DataBase, index) + #LF$
        EndSelect
      Next
      AddGadgetItem(Gadget, -1, text)
    Wend
    FinishDatabaseQuery(DataBase)
    HideGadget(Gadget, #False)
  Until #True
  ProcedureReturn count
  
EndProcedure

; ***************************************************************************************

CompilerIf #PB_Compiler_IsMainFile
  
  ; Constant
  Enumeration ;Window
    #Main
  EndEnumeration
  
  Enumeration ; Menu
    #Menu
  EndEnumeration
  
  Enumeration ; MenuItems
    #MenuExitApplication
  EndEnumeration
  
  Enumeration ; Gadgets
    #List
    #Edit
  EndEnumeration
  
  Enumeration ; Statusbar
    #Status
  EndEnumeration
  
  ; Global Variable
  Global ExitApplication
  
  ; Functions
  
  UseSQLiteDatabase()
  
  Procedure CheckDatabaseUpdate(Database, Query$)
    Protected Result = DatabaseUpdate(Database, Query$)
    If Result = 0
      Debug DatabaseError()
    EndIf
    ProcedureReturn Result
  EndProcedure
  
  Procedure CreateDummyDatabase(DataBase)
    Protected dbName.s, *buffer
    If 0
      dbName = GetUserDirectory(#PB_Directory_Downloads) + "dbTest.db"
      CreateFile(0, dbName)
      CloseFile(0)
    Else
      dbName = ":memory:"
    EndIf
    If OpenDatabase(DataBase, dbName, "", "")
      CheckDatabaseUpdate(DataBase, "CREATE TABLE food (recid INTEGER PRIMARY KEY ASC, name CHAR(50), weight REAL, date DATETIME, data BLOB)")
      CheckDatabaseUpdate(DataBase, "INSERT INTO food (name, weight, date) VALUES ('apple', '10.005', current_timestamp)")
      CheckDatabaseUpdate(DataBase, "INSERT INTO food (name, weight, date) VALUES ('pear', '5.9', '1900-01-01 12:00:00')")
      CheckDatabaseUpdate(DataBase, "INSERT INTO food (name, weight, date) VALUES ('banana', '20.35', '2400-12-31 12:00:00')")
      ; DateTime as quad
      SetDatabaseQuad(DataBase, 0, DateUTC())
      *buffer = UTF8("Hello world!")
      SetDatabaseBlob(DataBase, 1, *buffer, MemorySize(*buffer))
      CheckDatabaseUpdate(DataBase, "INSERT INTO food (name, weight, date, data) VALUES ('cherry', '10.00', datetime(?, 'unixepoch', 'localtime'), ?)")
      FreeMemory(*buffer)
    Else
      Debug "Can't open database !"
    EndIf
  EndProcedure
  
  Procedure UpdateWindow()
    Protected x, y, dx, dy, menu, status
    
    menu = MenuHeight()
    If IsStatusBar(#Status)
      status = StatusBarHeight(#Status)
    Else
      status = 0
    EndIf
    x = 0
    y = 0
    dx = WindowWidth(#Main)
    dy = WindowHeight(#Main) - menu - status
    ResizeGadget(#List, x, y, dx, dy)
  EndProcedure
  
  ; Main
  Procedure Main()
    Protected event, style, dx, dy, count
    
    style = #PB_Window_SystemMenu | #PB_Window_MinimizeGadget | #PB_Window_MaximizeGadget | #PB_Window_SizeGadget
    dx = 800
    dy = 600
    
    If OpenWindow(#Main, #PB_Ignore, #PB_Ignore, dx, dy, "Main", style)
      
      ; Menu
      CreateMenu(#Menu, WindowID(#Main))
      MenuTitle("Ablage")
      MenuItem(#MenuExitApplication, "Be&enden")
      
      CompilerIf #PB_Compiler_OS = #PB_OS_MacOS 
        ; Mac default menu
        If Not IsMenu(#Menu)
          CreateMenu(#Menu, WindowID(#Main))
        EndIf
        MenuItem(#PB_Menu_About, "")
        MenuItem(#PB_Menu_Preferences, "")
      CompilerEndIf
      
      ; Gadgets
      #ListIconFlags = #PB_ListIcon_AlwaysShowSelection | #PB_ListIcon_FullRowSelect | #PB_ListIcon_GridLines
      ListIconGadget(#List, 0, 0, dx, dy, "recid", 100, #ListIconFlags)
      
      ; Statusbar
      CreateStatusBar(#Status, WindowID(#Main))
      AddStatusBarField(#PB_Ignore)
      
      UpdateWindow()
      
      ;-Test database
      CreateDummyDatabase(0)
      
      ;If DatabaseQuery(0, "SELECT * FROM food")
      If DatabaseQuery(0, "SELECT recid as RecID, name as Name, weight as Gewicht, date as Datum, data as BlobData FROM food")
      ;If DatabaseQuery(0, "SELECT * FROM sqlite_master")
        count = ShowDatabaseRows(#List, 0, 1000, 3)
        StatusBarText(#Status, 0, "Items: " + count)
      EndIf
      
      FitColumnWidth(#List)
      
      ; Main Loop
      Repeat
        event = WaitWindowEvent()
        Select event
          Case #PB_Event_Menu
            Select EventMenu()
                CompilerIf #PB_Compiler_OS = #PB_OS_MacOS
                  
                Case #PB_Menu_About
                  
                Case #PB_Menu_Preferences
                  
                Case #PB_Menu_Quit
                  ExitApplication = #True
                  
                CompilerEndIf
                
              Case #MenuExitApplication
                ExitApplication = #True
                
            EndSelect
            
          Case #PB_Event_Gadget
            Select EventGadget()
              Case #List
                
            EndSelect
            
          Case #PB_Event_SizeWindow
            Select EventWindow()
              Case #Main
                UpdateWindow()
                
            EndSelect
            
          Case #PB_Event_CloseWindow
            Select EventWindow()
              Case #Main
                ExitApplication = #True
                
            EndSelect
            
        EndSelect
        
      Until ExitApplication
      
    EndIf
    
  EndProcedure : Main()
  
  End
  
CompilerEndIf

[juror]

Hey, mk-soft, glad you stopped by. I referenced some of your code to assist in my conversion.

Yes, I admit I'm confused.

"Strings in PureBasic are always UNICODE."

I'm looking for a way to get text from an editorgadget and insert it into a sqlite database as UTF8. There must be a way.

I can capture a "string" from the clipboard and insert it into the sqlite database as UTF8 text via

Code: Select all

   If IsClipboardFormatAvailable_(#CF_TEXT) <> 0
                hStrPtr = GetClipboardData_(#CF_TEXT)
                If hStrPtr
                  *pdata=GlobalLock_(hstrptr)
                  dbwText$=PeekS(*pData,-1,#PB_UTF8)

and write dbwTest$ to the sqlite db as Text as part of a sqlite command string as

cmd$+"'"+ dbwText$+"'," - where dbwText$ is inserted as Text and it works fine. I view it in the db and it is UTF8 not unicode.

However, when I attempt to create a string using the peeks in my original post above it is unicode.
So one peeks gives me a UTF8 string which I can insert on a database and the other peeks gives me unicode.

It appears I will once again need to store the memory address data as a blob (or possibly Base64 encoded). I had hoped since the clipboard capture PeekS worked, I might be able to get a UTF8 string from the editor gadget.

Yes, I'm very confused. And in all likelihood overlooking something obvious.

Again, thanks for stopping by and assisting.

cheers

[mk-soft]

PureBasic uses the standard of SQlite and thus the database as UTF8
Thus, the string that PB writes to the database in UTF8
Text$ = PeekS(*Pointer,-1, #PB_UTF8) converts the string into UNICODE so that you can also edit the text
When transferred to the database of the text$ (UNICODE), this is stored as UTF8 in the database.

You still have to test the Clipboard API ... not work here

Code: Select all

;-TOP

; Comment : ShowDatabaseRows
; Author  : mk-soft
; Version : v1.04.1
; Create  : 12.02.2017
; Update  : 29.03.2025
; Link    : https://www.purebasic.fr/english/viewtopic.php?t=75072

EnableExplicit

; ***************************************************************************************

Procedure.s ColumnTypeString(Type)
  Protected r1.s
  Select Type
    Case #PB_Database_Long
      r1 = "Long"
    Case #PB_Database_Quad
      r1 = "Quad"
    Case #PB_Database_Float
      r1 = "Float"
    Case #PB_Database_Double
      r1 = "Double"
    Case #PB_Database_String
      r1 = "String"
    Case #PB_Database_Blob
      r1 = "Blob"
  EndSelect
  ProcedureReturn r1
EndProcedure

; ----

Procedure GetTextWidth(Text.s, FontID.i = 0)
  Static image
  Protected result
  
  If Not image
    image = CreateImage(#PB_Any, 1, 1)
  EndIf
  
  If image And StartDrawing(ImageOutput(image))
    If FontID
      DrawingFont(FontID)
    EndIf
    result = TextWidth(Text)
    StopDrawing()
  EndIf
  ProcedureReturn result
EndProcedure

; ----

Procedure ClearGadgetColumns(Gadget)
  CompilerIf #PB_Compiler_Version <= 551
    ClearGadgetItems(Gadget)
    While GetGadgetItemText(Gadget, -1, 0)
      RemoveGadgetColumn(Gadget, 0)
    Wend
  CompilerElse
    RemoveGadgetColumn(Gadget, #PB_All)
  CompilerEndIf
EndProcedure

; ----

Procedure CountGadgetColumns(Gadget)
  Protected result
  CompilerIf #PB_Compiler_Version <= 551
    While GetGadgetItemText(Gadget, -1, result)
      result + 1
    Wend
  CompilerElse
    result = GetGadgetAttribute(Gadget, #PB_ListIcon_ColumnCount)
  CompilerEndIf
  ProcedureReturn result
EndProcedure

; ----

Procedure FitColumnWidth(Gadget, Column = #PB_All)
  Protected columns, rows, col, row, width, max, dx
  Static help_gadget
  
  CompilerIf #PB_Compiler_OS = #PB_OS_Windows
    If Column = #PB_All
      columns = CountGadgetColumns(Gadget) - 1
      For col = 0 To columns
        SendMessage_(GadgetID(Gadget), #LVM_SETCOLUMNWIDTH, col, #LVSCW_AUTOSIZE_USEHEADER)
      Next
    Else
      SendMessage_(GadgetID(Gadget), #LVM_SETCOLUMNWIDTH, Column, #LVSCW_AUTOSIZE_USEHEADER)
    EndIf
    
  CompilerElse
    rows = CountGadgetItems(Gadget) - 1
    dx = GetTextWidth("X", GetGadgetFont(Gadget))
    If Column = #PB_All
      columns = CountGadgetColumns(Gadget) - 1
      For col = 0 To columns
        For row = -1 To rows
          width = Len(GetGadgetItemText(Gadget, row, col))
          If width > max
            max = width
          EndIf
        Next
        max + 1
        width = max * dx
        SetGadgetItemAttribute(Gadget, 0, #PB_ListIcon_ColumnWidth, width, col)
        width = 0
        max = 0
      Next
    Else
      For row = -1 To rows
        width = Len(GetGadgetItemText(Gadget, row, Column))
        If width > max
          max = width
        EndIf
      Next
      max + 1
      width = max * dx
      SetGadgetItemAttribute(Gadget, 0, #PB_ListIcon_ColumnWidth, width, Column)
    EndIf
    
  CompilerEndIf
  
EndProcedure

; ----

Procedure ShowDatabaseRows(Gadget, DataBase, Limit = 1000, NbDecimals = 2, Hide = #False)
  Protected columns.i, index.i, count, size.i, text.s, fltVal.f, dblVal.d
  
  Repeat ; Do
    If GadgetType(Gadget) <> #PB_GadgetType_ListIcon
      Break
    EndIf
    If Not IsDatabase(DataBase)
      Break
    EndIf
    HideGadget(Gadget, Hide)
    ClearGadgetItems(Gadget)
    ClearGadgetColumns(Gadget)
    columns = DatabaseColumns(DataBase) - 1
    Dim ColumnType(columns)
    For index = 0 To columns
      ColumnType(index) = DatabaseColumnType(DataBase, index)
      text = DatabaseColumnName(DataBase, index)
      size = GetTextWidth(text) + 12
      AddGadgetColumn(Gadget, index, text, size)
    Next
    While NextDatabaseRow(DataBase)
      count + 1
      If count > Limit
        MessageRequester("ShowDatabaseRows", "More than " + limit + " rows. Output limited.", #PB_MessageRequester_Warning)
        Break
      EndIf
      text = ""
      For index = 0 To columns
        Select ColumnType(index)
          Case #PB_Database_Float
            fltVal = GetDatabaseFloat(DataBase, index)
            text + FormatNumber(fltVal, NbDecimals) + #LF$
          Case #PB_Database_Double
            dblVal = GetDatabaseDouble(DataBase, index) ; <- Bug Linux
            text + FormatNumber(dblVal, NbDecimals) + #LF$
          Default
            text + GetDatabaseString(DataBase, index) + #LF$
        EndSelect
      Next
      AddGadgetItem(Gadget, -1, text)
    Wend
    FinishDatabaseQuery(DataBase)
    HideGadget(Gadget, #False)
  Until #True
  ProcedureReturn count
  
EndProcedure

; ***************************************************************************************

CompilerIf #PB_Compiler_IsMainFile
  
  ; Constant
  Enumeration ;Window
    #Main
  EndEnumeration
  
  Enumeration ; Menu
    #Menu
  EndEnumeration
  
  Enumeration ; MenuItems
    #MenuExitApplication
  EndEnumeration
  
  Enumeration ; Gadgets
    #List
    #Edit
    #ButtonGetClipboard
  EndEnumeration
  
  Enumeration ; Statusbar
    #Status
  EndEnumeration
  
  ; Global Variable
  Global ExitApplication
  
  ; Functions
  
  UseSQLiteDatabase()
  
  Procedure CheckDatabaseUpdate(Database, Query$)
    Protected Result = DatabaseUpdate(Database, Query$)
    If Result = 0
      Debug DatabaseError()
    EndIf
    ProcedureReturn Result
  EndProcedure
  
  Procedure CreateDummyDatabase(DataBase)
    Protected dbName.s
    If 1
      dbName = GetUserDirectory(#PB_Directory_Downloads) + "dbTest.db"
      CreateFile(0, dbName)
      CloseFile(0)
    Else
      dbName = ":memory:"
    EndIf
    If OpenDatabase(DataBase, dbName, "", "")
      CheckDatabaseUpdate(DataBase, "CREATE TABLE Clipboard (recid INTEGER PRIMARY KEY ASC, text TEXT)")
    Else
      Debug "Can't open database !"
    EndIf
  EndProcedure
  
  Procedure UpdateDatabaseList(DataBase)
    Protected count
    If DatabaseQuery(DataBase, "SELECT * FROM Clipboard")
      count = ShowDatabaseRows(#List, 0, 1000, 3)
      StatusBarText(#Status, 0, "Items: " + count)
    EndIf
  EndProcedure
  
  Procedure UpdateWindow()
    Protected x, y, dx, dy, menu, status
    
    menu = MenuHeight()
    If IsStatusBar(#Status)
      status = StatusBarHeight(#Status)
    Else
      status = 0
    EndIf
    x = 0
    y = 0
    dx = WindowWidth(#Main)
    dy = WindowHeight(#Main) - menu - status
    ResizeGadget(#List, x, y, dx, dy - 40)
    ResizeGadget(#ButtonGetClipboard, 10, dy - 35, 140, 25)
  EndProcedure
  
  ; Main
  Procedure Main()
    Protected event, style, dx, dy, count, text$
    
    style = #PB_Window_SystemMenu | #PB_Window_MinimizeGadget | #PB_Window_MaximizeGadget | #PB_Window_SizeGadget
    dx = 800
    dy = 600
    
    If OpenWindow(#Main, #PB_Ignore, #PB_Ignore, dx, dy, "Main", style)
      
      ; Menu
      CreateMenu(#Menu, WindowID(#Main))
      MenuTitle("Ablage")
      MenuItem(#MenuExitApplication, "Be&enden")
      
      CompilerIf #PB_Compiler_OS = #PB_OS_MacOS 
        ; Mac default menu
        If Not IsMenu(#Menu)
          CreateMenu(#Menu, WindowID(#Main))
        EndIf
        MenuItem(#PB_Menu_About, "")
        MenuItem(#PB_Menu_Preferences, "")
      CompilerEndIf
      
      ; Gadgets
      #ListIconFlags = #PB_ListIcon_AlwaysShowSelection | #PB_ListIcon_FullRowSelect | #PB_ListIcon_GridLines
      ListIconGadget(#List, 0, 0, dx, dy - 40, "recid", 100, #ListIconFlags)
      ButtonGadget(#ButtonGetClipboard, 10, dy - 35, 140, 25, "Copy Clipboard")
      ; Statusbar
      CreateStatusBar(#Status, WindowID(#Main))
      AddStatusBarField(#PB_Ignore)
      
      UpdateWindow()
      
      ;-Test database
      CreateDummyDatabase(0)
      
      
      FitColumnWidth(#List)
      
      ; Main Loop
      Repeat
        event = WaitWindowEvent()
        Select event
          Case #PB_Event_Menu
            Select EventMenu()
                CompilerIf #PB_Compiler_OS = #PB_OS_MacOS
                  
                Case #PB_Menu_About
                  
                Case #PB_Menu_Preferences
                  
                Case #PB_Menu_Quit
                  ExitApplication = #True
                  
                CompilerEndIf
                
              Case #MenuExitApplication
                ExitApplication = #True
                
            EndSelect
            
          Case #PB_Event_Gadget
            Select EventGadget()
              Case #List
                
              Case #ButtonGetClipboard
                CompilerIf #PB_Compiler_OS = #PB_OS_Windows
                  Protected hStrPtr, *pdata
                  If IsClipboardFormatAvailable_(#CF_TEXT) <> 0
                    hStrPtr = GetClipboardData_(#CF_TEXT)
                    If hStrPtr
                      *pdata= GlobalLock_(hstrptr)
                      text$ = PeekS(*pData,-1,#PB_UTF8)
                      Globalunlock_(*pdata)
                    EndIf
                  EndIf
                CompilerElse
                  text$ = GetClipboardText()
                CompilerEndIf
                If text$
                  SetDatabaseString(0, 0, text$)
                  CheckDatabaseUpdate(0, "INSERT INTO Clipboard (text) VALUES (?)")
                EndIf
                UpdateDatabaseList(0)
                
            EndSelect
            
          Case #PB_Event_SizeWindow
            Select EventWindow()
              Case #Main
                UpdateWindow()
                
            EndSelect
            
          Case #PB_Event_CloseWindow
            Select EventWindow()
              Case #Main
                ExitApplication = #True
                
            EndSelect
            
        EndSelect
        
      Until ExitApplication
      
    EndIf
    
  EndProcedure : Main()
  
  End
  
CompilerEndIf

[mk-soft]

I don't know where the UTF8 text comes from.
But if you want to have this in the sqlite as text, you have to do it yourself via your own sqlite3 functions.

Update

Code: Select all

;-TOP by mk-soft, v1.01.2, 16.11.2025

UseSQLiteDatabase()

#SQLITE_OK = 0
#SQLITE_ERROR = 1
#SQLITE_DONE = 101

ImportC ""
  sqlite3_prepare_v2(db, sql.p-utf8, nByte, *ppStmt, *pzTail)
  sqlite3_step(stmt)
  sqlite3_finalize(stmt)
  sqlite3_bind_double(stmt, idx, double.d)
  sqlite3_bind_int(stmt, idx, int.l)
  sqlite3_bind_int64(stmt, idx, int64.q)
  sqlite3_bind_text(stmt, idx, *utf8_buffer, bytes, *mem_callback)
  
  sqlite3_errstr(int)
EndImport

Procedure.s sqlite3_error(errorcode)
  Protected *error
  *error = sqlite3_errstr(ErrorCode)
  ProcedureReturn PeekS(*error, -1, #PB_UTF8)
EndProcedure

Procedure CheckDatabaseUpdate(Database, Query$)
  Result = DatabaseUpdate(Database, Query$)
  If Result = 0
    Debug DatabaseError()
  EndIf
  
  ProcedureReturn Result
EndProcedure

DatabaseFile$ = GetUserDirectory(#PB_Directory_Downloads)+"Database.db"

If CreateFile(0, DatabaseFile$)
  CloseFile(0)
  
  If OpenDatabase(0, DatabaseFile$, "", "")
    
    CheckDatabaseUpdate(0, "CREATE TABLE MyTable (recid INTEGER PRIMARY KEY ASC, iVal INTEGER, dblVal DOUBLE, name TEXT)")
    sql.s = "INSERT INTO MyTable (iVal, dblVal, name) VALUES (?,?,?)"
    r1 = sqlite3_prepare_v2(DatabaseID(0), sql, StringByteLength(sql, #PB_UTF8), @stmt, 0)
    If r1 <> #SQLITE_OK
      Debug sqlite3_error(r1)
    Else
      r1 = sqlite3_bind_int64(stmt, 1, 1234567890)
      If r1 <> #SQLITE_OK
        Debug sqlite3_error(r1)
        Goto error
      EndIf
      r1 = sqlite3_bind_double(stmt, 2, 100.999)
      If r1 <> #SQLITE_OK
        Debug sqlite3_error(r1)
        Goto error
      EndIf
      *buffer = UTF8("Hello World!")
      ; Len of UTF8 Text without Zero Byte
      r1 = sqlite3_bind_text(stmt, 3, *buffer, MemorySize(*buffer) - 1, #Null)
      If r1 <> #SQLITE_OK
        Debug sqlite3_error(r1)
        Goto error
      EndIf
      r1 = sqlite3_step(stmt)
      If r1 <> #SQLITE_DONE
        Debug sqlite3_error(r1)
      EndIf
      error:
      r1 = sqlite3_finalize(stmt)
      If r1 <> #SQLITE_OK
        Debug sqlite3_error(r1)
      EndIf
      FreeMemory(*buffer)
    EndIf
    
    If DatabaseQuery(0, "SELECT * FROM MyTable")
      cnt = DatabaseColumns(0) - 1
      While NextDatabaseRow(0)
        text.s = ""
        For i = 0 To cnt
          text.s + GetDatabaseString(0, i) + " | "
        Next
        Debug text.s
      Wend
      FinishDatabaseQuery(0)
    EndIf
    
    CloseDatabase(0)
  Else
    Debug "Can't open database !"
  EndIf
Else
  Debug "Can't create the database file !"
EndIf

[juror]

Thanks mk-soft. I got a bit hung-up on the first post, but this one put me on the right track and I have a solution.

Thanks for your assistance and examples.

cheers

[skywalk]

Nice code mk-soft.

I checked my code and I get by with the PB functions ok as I use UTF8().
Only note I had was for stuffing an entire file into a record, I had to omit the final trailing null, else DB Browser for SQLite would flag the entire entry as binary.