Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for new Large Number / bigint store type #154

Open
lauxjpn opened this issue Oct 15, 2023 · 0 comments
Open

Add support for new Large Number / bigint store type #154

lauxjpn opened this issue Oct 15, 2023 · 0 comments

Comments

@lauxjpn
Copy link
Member

lauxjpn commented Oct 15, 2023

[...]
It should also be mentioned, that recent Access database versions have an actual (opt-in) large number data type.
[...]

Originally posted by @lauxjpn in #151 (comment)


The large number type for recent version (2016+) might not work. When you enable it it can only be opened by 2016 and later. And then its unknown how it would via OleDb/Odbc as it would need a version of the access driver/provider that can understand that format and data type

By the looks of it quickly one has to enable it directly via the desktop app. Not sure if it can be done programmatically.

Originally posted by @ChrisJollyAU in #151 (comment)


Not sure if it can be done programmatically.

Should be possible.

The large number type for recent version (2016+) might not work. When you enable it it can only be opened by 2016 and later. And then its unknown how it would via OleDb/Odbc as it would need a version of the access driver/provider that can understand that format and data type

Yeah, that's why we should make it an option for users to select, rather than a default. If someone enables it, it is that persons responsibility.
[...]

Originally posted by @lauxjpn in #151 (comment)


Some comments from a bit of research

  1. Saving decimal values with Odbc can be done if configured correctly. The OdbcType field of the parameter needs to be set to Numeric as in ((OdbcParameter)parameter).OdbcType = OdbcType.Numeric . The only drawback is that we would need to include System.Data.Odbc in EFCore.Jet (the main package) rather than it only being pulled in with the .Odbc specific package. Tested with Model79_CantSaveDecimalValue and it passes and round-trips correctly
  2. The Large Number data type in Access 2016+ can be used. Obviously if used the database gets stamped and can't be opened by a provider earlier than that (e.g. 2010 database engine).
  3. ADOX and DAO do support it and correctly returns adBigInt. We do have a bug there in that the code to convert that to a string returns integer
  4. The store types to use in the SQL are bigint and datetime2 (Date/Time Extended is also supported). Using these store types automatically stamps the database
  5. Int64 values seem to have a problem being saved (at least in OleDb - havent tested Odbc). It seems x64 gives a conversion error for a parameter (even if the value is just 2). However in x86 it works fine (probably only if the value is within Int32 range, even though parameter has been configured as DbType.Int64). parameter.DbType = System.Data.DbType.Object does the trick. Tested in 365_x64 and successfully round-trip Int64.MaxValue

Originally posted by @ChrisJollyAU in #151 (comment)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant