Missing symbols in database library?

[BarryHardhouse]

Hi All.
I’ve been trying to set up database libraries.
Thus far, I can connect to the database and see parts listed in it - but the parts have only a footprint, not a symbol. The symbol area in the parts browser remains saying “no part selected”.

I’ve used the .kicad_dbl libraries found in the documentation, and tried various modifications. As data sources I have tried Access, Excel and flat CSV text files. All have showed the same behavior. We have engaged professional support from kipro, who suspected a bug in the Access ODBC driver, although note I’ve seen the same behavior with other data sources.

Has anyone else seen this? Can anyone suggest what I might be doing wrong? I’m more and more convinced I am missing something obvious. Might anyone have a database or working kicad_dbl file they can share?

Thanks!

[gkeeth]

If you post your .kicad_dbl file, someone might be able to provide specific advice. Also post your version info from Help → About KiCad → Copy Version Info.

There is a simple sqlite database/.kicad_dbl file here you could try as a known-good example (it’s the QA database for KiCad regression testing): qa/data/dblib · master · KiCad / KiCad Source Code / kicad · GitLab

I have a working setup with sqlite, but I admit I used the example above as a starting point Many others have discussed working examples in this long thread and elsewhere on the forum: KiCad: The case for Database driven design (scroll towards the end).

I notice that of the three data sources you list, in my mind only one of them (Access) is really a database, though I imagine there may be ODBC drivers that make the others act like databases. Are you using the same driver with all three, or a different driver for each? Can you try a different backend/driver combination such as SQLite for comparison at least?

[Claudio.Lorini]

@BarryHardhouse

but the parts have only a footprint, not a symbol.

i think that the message “no part selected” means that you have no schematic symbol so nothing can be displayed in the window

Having only PCB footprint and not schematic symbols is a peculiar ‘corner case’; KiCad ‘normal’ workflow is relying in the fact that you start with a schematic to derive a PCB.

You can try to link a fake schematic symbol (even a empty one) to see if something different happens…

i’ve attached my .kicad_dbl, hope you can find some use of it. (the DB is just a bit to big to be attached )

EDLDB-local_kicad.kicad_dbl (48.8 KB)

P.S. maybe i misunderstood you post, please clarify:

• have you setup the db contents to link a schematic symbol and you cannot see the symbols in the ‘symbol chooser’?
  or
• you have linked only the pcb symbols to the DB?

in the first case maybe the contents of the ‘symbols’ field is not correct or the source library for the symbol is not correctly configured in the symbol library manager…

[BarryHardhouse]

Hi All,
Thanks for the replies. Apologies for not posting the relevant files - as I new user it seems I’m not allowed to upload files.

The most recent thing I have tried is text from a CSV file - here are the contents of the files:

{
“meta”: {
“version”: 0
},
“name”: “My Database Library”,
“description”: “A database of components”,
“source”: {
“type”: “odbc”,
“dsn”: “Text”,
“username”: “I’ve removed this for privacy”,
“password”: “”,
“timeout_seconds”: 2,
“connection_string”: “”
},
“libraries”: [
{
“name”: “Test”,
“table”: “pokpokpok.tab”,
“key”: “Part ID”,
“symbols”: “Symbols”,
“footprints”: “Footprints”,
“fields”: [
{
“column”: “Value”,
“name”: “Value2”,
“visible_on_add”: false,
“visible_in_chooser”: true,
“show_name”: true,
“inherit_properties”: true
}
]
}
]
}

Part ID Symbols Footprints Value
POK-UN-001 Device:Ammeter_AC Resistor_THT:R_Axial_DIN0207_L6.3mm_D2.5mm_P10.16mm_Horizontal 10
POK-UN-002 Device:Ammeter_DC Resistor_THT:R_Axial_DIN0207_L6.3mm_D2.5mm_P10.16mm_Horizontal 20
POK-UN-003 Device:Antenna Capacitor_THT:CP_Axial_L10.0mm_D4.5mm_P15.00mm_Horizontal 30
POK-UN-004 Device:Antenna_Chip Buzzer_Beeper:Buzzer_12x9.5RM7.6 40

The file contents won’t copy/paste properly, but each field is separated by a tab. I can read this ODBC data source into Excel with no problems.

Gkeeth:
Thanks for the link to the example - i will try that.
I’ve read the article you link to, but i still can’t see what’s wrong!
Sqlite will probably be my next thing to try - it doesn’t matter what data source we use, as long as it’s easy to edit.
Each of the data sources I mentioned require their own ODBC driver, and each has behaved the same. I don’t think we really need a real (relational) database, a flat file should do everything we need.

Claudio:
Yes, it’s behaving like there is no symbol in the database - but there is! As you can see, I have just picked the first few symbols from the “Device” library in my example. I have previously tried with my own custom library and had the same result.
Thanks also for your example - I will study it.
I believe I have done everything you say correctly: Named both a schematic symbol and a PCB footprint in the DB. The symbols seem to be ok because I can place them directly from their original library. However, I must be getting something wrong!

