SQL Server Views

Support
#1

Hi

I believe SQL Server Views where supposed to be supported in 6.6 but I don’t think they are.
Can we expect this to work on 6.7?

Thanks

#2

Well, it does not, just tested 6.7 RC1.

Other thing, I can “generate model” for external SQL Server DB only in some tables (I believe simpler ones)
Most of the time I get “Unexpected error java.lang.NullPointerException” when selecting the table and clicking next in the wizard and simply cant link it. Why is that?

It’s been a year, by now I was expecting a lot more in this question of accessing external databases.
Most of my projects require accessing external SQL Server Databases.

#3

Hi Pedro,

Support for DB views was a feature of Studio release 6.6: CUBA Platform and Studio Release Notes

Please give us more details about your situation: views and tables definitions, error stack trace.

1 Like
#4

Hi Konstantin

I’m using 6.6.4 and the generate model wizard from external SQL Server (2014) datasource.
Nothing of the above seems to be working.
I send you a SQL Server bak. Thats part of the Sage 50c (commercial billing program) structure I need to read.
In fact now I can´t even generate model for the table Customer that used to work in 6.5. If you select the table in the wizard it does not write the
@AttributeOverrides({
@AttributeOverride(name = “id”, column = @Column(name = “CustomerID”))
}) on the entity source and gives “SQLServerException: Invalid column name ‘ID’.” at runtime.
I also cant avoid the wizard to create the entity extending BaseUuidEntity. I think it should be BaseLongIdEntity by my working other example.
And I still haven´t got it editing the entity source code generated.
In the database you have a View “Sales Orders”. The first two colums give you a composite PK. You can select those in the wizard. Just to obtain a similar error. It does not work for me.
What would be the analogous code above for a two field composite key?

Thankssage50data.rar (2.3 MB)

I could read these DB’s with any ORM and Lightswitch now I can´t.

#5

Thank you for the database example and explanation. Will take a look at the issue ASAP.

#7

Hi,
Thank you for the database backup. I have restored it and tried to generate a model with the latest (6.7) Studio and platform.

  1. Regarding the database View. Columns in the database have names in mixed case. And by that reason Studio could not handle it correctly. We have created a YouTrack issue and probably we will fix it soon.

  2. Regarding the Customer entity and BaseUuidEntity. Studio recognizes that the column is the primary key of the table by the corresponding PK-constraint. Tables in your database have no constraints.
    How does your current system work without any constraints and indexes in DB?

Try to set PK constraint on the CustomerID column and make it auto-incremental. The Customer entity will be imported as BaseIdentityIdEntity and you will be able to create new Customers in you CUBA application.

#8

Well, I’ll track it ok. You “probably will fix it soon”? ok.

“CustomerID” IS the primary key of the table. I guess the constraints got lost in my copy, sorry.
Anyway that’s not my database has I was trying to explain. That’s why I want to use it as as external datasource. I cant change the casing for the table names for instance.
Linking against the “live” DB (not this copy) is almost never possible, it results is “Unexpected error java.lang.NullPointerException” I suppose on the size of the tables?

Can we have more control over the “Generate model” wizard? Like choosing base entity, keys,etc?
Otherwise the whole thing is, at least for me, pretty useless

#9

Hi,
The issue with column names is fixed in the Studio version 6.7.2. Try to generate the model using it.
Studio still recognizes PK columns by constraints. We decided to show a warning when the PK constraint is not found during the model generation. See the YouTrack Issue for more information.

Regards.