Skip to main content

Posts

Showing posts with the label SQL

MS SQL Server Views

"Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:

- To focus, simplify, and customize the perception each user has of the database.
- As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
- To provide a backward compatible interface to emulate a table whose schema has changed." [1]

Beside that, our team used view in order to improve the performance of our web apps when a database has a very complicated relationship between its tables by using ORM Frameworks such as Hibernate.

Example code:
--create CREATE VIEW placeholders AS SELECT EMPKEY AS empkey, CONNUMB AS connumb, EMPNBR AS empNbr, ACEEMPN AS empFirstName, ACEEMPFN AS empLastName, EMPNAM AS empFullName, EMPSIGN AS …

SQL Server - Multi language data support

Problem:

I used CKEditor to edit my pages. Then, I stored the content of the pages to SQL Server 2008 database. The page contents should support in different languages such as English, German and Vietnamese. I met a problem when I worked on Vietnamese pages.
The text was successfully saved but it was displayed wrong when reading again from database, likes the following:
Input: "Ðây là Tiếng Việt"
Display: "Ðây là Ti?ng Vi?t".

Solution:

We need to use nvarchar (nchar | ntext) data type for strings  and we also need to precede all unicode strings with 'N'

Here is my code:
--create table CREATE TABLE [dbo].[xedu_page_content]( [id] [numeric](11, 0) IDENTITY(1,1) NOT NULL, [category] [varchar](100) NOT NULL PRIMARY KEY, [content] [Ntext] NULL ) --save BEGIN TRAN IF not exists (SELECT category FROM xedu_page_content WHERE category='"+in.categoryKey+"') INSERT INTO xedu_page_content(category,content) VALUES ('"+i…