New SQL server plugin

Discussions about the preview versions of Virtual Radar Server.
Forum rules
Please bear in mind if you do install a preview version that it is unfinished code, there may be bugs, bits missing, things that don't work etc.

Posts in this forum will not show up in forum search results. Old threads will be pruned occasionally.
Bhaal
Posts: 37
Joined: Wed Aug 12, 2015 10:20 am

New SQL server plugin

Post by Bhaal » Mon Jan 22, 2018 2:09 pm

Hi agw,

Just wondering about this plugin, is it for connecting to MS SQL or will it connect to MySQL for those people running under Mono? If it could, then another plugin could be to store the entire flight path for each flight tracked, which could be displayed in the reports instead of just Start and End locations :D I know others have done this directly from dump1090 to mysql via some php, I saw it over a year ago, never got around to implementing it myself though :( But would be cool if VRS could do this natively (upon request, I know it'd make the DB largish)

Thanks

Datamix
Posts: 49
Joined: Thu Aug 10, 2017 7:36 pm

Re: New SQL server plugin

Post by Datamix » Mon Jan 22, 2018 6:44 pm

Since I'm running VRS under mono too I'm also highly interested in an a mysql conncetion.
VRS: v3 (24-JUN-2018)

agw
Posts: 2058
Joined: Fri Feb 17, 2012 3:20 am

Re: New SQL server plugin

Post by agw » Tue Jan 23, 2018 12:10 am

It's just SQL Server. However there were changes that I had to make to the server to accommodate using a proper multithreading database engine rather than the single-threaded SQLite library, now that they've been implemented there's nothing stopping a similar plugin being written for MySQL.

FWIW you can get SQL Server 2017 for free on a couple of Linux distros (https://docs.microsoft.com/en-us/sql/li ... inux-setup). I've not yet tried it though.

pavelkonir
Posts: 10
Joined: Mon Nov 21, 2016 11:40 am

Re: New SQL server plugin

Post by pavelkonir » Wed Feb 07, 2018 12:39 pm

I have installed SQL server 2017, server is running.

I set this connectin string: Server=localhost,1401;User Id=vrs; Password=vrs-password1;Database=master

Test connection return: A connection can be made with this connection string

But when I click on button "update schema" show error: The server reported an exception, see log for details: Exception has been thrown by the target of an invocation.

Is connection string correct?

Datamix
Posts: 49
Joined: Thu Aug 10, 2017 7:36 pm

Re: New SQL server plugin

Post by Datamix » Wed Feb 07, 2018 1:15 pm

pavelkonir wrote:
Wed Feb 07, 2018 12:39 pm
Server=localhost,1401;User Id=vrs; Password=vrs-password1;Database=master
Add a semicolon at the end

Like this:

Code: Select all

Server=localhost,1401;Database=master;Uid=vrs;Password=vrs-password1;
VRS: v3 (24-JUN-2018)

pavelkonir
Posts: 10
Joined: Mon Nov 21, 2016 11:40 am

Re: New SQL server plugin

Post by pavelkonir » Wed Feb 07, 2018 1:16 pm

Connection is now ready, schema was created, but tables are empty. In log are errors.

[2018-02-07 13:13:43.328 UTC] [t19] Caught an exception on a fast tick heartbeat event: System.ArgumentException: The parameter data type of System.Data.DataTable is invalid.
at System.Data.SqlClient.SqlParameter.InferSqlType (System.Object value) [0x00049] in <9ec44cbcc9fa4c6b81867c576ebbc98b>:0
at System.Data.SqlClient.SqlParameter.set_Value (System.Object value) [0x00008] in <9ec44cbcc9fa4c6b81867c576ebbc98b>:0
at Dapper.TableValuedParameter.Set (System.Data.IDbDataParameter parameter, System.Data.DataTable table, System.String typeName) [0x00007] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.DataTableHandler.SetValue (System.Data.IDbDataParameter parameter, System.Object value) [0x00000] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.SqlMapper+TypeHandlerCache`1[T].SetValue (System.Data.IDbDataParameter parameter, System.Object value) [0x00000] in <63a8cc06dfb842f1823bc51677815d4b>:0
at (wrapper dynamic-method) <>f__AnonymousType7`1[System.Data.DataTable]:ParamInfof0cf69b1-cbe6-4694-a5bc-894802d42ceb (System.Data.IDbCommand,object)
at Dapper.CommandDefinition.SetupCommand (System.Data.IDbConnection cnn, System.Action`2[T1,T2] paramReader) [0x000b4] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.SqlMapper+<QueryImpl>d__136`1[T].MoveNext () [0x000ba] in <63a8cc06dfb842f1823bc51677815d4b>:0
at System.Collections.Generic.List`1[T]..ctor (System.Collections.Generic.IEnumerable`1[T] collection) [0x00077] in <c9f8153c41de4f8cbafd0e32f9bf6b28>:0
at System.Linq.Enumerable.ToList[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00018] in <9da65c3aa2654e53b5f11b79677182e0>:0
at Dapper.SqlMapper.Query[T] (System.Data.IDbConnection cnn, System.String sql, System.Object param, System.Data.IDbTransaction transaction, System.Boolean buffered, System.Nullable`1[T] commandTimeout, System.Nullable`1[T] commandType) [0x0003c] in <63a8cc06dfb842f1823bc51677815d4b>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase+<>c__DisplayClass83_0.<GetManyAircraftAndFlightsCountByCode>b__0 (VirtualRadar.Plugin.SqlServer.ConnectionWrapper wrapper) [0x00042] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase.PerformInConnection (System.Action`1[T] action) [0x0000f] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase.GetManyAircraftAndFlightsCountByCode (System.Collections.Generic.IEnumerable`1[T] icao24s) [0x0001b] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Library.AircraftDetailFetcher.DoFetchManyAircraft (System.Collections.Generic.IEnumerable`1[T] fetchedDetails) [0x000d4] in <b7286c723c894270aa8da0ed53899e65>:0
at VirtualRadar.Interface.AircraftFetcher`2[TKey,TDetail].FetchAllAircraft (System.Collections.Generic.IEnumerable`1[T] fetchedDetails) [0x00011] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
at VirtualRadar.Interface.AircraftFetcher`2[TKey,TDetail].Heartbeat_FastTimerTicked (System.Object sender, System.EventArgs args) [0x00095] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
at (wrapper dynamic-method) System.Object:lambda_method (System.Runtime.CompilerServices.Closure,object,object,System.EventArgs)
at VirtualRadar.Interface.EventHelper.Raise[TEventArgs] (System.Delegate eventHandler, System.Object sender, System.Func`1[TResult] buildArgsCallback, System.Action`1[T] exceptionCallback, System.Boolean throwEventHelperException) [0x000f5] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
[2018-02-07 13:13:44.389 UTC] [t20] Caught an exception on a fast tick heartbeat event: System.ArgumentException: The parameter data type of System.Data.DataTable is invalid.
at System.Data.SqlClient.SqlParameter.InferSqlType (System.Object value) [0x00049] in <9ec44cbcc9fa4c6b81867c576ebbc98b>:0
at System.Data.SqlClient.SqlParameter.set_Value (System.Object value) [0x00008] in <9ec44cbcc9fa4c6b81867c576ebbc98b>:0
at Dapper.TableValuedParameter.Set (System.Data.IDbDataParameter parameter, System.Data.DataTable table, System.String typeName) [0x00007] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.DataTableHandler.SetValue (System.Data.IDbDataParameter parameter, System.Object value) [0x00000] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.SqlMapper+TypeHandlerCache`1[T].SetValue (System.Data.IDbDataParameter parameter, System.Object value) [0x00000] in <63a8cc06dfb842f1823bc51677815d4b>:0
at (wrapper dynamic-method) <>f__AnonymousType7`1[System.Data.DataTable]:ParamInfof0cf69b1-cbe6-4694-a5bc-894802d42ceb (System.Data.IDbCommand,object)
at Dapper.CommandDefinition.SetupCommand (System.Data.IDbConnection cnn, System.Action`2[T1,T2] paramReader) [0x000b4] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.SqlMapper+<QueryImpl>d__136`1[T].MoveNext () [0x000ba] in <63a8cc06dfb842f1823bc51677815d4b>:0
at System.Collections.Generic.List`1[T]..ctor (System.Collections.Generic.IEnumerable`1[T] collection) [0x00077] in <c9f8153c41de4f8cbafd0e32f9bf6b28>:0
at System.Linq.Enumerable.ToList[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00018] in <9da65c3aa2654e53b5f11b79677182e0>:0
at Dapper.SqlMapper.Query[T] (System.Data.IDbConnection cnn, System.String sql, System.Object param, System.Data.IDbTransaction transaction, System.Boolean buffered, System.Nullable`1[T] commandTimeout, System.Nullable`1[T] commandType) [0x0003c] in <63a8cc06dfb842f1823bc51677815d4b>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase+<>c__DisplayClass83_0.<GetManyAircraftAndFlightsCountByCode>b__0 (VirtualRadar.Plugin.SqlServer.ConnectionWrapper wrapper) [0x00042] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase.PerformInConnection (System.Action`1[T] action) [0x0000f] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase.GetManyAircraftAndFlightsCountByCode (System.Collections.Generic.IEnumerable`1[T] icao24s) [0x0001b] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Library.AircraftDetailFetcher.DoFetchManyAircraft (System.Collections.Generic.IEnumerable`1[T] fetchedDetails) [0x000d4] in <b7286c723c894270aa8da0ed53899e65>:0
at VirtualRadar.Interface.AircraftFetcher`2[TKey,TDetail].FetchAllAircraft (System.Collections.Generic.IEnumerable`1[T] fetchedDetails) [0x00011] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
at VirtualRadar.Interface.AircraftFetcher`2[TKey,TDetail].Heartbeat_FastTimerTicked (System.Object sender, System.EventArgs args) [0x00095] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
at (wrapper dynamic-method) System.Object:lambda_method (System.Runtime.CompilerServices.Closure,object,object,System.EventArgs)
at VirtualRadar.Interface.EventHelper.Raise[TEventArgs] (System.Delegate eventHandler, System.Object sender, System.Func`1[TResult] buildArgsCallback, System.Action`1[T] exceptionCallback, System.Boolean throwEventHelperException) [0x000f5] in <249e9c2d71db409a84e6c3a181bd8ef5>:0

pavelkonir
Posts: 10
Joined: Mon Nov 21, 2016 11:40 am

Re: New SQL server plugin

Post by pavelkonir » Wed Feb 07, 2018 1:29 pm

And is possible migration data from sqlite to sql server?

pavelkonir
Posts: 10
Joined: Mon Nov 21, 2016 11:40 am

Re: New SQL server plugin

Post by pavelkonir » Wed Feb 07, 2018 1:35 pm

I do not understand why there is no user and configuration table in that database.

agw
Posts: 2058
Joined: Fri Feb 17, 2012 3:20 am

Re: New SQL server plugin

Post by agw » Thu Feb 08, 2018 1:12 am

Did you install the preview server as well as the SQL Server plugin? Is this running on Windows or Mono?

There is a command-line utility that ships with the plugin - it's called BaseStationImport and it is installed into the VRS program folder. If you run BaseStationImport on its own then it shows some help. Note that there is a known truncation issue in the current preview (see viewtopic.php?f=11&t=1533)

Configuration will not be stored in the database. I've not decided on whether to put the connection log in there yet... it's not a problem when there's just one site using the database but it gets tricky when there are lots. With BaseStation you just have one site writing and the rest reading, there's no problem there. WIth multiple sites all writing to the same connection tables things could get tricky, the connection log currently assumes that each instance has the log to itself. Also connection log writes are not a big performance hog, especially once BaseStation reads and writes aren't using SQLite.

pavelkonir
Posts: 10
Joined: Mon Nov 21, 2016 11:40 am

Re: New SQL server plugin

Post by pavelkonir » Thu Feb 08, 2018 8:45 pm

Yes, I have installed preview version server. Environment is Mono.

Could not be the user database in the sql server?

Post Reply