f# - FSharp data type provider for Postgresql -

i trying fsharp data provider against postgresql using npgsql. , busted @ first line.

when trying create sqldataconnection throwing error message connection string not correct.

the type provider 'microsoft.fsharp.data.typeproviders.designtime.dataproviders' reported error: keyword not supported: 'port:5432;database'.

now, test connection string , data using servicestack.ormlite. uses idbconnection. so, connection correct. don't know why type provider not working.

here code.

    //type dbschema = sqldataconnection<connectionstring = "server=localhost;port=5432; database=testdb;user id=postgres;password=g00gle*92;" > [<climutable>] type person =      { id : int;       firstname : string;       lastname : string }  [<entrypoint>] let main args =      let dbfactory =          ormliteconnectionfactory             (              "server=localhost;port=5432; database=testdb;user id=postgres;password=*****;",               postgresqldialect.provider)     use dbconnection = dbfactory.opendbconnection()     console.writeline dbconnection.state     let persons = dbconnection.select<person>()     persons.foreach(fun p -> console.writeline p.firstname)     console.read() |> ignore     0 

in above code first commented line not working while same settings below code working. means issue type provider not connections imho.

do need other settings.

please let me know if other details required.

update

after kvb's comment tried both. here updated code web config.

//type dbschema = sqlentityconnection<connectionstringname = "testdb", provider="npgsql">     type dbschema = sqlentityconnection< connectionstringname="testdb" >      [<climutable>]     type person =          { id : int;           firstname : string;           lastname : string }      [<entrypoint>]     let main args =          let dbfactory =              ormliteconnectionfactory                 (                  "server=localhost;port=5432; database=testdb;user id=postgres;password=*******;",                   postgresqldialect.provider)         use dbconnection = dbfactory.opendbconnection()         console.writeline dbconnection.state         let persons = dbconnection.select<person>()         persons.foreach(fun p -> console.writeline p.firstname)         console.read() |> ignore         0 

and here web config

  <system.data>     <dbproviderfactories>       <add name="npgsql data provider"             invariant="npgsql"             description="data provider postgresql"             type="npgsql.npgsqlfactory, npgsql" />     </dbproviderfactories>   </system.data>   <connectionstrings>     <add name="testdb"           connectionstring="server=localhost:5432; database=testdb;user id=postgres;password=******;"           providername="npgsql" />    </connectionstrings> 

and here assembly in appconfig. don't think in gac added via nuget

 <dependentassembly>     <assemblyidentity name="npgsql" publickeytoken="5d8b90d52f46fda7" culture="neutral" />     <bindingredirect oldversion="0.0.0.0-2.0.12.0" newversion="2.0.12.0" />  </dependentassembly> 

above both 1 commented , 1 without not commented both failing different error. first 1 failing error

the type provider 'microsoft.fsharp.data.typeproviders.designtime.dataproviders' reported error: error reading schema. error 7001: specified store provider 'npgsql' cannot found in configuration, or 'npgsql' not valid. unable find requested .net framework data provider. may not installed.

and second 1 error

the type provider 'microsoft.fsharp.data.typeproviders.designtime.dataproviders' reported error: error reading schema. error 7001: provider did not return providermanifesttoken string. network-related or instance-specific error occurred while establishing connection sql server. server not found or not accessible. verify instance name correct , sql server configured allow remote connections. (provider: named pipes provider, error: 40 - not open connection sql server) network path not found

i still not understand why searching sql server.

please let me know if further infromation required.

i'm going post partial answer in hope can work out how next bit.

the following code compile:

open microsoft.fsharp.data.typeproviders open system.data.entity // important -- cannot see tables without  type internal dbschema =      sqlentityconnection<         connectionstring="server=localhost;database=testdb;user id=postgres;password=password;",          provider="npgsql">  [<entrypoint>] let main argv =      let context = dbschema.getdatacontext()     query { item in context.test_table             select item }     |> seq.iter (fun item -> printfn "%a" item)     0 

for table test_table in database testdb created via

create table test_table (   id integer not null,   value text,   constraint "pk_test_x_id" primary key (id) ) (   oids=false ); alter table test_table   owner postgres; 

to need 4 things:

• gac npgsql.dll ("c:\program files (x86)\microsoft sdks\windows\v8.0a\bin\netfx 4.0 tools\x64\gacutil.exe" /i [filename])
• gac mono.security.dll (in same directory npgsql.dll downloaded nuget
• add dbproviderfactory .net 4 64-bit machine.config ("c:\windows\microsoft.net\framework64\v4.0.30319\config\machine.config"): same thing have in app.config, added appropriate section in machine.config, mine has 1 entry @ moment microsoft sql server compact data provider. remember include correct public key token.

<system.data>   <dbproviderfactories>     <add name="npgsql data provider"       invariant="npgsql"       description="data provider postgresql"       type="npgsql.npgsqlfactory, npgsql, version=2.0.12.0, culture=neutral, publickeytoken=5d8b90d52f46fda7" /> </dbproviderfactories> </system.data> 

• restart visual studio.

now sqlentityconnection compile @ design time , able see of tables available. happily compile executable.

that answers question; weird bit mean still not happy. when run code throw argumentexception dbschema.getdatacontext() called saying:

the supplied connection string should either valid provider-specific connection string or valid connection string accepted entityclient.

the inner exception states

the 'server' keyword not supported.

with stack-trace

at system.data.entityclient.entityconnectionstringbuilder.set_item(string keyword, object value) @ system.data.common.dbconnectionstringbuilder.set_connectionstring(string value) @ system.data.entityclient.entityconnectionstringbuilder..ctor(string connectionstring) @ sqlentityconnection1.dbschema.getdatacontext()

i've tried frigging connection string work, think must bug in how provider creating connection string @ run-time vs design-time. since code emitted dynamic assembly isn't obvious how @ code see going on.