Zip Component, Email Component, Encryption Component ActiveX Control for Zip Compression .NET Components for ASP.NET
ActiveX and .NET Components for Zip Compression, Encryption, Email, XML, S/MIME, HTML Email, Character Encoding, Digital Certificates, FTP, and more ASP Email ActiveX Component

Index of Chilkat Blog Posts

March 28, 2008

Shift_JIS File –> String –> SQL Server DB –> String –> Shift_JIS File

This Visual Basic 6.0 example demonstrates a full round-trip from Shift_JIS encoded file to SQL Server and back.

A text file containing Japanese characters encoded in Shift_JIS is loaded into a VB6 Unicode string. (Note: all VB6 strings are Unicode. If you have a "String", you have Unicode.) It then inserts the string into a SQL Server database table. The DB field type is nvarchar. One big gotcha is the "N" notation when writing the SQL Insert statement. The "N" notation must be used for all Unicode strings (strings with non-ANSI characters). Next, the Japanese text is retrieved from the database back into a String variable (using a SQL Select statement). Finally the String is converted back to Shift_JIS bytes and saved to a file (which should be identical to the original file).

Option Explicit
Dim dbDbase As ADODB.Connection
Dim rsRecSet As ADODB.Recordset
Dim cc As New ChilkatCharset2
Private Sub Command5_Click()
    Set dbDbase = New Connection
    Set rsRecSet = New Recordset
    ' Connect to our SQL Server database
    Const sConnectionString = "Provider=sqloledb;Data Source=Ck2007;Initial Catalog=chilkat;User Id=sa;Password=admin123;"
    dbDbase.CursorLocation = adUseClient
    dbDbase.Open sConnectionString
    ' Unlock the Chilkat Charset component
    Dim success As Long
    success = cc.UnlockComponent("Anything for 30-day trial")
    If (success = 0) Then
        MsgBox "unlock failed."
        Exit Sub
    End If
    ' Our input file contains Japanese characters encoded using the
    ' Shift_JIS charset.  The ReadFile method reads the data unmodified
    ' into a Variant (containing the byte data).
    Dim fileData As Variant
    fileData = cc.ReadFile("japaneseText.txt")
    ' Convert from Shift_JIS bytes to a Unicode string:
    Dim unicodeStr As String
    cc.FromCharset = "Shift_JIS"
    unicodeStr = cc.ConvertToUnicode(fileData)
    ' Display the string in a Forms 2.0 (Unicode-capable textbox):
    TextBox1.Text = unicodeStr
    ' Insert the Japanese text into a SQL Server database table.
    ' Note, our database field is of type nvarchar.  You must use nvarchar
    ' and not varchar when storing Unicode.
    ' Use the "N" prefix to tell ADO to treat the string as Unicode.
    ' see:
    ' The database field name (i.e. column name) is "myNVarchar"
    dbDbase.Execute "Insert into TestTable (myNVarChar) values " & _
    " (N'" & unicodeStr & "')"
    ' Retrieve the Japanese text from the SQL Server database back into
    ' a Unicode string:
    Dim unicodeStr2 As String
    With rsRecSet
        .Open "Select * from TestTable", dbDbase, adOpenKeyset, adLockOptimistic
        unicodeStr2 = .Fields("myNVarChar")
    End With
    ' TextBox2 is also a Unicode-capable Forms 2.0 TextBox:
    TextBox2.Text = unicodeStr2
   ' Now save the string to an output file containing
   ' characters encoded using Shift_JIS
    Dim fileData2 As Variant
    ' Convert from Unicode to Shift_JIS:
    cc.ToCharset = "Shift_JIS"
    fileData2 = cc.ConvertFromUnicode(unicodeStr2)
    ' Save Shift_JIS bytes to a file.
    success = cc.WriteFile("out.txt", fileData2)
    If (success = 0) Then
        MsgBox cc.LastErrorText
        MsgBox "Success."
    End If
End Sub

Privacy Statement. Copyright 2000-2011 Chilkat Software, Inc. All rights reserved.
Send feedback to

Components for Microsoft Windows XP, 2000, 2003 Server, Vista, Windows 7, and Windows 95/98/NT4.