Unable to create database using SQL Macro or Query

mikeh369 · June 24, 2023, 5:46am

Tried with Firebird and HSQLDB
CREATE TABLE “Table_1” (“ID” BIGINT generated by default as identity primary key,
“Field_01” VARCHAR(5),
“Field_02” DATE,
“Field_03” INTEGER,
“Field_04” DOUBLE PRECISION
);

Results in:
SQL Status: HY000
Error code: 1000

Syntax error in SQL statement

A test database I downloaded with the exact same query works fine, but if I add a new query, up pops the error again (the original query still works fine).

Originally on 7.5.3. Did an advanced uninstall with Revo and deleted all folders and registry and appdata folders (manually checked)…

Version: 7.5.4.2 (X86_64) / LibreOffice Community
Build ID: 36ccfdc35048b057fd9854c757a8b67ec53977b6
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

Wanderer · June 24, 2023, 5:59am

? You try the code above as a query ?
.
It is no query. You may put it in the dialog, you can reach from the menu - I guess Tools->SQL in english.

Why? You may create a lot of embedded databases and everyone may have a table_1, but youvcan only run the code once per database unless you DROP the table before you run the second try.

mikeh369 · June 24, 2023, 6:09am

Hmm. Works fine in Tools > SQL. That doesn’t help though…
This is just an example and I delete the table before running it.

Just tried creating new windows user, same results

For clarification, I’m trying to get it working in a macro, just tried using a query because macro wouldn’t work.

elmau · June 24, 2023, 5:20pm

Show we your code.

elmau · June 24, 2023, 5:23pm

This example work fine.

import uno
from pathlib import Path
from typing import Any


CTX = uno.getComponentContext()
SM = CTX.getServiceManager()


def create_instance(name: str, with_context: bool=False, arguments: Any=None) -> Any:
    if with_context:
        instance = SM.createInstanceWithContext(name, CTX)
    elif arguments:
        instance = SM.createInstanceWithArguments(name, (arguments,))
    else:
        instance = SM.createInstance(name)
    return instance


def to_url(path):
    return Path(path).as_uri()


def main():
    SQL = """CREATE TABLE "Table_1" ("ID" BIGINT generated by default as identity primary key,
"Field_01" VARCHAR(5),
"Field_02" DATE,
"Field_03" INTEGER,
"Field_04" DOUBLE PRECISION
);
"""

    PATH = '/home/elmau/Projects/test/test.odb'
    dbc = create_instance('com.sun.star.sdb.DatabaseContext')
    db = dbc.createInstance()
    db.URL = 'sdbc:embedded:firebird'
    db.DatabaseDocument.storeAsURL(to_url(PATH), ())

    con = db.getConnection('', '')
    cursor = con.createStatement()

    result = cursor.execute(SQL)
    db.DatabaseDocument.store()

    con.close()

    return

mikeh369 · June 24, 2023, 9:22pm

This was the ticket:
CREATE TABLE ““Table_1"” (”“ID”" BIGINT generated by default as identity primary key)

It still generates a SQL exception, but even just the following does:
CREATE TABLE ““Table_1"” (”“ID”" INTEGER)

This is the exception
(BASIC runtime error.
An exception occurred
Type: com.sun.star.sdbc.SQLException
Message: .)

This is my code:

DatabaseContext = createUnoService(com.sun.star.sdb.DatabaseContext)
oReceiptDB = DatabaseContext.getByName(sDir & sFileNameNoExt & .odb)
If Not oReceiptDB.IsPasswordRequired Then
Connection = oReceiptDB.GetConnection("","")
Else
InteractionHandler = createUnoService(com.sun.star.sdb.InteractionHandler)
Connection = oReceiptDB.ConnectWithCompletion(InteractionHandler)
End If
Statement = Connection.createStatement()
sSQL =“CREATE TABLE ““Table_1"” (”“ID”” BIGINT generated by default as identity primary key) "
Statement.executeUpdate(sSQL)

mikeh369 · June 24, 2023, 9:51pm

Got rid of the exception by using .execute instead of .executeUpdate. It was actually 2 problems

This works
sSQL = “CREATE TABLE ““Table_1”” (”“ID”" BIGINT generated by default as identity primary key)"
Statement.execute(sSQL)

Thank you so much for your help

Wanderer · June 25, 2023, 6:47am

One hint for future occaisions: If you surround code by backticks on this site it increased readability and prevents the conversion to curly quotes. Here are "straight" quotes. For blocks of code use three backticks at beginning and end.

Code block here