Submit Input to an ASP Web Page and Retrieve the Result using VBS

17 01 2010

January 17, 2010

While it is helpful that SQL statements may be submitted directly from VBS scripts, in most cases the username and password for the database user will be exposed in the VBS script.  So, we need another way.  How about having the VBS script pass a value of interest to an ASP web page (note that this is not ASP.Net, this is old style programming without a Net).  First, we need to create the ASP web page (after, of course, enabling ASP in Microsoft’s IIS web server configuration):

<html>
<head>
<title>I am a Hidden Web Page - You will Not See Me</title>
</head>
<body>
   <%
    Dim adVarChar
    Dim adParamInput
    Dim adCmdText
    Dim strSQL
    Dim snpData
    Dim comData
    Dim strPartID

    Dim dbDatabase

    adVarChar = 200
    adParamInput = 1
    adCmdText = 1
    Set dbDatabase = Server.CreateObject("ADODB.Connection")
    Set comData = Server.CreateObject("ADODB.Command")
    Set snpData = Server.CreateObject("ADODB.Recordset")

    On Error Resume Next

    strPartID = cStr(Request("strPartID"))

    dbDatabase.Open "Provider=MSDAORA.1;User ID=MyUser;Password=MyPassword;Data Source=MyDB;Persist Security Info=True"

    With ComData
        strSQL = "SELECT /*+ LEADING(IT) INDEX(IT X_INV_TRANS_1) */" & vbCrLf
        strSQL = strSQL & "  IT.PART_ID," & vbCrLf
        strSQL = strSQL & "  TRUNC(SUM(DECODE(IT.CLASS,'I',DECODE(IT.TYPE,'O',IT.QTY,0),0))-SUM(DECODE(IT.CLASS,'I',DECODE(IT.TYPE,'I',IT.QTY,0),0))+.9999) AS NEW_ANNUAL_USAGE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  INVENTORY_TRANS IT," & vbCrLf
        strSQL = strSQL & "  PART P" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  IT.TRANSACTION_DATE>TRUNC(SYSDATE-365)" & vbCrLf
        strSQL = strSQL & "  AND P.ID=IT.PART_ID" & vbCrLf
        strSQL = strSQL & "  AND P.ID= ?" & vbCrLf
        strSQL = strSQL & "GROUP BY" & vbCrLf
        strSQL = strSQL & "  IT.PART_ID" & vbCrLf
        '
        .Parameters.Append .CreateParameter("part_id", adVarChar, adParamInput, 30, strPartID)
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase
    End With
    Set snpData = ComData.Execute

    Response.Write "<input type=""text"" name=""txtPartID"" size=30 value=""" & strPartID & """ disabled=true>"
    If Not (snpData Is Nothing) Then
        If Not(snpData.EOF) Then
            Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""" & cstr(snpData("new_annual_usage")) & """ disabled=true>"
            Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""RETRIEVED"" disabled=true>"
        Else
            Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""0"" disabled=true>"
            Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""NO TRANSACTIONS"" disabled=true>"
        End If
    Else
        Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""0"" disabled=true>"
        Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""ERROR"" disabled=true>"
    End If

    snpData.Close
    dbDatabase.Close

    Set snpData = Nothing
    Set comData = Nothing
    Set dbDatabase = Nothing
    %>
</body>
</html>

OK, reading the ASP web page code, we create an ADO database connection object, an ADO recordset object, and an ADO command object.  Next, we set the strPartID variable to the value of the passed in strPartID variable from the web session, build a SQL statement with a bind variable set to the value of the strPartID variable, and then execute the SQL statement.  If the SQL statement successfully executed, we build two HTML text box, the first with the value of NEW_ANNUAL_USAGE, and the second with a status of either RETRIEVED or NO TRANSACTIONS.  If the SQL statement failed to execute, the two HTML text boxes will contain 0 and ERROR.

Now for the VBS script that will call the ASP web page:

Dim intResult
Dim objIE
Dim strHTML
Dim strID
Dim ANNUAL_USAGE_QTY

On Error Resume Next

Set objIE = CreateObject("InternetExplorer.Application")

strID = "ABCDEF123456"
ANNUAL_USAGE_QTY = 100

objIE.Navigate "http://localhost/Update_Annual_Usage_Qty.asp?strPartID=" & strID

objIE.Width=100
objIE.Height=100
objIE.Statusbar=False
objIE.Menubar=False
objIE.Toolbar=False
objIE.Visible = False

Do While objIE.Busy <> False
    WScript.Sleep 200
Loop

'loop until the button is clicked
Do While intFlag = 0
    If Err <> 0 Then
        IntFlag = -1
    End If   
    If objIE is Nothing Then
        'User closed ID
        intFlag = -1
    Else
        If objIE.Document.All.txtOK.Value <> " " Then
            intFlag = 1
        End If
    End If
    WScript.Sleep 200
Loop

If intFlag = 1 Then
    If objIE.Document.Body.All.txtOK.Value = "ERROR" Then
        MsgBox "Error sending the query to the database"
    Else
        If objIE.Document.Body.All.txtOK.Value = "NO TRANSACTIONS" Then
            intResult = MsgBox ("No transactions for this part in the last year, OK to set the annual usage qty to 0?  The old value is " & cStr(ANNUAL_USAGE_QTY), vbQuestion + vbYesNo, "Annual Usage")
            If intResult = vbYes Then
                ANNUAL_USAGE_QTY = 0
            End If
        Else
            'Copy in the values from the web page
            intResult = MsgBox ("The old annual usage quantity value is " & cStr(ANNUAL_USAGE_QTY) & " - the database indicates that the updated quantity should be " & cstr(objIE.Document.Body.All.txtAnnualUsage.Value) & ".  Would you like to update the annual usage quantity?", vbQuestion + vbYesNo,"Annual Usage")
            If intResult = vbYes Then
                 ANNUAL_USAGE_QTY = objIE.Document.Body.All.txtAnnualUsage.Value
            End If
        End If
    End If
    objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing

The VBS script launches the ASP page in a hidden Internet Explorer window, passing in the value of strID on the address line (this is picked up in the ASP script as the strPartID session variable).  The VBS script then waits until the ASP page finishes loading.  Once the ASP page finishes, the VBS script reads the values of the two HTML text boxes and acts appropriately based on the values of those text boxes.

The neat thing about straight ASP programming code is that it looks a lot like the VBS programming code, and that looks a lot like the Excel macro programming code, and that looks a lot like the classic Visual Basic programming code, and that kind of looks like the classic BASIC programming code that I started working with in 1981/1982.  I have been sitting in on the technology training advisory committee for one of the local colleges.  The committee helps determine what computer classes will be taught to earn a degree at the college.  The question was asked what languages to teach – I heard C++ and Java being suggested… I wonder if I should have suggested Visual Basic?  VBS like languages are also used as macro languages in some ERP products and other packages (I believe that AutoCAD uses a similar macro syntax, as does PC-DMIS).


Comments : Leave a Comment »

Categories : HTML, VBS

If I Need to Fetch My Rows Faster, Is There Any Way?

17 01 2010

January 17, 2010

Yes, the title of this blog article is the question, the whole question, and nothing but the question from this OTN post:
http://forums.oracle.com/forums/thread.jspa?threadID=1013283&tstart=0

The OP stated in the subject line that his query needed to retrieve 1 lakh rows, which I assumed meant 100,000,000 rows, but a Google search indicates that it is just 100,000 rows.

One of the responders went in for the kill with this response:

The most precise way for fetching rows faster can be attained in number of ways.

  1. The first way is apply indexes and in case indexes got large number of deletions then rebuild it.
  2. The next way is the optimizer you are choosing.

Literaly these parameters are effective then this thing will automatically lead to faster fetching.

I was a bit confused by the above response (I dislike being confused).  So, I asked that responder for clarification of the suggestions for improving the precise way of fetching rows faster (for some reason, the phrase “Battle Against Any Guess” popped into my head).

  1. Are you suggesting that the OP should rebuild indexes to improve how quickly Oracle is able to find rows when there were a lot of deletions in the table? There is a fun series of blog articles here that might help before the OP attempts to rebuild indexes: http://richardfoote.wordpress.com/category/index-rebuild/
  2. Are you suggesting that the OP switch between the RULE based optimizer and the COST based optimizer (or vice-versa)?

I then offered the following to the original poster:

  1. What about changing the array fetch size (number of rows fetched in a single fetch request)?
  2. Why are you selecting so many rows – will a large number of the rows be eliminated in the client-side application. Is it possible to reduce the number of rows returned from the database by aggregating the data, filtering the data, or processing the data on the server?
  3. Are there any columns being returned from the database that are not needed? If so, remove those columns.
  4. Is there a high latency WAN connection, or a slow LAN connection between the server and the client? If so, repeat the test again when connected at gigabit speeds.
  5. Are table columns included in inline views in the SQL statement that are not used (discarded, not returned to the client) outside the inline view? If so, get rid of those columns – there is no sense in carrying those columns through a join, group by, or sort operation if the columns are never used. The same applies to statically defined views accessed by the SQL statement.
  6. Assuming that the cost-based optimizer is in use, have you checked the various optimizer parameters – have you done something silly like setting OPTIMIZER_INDEX_COST_ADJ to 1 and set OPTIMIZER_MODE to FIRST_ROWS?
  7. Have you set other parameters to silly values, like setting DB_FILE_MULTIBLOCK_READ_COUNT to 0, 1, 8, 16, etc?
  8. Have you not collected system (CPU) statistics, if available on your Oracle version (what is the Oracle version number, ex: 8.1.7.3, 9.2.0.7, 11.2.0.1, etc.).
  9. Have you examined an explain plan (or better yet, a DBMS_XPLAN with ‘ALLSTATS LAST’ as the format parameter)?
  10. Have you captured a 10046 trace at level 8, and either manually reviewed the file or passed it through TKPROF (or another utility)?
  11. Have you tried to re-write the SQL statement into an equivalent, but more efficient form?
  12. Have you collected a 10053 trace for a hard parse of the SQL statement?
  13. Have you recently collected table and index statistics for the objects?

What about finding the root cause of the performance problem? Sure, it might be fun to blindly try things to see if they help, but how do you know if what you have tried has helped without measuring?

 This brings me to the next suggestion – before posting a request to any forum or other website, make certain that you have provided something, anything, that will help someone answer your question.  Suggestions for what to include in your post are outlined here:
http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html
http://forums.oracle.com/forums/thread.jspa?messageID=1812597

This blog is not the place to post requests for help, and I likely will not respond to requests for help by email.  Requests for help should be directed to an appropriate forum or Oracle support (Metalink/MOS); those forums include the comp.databases.oracle.server / comp.databases.oracle.misc Usenet groups, the OTN forums, AskTom.Oracle.com, and Oracle-L.


Comments : 2 Comments »

Categories : General Administration