Questions about capabilities

jdhitsolutions / MySQLite

A small set of PowerShell commands for working with SQLite database files.

MIT License

42 stars 4 forks source link

Questions about capabilities #19

Closed PylotLight closed 1 year ago

PylotLight commented 1 year ago

Sorry I didn't know where else to log this @jdhitsolutions :

What version of SQLite is being used here, as I'm looking for the latest version which supports read and write of the DB at the same time.
Is there an autoincrement feature for ID so I don't have to add an $i++ in the loop to set to ID?
Is there a bulk copy or bulk transaction feature for large inserts?

jdhitsolutions commented 1 year ago

I updated the assembly to version1.0.117.0 last month I have no idea what version supports the feature you are looking for. I don't really know the answers to your other questions. Those would be language or syntax questions I'm not qualified to answer. You'll have to search for answers like this or this on autoincrementing.

PylotLight commented 1 year ago

Thanks for your response. I assumed the autoincrement and bulkcopy items needed to be added to the module as params or something as I haven't seen how to set that in the create table cmdlet, but will have another look as what I'm really looking for is concurrency/multi threaded support, cheers.

jdhitsolutions commented 1 year ago

The module is for loading the assemblies and to abstract things like creating databases and tables. Using the database with sqlite queries is up to you.

PylotLight commented 1 year ago

Yep, but the autoincrement for example would be part of that module side for creating the tables, not the writing insert queries bit. But all good for now. I assume you can't help with "WARNING: Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error cannot start a transaction within a transaction"" I'm probably not using transactions properly. Will keep researching and close this issue soon

jdhitsolutions commented 1 year ago

I looked for an autoincrement feature when I first started this module, After reading https://www.sqlite.org/autoinc.html I use the native ROWID.

PylotLight commented 1 year ago

~Apologies, did you have a small example of using that? As currently, I've set a PK on ID "Int" but it ends up empty if i don't specify it.~ Fixed

~Also any suggestions on handling strings with ' in the name, as that seems to break the string when using '$($FilePath)', Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error near "s": syntax error"~

Adding a -replace for every query to replace ' with '' for now until I find a better solution.