Was the first time for me as well 
I noticed that in your script output it shows table column header names, not the content of the first data row (first component). Feel free to share your SQLite database, I can take a look into it under Linux.
1 Like
That’s the problem. I’m looking at it now. I haven’t imported my .csv data into the database correctly.
That’s why KiCad isn’t reading it correctly.
I’d like to figure it out myself so I can get it right. I think we’re on the verge of success.
Thanks so much for your help.
1 Like
Two things: first the path should start with C:\Users for an absolute path, second perhaps the \n in the string after Users is being interpreted as a newline character and is needs to be escaped.
Thank you all for your comments. I still can’t get KiCad to connect to the database.
Thanks to cioma, I was able to test the connection to the database thru Microsoft PowerShell.
I connected to it by specifing the driver and database name:
(sorry I haven’t figured out the horizontal scroll bars)
(“Driver=SQLite3 ODBC Driver;Database=C:\Users\nasad\Documents\KiCad\DB_Files\Test\EPC_Db.sqlite;”)
Then by the DSN name: (“DSN=SQLite3DSN;”)
Since this works in PowerShell, my .kicad_dbl file must not be correct.
Using the driver & database name method:
“name”: “EPC Database”,
“description”: “EPC parts library”,
“source”: {
“type”: “odbc”,
“dsn”: “”,
“username”: “”,
“password”: “”,
“timeout_seconds”: 2,
“connection_string”: “Driver=SQLite3 ODBC Driver;Database=C:\Users\nasad\Documents\KiCad\DB_Files\Test\EPC_Db.sqlite;”
I tried escaping the \n in nasad.
Using DSN method:
“name”: “EPC Database”,
“description”: “EPC parts library”,
“source”: {
“type”: “odbc”,
“dsn”: “”,
“username”: “”,
“password”: “”,
“timeout_seconds”: 2,
“connection_string”: “DSN=SQLite3DSN;”
I tried DSN= in lower case
Putting a semicolon before DSN
made connection string="" and putting the DSN where dsn:“SQLite3DSN”
In all cases I get
Its possible that the rest of the .kicad_dbl file has a mistake in it, but I can’t see it.
Thank you all for your help.
nasa
EPC_Db.sqlite (156 KB)
EPC_DbDriver.kicad_dbl (4.9 KB)
EPC_DbDSN.kicad_dbl (4.8 KB)
maybe your DB tables are missing a primary key?
I’ve been struggling this on macOS too - I’m on an M1 MacBook. I have the QA SQLite database installed and have validated the schema as correct with DBBrowser. I installed the database & ODBC drivers with brew install sqlite and brew install sqliteodbc. Testing the sqliteodbc installation with brew test sqliteodbc reveals the location of the .so to be /opt/homebrew/Cellar/sqliteodbc/0.9998/lib/libsqlite3odbc.so and I have set this in the /opt/homebrew/etc/odbcinst.ini files (as found by odbcinst -j).
Unfortunately, this fails with a database connection failure (and I believe I have tried every combination in the connection string!)
Symbol library 'qa_dblib' failed to load.
Could not load database library: could not connect to database KiCad (/Users/vagrant/kicad/thirdparty/nanodbc/nanodbc/nanodbc.cpp:996: 0100: [unixODBC][Driver Manager]Can't open lib '/opt/homebrew/Cellar/sqliteodbc/0.9998/lib/libsqlite3odbc.so' : file not found )
The missing file is definitely there and the path is correct.
After nearly giving up, I installed the DevArt ODBC drivers as a demo. Just setting the ‘dsn’ details in the .kicad_dbl file resulted in a connection and I could place symbols from the test database.
So there is definitely something slightly flaky here with the normal ODBC drivers & it might be worth trying the DevArt ones.
I will plug away at this a bit more to see if I can produce a more useful bug report.
[Disclaimer] I have absolutely no commercial connection with DevArt.
On M1, you need to make sure to use x86_64 ODBC drivers rather than arm64 ones, as KiCad is not compiled for arm64. I’m not familiar with DevArt but I wonder if that’s the difference.
Yay - that works!
So the SQLite can be ARM native but the SQLiteODBC needs to be x86_64. Didn’t appreciate that as I assumed the SQLite interface was simply an external program - I should have twigged it was a shared object/dylib. Thanks for the hint.
in your dbl files there are some items with extra ‘,’ terminations, for example:
...
{
"column": "Power_Rating",
"name": "Power_Rating",
"visible_on_add": false,
"visible_in_chooser": true,
},
{
"column": "Material",
"name": "Material",
"visible_on_add": false,
"visible_in_chooser": true,
},
{
"column": "MFG",
"name": "MFG",
"visible_on_add": false,
"visible_in_chooser": true,
},
...
Claudio,
Thanks you for your comments. I thought I took out all the extra “,” out as I thought this is not correct.
I guess I missed some. Anyway:
The database now works!!!
Thank you and everyone for your comments & suggestion.
I feel humbled
Regards,
nasa
2 Likes
You can use a JSON validator like https://codebeautify.org/jsonvalidator to check your kicad_dbl files.
It points out that in the version where you use a connection string, the string is not valid because you use backslashes without escaping them. If you include a path to a file (on Windows) in your connection string, you must use \\ instead of \ for it to be a valid string. Otherwise, it treats the \ as an escape character.
Thank you craftyjon. I started using the DSN connection so wouldn’t have to specify a path name for the driver. I just used the JSON validator to check my kicad_dbl file & it is valid.
I have my resistor & capacitor database libraries working perfectly.
When I include another library like diodes, Thru the library symbol chooser, I see the symbol but the footprint library says no footprints found.
I have imported all the diodes footprints & can see the footprints if I open the footprint library in the pcb chooser. I think it must be the kicad_dbl file. I’ll keep on looking at it.
I’ve been using Altium for a long time & I see alot of parallels in KiCad. Some things you are doing easier than Altium.
There are some Altium features I miss such as the way Altium copy/pastes a part relative to a point.
I’ve been somewhat successful in mimicing out job functionality thru batch files & a custom Python/Tkinter program. Less prone to fabrication error.
Thanks for all you do,
nasa
There is a “copy with reference” action that you can map to a hotkey if desired
The other thing to check is your database table – does the library nickname exactly match whatever your diodes library is called in your KiCad footprint library table?
Changing nicknames is an easy and convenient way to relocate a personal library in the library table.
This is detrimental to a data base or anything else?
The database links rows to symbol and footprint libraries by nickname. If you change the nickname, you must also update the database.
Note that when you are using a database library, the database table itself will show up with a different nickname in the library chooser. The “source” library providing symbols can be hidden from the symbol chooser so that you don’t accidentally place “plain” symbols that don’t come from the database.
1 Like
Thanks, I’ll check today
Jon,
There was a mismatch between the kicad_dbl & the field name in my KiCad database for the footprint. I guess when you make the mistake, its harder to see it. It all works now!!!
Regards,
nasa
1 Like
@John_Pateman I’m gonna try to test this out this weekend on a M1 MBP as well.
Could you provide a bit more detail how you got this working? Did you have to install sqliteodbc using the x86_64 version of homebrew or did you get it working some other way?
Few random hints to get this working!. It is useful to have the iODBC DataSource Administrator installed just to quickly check your configuration.
I installed the x86 version of the sqliteodbc ODBC driver with
arch -x86_64 brew install sqliteodbc
and the ARM version of sqlite with
brew install sqlite
The ODBC config files can be found at and can be edited by hand but I found the Data Source Administrator 64 helpful to confirm that I had a valid setup.
/Library/ODBC/odbc.ini and /Library/ODBC/odbcinst.ini are the files that control the ODBC setup.
If going by the hand rolled route, you will need to check that the /Library/ODBC/odbcinst.ini contains
[SQlite]
Driver = /usr/local/lib/libsqlite3odbc.dylib
Setup = /usr/local/lib/libsqlite3odbc.dylib
And the /Library/ODBC/odbc.ini contains something like the following snippet (not sure this is completely necessary - and the DEVART_SQLITE is a hangover from the DevArt ODBC drivers which I tried out (which work but are not cheap and not necessary)
[ODBC Data Sources]
myodbc = MySQL ODBC 8.0 Unicode Driver
myodbca = MySQL ODBC 8.0 ANSI Driver
DEVART_SQLITE = Devart ODBC Driver for SQLite
[ODBC]
TraceLibrary =
[myodbc]
Driver = MySQL ODBC 8.0 Unicode Driver
SERVER = localhost
NO_SCHEMA = 1
[myodbca]
Driver = MySQL ODBC 8.0 ANSI Driver
SERVER = lqocalhost
NO_SCHEMA = 1
Its helpful to have a schema viewer to adjust and import data from an external source eg a spreadsheet into your database. I found db-browser for sqlite easy to use.
brew install --cask db-browser-for-sqlite
Hope that helps to get you going.
Here is my database and .kicad_dbl files if you want to play. You will need to add it to your library table as database connection.
Let me know if this works for you!
database.sqlite (136 KB)
qa_dblib.kicad_dbl (3.8 KB)
2 Likes