Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
SQL server CLOB colums (Read 20471 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
SQL server CLOB colums
30.10.09 at 10:16:29
 
SQL server CLOB colums
 
CLOB (Character Large Object) is a large collection of character data.  
Different database servers handles CLOB differently.  
 
But there are some common characteristics like:

  • CLOB stores character data
  • CLOB data is usually large.  
    Many database servers offer very high maximum sizes like 4 GB.
  • Operations on CLOB data is usually restricted.  
    For example, LIKE may not be used on CLOB data.

 
SQL Server support CLOB with the regular VARCHAR data types. When creating a CLOB column you need to specify a special length value called MAX.  
Example to create CLOB columns in SQL Server 2005:
create table myclobtest( clob_column varchar(max) );
 
To insert data into a CLOB column you can use the SQL*XL insert features so you do not need to write any SQL yourself. However if you want to you can execute an insert statement to insert data into CLOB columns.
Example:
insert into clob_test2
(      [clob_varbinary]
,      [clob_varchar] )
values
( convert(varbinary(max),'some data' )
, convert(varchar(max) ,'some other data' )
);
 
 
To retrieve data from CLOB columns you can just query the table. Even just a select * from table works. Please note that varbinary columns are treated as binary and the data will be shown as binary data.
 
SQL*XL is confirmed to work ok with CLOB columns in SQL Server. Using SQL*XL it is very easy to use CLOB columns in Excel.
For more information about SQL*XL see www.oraxcel.com/projects/sqlxl
 
See also:
(Oracle) How to Insert CLOB values from Excel into the database  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1148046982
Bytes retrieved from CLOB  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1252325586
CLOB - Wikipedia
http://en.wikipedia.org/wiki/Character_large_object
 
Source:
SQL Server CLOB (Character Large Object) - TEXT
http://www.herongyang.com/jdbc/SQL-Server-CLOB-Large-Object.html
Back to top
 
« Last Edit: 30.10.09 at 11:03:24 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
SQL server 2008 CLOB colums
Reply #1 - 30.10.09 at 15:00:18
 
SQL server 2008 CLOB colums
 
In SQL Server 2008 CLOB columns can be also of type nvarchar(max) in addition to the varchar(max) and varbinary(max) types.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1