Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Archive for the ‘asp’ Category

IIS SERVER 2016 – ERROS AMIGÁVEIS NO ASP – Microsoft-IIS/10.0

Posted by karuta em junho 26, 2018

Clicar em: “Erros Pages” (no IIS)

Botão direito: “Edit Feature Settings”

Marcar: Detailed erros

 

Anúncios

Posted in asp | Leave a Comment »

ASP PROBLEMAS EM MOSTRAR VARCHAR(MAX)

Posted by karuta em janeiro 17, 2018

DICA 1

Crie uma variável ASP com o conteúdo enviado pelo SQL SERVER e mostre a variável ao invés do que é enviado pelo banco.

EX: enviado pelo banco: (rsArquivos.Fields.Item(“html”).Value)

código:

html = (rsArquivos.Fields.Item(“html”).Value)
if len(trim(html)) <> 0 then %>
<strong><%=(html) %></strong>
end if

 

DICA 2 (mas temo um problema de erro nas SPs ainda não descobri o por que)

use em sua string de conexão ao invés de:  DRIVER={SQL Server}

 

Provider=SQLNCLI; quando sua versão for SQL2005
Provider=SQLNCLI10;  quando sua versão for SQL2008
Provider=SQLNCLI11;  quando sua versão for SQL2012
 
 

Posted in asp | Leave a Comment »

VERIFICAR PARAMETROS EM ADODB.Command PARA DEBUG

Posted by karuta em setembro 6, 2017

for each param in cmd.Parameters

response.write param.Name & “: ” & param.Value & “<br>”

next

Posted in asp | Leave a Comment »

ASP ADD – strip_tags()

Posted by karuta em junho 7, 2017

Strip all HTML/ASP/PHP tags from a string; essential for validating user input. (But not solely sufficient for validating user input. Use additional methods as appropriate.)

<%
 ' Copyright (c) 2008, reusablecode.blogspot.com; some rights reserved.
 '
 ' This work is licensed under the Creative Commons Attribution License. To view
 ' a copy of this license, visit http://creativecommons.org/licenses/by/3.0/ or
 ' send a letter to Creative Commons, 559 Nathan Abbott Way, Stanford, California
 ' 94305, USA.

' Strip HTML/ASP/PHP tags from a string.
 function strip_tags(unsafeString)
 dim regEx

set regEx = new RegExp
 
 with regEx
 .Global = true
 .IgnoreCase = true
 .Pattern = "(\<(/?[^\>]+)\>)"
 end with

strip_tags = regEx.Replace(unsafeString, "")

set regEx = nothing
 end function
%>

Posted in asp | Leave a Comment »

ASP ADD htmlspecialchars()

Posted by karuta em junho 7, 2017

ASP implementation of the PHP functions with the same names.

<%
‘ Copyright (c) 2009, reusablecode.blogspot.com; some rights reserved.

‘ This work is licensed under the Creative Commons Attribution License. To view
‘ a copy of this license, visit http://creativecommons.org/licenses/by/3.0/ or
‘ send a letter to Creative Commons, 559 Nathan Abbott Way, Stanford, California
‘ 94305, USA.

‘ Convert special characters to HTML entities.
function htmlspecialchars(someString)
‘ Critical that ampersand is converted first, since all entities contain them.
htmlspecialchars = replace(replace(replace(replace(someString, “&”, “&amp;”), “>”, “&gt;”), “<“, “&lt;”), “”””, “&quot;”)
end function

‘ Convert HTML entities to special characters.
function htmlspecialchars_decode(someString)
htmlspecialchars_decode = replace(replace(replace(replace(someString, “&amp;”, “&”), “&gt;”, “>”), “&lt;”, “<“), “&quot;”, “”””)
end function
%>

Posted in asp | Leave a Comment »

ASP ADD and strip slashes

Posted by karuta em junho 7, 2017

ASP implementation on the PHP functions by the same name, but with support for more problematic characters.

 

