Error Import from MSSQL (Primary key property is empty)

Forum for posting problems using QxEntityEditor

Error Import from MSSQL (Primary key property is empty)

Postby zoggler » Tue Aug 26, 2014 8:00 am

Hello,

I try to import a table from a MSSQL Server, but i get always the error "Primary key property is empty".
I was trying to move my db table to the default schema of the MSSQL "DBO" and than the import is functioning.

I test with the actual version "QxEntityEditor 1.1.6 - Windows 64 bits (portable)"

Regards zoggler
zoggler
 
Posts: 6
Joined: Tue Aug 26, 2014 7:29 am

Re: Error Import from MSSQL (Primary key property is empty)

Postby qxorm » Tue Aug 26, 2014 8:34 am

Hello,

1- In the "Import from database using ODBC" screen, have you selected the database type (by default this is "Generic", but in your case you have to put "MS SQL Server") ?
2- Maybe your table doesn't have any defined primary key (QxEntityEditor cannot manage entities without primary key) ?
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Error Import from MSSQL (Primary key property is empty)

Postby zoggler » Tue Aug 26, 2014 8:48 am

Hello ,

I use the "MS SQL Server" setting and I have a primary key defined.
Here is a SQL script of a table in my db.

Regards

Code: Select all
CREATE TABLE [icore].[usergroup](
   [usergroup_id] [int] IDENTITY(1,1) NOT NULL,
   [version] [int] NULL,
   [ownercorporation_id] [int] NULL,
   [ownerplant_id] [int] NULL,
   [deleted] [tinyint] NULL,
   [creationdate] [datetime] NULL,
   [name] [varchar](50) NOT NULL,
   [ownerbusinessunit_id] [int] NULL,
PRIMARY KEY CLUSTERED
(
   [usergroup_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
   [usergroup_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

zoggler
 
Posts: 6
Joined: Tue Aug 26, 2014 7:29 am

Re: Error Import from MSSQL (Primary key property is empty)

Postby qxorm » Tue Aug 26, 2014 9:14 am

Ok, I can reproduce your issue with your table script.
Here is the way how QxEntityEditor get all information from a MS SQL Server database :

1- To get all tables/views :
Code: Select all
SELECT
  table_name,
  table_schema,
  table_type
FROM
  information_schema.tables
WHERE
  (table_type = 'BASE TABLE' OR table_type = 'VIEW');


2- To get the primary key of a specific table :
Code: Select all
SELECT
  column_name
FROM
  information_schema.key_column_usage
WHERE
  OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND
  table_schema = 'icore' AND
  table_name = 'usergroup';


The query to get the primary key returns empty : that means that this condition doesn't work : OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey')

Do you have any idea why this condition doesn't work ?
Do you have a better query to get the primary key of a table ?
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Error Import from MSSQL (Primary key property is empty)

Postby zoggler » Tue Aug 26, 2014 10:14 am

The problem is the function "OBJECT_ID" ! You have to add the constraint_schema information.
Here is a sample.
Is it possible to get a new version of the plugin?

Regards

Code: Select all
OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] object_name' [ ,'object_type' ] )

SELECT  column_name
FROM
  information_schema.key_column_usage
WHERE
  OBJECTPROPERTY(OBJECT_ID(constraint_catalog+'.'+constraint_schema+'.'+constraint_name), 'IsPrimaryKey') = 1 AND
  table_schema = 'icore' AND
  table_name = 'usergroup';
zoggler
 
Posts: 6
Joined: Tue Aug 26, 2014 7:29 am

Re: Error Import from MSSQL (Primary key property is empty)

Postby qxorm » Tue Aug 26, 2014 10:51 am

Thank you for your fix !
You can download the plugin (for QxEntityEditor 64bits on Windows) with your fix here : http://www.qxorm.com/temp/QxEEOdbcImport.zip

Please let me know if it is working fine now with this new plugin...
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Error Import from MSSQL (Primary key property is empty)

Postby zoggler » Tue Aug 26, 2014 11:08 am

It's functioning now, thanks!
zoggler
 
Posts: 6
Joined: Tue Aug 26, 2014 7:29 am

Re: Error Import from MSSQL (Primary key property is empty)

Postby qxorm » Tue Aug 26, 2014 11:18 am

It's functioning now, thanks!

Great !
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Error Import from MSSQL (Primary key property is empty)

Postby zoggler » Tue Aug 26, 2014 11:33 am

Hi ,
a other question.

I want to buy a version of the editor. Now my questions:
1. I also get the source code
2. Do I have to buy a license from the QxOrm to start a commercial project or the licence is includet in the editor fees

Regards
zoggler
 
Posts: 6
Joined: Tue Aug 26, 2014 7:29 am

Re: Error Import from MSSQL (Primary key property is empty)

Postby qxorm » Tue Aug 26, 2014 12:23 pm

Hi,
To purchase a QxOrm QXPL license and a QxEntityEditor license key, please contact us at this e-mail : contact@qxorm.com
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am


Return to QxEntityEditor - Help

Who is online

Users browsing this forum: No registered users and 1 guest

cron