B4J Question build SQL-terminal into app - how better ?

[peacemaker]

HI, All

For big projects and various debugging things on a remote host (with several databases, including some based on rather big SQLite files that must be downloaded to check) - i think, it's convenient to make a web-page with SQL-terminal, for commands.

How to do it more correctly for ... say data editing safety ?
Any ready code example of a simple SQL console form and parser ?

 

[EnriqueGonzalez]

Sqlite studio has a portable version. You could just distribute it where needed

 

[peacemaker]

on server non-UI host ?
let's better try to find solution for B4J SQL terminal. Web-interface will be ABMaterial.

 

[EnriqueGonzalez]

Ah then just use Sqlite itself

Command Line Shell For SQLite

 

[peacemaker]

Sqlite itself

Hmmmm, interesting... It needs to distribute the sqlite binary file and start it by jShell with a comand from web-form...., receiving the stdout and showing it in the web-interface...
Correct ?

 

[EnriqueGonzalez]

It needs to distribute the sqlite binary file and start it by jShell with a comand from web-form...., receiving the stdout and showing it in the web-interface...
Correct ?

If you really want the web part. Just try aeric API

[Project Template] Web API Server 2

Version: 2.08 Description: Create Web API Server using B4X project template Database: MySQL, SQLite Sample project: Support Ticketing System If you don't want to connect to any SQL database, see MinimaList API Server For older version, see Web API Server (v1.16) Template: Web API Server...

www.b4x.com

 

[peacemaker]

I'm thinking about the solution way, in whole, not by the details now... But thanks...

 

[peacemaker]

Strange, but no stdout text from the sqlite3 process to show. But if to enter wrong commands - stderr is returned, but corrupted...
Why so ?

Sub butOpenSQL_Clicked(Target As String)
    If shl.IsInitialized Then
        shl.KillProcess
    End If
    ShellParameters = File.Combine(others.Folder, db.DBname)
    If File.Exists("", ShellParameters) = False Then
        page.ShowToast("", "", "No database available: " & ShellParameters, 5000, False)
        Return
    End If
 
    ProcessStatus = 1
    If others.DetectOS = "linux" Then
        Dim WorkingDirectory As String = File.Combine(File.DirApp, "sqlite-tools-linux-x86-3430100")
        Dim binary As String = "sqlite3"
    Else If others.DetectOS = "windows" Then
        Dim WorkingDirectory As String = File.Combine(File.DirApp, "sqlite-tools-win32-x86-3430100")
        Dim binary As String = "sqlite3.exe"
    End If
    binary = File.Combine(WorkingDirectory, binary)
 
    shl.InitializeDoNotHandleQuotes("shl", binary, Array As String(ShellParameters))
    shl.InputStreamEnabled = True
    shl.WorkingDirectory = WorkingDirectory
    ProcessStatus = 3    'working
    shl.RunWithOutputEvents(-1)
    Addto_Log("DB opened OK")
End Sub

Sub editSQLcommand_EnterPressed(value As String)
    Log(value)
    If value.Trim = "" Then Return
    Dim m As ABMModalSheet = page.ModalSheet("SQLconsole_ModalSheet")
    editSQLcommand = m.Footer.Component("editSQLcommand")

 
    'send to Shell stream
    If shl.IsInitialized Then
        Addto_Log(value)
        editSQLcommand.Text = ""
        m.Footer.Refresh
  
        Dim v As String = value & CRLF
        shl.WriteToInputStream(v.GetBytes("UTF8"))
    Else
        page.ShowToast("", "", "Open database first", 5000, False)
    End If
End Sub

Sub shl_ProcessCompleted (Success As Boolean, ExitCode As Int, StdOut As String, StdErr As String)
    isError = Not(Success)
    Log("shl_ProcessCompleted: " & Success & " ; exitcode = " & ExitCode)
    ProcessStatus = 1
    If isError Then
        Log(StdErr)
        Addto_Log(StdErr)
    End If
End Sub

Sub shl_StdOut (Buffer() As Byte, Length As Int)
    Dim s As String = BytesToString(Buffer, 0, Length, "UTF-8")
    CallSubDelayed2(Me, "Addto_Log", s)
End Sub

Sub shl_StdErr (Buffer() As Byte, Length As Int)
    Dim s As String = BytesToString(Buffer, 0, Length, "UTF-8")
    CallSubDelayed2(Me, "Addto_Log", s)
