MS SQL Server JDBC - doesn't recognize nvarchar columns in source when Publishing Database Service
Wed, 06/16/2010 - 17:29
glenn.adams
Offline
Joined: 05/18/2010
Points: 80
So I just installed the JDBC Driver for MS SQL Server. Relatively straight forward to get Presto hooked up with a new Driver and DataSource. Now I want to publish a Database Service using an existing database and add a custom Finder as shown in the Training Video ( http://www.jackbe.com/enterprise-mashup/videos/mashup-fundamentals-unit-3-2 ) .
Click the "DB" button in Service Explorer to create a new Database Service
Select the Datasource I created above
Select the 'dbo' schema - I see a tabbed list of Tables, Views and Procedures
I select the "Customer" table and click Proceed - so far so good...
On the next wizard page, I select the "Customer" table in the tree view, and the Metadata section populates - I see a tabbed list of Columns and Finders.
After blindly going through the steps of the video tutorial using the known column 'lastname' ( I know the schema of this database pretty well ) and getting errors when tying to "Make Mashlet" from the service, I finally looked at the list of columns.
NONE of the nvarchar SQL Type columns are listed! I see INT, BIT, DATETIME, FLOAT, even MONEY types, but no nvarchar.
to test, I created a new table with varchar, char(50) and Nvarchar versions of the fields I was missing. Sure enough, the varchar and char versions of the columns are listed in "Publish Database Service", but the nvarchar versions are missing.
I guess the obvious solution is to create views that cast the nvarchars as varchar, but that just seems silly. Is there a way to make the very common nvarchar fields be recognized? Is this an issue with JDBC in general or the MS SQL JDBC driver in particular?
If I can't use an existing database as a service without jumping through hoops, what's the point?
So I just installed the JDBC Driver for MS SQL Server. Relatively straight forward to get Presto hooked up with a new Driver and DataSource. Now I want to publish a Database Service using an existing database and add a custom Finder as shown in the Training Video ( http://www.jackbe.com/enterprise-mashup/videos/mashup-fundamentals-unit-3-2 ) .
Click the "DB" button in Service Explorer to create a new Database Service
Select the Datasource I created above
Select the 'dbo' schema - I see a tabbed list of Tables, Views and Procedures
I select the "Customer" table and click Proceed - so far so good...
On the next wizard page, I select the "Customer" table in the tree view, and the Metadata section populates - I see a tabbed list of Columns and Finders.
After blindly going through the steps of the video tutorial using the known column 'lastname' ( I know the schema of this database pretty well ) and getting errors when tying to "Make Mashlet" from the service, I finally looked at the list of columns.
NONE of the nvarchar SQL Type columns are listed! I see INT, BIT, DATETIME, FLOAT, even MONEY types, but no nvarchar.
to test, I created a new table with varchar, char(50) and Nvarchar versions of the fields I was missing. Sure enough, the varchar and char versions of the columns are listed in "Publish Database Service", but the nvarchar versions are missing.
I guess the obvious solution is to create views that cast the nvarchars as varchar, but that just seems silly. Is there a way to make the very common nvarchar fields be recognized? Is this an issue with JDBC in general or the MS SQL JDBC driver in particular?
If I can't use an existing database as a service without jumping through hoops, what's the point?