<%
 ' Copyright (c) 2009, reusablecode.blogspot.com; some rights reserved.
 '
 ' This work is licensed under the Creative Commons Attribution License. To view
 ' a copy of this license, visit http://creativecommons.org/licenses/by/3.0/ or
 ' send a letter to Creative Commons, 559 Nathan Abbott Way, Stanford, California
 ' 94305, USA.
 
 ' Despite the identical naming, these functions are more comprehensive than their PHP equivalents. 
 ' They go above and beyond even mysql_real_escape_string(), by including support for backspace and horizontal tab.
 
 ' List of characters handled:
 ' \000 null
 ' \010 backspace
 ' \011 horizontal tab
 ' \012 new line
 ' \015 carriage return
 ' \032 substitute
 ' \042 double quote
 ' \047 single quote
 ' \134 backslash
 ' \140 grave accent
 
 ' Returns a string with backslashes before characters that need to be quoted in database queries
 function addslashes(unsafeString)
 dim regEx

set regEx = new RegExp
 
 with regEx
 .Global = true
 .IgnoreCase = true
 .Pattern = "([\000\010\011\012\015\032\042\047\134\140])"
 end with
 
 addslashes = regEx.replace(unsafeString, "\$1")
 
 set regEx = nothing
 end function

' Un-quote string quoted with addslashes()
 function stripslashes(safeString)
 dim regEx

set regEx = new RegExp
 
 with regEx
 .Global = true
 .IgnoreCase = true
 .Pattern = "\\([\000\010\011\012\015\032\042\047\134\140])"
 end with
 
 stripslashes = regEx.replace(safeString, "$1")
 
 set regEx = nothing
 end function
%>

Posted in asp | Leave a Comment »

Send file data via JSON from REST API and open as download file in browser (with Classic ASP/JQuery)

Posted by karuta em junho 7, 2017

Pet project challenge of the day. I have a REST API that delivers a JSON response, and I want it to include file data, so the browser at the client end can download the file. In real world what I’m doing is offering a link to the user to export some data to an XLS and download to their desktop.

I messed around with this for a while. Carriage of the file data seemed simple – at the REST end encode it to base64 and stick that into the JSON. The problem lies at the client end. I have JQuery calling the REST, and dealing with the response. How then to get the base64 out of the JSON and contrive to offer it as a File Save As situation?

Finally here’s what I came up with, it was sparked by something I saw on StackOverflow, although I cannot lay my hands on the post right now:

At the REST, encode the XLS file text to base64, and return the base64 in the JSON as a text element

On the client, I have an ASP script that acts as the controller, it receives requests from the pages (eg JQuery calls this page), then it calls the remote REST, and returns the response back to JQuery. Obviously cross-browser rules means my JQuery can only call URLs on the same domain, and my REST is not. So this script acts as the transporter for the calls. It’s even called Frank.

On the page I have a form with a hidden field, JQuery puts the base64 file data into that hidden field, then submits the form to another page.

1
2
3
4
<form id="base64Form" action="openFileToBrowser.asp" method="post" name="base64Form">
<input id="base64data" type="hidden" name="base64data" value="" />
<input id="fileName" type="hidden" name="fileName" value="responses.xls" />
</form>

This new page grabs the file data, decodes it from base64 back to text, sets the content headers to XLS etc. And hey presto, the user gets a File Save As dialog. Here’s the code on that page:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% Option Explicit%>
<% response.expires = 0 %>
<!--#include virtual="/includes/hex_sha1_base64.asp" -->
<%
    dim base64data
    base64data=request.form("base64data")
    if base64data<>"" then
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "attachment; filename="&request.form("fileName")
    response.write Base64_Decode(base64data)
    response.Flush()
    end if
%>

The base64 encode/decode routine I’m using is part of the very handy hex_sha1_base64.asp file which I also use for other encryption and encoding tasks (for example I use it for encryption as part of talking to OAUTH based APIs).

I haven’t tested this in all browsers yet, be interesting to see if kicks up problems with IE etc.

Posted in asp, Jquery | 1 Comment »

Mystery of why isNumeric doesn’t work how you might think

Posted by karuta em junho 7, 2017

