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.

 

[PaulMeuris]

The answer to the question "example from b4x using sql to insert a record in duckdb" did not include any insert SQL statement.
The AI has no knowledge of the content of the forum thread. It just copied some sentences.
This is not "intelligence"!

 

[William Lancee]

It worked - now it puts the b4x code in code tags.
Google Search Term: AI Overview example of code to insert a row in duckDB in B4X

@PaulMeuris My intention for this post was to express my surprise at how fast the AI could capture new info put in the Forum.
I couldn't agree with you more that AI is not human intelligence - that would be impossible by definition.
That it can incorporate new complex information quickly in its LLM is still astonishing to me.
But when electronic calculators came out, I was astonished too - and I took one apart to see how it worked!
Now of course those things are largely superseded by smart phones and smart phones themselves are "nothing special".

Each of these developments represents another opportunity for intelligent humans to add another layer of abstraction to
search "Where no man has gone before" and create things that were not possible before.

 

[PaulMeuris]

Indeed, the race for information by the search bots is on.
And a new skill is needed: prompt writing... The AI's respond differently when you write a question in a certain way.

Sometimes i use the DuckDuckGo search engine and its Duck.ai component.
Here's what happened when i used the search term you used in Google: AI Overview example of code to insert a row in duckDB in B4X

GPT-4o mini
To insert a row into a DuckDB database using B4X, you need to execute an SQL INSERT statement through the B4XSQL library. Below is an example that demonstrates how to set up the connection, create a table, and insert a row into it.

B4X Example Code

' Initialize the DuckDB database
Dim DuckDB As B4XSQLite
DuckDB.Initialize("MyDatabase.duckdb")

' Create a table if it doesn't exist
Dim CreateTableSQL As String
CreateTableSQL = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Name TEXT, Age INTEGER)"
DuckDB.ExecNonQuery(CreateTableSQL)

' Prepare the SQL insert statement
Dim InsertSQL As String
InsertSQL = "INSERT INTO Users (Name, Age) VALUES (?, ?)"

' Create a new B4XSqlQuery object
Dim SQL As B4XSQL
SQL.Initialize(DuckDB)

' Insert a row with parameters
Dim Name As String = "John Doe"
Dim Age As Int = 30

' Execute the insert
SQL.ExecNonQuery(InsertSQL, Array As Object(Name, Age))

' Commit and close the database connection
DuckDB.Commit
DuckDB.Close

Explanation
Initialize the Database: The code begins by initializing the DuckDB database.
Create Table: It creates a table named Users if it doesn't already exist. The table has three columns: Id, Name, and Age.
Prepare Insert Statement: An SQL INSERT statement is prepared to add records to the Users table.
Insert Data: The parameters (Name and Age) are defined, then the ExecNonQuery method is used to execute the insert operation with the parameters.
Commit Changes: Finally, changes are committed, and the database connection is closed.
Additional Notes
Ensure that DuckDB is correctly set up and the B4XSQLite library is included in your project.
Adjust the database path and table schema as needed for your specific application.
If you have further questions or need more examples, feel free to ask!

It seems that there is a B4XSQLite library. And Google search answered:

B4XSQLite refers to the use of SQLite databases within the B4X cross-platform development environment. It allows developers to use the standard SQL language to manage data storage in tables within their applications, which can be built for Android, iOS, or desktop. The B4X platform includes documentation, libraries, and example projects specifically for B4XSQLite, which simplify common database tasks like creating tables, inserting, and querying data.

The SQL libraries used in B4A are SQL and SQLCipher, in B4I it's iSQL and in B4J it's called jSQL.
A B4X forum member made a wish in 2017 by suggesting to rename all the B4X SQL libraries to B4XSQLite...
Did his wish became true?