ODBC support for 64bit odbc drivers? The table schema is missing in the sql and also the limit clause...

[giuliohome]

Is your feature request related to a problem? Please describe. I have a 64 bit odbc driver and I'm looking for a basic support, nothing fancy, but, at first try, it seems that

type db = SqlDataProvider<Common.DatabaseProviderTypes.ODBC, dnsConn>

works only with 32bit architecture? Is it a problem to support also 64bit drivers or just a matter of rebuilding something with the appropriate settings?

Thank you

[giuliohome]

Ops... I'm under the impression that this could be an issue originating from the fact that in design mode (that is the way of working of a type provider to provide design time types) the app is Visual Studio and ... is it the reason why it matches only 32bit odbc dirvers? Is there already workaround to detour this or is it a new feature request? Maybe the topic is more generic than odbc drivers, let me ask: do you have some special trick to work with 64bit db drivers from Visual Studio? Is it maybe an issue of my personal configuration (I'm on windows 10 64bit though... and I have latest version of VS, so I can't spot anything wrong with my configuration at the moment)

[Thorium]

Try VS Code, I think that is running FSI/FSC on 64bit by default.

[giuliohome]

Running FSI 64bit is a good tip indeed, I will try this for the moment and will reopen if further discussion is needed.

[giuliohome]

If I must use VS Code I have to port the project from .NET Framework to .NET Core, right? After a first try in .NET Code I get an

error FS3033:
Errore segnalato dal provider di tipi 'FSharp.Data.Sql.SqlTypeProvider': 
Could not load file or assembly 'System.Data.Odbc, Version=4.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'.

On the other side, since I should really go with Visual Studio for other reasons, I've also asked on SO.

[Thorium]

Maybe you can configure what FSC is used by your IDE, and with what parameters. I think you can use VS Code with full .NET Framework, you just do the build/compilation command line.

[Thorium]

One more thing to try: If you use VS2019 on 32bit and a 32bit System.Data.Odbc.dll on design-time/compilation time, to build 64bit target, then replace the bin-folder System.Data.Odbc.dll with 64bit one, and try to run it with 64bit execution on runtime. (If it works, setup a post-build script to do that.) I'm expecting the dlls themselves are identical on features.

[giuliohome]

I on Win10 64bit of course and I am using VS2019 with .NET Framework and/or VS Code with .NET Core. Notice that with pure odbc connection, command and data-reader both VS2019 and VS Code can build and run fine, without any issues with odbc at compile and runtime.

    let conn = new OdbcConnection(dnsConn)
    conn.Open()
    let cmd = new OdbcCommand(sql, conn)
    let DR = cmd.ExecuteReader()
    while (DR.Read()) do

