llblGen and ASP.NET Core

Posts   
1  /  2
 
    
gregkuha60
User
Posts: 47
Joined: 23-Jan-2012
# Posted on: 29-Jan-2016 12:23:39   

Hello Has anyone successfully used an existing library in the Asp.Net Core version 1.0.0-rc1-update-1? I have started a little project and trying to add reference to an "old" library e.g "SD.LLBLGen.Pro.ORMSupportClasses.dll" but it generates error when trying to use it 'The name 'DataAccessAdapter' does not exist in the current context' WebApp1.DNX Core 5.0'.

The project.jason looks like this "frameworks": { "dnx451": { "dependencies": { "SD.LLBLGen.Pro.DQE.SqlServer": "1.0.0-", "SD.LLBLGen.Pro.ORMSupportClasses": "1.0.0-" } }, "dnxcore50": { } },

The problem is not the llblGen library, it's doesn't matter what library to use

Wait for the release of llblGenPro 5.0 is the answer?

Any thoughts about this? Thank's

gregkuha60
User
Posts: 47
Joined: 23-Jan-2012
# Posted on: 29-Jan-2016 13:52:49   

Find something about they have to been in the gac so i did the following.

Removed my referenced files and did it manually from the project.jason file.

When adding the dll's "manually" by typing them in to the project.jason file, then i got the version number. "frameworks": { "dnx451": { "dependencies": { "SD.LLBLGen.Pro.DQE.SqlServer": "4.2.20150410", "SD.LLBLGen.Pro.ORMSupportClasses": "4.2.20150410"

However under the reference directory it still says 1.0.0 and it still doesnt compile, same error

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 29-Jan-2016 15:07:42   

our runtime is currently not supported on .NET core, as the .NET core BCL hasn't been finalized yet and porting our runtime is a big effort.

If you want to run your llblgen pro application on linux, you can use mono, our runtime runs fine on Mono simple_smile

So when MS finally has decided to fix some gaping issues in ADO.NET for .NET Core we can begin to schedule a port of the code base. So that's not going to happen soon, as MS is very slow in fixing the ADO.NET issues currently blocking many ORM writers to port code to .NET Core.

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 23-Aug-2016 11:49:02   

Otis wrote:

If you want to run your llblgen pro application on linux, you can use mono, our runtime runs fine on Mono simple_smile

Otis is right. Apart from one little issue (see below) LLBLGen does work great with current Mono framework.

I'm developing on an Apple MacBook and use XAMARIN Studio to get things done. The project I'm currently working on is a WebAPI 2 project with the LLBLGen 4.2. Framework as DAL to access the MS SQL Server DB.

The LLBLGen Runtime is linked as SourceCode (using ORMSupport Classes.NET45, SqlServerDQE and TypeConverters project).

Compiling is done with no additional compiler switches.

Deployment to production server (UBUNTU 16.04 LTS machine running NGINX with HyperFastCGI ( https://github.com/xplicit/HyperFastCgi )) happens via "git pull" and a recompile on the UBUNTU server.

The Issue

The only issue I came across is with NVARCHAR(max) fields and this is due to a mono incompatibility with the .NET Framework.

Line #42 in Mono.Data.Tds.TdsMetaParameter.cs declares a wrong length for NVarchar(max)

Here's the relevant code:



#region Static 
public const int maxVarCharCharacters = 2147483647; // According to MS, max size is 2GB, 1 Byte Characters
public const int maxNVarCharCharacters = 1073741823; // According to MS, max size is 2GB, 2 Byte Characters
#endregion


This is wrong!

Under https://msdn.microsoft.com/en-US/library/ms186939.aspx MS states that: _... max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. _

As LLBLGen uses the correct value of 2147483647 for declaring the length of an nvarchar parameter, under mono UPDATE and INSERT statements which try to write an NVARCHAR(max) field lead to a TDS parser exception.

As I didn't feel in the mood for a lengthy debate with the mono devels and to fix the issue nonetheless I modded FieldInfo.cs and FieldPersistenceInfo.cs under RuntimeLibraries/ORMSupportClasses/Persistence.

FieldInfo.cs:


private void InitClass(string name, string containingObjectName, Type dataType, bool isPrimaryKey, bool isForeignKey, bool isReadOnly, bool isNullable, int fieldIndex, int maxLength, byte scale, byte precision) {

if (Environment.OSVersion.Platform >= PlatformID.Unix) {
  if (dataType == typeof(string) && maxLength == 2147483647) {
    maxLength = 1073741823;
  }
}
 ...................



FieldPersistenceInfo.cs:


private void InitClass(string sourceCatalogName, string sourceSchemaName, string  sourceObjectName, string sourceColumnName, bool isSourceColumnNullable,  string sourceColumnDbType, int sourceColumnMaxLength, byte sourceColumnScale, byte sourceColumnPrecision, bool isIdentity,  string identityValueSequenceName, TypeConverter typeConverterToUse, Type actualDotNetType) {

if (Environment.OSVersion.Platform >= PlatformID.Unix) {
  if (sourceColumnDbType == "NVarChar" && sourceColumnMaxLength == 2147483647) {
    sourceColumnMaxLength = 1073741823;
  }
}

.........

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 25-Aug-2016 11:00:19   

Thanks for sharing, Kamiwa! simple_smile

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 25-Aug-2016 11:47:07   

You're welcome!

If somebody knows how to inform the mono developers about this incompatibility in the current framework, feel free to use this info in your bug report. Fixing the bug is definitely a better solution than the above workaround.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 25-Aug-2016 19:13:08   

This line? https://github.com/mono/mono/blob/master/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs#L42

It looks like that constant is actually right, it represents the character length. However, the 'size' in bytes needs to be calculated differently. It's a bit confusing what's going on: whether they're using the size defined there for the # of bytes, or for the # of chars.

Looking at where that constant is used, I can only find: https://github.com/mono/mono/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs#L185 which looks like the size in chars.

Did you try open an issue on the mono repo? If you could do that, with stacktrace where things go wrong, I can chime in.

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 25-Aug-2016 19:49:50   

Sorry Frans!

But like you did now, I checked the mono source code for the max. size definition for nvarchar. and line 42 in TdsMetaParameter.cs looked like the most promising candidate. sunglasses

Fact is, as you most likely have figured out by now, that if you define an SqlParamter with a max size of 2GB and use it in an SqlCommand to update a nvarchar(max) field, things will work fine when under .NET but will throw a Tds Parser Exception on Mono.

I'll write some sample code tomorrow (too warm today, for any action) and see if I can file it somewhere. Is Mono using BugZilla or is there a place on GitHub where you can report bugs?

When trying to find out, whether this is a known issue, I came a across some older posts from the mono mailing list which were related to some other TDS related bug.

The lengthy discussion between the OP of the bug and the mono community weren't exactly motivating me to report the bug simple_smile

kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 26-Aug-2016 12:22:25   

Here we go:

First of all a small SQL Script to create a DB and a table, so that we've got something to test with:


USE master
GO

CREATE LOGIN DebugUser WITH PASSWORD=N'monotest', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE USER DebugUser FOR LOGIN DebugUser
GO

ALTER ROLE [db_owner] ADD MEMBER [DebugUser]
GO

CREATE TABLE NVarCharTest
(
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [LongName] [nvarchar](MAX)  NOT NULL
)
GO

ALTER TABLE NVarcharTest ADD CONSTRAINT PK_NVarcharTest PRIMARY KEY ([Id])
GO

Next create a console application, name it "TestNVarCharMax", add a reference to System.Data to the project and overwrite the content of Program.cs with the following code:


using System.Data.SqlClient;
using System.Data;
using System;

namespace TestNVarCharMax {
    class MainClass {
        static string ConnectionString =
            @"data source=localhost;initial catalog=TestDB;User ID=DebugUser;Password=monotest;persist security info=False;packet size=4096;Connection Timeout=300";

        public static void Main() {

            const string commandText = @"INSERT INTO NVarCharTest (LongName) VALUES (@LongName)";
            const int maxNVarCharLengthDotNet = 2147483647;
            const int maxNVarCharLengthMono = 1073741823;

            using (var connection = new SqlConnection(ConnectionString)) {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection)) {
                    var longNameParameter = new SqlParameter("@LongName", SqlDbType.NVarChar, maxNVarCharLengthDotNet);
                    longNameParameter.Value = @"Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.";

                    command.Parameters.Add(longNameParameter);

                    try {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex) {
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }

                    longNameParameter.Size = maxNVarCharLengthMono;

                    try {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex) {
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }
                }
            }
        }
    }
}


Replace "localhost" in the connection string with ip address, name or instance name of your sql server.

Running the code under mono, line #25 will give you the following error message: Der eingehende Tabular Data Stream (TDS) für das RPC-Protokoll (Remote Procedure Call) ist nicht richtig. Parameter 3 ('@LongName'): Der 0xE7-Datentyp hat eine ungültige Datenlänge oder Metadatenlänge.

English translation: The incoming Tabular Data Stream (TDS) for the RPC protocol (Remote Procedure Call) is not correct. Parameter 3 ('@LongName'): The 0xE7-datatype has an invalid data length or metadata length.

Stack trace is attached.

Running the same code under .NET on Windows doesn't throw any errors.

QED sunglasses

Edit: To save you copy and paste: git clone https://bitbucket.org/kamiwa/mononvarcharmaxbug.git

Attachments
Filename File size Added on Approval
StackTrace.txt 2,816 26-Aug-2016 12:23.01 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 26-Aug-2016 16:42:50   

It's very confusing simple_smile SqlParameter.Size is size in bytes (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.size(v=vs.110).aspx), so 2GB-1 is a proper value indeed. However, MS documented it pretty crappy, but for (n)varchar(max) it seems one needs to specify -1: http://stackoverflow.com/questions/973260/what-size-do-you-use-for-varcharmax-in-your-parameter-declaration

But 2GB-1 works fine too. On Mono, they indeed confused char length with byte length. I think it's either 2GB-1 or -1 that SQL server accepts. So their code has a bug.

Not sure what I should do now though. I don't know where to file issues for Mono, but even so, I'm not going to file issues on behalf of others: if Mono people are not willing to fix it, then I'll reply, no problem, but I'm not going to file issues on behalf of others as that simply costs me too much time, sorry.

So that's why I asked you to file the issue yourself. It's a problematic issue though, as it has high impact for you, but highly unlikely for many others, so their priority might be low for this.

I'm thinking about a workaround for this in our own code. It needs an adjustment here: http://referencesource42.llblgen.com/#SD.LLBLGen.Pro.DQE.SqlServer/SqlServerSpecificCreator.cs,133

If we check for nvarchar/varchar and a length > 8K, we can assume 'MAX' and specify -1 as length. It would then work on mono or .NET regardless.

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 26-Aug-2016 17:19:12   

Otis wrote:

I'm not going to file issues on behalf of others as that simply costs me too much time, sorry.

Understandable and agreed upon.simple_smile

As I already said: The long thread on mono's mailing list I had read, lead me to not posting the bug.

Otis wrote:

I don't know where to file issues for Mono, ....

And even if I had decided to file the issue, like you I didn't know where to post it.

Otis wrote:

It's a problematic issue though, as it has high impact for you, but highly unlikely for many others, so their priority might be low for this.

Impact for me is not as high as you think, now that I found the dirty hack I used in FieldInfo.cs and FieldPersistenceInfo.cs. I actually think that the impact is much higher for LLBLGen as a product than it is for me, as sooner or later anybody using mono with LLBLGen will run into trouble when it comes to updating nvarchar(max) fields.

Otis wrote:

I'm thinking about a workaround for this in our own code. It needs an adjustment here: http://referencesource42.llblgen.com/#SD.LLBLGen.Pro.DQE.SqlServer/SqlServerSpecificCreator.cs,133. If we check for nvarchar/varchar and a length > 8K, we can assume 'MAX' and specify -1 as length. It would then work on mono or .NET regardless.

Sounds a cunning plan to me simple_smile

Edit: Let's see what happens: simple_smile https://bugzilla.xamarin.com/show_bug.cgi?id=43795

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 01-Sep-2016 11:41:45   

I've changed the size to -1 for (n)varchar(max) / varbinary(max), and tests run fine. I've attached a new release build of this dll. Could you try this on mono to see whether it works now (as we don't have a test setup with mono here)

Attachments
Filename File size Added on Approval
SD.LLBLGen.Pro.DQE.SqlServer.zip 15,383 01-Sep-2016 11:41.51 Approved
Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 02-Sep-2016 09:49:08   

Otis wrote:

Could you try this on mono to see whether it works now (as we don't have a test setup with mono here)

Will do over the weekend!

Edit:

Just tested the attached assembly on mono and from what I can see it seems to be running just fine!

But let me say this:

I'm not convinced that using the .NET pre-compiled assemblies from LLBLGen Runtime under mono is a good idea.

When I first started the "Try LLBLGen under mono" project, I wasn't sure, whether LLBLGen would work with mono at all. I remembered a rather old post here on this forum where you or a member of your team had stated that it SHOULD work fine but that you don't actually test it under mono and that users wanting to use it under mono should use the source and compile it under mono and then see if it works.

So instead of using the pre-compiled binaries I always linked the LLBLGen Runtime source code and linked them into my projects.

In order to make the source code compile under mono, I had to remove


[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("C:\\Myprojects\\mystrongkey.key")]
[assembly: AssemblyKeyName("")]

from SharedAssemblyInfo.cs under LLBLGenPro/RuntimeLibraries and remove any PostBuild events in the *.csproj files that copied the output binaries to a common output folder.

This of course is intended behaviour and won't have much effect on the resulting assembly when it comes to binary compatibility.

BUT: Other than the above changes I additionally had to make two changes in RuntimeLibraries/ORMSupportClasses/AdapterSpecific/EntityCollectionBase2.cs.

In Line 1875 I had to change


hasDependingRelatedEntities = entity.GetDependingRelatedEntities().Count != 0;

to


hasDependingRelatedEntities = ((IEntity2)entity).GetDependingRelatedEntities().Count != 0;

and in Line 1879 I had to change


hasDependentRelatedEntities = entity.GetDependentRelatedEntities().Count != 0;

to


hasDependentRelatedEntities = ((IEntity2)entity).GetDependentRelatedEntities().Count != 0;

as the mono C# compiler wouldn't see the inherited IEntity2 interface and needed an implicit cast.

I'm not sure whether this has an effect on the resulting MSIL so I rather stayed with the modded source code.

This additionally has the advantage that if a bug shows up, it's easier to find out what exactly is going on. If I had used the precompiled binaries, I'd never would have found the mono incompatibility with the max length of a nvarchar(max) parameter.

One more thing to say:

When I filed the bug in mono's bugzilla, I sadly had to find out that alone in System.Data namespace there are round about 50 bugs that were filed since 2011 and were never fixed. Many of them still have the status NEW which most likely means that the mono developers didn't find time to even look at them.

During the last week my developer colleague an me ran into issue on several occassions that we couldn't explain. From the error messages it seemed, as if the json data structures that were posted to the rest API had there fields shifted. Error message was that the string value "foo" couldn't be casted to boolean and thus couldn't be assigned to the boolean field foobar.

Unfortunately another user had reported a similar behavior on bugzille. Another user reported problems with unexpected timeouts they couldn't find an explanation for. Another user reported that after they had run into an error condition data returned was not the result for the actual request but instead the one from the previous request.

According to the posters these problems can't be reproduced under .NET.

None of the above reported issues did show up here.

But the fact, that these issues have been reported and that the mono developers don't seem to find the time to look into them plus the fact that these problems don't seem to show up under .NET has led us to the decision to take mono from the menu.

At least for production.

I'm still developing and debugging in Xamarin Studio on Mac. XS is really not as bad as you might think when reading some posts on the web.

But when it comes to production we're back to using Microsoft Internet Explorer and .NET which is sad as especially NGINX in conjunction with HyperFastCGI deliver a far better performance then IIS does.

Even Apache2 with mod_mono and XSP4 is faster than IIS.

So if you're planning to use the Mono framework in production, be prepared for some issues and do so at your own risk.

Perhaps, in the end .NET Core will turn out to be the better solution for going cross platform. At least instead of dealing with two different implementations we would have the same source on all three platforms.

This of course depends on MS fixing the ADO.NET problems and keeping the project alive long enough to give it a chance to really become the successor of .NET

And it of course depends on people like Otis and other developers who currently deliver great existing and already working .NET solutions to be able (and willing) to port their solutions over to .NET Core.

Well, let's see what future brings.

@Otis: Sorry for this lengthy post. Have you had a chance to look into the mail I send you on Saturday? I think I found a problem in LLBLGen regarding LLBLGen's paging capabilities.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 05-Sep-2016 12:20:52   

Hmm. that sounds sad. Mono was and still is a tremendous effort and now Xamarin is under MS wings, I hope(d) to see more time spent on it. They ported a lot of code from reference source back to mono already but apparently not really in the database department disappointed Not sure whether there will be a lot of time invested in this in the future by MS. I have the feeling the time they'll invest in it will be on xamarin tooling, and what's needed for that, the rest is slowly becoming irrelevant if .net core is going to get bigger, as why use mono when there's .net core on linux?

IIS + ASPNET is indeed pretty slow, however IIS as front for Kestrell and asp.net core seems to be much faster. Caveat is then of course to use .net core which is currently unusable.

MS will create some sort of linker system to make it easy to port code to .NET core, until then it's not going to be a target we can use (and many with us, so it's a bit of a waiting game).

I've received your email, it doesn't show the patch file in outlook but that's ok, I see what needs to be changed to make the TOP query use the parameter.

I'll make the change to the v4.2 and later versions of the SQLServer DQE to use the parameter for TOP in a paging query. Thanks for the heads up on that! I'll let you know when the new build is available (with source). I'll make the casts as well so you don't have to change these wink

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 05-Sep-2016 12:34:30   

Perhaps I forgot to attach the patch to the mail! Will check! Meanwhile I attached it here!

Attachments
Filename File size Added on Approval
LLBLGen, SqlServerDQE, DynamicQueryEngine, Method ManglePageSelectDQ2005.diff 1,767 05-Sep-2016 12:34.42 Approved
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 05-Sep-2016 13:13:43   

Otis wrote:

Mono was and still is a tremendous effort and now Xamarin is under MS wings, I hope(d) to see more time spent on it. They ported a lot of code from reference source back to mono already but apparently not really in the database department Dissapointed Not sure whether there will be a lot of time invested in this in the future by MS. I have the feeling the time they'll invest in it will be on xamarin tooling, and what's needed for that, the rest is slowly becoming irrelevant if .net core is going to get bigger, as why use mono when there's .net core on linux?

Just in case that my previous message lead to the impression that I'm not appreciating what mono and now XAMARIN have done and still do for .NET, I'd like to say that I fully agree with your above statement.

I've been using XAMARIN to write a Windows/Mac cross platform MVVM based desktop application. At least 90% of the code in this project is shared between the two versions. The only parts not being cross platform compatible is the actual GUI code.

It's just awesome to be able to write a program that can work on two completely different platforms w/o having to use two completely different languages.

Open-sourcing MS's .NET reference code was the best thing that ever happened to .NET and it really helped to improve mono quality tremendously. Perhaps the .NET Core approach wouldn't have been necessary at all if only MS had decided to open-source .NET earlier.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 05-Sep-2016 13:50:39   

simple_smile

I've added the interface cast to the entity methods and the parameter to the TOP clause and have uploaded a new 4.2 build as well as sourcecode.

Should be all good now simple_smile

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 28-Sep-2016 18:42:02   

Otis wrote:

I've changed the size to -1 for (n)varchar(max) / varbinary(max), and tests run fine. I've attached a new release build of this dll. Could you try this on mono to see whether it works now (as we don't have a test setup with mono here)

Frans I'm soooooo sorry! simple_smile Seems we forgot about good ol' XML column type.

SqlSpecificCreator needs another patch in Line 160:


case "Xml":
    // Pass -1 as size, as documented by Microsoft. It then also works on Mono
    sizeToUse = -1;
    break;

Works on Mono. Not checked under .NET.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 29-Sep-2016 10:50:19   

Ok, we'll look into it.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 29-Sep-2016 15:45:26   

Fixed (v4.2), new lib/installer/source is now available. Will upload new 5.0.7 build shortly. (-1 works fine on .net too)

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 29-Sep-2016 15:47:03   

Awesome! Thank you!

kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 24-Dec-2016 17:14:37   

Found another one:

Mono cannot handle SqlParameter with Type SqlDBType.Real and a NULL value, and will throw a TDS parser exception.

Dirty Hack:

Add


#if __MonoCS__
                case "Real":
                    if (valueToPass == null) {
                        parameterTypeToPass = "Int";
                    }
                    break;
#endif

to method CreateParameter in SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.

BTW: Merry Christmas!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 25-Dec-2016 09:46:58   

Happy holidays to you too wink

Sounds like a mono bug, sorry. Do they respond at all? Is it still alive btw, or are they dead? Do they accept any other type for a NULL value except for 'real' (which is an alias)?

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 25-Dec-2016 10:20:40   

Otis wrote:

Sounds like a mono bug, sorry.

It sure is a mono bug.

Otis wrote:

Do they respond at all?

I never received any response to the bug I filed at https://bugzilla.xamarin.com/show_bug.cgi?id=43795 (see above).

Otis wrote:

Is it still alive btw, or are they dead?

Mono still must be alive as the framework still gets updated regularly. On November 20th, Microsoft released a Visual Studio for Mac Preview that relies on Mono 4.8. They sure wouldn't have done so, if they believed the Mono Project was dead. Especially not as Mono is now under MS control.

Otis wrote:

Do they accept any other type for a NULL value except for 'real' (which is an alias)?

Any other none null value does work OK with an SqlParameter of SqlDbType.Real, Length = 4, Scale and Precision = 0 and so far I didn't find any problems with SqlParameters with types other than Real and value = null. The more frequent types (Int, NVarchar, DateTime, Bit) never caused any problems here. I know, that Real is an Alias for Float(n). Will try what happens if I mod your code to use float instead of Real.

BTW Frans: This is not so important that it needs to be solved during your XMAS holidays. We can easily postpone this till beginning of next year. Take a break. You deserve it. OK? sunglasses

kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 25-Dec-2016 12:49:12   

Just checked with SqlDbType.Float.


public static void Main() {
  var conString =
    "data source=<Your server>;initial catalog=<Your database>;User ID=<user>;Password=<password>;persist security info=False;packet size=4096;Connection Timeout=300";

  var commandText =
    @"INSERT INTO [dbo].[XLocationAddress] 
    ([AddressMemberId], [City], [CountryId], [IsDefault], [Latitude], [Longitude], [PostalCode], [Street]) 
    VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)";

  try {
    using (var sqlConnection = new SqlConnection(conString)) {
      sqlConnection.Open();
      using (var command = new SqlCommand(commandText, sqlConnection)) {
        command.Parameters.Add("@p1", SqlDbType.Int).Value = 123;
        command.Parameters.Add("@p2", SqlDbType.NVarChar).Value = "Some City";
        command.Parameters.Add("@p3", SqlDbType.Int).Value = 57;
        command.Parameters.Add("@p4", SqlDbType.Bit).Value = 1;

        SqlParameter sqlParameter1 =
        command.Parameters.Add("@p5", SqlDbType.Float);
        sqlParameter1.Value = DBNull.Value;
        sqlParameter1.Size = 4;

        SqlParameter sqlParameter2 =
        command.Parameters.Add("@p6", SqlDbType.Float);
        sqlParameter2.Value = DBNull.Value;
        sqlParameter2.Size = 4;

        command.Parameters.Add("@p7", SqlDbType.NVarChar).Value = "12345";
        command.Parameters.Add("@p8", SqlDbType.NVarChar).Value = "Some Street";

        command.ExecuteNonQuery();
      }
    }
  }
  catch (Exception ex) {
    Console.WriteLine(ex.Message);
  }

  Console.ReadKey();
}


will give you the same exception as if you were using


command.Parameters.Add("@p5", SqlDbType.Real).Value = DBNull.Value;
command.Parameters.Add("@p6", SqlDbType.Real).Value = DBNull.Value;

In both cases you'll get


Der eingehende Tabular Data Stream (TDS) für das RPC-Protokoll (Remote Procedure Call) ist nicht richtig. Parameter 7 ('@p5'): Der 0x6D-Datentyp hat eine ungültige Datenlänge oder Metadatenlänge.

Translation:
The incoming Tabular Data Stream (TDS) for the RPC-Protocol (Remote Procedure Call) is not correct. Parameter 7 ('@p5'): The 0x6D-Datatype has an invalid data or metadata length.

So my above posted dirty hack now needs to include float as well:

Add


#if __MonoCS__
                case "Float":
                case "Real":
                    if (valueToPass == null) {
                        parameterTypeToPass = "Int";
                    }
                    break;
#endif

In case you want to reproduce it, heres the CREATE TABLE script for the LocationAddress table:


CREATE TABLE [dbo].[LocationAddress](
  [AddressId] [int]  NOT NULL,
  [CountryId] [int]  NOT NULL,
  [FederalStateId] [int]  NULL,
  [PostalCode] [nvarchar](20)  NOT NULL DEFAULT (N''),
  [City] [nvarchar](120)  NOT NULL DEFAULT (N''),
  [Street] [nvarchar](120)  NOT NULL DEFAULT (N''),
  [HouseNumber] [nvarchar](12)  NULL,
  [Building] [nvarchar](120)  NULL,
  [Appartement] [nvarchar](120)  NULL,
  [Floor] [nvarchar](120)  NULL,
  [Longitude] [real]  NULL,
  [Latitude] [real]  NULL,
  [IsDefault] [bit]  NOT NULL DEFAULT ((0))
)


1  /  2