KiCad database library error?

RianDeRous · December 23, 2024, 1:42pm

Dear all,

I’m trying to get the database library function operational but I get this error when I want to add a component to the schematic.

Errors loading symbols:
Error loading symbol library cspect_lib.
Could not load database library: could not connect to database (C:\jenkins\workspace\build-windows-kicad-msvc-8.0-stable\.build\kicad\thirdparty\nanodbc\nanodbc\nanodbc.cpp:1044: IM00: [Microsoft][ODBC Driver Manager] The data source name was not found and no default driver was specified )

My JSON file:

{
    "meta": {
        "version": 0
    },
    "name": "CSPECT component database",
    "description": "A database of electronic components for KiCAD",
    "source": {
        "type": "odbc",
        "dsn": "",
        "username": "",
        "password": "",
        "timeout_seconds": 2,
        "connection_string": "DRIVER=SQLite3 ODBC Driver;Database=Z:\\CSPECT\\0.General\\0.6 R&D\\KICAD\\parts library.db;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
    },
    "libraries": [
        {
            "name": "Connectors",
            "table": "Connectors",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        },
        {
            "name": "Microcontrollers",
            "table": "Microcontrollers",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        },
        {
            "name": "Transistors/Mosfet",
            "table": "Transistors/Mosfet",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        },
        {
            "name": "DIODE",
            "table": "DIODE",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        },
        {
            "name": "Resistors",
            "table": "Resistors",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        }
    ]
}

craftyjon · December 23, 2024, 1:45pm

If you open the Windows ODBC tool (search it in the start menu) and go to the Drivers tab, what is listed?

RianDeRous · December 23, 2024, 1:56pm

I guess you mean this window @craftyjon ?

craftyjon · December 24, 2024, 5:04pm

Yes that’s the one.

Do you have the SQLite 3 driver installed? I don’t see it on that list, but it also looks like the list can be scrolled.

You can get it from SQLite ODBC Driver - direct download sqliteodbc_w64.exe

You may also have to refer to the driver as {SQLite3 ODBC Driver} (with the curly braces) in the connection string, looking at the driver documentation. I don’t currently have access to a Windows machine to test this, though.

RianDeRous · January 2, 2025, 10:57am

I guess I’m one step closer to get it working now.

I verified the JSON format with jsonlint.com
I checked naming of tables in the database and the kicad_dbl file,

but it’s not clear which key or what could be wrong.

{
    "meta": {
        "version": 0
    },
    "name": "CSPECT component database",
    "description": "A database of electronic components for KiCAD",
    "source": {
        "type": "odbc",
        "dsn": "",
        "username": "",
        "password": "",
        "timeout_seconds": 2,
        "connection_string": "DRIVER={SQLite3 ODBC Driver};Database=Z:\\CSPECT\\0.General\\0.6 R&D\\KICAD\\parts library.db;"
    },
    "libraries": [
        {
            "name": "Connectors",
            "table": "Connectors",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        },
           {
            "name": "Microcontrollers",
            "table": "Microcontrollers",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        },
        {
            "name": "Transistors/Mosfet",
            "table": "Transistors/Mosfet",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        },
        {
            "name": "DIODE",
            "table": "DIODE",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        },
        {
            "name": "Resistors",
            "table": "Resistors",
            "key": "id",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [],
            "properties": {
                "description": "DESCRIPTION"
            }
        }
     
    ]
}

teletypeguy · January 2, 2025, 3:08pm

When moving from linux to win I recall the oddity of the required ODBC extra step and how the connection line changes to match the odbc name. This is on win 10 – connection line points to odbc nickname which you define in the odbc tool, and the “configure” button is where you point it to the actual database file:

My little database adventure documented here (linux at the beginning and win at the end):
https://forum.kicad.info/t/a-database-case-study/39631

And a custom BOM script for my db:
https://forum.kicad.info/t/create-bom-referencing-master-parts-list/56043/15

craftyjon · January 2, 2025, 3:43pm

“invalid map key” is an odd error message. It is not generated by KiCad or any obvious library I can find so far.

It is worth trying setting up a DSN rather than using a connection string, to see if the behavior changes.

RianDeRous · January 2, 2025, 3:48pm

Both @teletypeguy’s method by putting the DSN in the connection string and the way to put the DSN in the DSN field gives the invalid map key error as above.

teletypeguy · January 2, 2025, 3:50pm

show your odbc admin and config screens

craftyjon · January 2, 2025, 3:50pm

Can you share your database itself (the sqlite file)?

RianDeRous · January 2, 2025, 3:56pm

cspect_lib.kicad_dbl (1.9 KB)
parts library.db (92 KB)

thanks in advance for your help!

craftyjon · January 2, 2025, 5:54pm

Right now KiCad is case-sensitive for the database column names – it shouldn’t be, so I’ll fix that in a future version. But the first problem is that your database and your kicad_dbl file do not agree on the case of your column names. For example, your key name is “id” in the config file but “ID” in the Connectors table.

The second issue is that your database tables are missing the Symbol/Footprint columns you put in the config file. So, even after fixing the case issue, there are no symbol/footprint mappings for KiCad to use.

teletypeguy · January 2, 2025, 6:15pm

First, try changing your kicad_dbl file connection line to point to your odbc nickname as a DSN:

“connection_string”: “DSN=CSPECT_Kicad_Library;”

.
.
Second, verify that the path to your “Database-Name” file is correct:

.
I can’t see the whole path there, but earlier you had:
Z:\CSPECT\0.General\0.6 R&D\KICAD\parts library.db

I see two possible issues: my database file is named .sqlite (not .db), and I also avoid spaces in file and folder names and use underscores instead (it may or may not be an issue here but has caused me problems in the past).

I would recommend renaming it to something like (and update your odbc Database-Name):
Z:\CSPECT\0.General\0.6_R&D\KICAD\parts_library.sqlite

How are you editing your database? I use SQLite-Studio and can just double-click the .sqlite file to open it. If your database is not correct you have other problems with which to deal.

craftyjon · January 2, 2025, 6:21pm

Neither of these are issues (sqlite doesn’t care about the file extension or the spaces in the filename)

Using a DSN vs. not using a DSN also doesn’t matter, as mentioned above.

teletypeguy · January 2, 2025, 7:08pm

Thanks Jon – just trying to define what has worked for me as I got things working on win. Sounds like the database itself needs attention.

RianDeRous · January 3, 2025, 8:30am

Hi @teletypeguy and @craftyjon

Thank you very much for your help! It was indeed the case sensitive “id” that caused troubles.

Now I have my initial db working, so I can start adding more components and link them with footprints etc…