So the build error comes out only when I introduce

    type db = SqlDataProvider<Common.DatabaseProviderTypes.ODBC, dnsConn> `

and therefore seems to be relate only to this repo AFAICS or to some hidden setting in my IDEs, but I'm on new 64-bit stack and I mean to build only for 64-bit...

[Thorium]

I guess your VS2019 is running as 32bit process and that's why your typeprovider intellisense and compilation needs 32bit dll.

[giuliohome]

Yes I'm sure that could be the answer only for intellisense and the designer part in VS2019, but it doesn't explain to me why the build itself fails in both IDEs...

As far as the compilation itself is concerned, both IDEs can build the 64-bit odbc, is the SQLProvider requiring a 32-bit odbc instead? Why?

On Sun, Jul 12, 2020, 12:26 PM Tuomas Hietanen notifications@github.com wrote:

I guess your VS2019 is running as 32bit process and that's why your typeprovider intellisense and compilation needs 32bit dll.

— You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub https://github.com/fsprojects/SQLProvider/issues/686#issuecomment-657203301, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAY66445VSLHU7WH6DQZ7Y3R3GFWLANCNFSM4OVQFMGQ .

[giuliohome]

If you mean that it is a SQLProvider's requirement that I have to install the 32 bit version of the odbc specific driver and that the 32 bit driver is required also to build the x64 release (which is not intuitive at all), then please confirm it also with such an answer on SO. Then I will ask for and install also the 32 bit version of the driver and set up a 32 bit DSN and I will check if intellisense works and if I can finally deliver a x64 release version with the 64-bit version of the driver and in that case I will accept your answer (otherwise this would be considered a bug issue or at least an open request for an enhancement).

[Thorium]

I'm not expert on this topic: SQLProvider just loads the reference dlls, there is zero code about how many bits. I think this is either Visual Studio or FSC related thing, not SQLProvider, and I don't know enough of those to close the SO-issue.

the answer only for intellisense and the designer part in VS2019, but it doesn't explain to me why the build itself fails in both IDEs...

As far as I understand, for the type providers, the build and intellisense are essentially the same thing, Visual Studio is using FSC when resolve loading the references needed. So there are only 1) design (& build) time, 2) runtime.

[giuliohome]

Ok, if there is zero code - and any sort of configuration, references, etc - about how many bits, we can close this issue. I've had already asked for the 32-bit version of the driver. Me or you or someone else (expert of Visual Studio or FSC related things, as you say... but notice that I also have an error with VSCode and .NET Core), we/they will answer the SO question, also based on what I can see after I install both 32bit and 64bit version of the driver (I'll keep the question updated there on my side).

[giuliohome]

I've installed the 32 bit version, configured myDSN32 and tried the project as x86 but I see the

Errore  FS3021  Eccezione imprevista dal tipo fornito 'FSharp.Data.Sql.SqlDataProvider,DatabaseVendor="6",ConnectionString="DSN=MyDSN32"+dataContext' membro 'GetMethods': Errore segnalato dal provider di tipi 'FSharp.Data.Sql.SqlTypeProvider': The design-time type 'System.Data.IDbConnection' utilized by a type provider was not found in the target reference assembly set '[tgt assembly FSharp.Data.SqlProvider, Version=1.1.91.0, Culture=neutral;
 tgt assembly mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089;
 tgt assembly FSharp.Core, Version=4.7.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a;
 tgt assembly System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089;
 tgt assembly System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089;
 tgt assembly System.Numerics, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089;
 tgt assembly System.ValueTuple, Version=4.0.2.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51]'. You may be referencing a profile which contains fewer types than those needed by the type provider you are using. 

This is a SQLProvider specific error and it looks like, even on 32-bit, the odbc provider implementation of the SQLProvider is not generic enough to support my specific odbc driver. I guess this closes this experiment on my side, unfortunately,

[giuliohome]

Ops... Adding System.Data ref fixed it! As suggested here

[giuliohome]

FYI

I've posted my answer on SO, I would be glad if you could review it.

I'm also re-opening this issue, bucause I see that when I do

    let Schema = ctx.MySchema
    let extract = 
        query {
            for trade in Schema.Trade do
            take 100
            select (trade)
        }

    let result = extract |> Seq.toArray   

I'm getting an OdbcException since the sql is not generated with the schema before the table name... Could you please point me to the solution of this problem (if it already exists)?

Another problem when I look at the generated sql is that the LIMIT 100 clause is competely missing.

Thank you

[giuliohome]

Aside from the 2 issues above (the table schema and the limit missing in the generated sql), I've also opened another question about F# linq because I'm not sure if this third issue is due to code in SQLProvider.

[Thorium]

I guess people are using ODBC TypeProvider with data-sources that don't have schemas (like Excel).

The sqlQuery.Take-handling seems to be missing from the Providers.Odbc.fs. Is there a standard syntax/command for ODBC to limit the resultset? Here:

https://github.com/fsprojects/SQLProvider/blob/a1858d00279a2c368d9e06a511e4bae49fc022cd/src/SQLProvider/Providers.Odbc.fs#L590

That should be fairly simple to add by looking how other providers do it...

I'll gladly accept PRs to improve Odbc TypeProvider and release the packages. :-)

[giuliohome]

Ok, I think I will prepare a PR for this 2 points. What's your opinion about the 3rd one?

[giuliohome]

Anyway, there is still a preliminary point, because I can't compile the odbc under .NET Core 3.1 yet

error FS3033:
Errore segnalato dal provider di tipi 'FSharp.Data.Sql.SqlTypeProvider': Could not load file or assembly 'System.Data.Odbc, Version=4.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'. Impossibile trovare il file specificato.

Why doesn't it get the reference to the nuget package System.Data.Odbc -Version 4.7.0 from https://www.nuget.org/packages/System.Data.Odbc/ ? This should be fixed first of all...

I deem it is related to an open issue here: https://github.com/fsprojects/SQLProvider/issues/645

[giuliohome]

Ok, I think I will prepare a PR for this 2 points. What's your opinion about the 3rd one?

Even if I have opened an issue in dotnet/fsharp, I suspect there could be a problem here in SQLProvider SqlRuntime.Patterns,

see my answer on SO

[Thorium]

That's weird, there should be reference to System.Data.Odbc.dll already...