KiCad version info is:

Application: KiCad x64 on x64
Version: 7.0.5, release build
Libraries:
wxWidgets 3.2.2
FreeType 2.12.1
HarfBuzz 6.0.0
FontConfig 2.14.1
libcurl/7.88.1-DEV Schannel zlib/1.2.13
Platform: Windows 10 (build 19045), 64-bit edition, 64 bit, Little endian, wxMSW
Build Info:
Date: May 27 2023 02:48:13
wxWidgets: 3.2.2 (wchar_t,wx containers)
Boost: 1.81.0
OCC: 7.6.3
Curl: 7.88.1-DEV
ngspice: 40
Compiler: Visual C++ 1936 without C++ ABI
Build settings:
KICAD_SPICE=ON

[Claudio.Lorini]

Can you show us your symbol libraries configuration? it should be something like:

[jmk]

@BarryHardhouse

as I new user it seems I’m not allowed to upload files.

25 posts in 5 topics is all you have left to be able to upload files
https://forum.kicad.info/t/new-member-information/38391

[BarryHardhouse]

Thanks JMK. I’d better get typing!

[BarryHardhouse]

Since I can’t attach the screen shot, see whether this link works for you:

Edit… oh, I see! I can upload pictures, just not “other” files. Curious. Anyway, here you go:

[Claudio.Lorini]

you have the library nickname ‘Device’ on your library list, right?

can you snapshot what happens if you type ‘POK-UN-004’ in the search field of the Symbol chooser?

Have you tried to access the DB using a connection string and dsn via ODBC driver instead of direct login with login/passwd?

[BarryHardhouse]

Thanks Claudio. Yes, I have the “device” library installed and enabled, and I can place parts from it.

[BarryHardhouse]

This is what I see when I browse my database library. One interesting thing is that the description is pulled from the database, although the symbol does not show.
If I try to place these parts on the schematic it appears to work normally, although of course no symbol appears on the schematic. If I then inspect the BoM, nothing seems to have appeared (ie, it does not seem to have placed an “invisible” part).

[BarryHardhouse]

As for this:

@Claudio.Lorini

Have you tried to access the DB using a connection string and dsn via ODBC driver instead of direct login with login/passwd?

No, I’ve not tried this - but I did notice that some of the example files used a different format. To be honest I don’t really understand what all these fields mean and how they interact. However, I will give this a try when I have time.
Thanks for the support.

[BarryHardhouse]

Ok, update.
The syntax:

"name": "My Database Library",
"description": "A database of components",
"source": {
    "type": "odbc",
    "dsn": "",
    "username": "",
    "password": "",
    "timeout_seconds": 2,
    "connection_string": "DSN=Text"

Does not change the behavior.

[jmk]

@BarryHardhouse

Thanks JMK. I’d better get typing!

Not type all those posts, we’re not that cruel.
All you have to do is read that number of posts in at least 5 topics.

By my reckoning, you have one more topic and 7 more posts to open

[BarryHardhouse]

Ok, so I have downloaded the QA SQLite database gkeeth linked in the initial post, and it works!

The string:
“connection_string”: “Driver={SQLite3};Database=${CWD}/database.sqlite”`
did not work for me, even with the correct path inserted, but setting up the SQLite database as a data source in the ODBC Administrator tool like this:

Allowed either the lines
“dsn”: “SQLite3”,
or
“connection_string”: “DSN=SQLite3”
to work interchangeably.

I now need to unpick the chain and see what was going wrong…

[BarryHardhouse]

I’ve now exported the data from the example SQLite database, via ODBC, into Excel, and saved it as tab-separated text.
I can then happily re-import the date via ODBC from the Text DSN back into Excel, and get exactly the same data I started with.
However, if I point KiCad at the Text DSN, the error is still present - no symbols!

It seems, then, that the problem is not in the data, but in the interaction between the ODBC driver and KiCad. I’ve attached the text data file - I’d be interested to know if anyone else experiences the same problems I’m having when using this file.

QCdatabase.tab (631 Bytes)

In summary, it seems that SQLite3 is the only ODBC driver that works correctly for me. I’ve not been able to try a “real” SQL database yet, because although I have “permission” to set one up, I don’t actually have Permission (ie, the boss has said it’s ok, but no-one has altered the server access controls to allow me to do it).

[craftyjon]

Only SQL databases have been tested before on Windows, not Access/Excel/plain text. If Seth doesn’t fix it first, I’ll have to look at this later this month and see what is going on (assuming I can find a license for MS Access)

[BarryHardhouse]

Cheers, Jon.
Of course, SQL is always the right answer for databases. I don’t think even Microsoft likes Access.
Our workplace, however, is heavily invested in the MS Office ecosystem - it’s just what we’ve got, so it’s what I started off using. Getting an SQL server up and running was likely going to cause a headache, but it looks like SQLite might be a nice solution for us.
Appreciate you looking into it.