In Classic ASP there is the function isNumeric, which one uses to test if a value is a number. So for example:

Response.write IsNumeric("21")  // true
Response.write IsNumeric("David")  // false

Seems straightforward so far. Then you try this:

Response.write IsNumeric("15447D")  // true

Every so often this little quirk would popup. This morning it’s been driving me mad because I had a set of string values that were mixtures of alphanumeric, or numeric values. I wanted to discriminate between numbers and not-numbers and store the values in different columns in a database table. Using isNumeric though kept throwing errors – it tested 15447D as being a number, and of course once I tried to insert that into an integer column an error was thrown.

Finally figured how what’s happening after a little digging online. isNumeric also considers hex values to be valid numbers. 15447D is hex for 1393789. Now the lights go on – only wondered about this for 20 years.

Apparently this is also the case for octal values.

So a custom function along these lines is the answer:

Function myIsNumeric(ByVal Value)
	Dim regEx
	Set regEx = New RegExp
	regEx.pattern = "^(0|[1-9][0-9]*)$"
	myIsNumeric = Regex.Test(Value)
End Function

tks David Eedle

Posted in asp | Leave a Comment »

.NET – FASTEST WAY TO LOAD TEXT FILE TO SQL – SQLBULKCOPY

Posted by karuta em fevereiro 7, 2017

An exercise in coding. Loading a text file to SQL. There are a billion different ways to do it, and depending on your source data format and such, you can parse the file a million ways too. But how fast can you get data from disk to SQL using .NET code? (VB.NET or C#) . This post is going to show the differences in some of the ways you can load data from disk to SQL.

I am sure I could do more, but this is a good sampling. Lets assume a 1,00,000 row file, comma separated with 3 columns, string, int, string of variable length. Lets assume our destination is SQL Server 2005, table already created, no keys or anything on the table.

We will call our table LoadedData. Our test app will be a VB.NET Console Application, running on the same box as SQL 2005 is loaded. Now, there are many ways to load files. A few are: Reading them line by line, ReadToEnd() and also using the JET engine to read in a CSV, etc. From the testing I have been doing, all of these seem to work fairly fast, maybe a comparison on these is for another blog post, but for brevity’s sake, lets just say they are all comparable. Now, I chose 3 methods of inserting data.

1) StreamReader.ReadLine, Insert Line By Line

Sub Method1()
Dim i As Long = 0

Dim sr As StreamReader = New StreamReader(filename)
Dim line As String = sr.ReadLine()
Dim dbConn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings(“MyDB”).ToString())
Dim dbCmd As SqlCommand = New SqlCommand()
dbCmd.Connection = dbConn

Dim wholeFile As String = sr.ReadToEnd()

Do
Dim fields() As String = line.Split(“,”)

dbCmd.CommandText = “INSERT INTO dbo.TestData (Column1,Column2,Column3) ” & _
” VALUES (‘” & fields(0) & “’,” & fields(1) & “,’” & fields(2) & “’)”

dbConn.Open()
dbCmd.ExecuteNonQuery()
dbConn.Close()
i = i + 1
line = sr.ReadLine()

Loop While Not line = String.Empty

End Sub

2) StreamReader.ReadLine, Batch Insert With DataAdapter

Sub Method2()
Dim i As Long = 0
Dim dbConn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings(“MyDB”).ToString())
Dim sr As StreamReader = New StreamReader(filename)
Dim line As String = sr.ReadLine()

Dim strArray As String() = line.Split(“,”)
Dim dt As DataTable = New DataTable()
Dim row As DataRow

For Each s As String In strArray
dt.Columns.Add(New DataColumn())
Next

Do

row = dt.NewRow()
row.ItemArray = line.Split(“,”)

dt.Rows.Add(row)

i = i + 1
line = sr.ReadLine()

Loop While Not line = String.Empty

Dim dataAdapter As New SqlDataAdapter()

dataAdapter.SelectCommand = New SqlCommand(“SELECT TOP 1 Column1,Column2,Column3 from dbo.TestData”, dbConn)

Dim cmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataAdapter)

dbConn.Open()