End Sub


Sub Addto_Log(txt As String)
    If txt = "" Then Return
    Dim m As ABMModalSheet = page.ModalSheet("SQLconsole_ModalSheet")
    logSQL = m.Content.Component("logSQL")
    logSQL.Text = logSQL.Text & txt & CRLF
    logSQL.Refresh
End Sub

 

[EnriqueGonzalez]

Look at the third point

Command Line Shell For SQLite

You need a dot command .output to send the results to stdout

 

[peacemaker]

Thanks ! Indeed

 

[EnriqueGonzalez]

That's awesome! You made it work!

 

[peacemaker]

Thanks, with your help.
The final code here is updated.

 

[peacemaker]

But if to continue the topic: how to release an universal B4J SQL-client, for any SQL DB type, including MySQL or other connected via jdbc driver ?

I guess, it needs some command parser...

 

[peacemaker]

So, it's also possible, and even simpler than SQLite native engine binary, with some "SQL_Executor" sub

Sub editSQLcommand_EnterPressed(value As String)
    Log(value)
    If value.Trim = "" Then Return
    Dim m As ABMModalSheet = page.ModalSheet("SQLconsole_ModalSheet")
    editSQLcommand = m.Footer.Component("editSQLcommand")
  
    Addto_Log(value)
    editSQLcommand.Text = ""
    m.Footer.Refresh

    Dim res As String = SQL_Executor(db.SQL, value)
    Addto_Log(res)
    If res = "OK" Then
        If SQL_Executor_List.IsInitialized Then
            If SQL_Executor_List.Size > 0 Then
                Addto_Log(SQL_Executor_List.Size & " record(s)")
                For i = 0 To SQL_Executor_List.Size - 1
                    Dim s() As String = SQL_Executor_List.Get(i)
                    Dim tbl As StringBuilder
                    tbl.Initialize
                    For j = 0 To s.Length - 1
                        tbl.Append("|").Append(s(j)).Append("|")
                    Next
                    Addto_Log(tbl.ToString)
                    tbl = Null
                Next
            Else
                Addto_Log("0 records")
            End If
        Else
            Addto_Log("0 records")
        End If
    End If
End Sub

Sub SQL_Executor(sqldb As SQL, command As String) As String
    If sqldb.IsInitialized = False Then
        Return "DB is closed, open it first"
    End If
    Dim res As String = "Unknown command"
    Dim isRequest As Boolean = IIf(command.Trim.ToLowerCase.StartsWith("select"), True, False)
    Dim SQL_Executor_List As List

    Try
        If isRequest Then
            SQL_Executor_List = DBUtils.ExecuteMemoryTable(sqldb, command, Null, 0)
        Else
            sqldb.ExecNonQuery(command)
        End If
        res = "OK"
    Catch
        Log("SQL_Executor.error: " & LastException.Message)
        res = LastException.Message
    End Try
  
    Return res
End Sub

 

[MicroDrie]

Sqlite studio has a portable version. You could just distribute it where needed

With portableApps you don't have to install anything on a PC or LapTop and you can even use the programs from a USB stick. For SQLite you can use DB Browser for SQLite Portable database, and / or Database Browser Portable for multiple database programs without the need for an installation on PC/LapTop.

You can use it to build a completely new database, adjust it, and test queries and use the SQL statements used for this purpose in a B4X program. PortableApps indicates when starting if there is a new version of the program itself and / or of the (database) programs you use. That's why it's no problem if you don't use your USB stick for a very long time. You automatically use the latest program version.

 

[peacemaker]

you don't have to install anything

Your post is offtopic - question is about the software built-in solution, according to the subject.

 

[MicroDrie]

Your post is offtopic - question is about the software built-in solution, according to the subject.

I responded to your question to do it in a safe way. If you let me create SQL queries against a live SQL database, it certainly wouldn't be safe given my limited SQL knowledge for complex SQL queries.?

How to do it more correctly for ... say data editing safety ?

With these free programs (oh well, I remain Dutch of course) you can write and test queries and if the result meets your expectations, you can include the correctly tested queries in your own program with a simple error-free copy paste command without having to change the made adjustments to the original database (very safe testing for an SQL newby like me).
You don't need to save the queries you've run on your database or make copies of very large database files, as long as you can connect to them.