Android Question Sqlite database on microsd card

Tim Chapman · Friday at 11:35 AM

I am trying to do just what the title says. I am having no success with initializing a sqlite db on a microsd card on Android. Does anyone know how to do this?

The normal db.Initialize(folderPath, DBNAME, True) won't work. Is there a work around for this?

This is the error I get:

Stack trace: (SQLiteCantOpenDatabaseException) android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file (code 1806 SQLITE_CANTOPEN_ENOENT[1806]): No such file or directory

DonManfred · Friday at 12:24 PM

What exactly is the value of folderPath?
You know that you do not have access to paths outside DirInternal, rigth? At least on higher android versions.

android.jar / targetSdkVersion / minSdkVersion

There are several versioned components that affect the compilation process and the runtime behavior of our apps. The purpose of this tutorial is to explain the differences between them and help you choose which version to use. Each Android version is mapped to an api level. You can see this...

www.b4x.com

Sagenut · Friday at 12:35 PM

If just for private use (it means NO publish on Google Play) you can try this
https://www.b4x.com/android/forum/t...cess-internal-external-storage-sdk-30.130411/

Tim Chapman · Friday at 9:29 PM

DonManfred said:
What exactly is the value of folderPath?

MicroSDcardRoot/folder1/folder2/folderWithDatabaseFile.

Tim Chapman · 2026-01-04T05:50:20+0000

I have made a workaround that allows database like functions using a jsonl file. I am sorry that I don't have time to refine it more for a public display, but I wanted to share it regardless. It works.

sqlite workaround:

'ChatHistoryDB - JSONL File-Based Drop-In Replacement
'Stores chat history, tool calls/responses, and system prompts as JSON lines on microSD card
'Same API as SQL version - code doesn't know the difference
'FIXED: Added storage parameter and SetCampaignFolder method
'FIXED: Robust JSON string escaping (handles backslashes, quotes, newlines, tabs, carriage returns)