Dim ds As DataSet = New DataSet
dataAdapter.Fill(dt)

dataAdapter.UpdateBatchSize = 1000
dataAdapter.Update(dt)

dbConn.Close()
End Sub

3) StreamReader.ReadLine, SqlBulkCopy

Sub Method3()
Dim i As Long = 0
Dim dbConn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings(“MyDB”).ToString())
Dim sr As StreamReader = New StreamReader(filename)
Dim line As String = sr.ReadLine()

Dim strArray As String() = line.Split(“,”)
Dim dt As DataTable = New DataTable()
Dim row As DataRow

For Each s As String In strArray
dt.Columns.Add(New DataColumn())
Next

Do

row = dt.NewRow()
row.ItemArray = line.Split(“,”)

dt.Rows.Add(row)

i = i + 1
line = sr.ReadLine()

Loop While Not line = String.Empty

Dim bc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.TableLock, Nothing)
bc.DestinationTableName = “TestData”

bc.BatchSize = dt.Rows.Count

dbConn.Open()
bc.WriteToServer(dt)
dbConn.Close()
bc.Close()
End Sub

The results of the 3 methods are surprising. The thing is, most people are going to use Method1 because it just is the first thing you think of doing, and maybe the easiest to code (everyone learns loops in school, etc) – now, nitpickers will say “use a stored proc” etc – that will save minimal time, and in best practice yes, but for the sake of the example bear with it..

Method2 is less intuitive, and really tricky to get working (at least I had some issues with it) but once it works, it makes a little bit more sense then Method1.

Method3 is something that no one ever hears or uses, but once they do, they never go back.

Side note: about 5 years ago I worked on a program that inserted huge files, and they were taking 10-20 minutes a piece. I was using VB6, and converted the line by line insert to use BCP from code and got it down to 2-3 minutes, which was good. So I know about BCP and BULK INSERT. I just didn’t know it was built into .NET, now I do..anyways, on to the results.

Method 1- 14.69 minutes to insert 1 million records

Method 2 – 7.88 minutes to insert 1 million records

Method 3 – 0.28 minutes to insert 1 million records

So yeah. Wow. That is not a typo for Method 3. Roughly 17 seconds. Now, give Method2 so credit, it reduced the time from Method1 by 50% but Method3 just KILLS them both. Man, just awesome. When you run something like that and see that kind of performance, you can’t help but smile.

A few caveats and gotchas:

Method2 – the BatchSize property I have set to 1000. If you set to 0 it uses the max. I tried this and locked my machine up. Yikes.

Method3 – The SqlBulkCopyOptions makes a difference – TableLock speeds up the operation. The BatchSize here I have set to the # of rows, It might run differently with different batch sizes, I really didn’t experiment with it, but adding the Copy Options and BatchSize, it sped up the operations.

So, the fastest way I have found in .NET to load data from files to sql – hands down – SqlBulkCopy. Since it took 17 seconds, The next step is to try different file reading methods and see what time times are there, like I said, maybe that will be my next post. 🙂

p.s. yes I am up at 2:00 AM posting this, insomnia anyone? In any event I got to watch the repeat of the Nevada Democratic Debate 🙂

Posted in asp | Leave a Comment »

ASP Inverse Function Server.HTMLEncode (string)

Posted by karuta em outubro 21, 2016

<%
Private Function HTMLDecode(byVal encodedstring)
Dim tmp, i
tmp = encodedstring
tmp = Replace( tmp, “%20″, ” ” )
tmp = Replace( tmp, “%22″,”””” )
tmp = Replace( tmp, “&quot;”, chr(34) )
tmp = Replace( tmp, “&lt;” , chr(60) )
tmp = Replace( tmp, “&gt;” , chr(62) )
tmp = Replace( tmp, “&amp;” , chr(38) )
tmp = Replace( tmp, “&nbsp;”, chr(32) )
For i = 1 to 255
tmp = Replace( tmp, “&#” & i & “;”, chr( i ) )
Next
HTMLDecode = tmp
End Function
%>

Posted in asp | Leave a Comment »