Problems with runnig UTF-8 encoded sql files in Classic ASP

Question

I have a bunch of sql files with queries that need no be run on my local database each time there are some changes in those files. We use classic ASP code to run these files in a loop written on VBScript, like this:

Dim arrSqlLines()
i = 0
set t = fso.OpenTextFile(filePath, 1, false)
Do Until t.AtEndOfStream
    Redim Preserve arrSqlLines(i)
    arrSqlLines(i) = t.ReadLine
    i = i + 1
Loop
t.close

For Each sqlLine in arrSqlLines
    sqlLine = Trim(sqlLine)
    sqlBatch = Trim(sqlBatch & sqlLine) & vbCrLf
    Call dbexecConnection(sqlBatch, objDbConnection)
Next

Function dbexecConnection(sql, objConnExec)
    dim cmdTemp
    on Error Resume Next
    set cmdTemp=Server.CreateObject("ADODB.Command")
    set cmdTemp.ActiveConnection=objConnExec
    cmdTemp.CommandText = sql

    cmdTemp.Execute
    if err.number<>0 Then
        if Session("SkipError") <> -1 Then
            response.write "Error in dbexecute: " & sql & "<br/>"
            response.write "Error=(" & err.description & ")"
            response.End
        end If
    end If
    on error goto 0
End Function

The problem is that if a sql file is ecoded in UTF-8 without BOM it runs it OK, but if any file is encoded in UTF-8 format, it produces an error:

For example, this very sql file starts like this:

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('RPM_GET_ACTUAL_COST_FOR_EPIC') IS NOT NULL
    DROP Function [RPM_GET_ACTUAL_COST_FOR_EPIC]
GO

CREATE Function [RPM_GET_ACTUAL_COST_FOR_EPIC]
(
    @EpicID as int,
    @RateType as Int, -- 1 for Blended, 2 for CostCenter
    @CalcType as Int -- 1 for by Hours or 2 for Points
)
returns float
BEGIN

declare @Cost float
declare @CostX float

declare @ItStorys TABLE (
    StorylD int,
    State int,
    DemoStatus int,
    Dfficulty int,
    Feature int,
    TeamID int,
    TrackType int,
    Iteration int
    )

insert into @tStorys(StoryID, State, DemoStatus, Dfficulty, Feature, TeamID, TrackType, Iteration)

I cannot guarantee that all files will be encoded in UTF-8 without BOM so I have to find the way to make it run correctly files with UTF-8 as well. how can i possibly do that?

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Just need to tell Classic ASP and IIS that you are wanting to process the ASP page using UTF-8.

First save the ASP page making sure it is saved using Code Page 65001 (if using Visual Studio this can be done through the Advanced Save Options menu option).

Then modify the ASP page to include the following initial lines.

<%@Language="VBScript" CodePage = 65001 %>
<%
'Assuming this is the whole script the above line MUST always
'be the very first line in the source file.
'Tell ASP strings should be returned UTF-8 encoded.
Response.CodePage = 65001
'Tell the Browser to expect UTF-8 encoded data.
Response.Charset = "UTF-8"

Dim arrSqlLines()
i = 0
set t = fso.OpenTextFile(filePath, 1, false)
Do Until t.AtEndOfStream
    Redim Preserve arrSqlLines(i)
    arrSqlLines(i) = t.ReadLine
    i = i + 1
Loop
t.close

For Each sqlLine in arrSqlLines
    sqlLine = Trim(sqlLine)
    sqlBatch = Trim(sqlBatch & sqlLine) & vbCrLf
    Call dbexecConnection(sqlBatch, objDbConnection)
Next

Function dbexecConnection(sql, objConnExec)
    dim cmdTemp
    on Error Resume Next
    set cmdTemp=Server.CreateObject("ADODB.Command")
    set cmdTemp.ActiveConnection=objConnExec
    cmdTemp.CommandText = sql

    cmdTemp.Execute
    if err.number<>0 Then
        if Session("SkipError") <> -1 Then
            response.write "Error in dbexecute: " & sql & "<br/>"
            response.write "Error=(" & err.description & ")"
            response.End
        end If
    end If
    on error goto 0
End Function
%>

Useful Links

Convert UTF-8 String Classic ASP to SQL Database

The problem is about failing sql commands starting with BOM. Changing CodePage nor saving ASP file UTF-8 encoded will not prevent SQL syntax error. — Kul-Tigin, Jan 17 '16 at 06:50

Kul-Tigin · Accepted Answer · 2016-01-17T06:52:32.490

It is certain that FileSystemObject does not handle UTF-8 but Unicode and ANSI.
ADODB.Stream can handle a lot of character sets including utf-8 so you can use it instead.
Replace your code up to the first For with the following.

Dim arrSqlLines
With Server.CreateObject("Adodb.Stream")
    .Charset = "utf-8"
    .Open
    .LoadFromFile filePath
    If .EOS Then
        'an empty array if file is empty
        arrSqlLines = Array()
    Else
        'to obtain an array of lines like you desire
        'remove carriage returns (vbCr) if exist 
        'and split the text by using linefeeds (vbLf) as delimiter
        arrSqlLines = Split(Replace(.ReadText, vbCr, ""), vbLf)
    End If
    .Close
End With

Problems with runnig UTF-8 encoded sql files in Classic ASP

2 Answers2

Useful Links