You can influence AI responses!

[William Lancee]

Prior to posting
https://www.b4x.com/android/forum/threads/b4j-duckdb-works-with-the-b4x-sql-library-as-is.169385/
I searched the Forum and asked Google Search about B4X and DuckDB. Both came up virtually empty.
The only reference to DuckDB was on the Chit Chat Forum which is not indexed.

Half a day after I posted I asked Google again and it answered with my post, almost verbatim, including a reference link.
From now on according to AI (Google search generative AI), B4X can handle DuckDB SQL queries without difficulties.

 

[PaulMeuris]

The Google AI answered the following question

can you give an example sql statement using B4X and DuckDB?

with this:

In B4X (e.g., B4J) with the DuckDB JDBC driver, you use the standard B4X SQL library for database interactions.
Here is an example demonstrating connection, table creation, data insertion (using a batch operation), and selection:
Prerequisites
In your B4J project, you need to add the DuckDB JDBC driver to your project and add the following attribute to your Globals or StarterService module:
b4x
#AdditionalJar: duckdb_jdbc-x.x.x.jar ' Replace x.x.x with the actual version number
You also need a SQL object declared in your globals:
b4x
Private SQL As SQL
Example Code
An example demonstrating connecting to a DuckDB database, creating a table, inserting data using a batch operation, and selecting data can be found on b4x.com.

The AI could not give me a SQL statement and referred to the B4X.com site... It doesn't know how to write a simple SQL statement?!

 

[William Lancee]

Search term: "example from b4x using sql to insert a record in duckdb"
AI response:

To insert a record into a DuckDB database using B4X, you use the standard B4X SQL library with parameterized queries, as DuckDB is compatible with the library's syntax.

Prerequisites

1. Add the JDBC driver JAR: Download the DuckDB JDBC driver JAR file from the DuckDB installation page and place it in your project's AdditionalLibraries folder.
2. Add Project Attribute: Add the following line to your B4X project attributes (usually at the top of a module):
   b4x
   #AdditionalJar: duckdb_jdbc-<version>.jar ' Replace <version> with the actual version number (e.g., duckdb_jdbc-1.4.2.0.jar)
   ```
   

B4X Code Example
Here is an example of how to connect to a DuckDB database and insert a record using AddNonQueryToBatch for better performance:

b4x [I, WiL, put the code in code tags]

Private SQL As SQL
Private dbPath As String = File.DirApp & "/my_analytics.duckdb" ' Path for persistent database file

Sub ConnectAndInsertExample
    Try
        ' Initialize connection using the DuckDB JDBC driver and connection string
        SQL.Initialize("org.duckdb.DuckDBDriver", "jdbc:duckdb:" & dbPath)
        Log("Database connected successfully")

        ' Create table if it doesn't exist
        SQL.ExecNonQuery("CREATE TABLE IF NOT EXISTS data (id INTEGER, value VARCHAR)")

        ' Insert a single record using parameterized query
        SQL.ExecNonQuery2("INSERT INTO data VALUES (?, ?)", Array As Object(1, "test value"))

        ' For multiple inserts, use a batch operation
        SQL.AddNonQueryToBatch("INSERT INTO data VALUES (?, ?)", Array As Object(2, "hello batch"))
        SQL.ExecNonQueryBatch("SQL")

    Catch
        Log(LastException.Message)
    End Try
End Sub

' Event handler for batch operations
Sub SQL_NonQueryComplete (Success As Boolean)
    If Success Then
        Log("SQL non query batch operation completed successfully.")
    Else
        Log("SQL non query batch operation failed.")
    End If
End Sub

Key Points

• Connection String: Use jdbc:duckdb: followed by the file path for a persistent database, or just jdbc:duckdb: for an in-memory database.
• Parameterized Queries: Use ? placeholders for values to prevent SQL injection and improve performance.
• Batch Inserts: Use AddNonQueryToBatch and ExecNonQueryBatch for efficient bulk inserts.

 

[William Lancee]

I'll check tomorrow if next time it puts the B4X code in code tags.