Sub Class_Globals
    Private storage As ExternalStorage
    Private campaignFolder As ExternalFile
    Private Const ChatFileName As String = "chat_history.jsonl"
    
    ' Escaping constants for JSON safety
    Private Const BACKSLASH As String = "\"
    Private Const JSON_QUOTE As String = """"
    Private Const JSON_BACKSLASH As String = "\\"
    Private Const JSON_NEWLINE As String = "\n"
    Private Const JSON_TAB As String = "\t"
    Private Const JSON_CARRIAGE_RETURN As String = "\r"
End Sub

Public Sub Initialize
    Log("ChatHistoryDB Initialized")
    storage.Initialize(Me, "Storage")
End Sub

Public Sub InitializeDatabase(campaignFolder_Param As ExternalFile, storage_param As ExternalStorage) As Boolean
    Try
        storage = storage_param
        campaignFolder = campaignFolder_Param
        
        If Not(campaignFolder.IsInitialized) Then
            Log("❌ ERROR: Campaign folder not initialized")
            Return False
        End If
        
        If Not(storage.IsInitialized) Then
            Log("❌ ERROR: Storage not initialized")
            Return False
        End If
        
        Log("📊 Attempting to initialize database...")
        Log("   Folder: " & campaignFolder.Name)
        Log("   DB File: " & ChatFileName)
        
        ' File will be created on first write
        Log("✅ ChatHistoryDB initialized successfully!")
        Log("   Using JSONL format on microSD card")
        Log("   File: " & campaignFolder.Name & "/" & ChatFileName)
        
        Return True
        
    Catch
        Log("❌ ERROR initializing ChatHistoryDB")
        Log("   Exception: " & LastException.Message)
        Return False
    End Try
End Sub

' ========================================
' ROBUST JSON STRING ESCAPING
' ========================================
' Escapes special characters for valid JSON output
' Handles: quotes, backslashes, newlines, tabs, carriage returns
' Returns: Safe-for-JSON string
Private Sub EscapeJsonString(s As String) As String
    If s = "" Then Return ""
    
    Dim sb As StringBuilder
    sb.Initialize
    
    For i = 0 To s.Length - 1
        Dim c As String = s.CharAt(i)
        
        Select c
            Case QUOTE ' Double quote (ASCII 34)
                sb.Append(JSON_QUOTE) ' Becomes \"
                
            Case BACKSLASH ' Backslash (ASCII 92)
                sb.Append(JSON_BACKSLASH) ' Becomes \\
                
            Case Chr(10) ' Newline (LF)
                sb.Append(JSON_NEWLINE) ' Becomes \n
                
            Case Chr(13) ' Carriage return (CR)
                sb.Append(JSON_CARRIAGE_RETURN) ' Becomes \r
                
            Case Chr(9) ' Tab
                sb.Append(JSON_TAB) ' Becomes \t
                
            Case Else
                sb.Append(c) ' All other characters unchanged
        End Select
    Next
    
    Return sb.ToString
End Sub

' Save a single message to database (JSONL format)
Public Sub SaveMessageToDatabase(sessionNum As Int, turnNum As Int, messageIdx As Int, role As String, content As String, isToolCall As Int, isToolResponse As Int)
    If Not(storage.IsInitialized) Then
        Log("❌ ERROR: Database not initialized - cannot save message")
        Return
    End If
    
    Try
        ' Escape all special characters in content
        Dim escapedContent As String = EscapeJsonString(content)
        Dim escapedRole As String = EscapeJsonString(role)
        
        ' Build JSON line manually with proper escaping
        Dim sb As StringBuilder
        sb.Initialize
        sb.Append("{")
        sb.Append(QUOTE & "sessionnumber" & QUOTE & ":").Append(sessionNum).Append(",")
        sb.Append(QUOTE & "turnnumber" & QUOTE & ":").Append(turnNum).Append(",")
        sb.Append(QUOTE & "messageindex" & QUOTE & ":").Append(messageIdx).Append(",")
        sb.Append(QUOTE & "role" & QUOTE & ":" & QUOTE).Append(escapedRole).Append(QUOTE & ",")
        sb.Append(QUOTE & "content" & QUOTE & ":" & QUOTE).Append(escapedContent).Append(QUOTE & ",")
        sb.Append(QUOTE & "istoolcall" & QUOTE & ":").Append(isToolCall).Append(",")
        sb.Append(QUOTE & "istoolresponse" & QUOTE & ":").Append(isToolResponse).Append(",")
        sb.Append(QUOTE & "timestamp" & QUOTE & ":" & QUOTE).Append(DateTime.Now).Append(QUOTE)
        sb.Append("}")
        
        Dim jsonLine As String = sb.ToString & Chr(10)
        
        ' Find or create the file
        Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
        
        If ChatFile.IsInitialized Then
            ' File exists - read and append
            Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
            Dim reader As TextReader
            reader.Initialize(inputStream)
            Dim existingContent As String = reader.ReadAll
            reader.Close
            
            ' Write back with new line appended
            Dim outputStream As OutputStream = storage.OpenOutputStream(ChatFile)
            Dim writer As TextWriter
            writer.Initialize(outputStream)
            writer.Write(existingContent & jsonLine)
            writer.Close
        Else
            ' Create new file
            ChatFile = storage.CreateNewFile(campaignFolder, ChatFileName)
            
            Dim outputStream As OutputStream = storage.OpenOutputStream(ChatFile)
            Dim writer As TextWriter
            writer.Initialize(outputStream)
            writer.Write(jsonLine)
            writer.Close
        End If
        
        Log("✅ Message saved: " & escapedRole & " (idx " & messageIdx & ")")
        
    Catch
        Log("❌ Error saving message: " & LastException.Message)
    End Try
End Sub

' Retrieve history for AI - returns last N messages (reads backwards from EOF efficiently)
' Fixed: String type conversion for sessionNum, proper JSON key matching, robust escaping
Public Sub RetrieveHistoryForAI(sessionNum As String, turnsBack As Int) As List
    Dim messages As List
    messages.Initialize
    
    If Not(storage.IsInitialized) Then
        Log("❌ ERROR: Not initialized - cannot retrieve messages")
        Return messages
    End If
    
    Try
        ' Validate turnsBack parameter
        If turnsBack < 1 Then turnsBack = 10
        If turnsBack > 100 Then turnsBack = 100
        
        Log("🔄 Retrieving last " & turnsBack & " messages...")
        
        ' Find the chat file
        Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
        
        If Not(ChatFile.IsInitialized) Then
            Log("ℹ️  No chat history file found yet")
            Return messages
        End If
        
        ' Read entire file
        Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
        Dim reader As TextReader
        reader.Initialize(inputStream)
        Dim content As String = reader.ReadAll
        reader.Close
        
        ' Split into lines
        Dim lines() As String = Regex.Split(Chr(10), content)
        
        Log("📄 Read " & lines.Length & " lines from chat file")
        
        ' Parse lines as JSON, filter by sessionNum, collect last N
        Dim lineCount As Int = 0
        For i = lines.Length - 1 To 0 Step -1
            If messages.Size >= turnsBack Then Exit
            
            Dim line As String = lines(i).Trim
            If line = "" Then Continue
            
            Try
                Dim parser As JSONParser
                parser.Initialize(line)
                Dim msg As Map = parser.NextObject
                
                ' Key fix: Convert sessionNum from JSON (Int) to String for comparison
                Dim msgSessionNum As String = msg.Get("sessionnumber") & ""
                
                If msgSessionNum = sessionNum Then
                    messages.InsertAt(0, msg)  ' Insert at beginning to maintain order
                    lineCount = lineCount + 1
                End If
            Catch
                ' Skip malformed JSON lines
                Log("⚠️  Skipping malformed line: " & line)
            End Try
        Next
        
        Log("✅ Retrieved " & messages.Size & " messages from session " & sessionNum)
        Return messages
        
    Catch
        Log("❌ ERROR in RetrieveHistoryForAI: " & LastException.Message)
        Return messages
    End Try
End Sub

' Get message count for session
Public Sub GetMessageCountForSession(sessionNum As String) As Int
    If Not(storage.IsInitialized) Then Return 0
    
    Try
        Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
        If Not(ChatFile.IsInitialized) Then Return 0
        
        ' Read and count lines for this session
        Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
        Dim reader As TextReader
        reader.Initialize(inputStream)
        Dim content As String = reader.ReadAll
        reader.Close
        
        Dim lines() As String = Regex.Split(Chr(10), content)
        Dim countFound As Int = 0
        
        For Each line As String In lines
            If line.Trim.Length > 0 Then
                Try
                    Dim parser As JSONParser
                    parser.Initialize(line)
                    Dim msg As Map = parser.NextObject
                    
                    If msg.Get("sessionnumber") = sessionNum Then
                        countFound = countFound + 1
                    End If
                Catch
                    ' Skip malformed
                    Log(LastException.Message)
                End Try
            End If
        Next
        
        Log("📊 Message count for session " & sessionNum & ": " & countFound)
        Return countFound
        
    Catch
        Log("❌ Error counting messages: " & LastException.Message)
        Return 0
    End Try
End Sub

' Get highest turn number for session
Public Sub GetHighestTurnNumber(sessionNum As String) As Int
    If Not(storage.IsInitialized) Then Return 0
    
    Try
        Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
        If Not(ChatFile.IsInitialized) Then Return 0
        
        Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
        Dim reader As TextReader
        reader.Initialize(inputStream)
        Dim content As String = reader.ReadAll
        reader.Close
        
        Dim lines() As String = Regex.Split(Chr(10), content)
        Dim maxTurn As Int = -1
        
        For Each line As String In lines
            If line.Trim.Length > 0 Then
                Try
                    Dim parser As JSONParser
                    parser.Initialize(line)
                    Dim msg As Map = parser.NextObject
                    
                    If msg.Get("sessionnumber") = sessionNum Then
                        Dim turnNum As Int = msg.Get("turnnumber")
                        If turnNum > maxTurn Then maxTurn = turnNum
                    End If
                Catch
                    ' Skip malformed
                    Log(LastException.Message)
                End Try
            End If
        Next
        
        Log("📊 Highest turn for session " & sessionNum & ": " & maxTurn)
        Return maxTurn
        
    Catch
        Log("❌ Error getting highest turn: " & LastException.Message)
        Return 0
    End Try
End Sub

' Get messages in turn range
Public Sub GetMessagesInTurnRange(sessionNum As String, startTurn As Int, endTurn As Int) As List
    Dim messages As List
    messages.Initialize
    
    If Not(storage.IsInitialized) Then Return messages
    
    Try
        Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
        If Not(ChatFile.IsInitialized) Then Return messages
        
        Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
        Dim reader As TextReader
        reader.Initialize(inputStream)
        Dim content As String = reader.ReadAll
        reader.Close
        
        Dim lines() As String = Regex.Split(Chr(10), content)
        
        For Each line As String In lines
            If line.Trim.Length > 0 Then
                Try
                    Dim parser As JSONParser
                    parser.Initialize(line)
                    Dim msg As Map = parser.NextObject
                    
                    If msg.Get("sessionnumber") = sessionNum Then
                        Dim turnNum As Int = msg.Get("turnnumber")
                        If turnNum >= startTurn And turnNum <= endTurn Then
                            messages.Add(msg)
                        End If
                    End If
                Catch
                    ' Skip malformed
                    Log(LastException.Message)
                End Try
            End If
        Next
        
        Log("✅ Retrieved " & messages.Size & " messages in range " & startTurn & " to " & endTurn)
        Return messages
        
    Catch
        Log("❌ Error retrieving turn range: " & LastException.Message)
        Return messages
    End Try
End Sub

' Get all messages for session
Public Sub GetAllMessagesForSession(sessionNum As String) As List
    Dim messages As List
    messages.Initialize
    
    If Not(storage.IsInitialized) Then Return messages
    
    Try
        Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
        If Not(ChatFile.IsInitialized) Then Return messages
        
        Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
        Dim reader As TextReader
        reader.Initialize(inputStream)
        Dim content As String = reader.ReadAll
        reader.Close
        
        Dim lines() As String = Regex.Split(Chr(10), content)
        
        For Each line As String In lines
            If line.Trim.Length > 0 Then
                Try
                    Dim parser As JSONParser
                    parser.Initialize(line)
                    Dim msg As Map = parser.NextObject
                    
                    If msg.Get("sessionnumber") = sessionNum Then
                        messages.Add(msg)
                    End If
                Catch
                    ' Skip malformed
                    Log(LastException.Message)
                End Try
            End If
        Next
        
        Log("✅ Retrieved " & messages.Size & " total messages for session " & sessionNum)
        Return messages
        
    Catch
        Log("❌ Error retrieving all messages: " & LastException.Message)
        Return messages
    End Try
End Sub

' Get messages matching query
Public Sub GetMessagesMatchingQuery(sessionNum As String, searchTerm As String) As List
    Dim messages As List
    messages.Initialize
    
    If Not(storage.IsInitialized) Then Return messages
    
    Try
        Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
        If Not(ChatFile.IsInitialized) Then Return messages
        
        Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
        Dim reader As TextReader
        reader.Initialize(inputStream)
        Dim content As String = reader.ReadAll
        reader.Close
        
        Dim lines() As String = Regex.Split(Chr(10), content)
        Dim searchLower As String = searchTerm.ToLowerCase
        
        For Each line As String In lines
            If line.Trim.Length > 0 Then
                Try
                    Dim parser As JSONParser
                    parser.Initialize(line)
                    Dim msg As Map = parser.NextObject
                    
                    If msg.Get("sessionnumber") = sessionNum Then
                        Dim msgContent As String = msg.Get("content")
                        If msgContent <> Null And msgContent.ToLowerCase.Contains(searchLower) Then
                            messages.Add(msg)
                        End If
                    End If
                Catch
                    ' Skip malformed
                    Log(LastException.Message)
                End Try
            End If
        Next
        
        Log("✅ Found " & messages.Size & " messages matching " & QUOTE & searchTerm & QUOTE)
        Return messages
        
    Catch
        Log("❌ Error searching messages: " & LastException.Message)
        Return messages
    End Try
End Sub

' Get tool calls in range
Public Sub GetToolCallsInRange(sessionNum As String, startTurn As Int, endTurn As Int) As List
    Dim toolCalls As List
    toolCalls.Initialize
    
    If Not(storage.IsInitialized) Then Return toolCalls
    
    Try
        Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
        If Not(ChatFile.IsInitialized) Then Return toolCalls
        
        Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
        Dim reader As TextReader
        reader.Initialize(inputStream)
        Dim content As String = reader.ReadAll
        reader.Close
        
        Dim lines() As String = Regex.Split(Chr(10), content)
        
        For Each line As String In lines
            If line.Trim.Length > 0 Then
                Try
                    Dim parser As JSONParser
                    parser.Initialize(line)
                    Dim msg As Map = parser.NextObject
                    
                    If msg.Get("sessionnumber") = sessionNum Then
                        Dim turnNum As Int = msg.Get("turnnumber")
                        Dim isToolCall As Int = msg.Get("istoolcall")
                        
                        If turnNum >= startTurn And turnNum <= endTurn And isToolCall = 1 Then
                            toolCalls.Add(msg)
                        End If
                    End If
                Catch
                    ' Skip malformed
                    Log(LastException.Message)
                End Try
            End If
        Next
        
        Log("✅ Retrieved " & toolCalls.Size & " tool calls")
        Return toolCalls
        
    Catch
        Log("❌ Error retrieving tool calls: " & LastException.Message)
        Return toolCalls
    End Try
End Sub

aeric · 2026-01-04T05:54:13+0000

I believe the code is provided by AI. The code can be much simplified with Json library.

Tim Chapman · 2026-01-04T07:56:59+0000

aeric said:
I believe the code is provided by AI. The code can be much simplified with Json library.

Hi aeric,
You are correct. If you have the time (and I certainly understand if you don't), I would appreciate the simplified code.

aeric · 2026-01-04T13:51:00+0000

Tim Chapman said:
' Escape all special characters in content
Dim escapedContent As String = EscapeJsonString(content)
Dim escapedRole As String = EscapeJsonString(role)

' Build JSON line manually with proper escaping
Dim sb As StringBuilder
sb.Initialize
sb.Append("{")
sb.Append(QUOTE & "sessionnumber" & QUOTE & ":").Append(sessionNum).Append(",")
sb.Append(QUOTE & "turnnumber" & QUOTE & ":").Append(turnNum).Append(",")
sb.Append(QUOTE & "messageindex" & QUOTE & ":").Append(messageIdx).Append(",")
sb.Append(QUOTE & "role" & QUOTE & ":" & QUOTE).Append(escapedRole).Append(QUOTE & ",")
sb.Append(QUOTE & "content" & QUOTE & ":" & QUOTE).Append(escapedContent).Append(QUOTE & ",")
sb.Append(QUOTE & "istoolcall" & QUOTE & ":").Append(isToolCall).Append(",")
sb.Append(QUOTE & "istoolresponse" & QUOTE & ":").Append(isToolResponse).Append(",")
sb.Append(QUOTE & "timestamp" & QUOTE & ":" & QUOTE).Append(DateTime.Now).Append(QUOTE)
sb.Append("}")

Dim jsonLine As String = sb.ToString & Chr(10)

I mean I think this part will look more readable using Map and Json.

B4X:

Dim MyMap As Map
MyMap.Initialize
MyMap.Put("sessionnumber", sessionNum)
MyMap.Put("turnnumber", turnNum)
MyMap.Put("messageindex", messageIdx)
MyMap.Put("role", role)
MyMap.Put("content", content)
MyMap.Put("istoolcall", isToolCall)
MyMap.Put("istoolresponse", isToolResponse)
MyMap.Put("timestamp", DateTime.Now)

Dim jsonLine As String = MyMap.As(JSON).ToCompactString & Chr(10)

Android Question Sqlite database on microsd card

Tim Chapman

Active Member

DonManfred

Expert

android.jar / targetSdkVersion / minSdkVersion

Sagenut

Expert

Tim Chapman

Active Member

Tim Chapman

Active Member

aeric

Expert

Tim Chapman

Active Member

aeric

Expert

Similar Threads

Android Question Sqlite database on microsd card

Active Member

Expert

Expert

Active Member

Active Member

Expert

Active Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency