Situation:
We had to make our SQLServer 2000 database multi-lingual. That is, certain
things (such as product names) in the database should be shown in the
language the user is using (Finnish, Swedish or English). There are about
a dozen tables with columns that need localization.
Doing this in the application level was a no-goer. It would have taken far
too much time (there is a *lot* of code and unfortunately most of the
multi-lingual tables are very central to the system).
Solution:
The easiest solution we could think of was to create views with similar
names to the tables that included columns that need to be multi-lingual
and create tables holding the different multi-lingual columns. See the
examples on how we did this (notice that we also have UPDATE and DELETE
triggers not included here).
We did run into some problems, namely SQLServer's notation for outer joins
(*= and =*) wouldn't work with the views. So we had to remove those from
queries that used one of the multi-lingual tables. Thankfully there
weren't too many places where this problem occured so I fixed them in
about two days.
Additional testing in Query Analyzer looked good: we could insert, update
and select rows through the views.
Then came the bummer. Running one of our applications and inserting values
using ADODB.Recordset gave the very helpful "Multi-step OLE DB operation
caused an error" message. I have commented the code below to show where
the error occurs. I included line numbers for clarity.
Fixing every place where we use this notation is not really an option
either.
How to reproduce the error:
1. Create a database user and a login name, both named "england".
2. Run the SQL scripts in Query Analyzer.
3. Create a VB exe project.
4. Add a command button to to the form.
5. Paste the code to the form's code window. Fix the parameter for
ADODB.Connectio n() function (line 20).
6. Add a breakpoint to the start of the DoStuff() function.
7. Walk through the code with F8 and observe where the error happens (line
130).
My analysis:
As far as I can tell, the problem within the Microsoft Cursor Engine. I
think the problem is that MCE is too smart, and therefore can't figure out
which table it should use in the insertion. The error I get is "The value
violated the data source schema constraint for the field", and originator
was MCE.
So, is there a way around this?
Or is there some other way of doing what we are trying to do?
Lastly: I have several silly looking restrictions using views in
SQLServer. As I'm not familiar with other databases I'm curious if these
are universal restrictions, or just lazy programming at Microsoft? For
instance, combining tables to create a view has many restrictions, and
inserting or updating through these views is quite tricky.
SQL Queries:
CREATE TABLE dbo.TestTable
(
ID Int Identity(1,1) NOT NULL,
Name Varchar(10) NOT NULL,
Misc Varchar(10),
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
)
GO
CREATE TABLE england.TestTab le_LT
(
ID Int NOT NULL,
Name Varchar(10),
CONSTRAINT [KF_LT_TestTable] FOREIGN KEY
(
[ID]
) REFERENCES dbo.TestTable (
[ID]
)
)
GO
CREATE VIEW england.TestTab le
AS
SELECT ID = CASE TBL.ID
WHEN -8 THEN NULL
ELSE TBL.ID
END,
CASE WHEN LT.Name IS NOT NULL
THEN LT.Name
ELSE TBL.Name
END As Name,
TBL.Misc
FROM dbo.TestTable TBL
LEFT JOIN england.TestTab le_LT LT ON LT.ID = TBL.ID
GO
CREATE TRIGGER [england].trg_TestTable_ LT_insert
ON [england].[TestTable] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.TestTable(N ame, Misc) SELECT Name, Misc FROM inserted
INSERT INTO england.TestTab le_LT (ID, Name) SELECT SCOPE_IDENTITY( ), Name
FROM inserted
END
GO
VB Code:
Option Explicit
Private Sub Command1_Click( )
DoStuff
End Sub
Private Sub DoStuff()
Dim conn As ADODB.Connectio n
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
10 Set conn = New ADODB.Connectio n
' Fix following line:
20 conn.Open
"PROVIDER=SQLOL EDB;SERVER=w2kt s1;DATABASE=Rah ti16;UID=sverig e;PWD=sverige"
' METHOD 1
30 Set cmd = New ADODB.Command
40 cmd.ActiveConne ction = conn
' The following insert works as imagined
50 cmd.CommandText = "INSERT INTO TestTable(Name,
Misc) VALUES('Foo', 'Bar')"
60 cmd.Execute
' METHOD 2 - Does not work
70 Set rst = New ADODB.Recordset
80 rst.CursorLocat ion = adUseServer
90 rst.Open "SELECT * FROM TestTable WHERE ID = 0", conn,
adOpenDynamic, adLockOptimisti c
100 With rst
110 .AddNew
120 !Misc = "Foo" ' This works
130 !Name = "Bar" ' But this bombs
140 .Update
150 End With
End Sub
--
Marko Poutiainen | These are my principles.
mep@paju.oulu.f i | If you don't like them, I have others.
http://www.toffeeweb.com | -Groucho Marx
We had to make our SQLServer 2000 database multi-lingual. That is, certain
things (such as product names) in the database should be shown in the
language the user is using (Finnish, Swedish or English). There are about
a dozen tables with columns that need localization.
Doing this in the application level was a no-goer. It would have taken far
too much time (there is a *lot* of code and unfortunately most of the
multi-lingual tables are very central to the system).
Solution:
The easiest solution we could think of was to create views with similar
names to the tables that included columns that need to be multi-lingual
and create tables holding the different multi-lingual columns. See the
examples on how we did this (notice that we also have UPDATE and DELETE
triggers not included here).
We did run into some problems, namely SQLServer's notation for outer joins
(*= and =*) wouldn't work with the views. So we had to remove those from
queries that used one of the multi-lingual tables. Thankfully there
weren't too many places where this problem occured so I fixed them in
about two days.
Additional testing in Query Analyzer looked good: we could insert, update
and select rows through the views.
Then came the bummer. Running one of our applications and inserting values
using ADODB.Recordset gave the very helpful "Multi-step OLE DB operation
caused an error" message. I have commented the code below to show where
the error occurs. I included line numbers for clarity.
Fixing every place where we use this notation is not really an option
either.
How to reproduce the error:
1. Create a database user and a login name, both named "england".
2. Run the SQL scripts in Query Analyzer.
3. Create a VB exe project.
4. Add a command button to to the form.
5. Paste the code to the form's code window. Fix the parameter for
ADODB.Connectio n() function (line 20).
6. Add a breakpoint to the start of the DoStuff() function.
7. Walk through the code with F8 and observe where the error happens (line
130).
My analysis:
As far as I can tell, the problem within the Microsoft Cursor Engine. I
think the problem is that MCE is too smart, and therefore can't figure out
which table it should use in the insertion. The error I get is "The value
violated the data source schema constraint for the field", and originator
was MCE.
So, is there a way around this?
Or is there some other way of doing what we are trying to do?
Lastly: I have several silly looking restrictions using views in
SQLServer. As I'm not familiar with other databases I'm curious if these
are universal restrictions, or just lazy programming at Microsoft? For
instance, combining tables to create a view has many restrictions, and
inserting or updating through these views is quite tricky.
SQL Queries:
CREATE TABLE dbo.TestTable
(
ID Int Identity(1,1) NOT NULL,
Name Varchar(10) NOT NULL,
Misc Varchar(10),
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
)
GO
CREATE TABLE england.TestTab le_LT
(
ID Int NOT NULL,
Name Varchar(10),
CONSTRAINT [KF_LT_TestTable] FOREIGN KEY
(
[ID]
) REFERENCES dbo.TestTable (
[ID]
)
)
GO
CREATE VIEW england.TestTab le
AS
SELECT ID = CASE TBL.ID
WHEN -8 THEN NULL
ELSE TBL.ID
END,
CASE WHEN LT.Name IS NOT NULL
THEN LT.Name
ELSE TBL.Name
END As Name,
TBL.Misc
FROM dbo.TestTable TBL
LEFT JOIN england.TestTab le_LT LT ON LT.ID = TBL.ID
GO
CREATE TRIGGER [england].trg_TestTable_ LT_insert
ON [england].[TestTable] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.TestTable(N ame, Misc) SELECT Name, Misc FROM inserted
INSERT INTO england.TestTab le_LT (ID, Name) SELECT SCOPE_IDENTITY( ), Name
FROM inserted
END
GO
VB Code:
Option Explicit
Private Sub Command1_Click( )
DoStuff
End Sub
Private Sub DoStuff()
Dim conn As ADODB.Connectio n
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
10 Set conn = New ADODB.Connectio n
' Fix following line:
20 conn.Open
"PROVIDER=SQLOL EDB;SERVER=w2kt s1;DATABASE=Rah ti16;UID=sverig e;PWD=sverige"
' METHOD 1
30 Set cmd = New ADODB.Command
40 cmd.ActiveConne ction = conn
' The following insert works as imagined
50 cmd.CommandText = "INSERT INTO TestTable(Name,
Misc) VALUES('Foo', 'Bar')"
60 cmd.Execute
' METHOD 2 - Does not work
70 Set rst = New ADODB.Recordset
80 rst.CursorLocat ion = adUseServer
90 rst.Open "SELECT * FROM TestTable WHERE ID = 0", conn,
adOpenDynamic, adLockOptimisti c
100 With rst
110 .AddNew
120 !Misc = "Foo" ' This works
130 !Name = "Bar" ' But this bombs
140 .Update
150 End With
End Sub
--
Marko Poutiainen | These are my principles.
mep@paju.oulu.f i | If you don't like them, I have others.
http://www.toffeeweb.com | -Groucho Marx