Incorrect Syntax

Mar 10, 2015 at 12:41 PM
First off let me start by saying, great work. I'm impressed, very impressed and those who know me know that takes some doing :-)

However, I seem to have hit a sticking point.

I'm still in the exploratory phase of looking at Vita, so I hope it's not a big issue, but I'm consistently getting an "Incorrect Syntax near '-'" error when Vita tries to perform the connect to my Db.

Having had great success with pure code first on a new Db, I decided to try DbFirst from an existing Db...

After not being sure if I was getting things correct, I decided to use your vdbtool to generate some classes, and compare them to mine.

That showed me what I was doing wrong, so problem fixed, but it introduced a new one.

When I look at the entity VIta's tool generates I see the following:
  [Entity(TableName = "UrlEntities")]
  public interface IUrlEntities {

    [Column("RecordId"), PrimaryKey, Identity]
    int RecordId { get; }

    [Column("BelongsToStatusId", Precision = 18)]
    Decimal BelongsToStatusId { get; set; }

    [Column("DisplayUrl", DbTypeSpec = "varchar(-1)"), Unlimited, Nullable]
    string DisplayUrl { get; set; }

    [Column("ExpandedUrl", DbTypeSpec = "varchar(-1)"), Unlimited, Nullable]
    string ExpandedUrl { get; set; }

    [Column("Url", DbTypeSpec = "varchar(-1)"), Unlimited, Nullable]
    string Url { get; set; }
  }
The schema for the "UrlEntites" table is as follows:
USE [twitterBackup]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UrlEntities](
    [RecordId] [int] IDENTITY(1,1) NOT NULL,
    [BelongsToStatusId] [decimal](18, 0) NOT NULL,
    [DisplayUrl] [varchar](max) NULL,
    [ExpandedUrl] [varchar](max) NULL,
    [Url] [varchar](max) NULL,
 CONSTRAINT [PK_UrlEntities] PRIMARY KEY CLUSTERED 
(
    [RecordId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
When I looked behind the scenes (Using SQL profiler to see what Vita was sending to the DB) i see the following Sproc being created at the time the error is thrown:
CREATE PROCEDURE [dbo].[UrlEntitiesInsert] 
    @RecordId int OUTPUT,
    @BelongsToStatusId decimal(18,0),
    @DisplayUrl varchar(-1) = NULL,
    @ExpandedUrl varchar(-1) = NULL,
    @Url varchar(-1) = NULL 
  AS 
BEGIN
  SET NOCOUNT ON;
  -- Description: Inserts a new entity.
  -- VITA/Generated: CRUD/[dbo].[UrlEntities]/Insert
  
INSERT INTO [dbo].[UrlEntities] 
  ("BelongsToStatusId", "DisplayUrl", "ExpandedUrl", "Url") 
  VALUES
    (@BelongsToStatusId, @DisplayUrl, @ExpandedUrl, @Url); 

SET @RecordId = SCOPE_IDENTITY(); 

END
I've verified by hand that taking the -1 out of the script and replacing it with 'max' then running things in SMSS, the Sproc is created successfully with no issues. Based on these findings I then changed the Entity model so that the -1's where replaced with 'max' as follows:
  [Entity(TableName = "UrlEntities")]
  public interface IUrlEntities {

    [Column("RecordId"), PrimaryKey, Identity]
    int RecordId { get; }

    [Column("BelongsToStatusId", Precision = 18)]
    Decimal BelongsToStatusId { get; set; }

    [Column("DisplayUrl", DbTypeSpec = "varchar(max)"), Unlimited, Nullable]
    string DisplayUrl { get; set; }

    [Column("ExpandedUrl", DbTypeSpec = "varchar(max)"), Unlimited, Nullable]
    string ExpandedUrl { get; set; }

    [Column("Url", DbTypeSpec = "varchar(max)"), Unlimited, Nullable]
    string Url { get; set; }
  }
Upon running my test app however, I still see the same yellow screen of death, complaining about the - causing a syntax error at the same place, which in turn causes the parameter to be not declared, which then causes Sproc creation to fail.

Is this a known issue, or is it something I'm not seeing/doing correctly.

Shawty (Lidnug)
Coordinator
Mar 10, 2015 at 4:55 PM
Hi
thank you for your kind words. Yeah, it looks like a bug. The 'problem' seems to be combination of varchar and max - I mostly use nvarchar in tests. Try replacing DbTypeSpec with DbType = DbType.AnsiString, this might work as a workaround.
I will look into this and fix it by the end of the week (with a number of other fixes).
thanks again, keep beating it, you gonna like it after all, I promise!
Roman
Coordinator
Mar 10, 2015 at 5:04 PM
yep, just tried it, with varchar(max) it fails, but with DbType it goes ok:

[Column("DisplayUrl", DbType = DbType.AnsiString), Unlimited, Nullable]
string DisplayUrl { get; set; }

the only problem is that the column created in db is of type 'Text'.

I will look into this more and fix it.
Mar 10, 2015 at 5:37 PM
Hi Roman,

Excellent, I'll give that a try. I got to know about it because someone started a discussion in the user group I help run, and later in that discussion someone suggested trying Vita, being who I am I had to go take a look and as I said, I really do like what I see.

I'm still at the teething stage, but already considering using it for a couple of production projects.

In case your interested, the reason I'm here is because of this:

https://www.linkedin.com/groups/What-ORM-should-I-choose-43315.S.5980334248870051841?trk=groups_most_recent-0-b-ttl&goback=%2Egmr_43315

and I have to say, I really, Really wish I'd known about Vita when I originally wrote this:

http://www.codeguru.com/columns/dotnet/the-sorry-state-of-.net-orms.html

because it's highly likely that Vita would have gotten my vote rather than MicroLite.

I'll give your suggestion a try once I work out how to add a list of concrete classes to one of my tables without having to for each over the list and session.NewEntity<T> each one separately :-)

Shawty
Coordinator
Mar 10, 2015 at 6:25 PM
Man, what took you so long?! I've put a comment there in 'sorry state' article about Vita, loong ago. Really enjoyed the article btw, and appreciate the effort you made looking at all these ORMs - did it myself in the past, and I remember the most frustrating thing of the exercise - after lot of effort all you get is another disappointment.
Welcome to the club, I think you really like all these things I put there. Don't miss the advanced features - Caching, Authorization and (!!!) Modular construction. The component-based development might be a real advancement. Have a look at LoginModule as an example. I will update it by end of week, fix one ugly part with second-factor storage, and add encrypted data storage, to store sensitive things like email for password recovery in encrypted form. Imagine getting all this functionality by just adding a module to your app.
Also note that several enterprise apps on VITA are in production, it is a real thing working in the cloud. And my best feedback loop - driven by real world needs, not by 'what's flashy in presentation'.
Looking forward to your feedback!
Roman
PS and I'll fix it, no problem.
Mar 10, 2015 at 6:48 PM
Oh wow, I've just realised that what you meant by "I put a comment in there", ha ha, and I even replied to you :-)

Ok, my bad slaps self on the wrist

I have my own special, but quite simple way of encrypting password strings, shoot me a private message on Twitter ( @shawty_ds ) and I'll give you my EMail addy then I can send you the code.

Might give you a bit of inspiration for your encryption modules, my way of saying thanks.

Shawty
Coordinator
Mar 12, 2015 at 4:49 PM
I've fixed that varchar(max) problem, will push this and other fixes over weekend.