having trouble with sql statements (1 Viewer)

[and1_hotsauce]

hi
I have a table with two fields like below. The sender sends an sms with an ID eg. AA and gets a reply from the server ($1). However, I'm having trouble trying to decode the sql statement below



ID................. VALUE

AA...................$1
BB...................$3.59
CC..................$2.12
DD..................$3
EE..................$21

select all rows from the stocks table where the ID = ??. This is where I'm confused. I am not sure why its being concanetated twice.

strQuery = "SELECT * FROM Stocks WHERE ID = '" & strSymbol &"'"

full code is below if further information is needed.

' // ========================================================================
' // C:\Program Files (x86)\ActiveXperts\SMS Messaging Server\Projects\uni project\Triggers\uni project.vbs
' // ------------------------------------------------------------------------
' // 
' // 
' // ========================================================================
 
Option Explicit
CONST STR_DEBUGFILE     = "C:\Program Files (x86)\ActiveXperts\SMS Messaging Server\Sys\Tmp\uni project.txt"
CONST STR_DATABASEFILE = "C:\Program Files (x86)\ActiveXperts\SMS Messaging Server\Projects\uni project\Database\Stock.mdb"
' Declaration of global objects
Dim g_objMessageDB, g_objDebugger, g_objConstants
' Creation of global objects
Set g_objConstants      = CreateObject( "AxSmsServer.Constants" )
Set g_objMessageDB      = CreateObject( "AxSmsServer.MessageDB" ) 
Set g_objDebugger       = CreateObject( "ActiveXperts.VbDebugger" )
' Set Debug file - for troubleshooting purposes
g_objDebugger.DebugFile = STR_DEBUGFILE
g_objDebugger.Enabled   = False
 
' // ========================================================================
' // Function: ProcessMessage
' // ------------------------------------------------------------------------
' // ProcessMessage trigger function to process incoming messages
' // ========================================================================
Function ProcessMessage( numMessageID )
   Dim objMessageIn, objMessageOut
   g_objDebugger.WriteLine ">> ProcessMessage"
   ' Open the Message Database
   g_objMessageDB.Open
   If( g_objMessageDB.LastError <> 0 ) Then
      g_objDebugger.WriteLine "<< ProcessMessage,  unable to open database"
      Exit Function
   End If
   ' Retrieve the message that has just been received. If it fails then exit script 
   Set objMessageIn   = g_objMessageDB.FindFirstMessage ( "ID = " & numMessageID ) 
   If g_objMessageDB.LastError <> 0 Then
      g_objMessageDB.Close
      g_objDebugger.WriteLine "<< ProcessMessage,  FindFirstMessage failed, error: [" & g_objMessageDB.LastError & "]"
      Exit Function
   End If
   ' Change Status to from Pending to Success. If you don't do it, the message will be processed by subsequent triggers (if defined) because message is still pending
   objMessageIn.Status = g_objConstants.MESSAGESTATUS_SUCCESS
   g_objMessageDB.Save objMessageIn   
   g_objDebugger.WriteLine "Incoming message saved, result: [" & g_objMessageDB.LastError & "]"
   ProcessQuery ( objMessageIn )
 
   ' Close the Message Database
   g_objMessageDB.Close
   g_objDebugger.WriteLine "<< ProcessMessage"
End Function
 
' // ========================================================================
' // ProcessQuery
' // ------------------------------------------------------------------------
' // Parse the SMS and send a response back
' // ========================================================================
Function ProcessQuery ( objMessageIn )
   Dim objMessageOut, curCurrentPrice, strReplymessage
   g_objDebugger.WriteLine ">> ProcessQuery"
   g_objDebugger.WriteLine "Selected Stock: " & objMessageIn.Body
   If( QueryStock ( objMessageIn.Body, curCurrentPrice ) = False ) Then
 strReplymessage = "Stock symbol not found. Please send a valid stock to query the current price, for instance: AXP"
   Else
        strReplymessage = "Current Price for " & objMessageIn.Body & ": $" & curCurrentPrice
   End If
 
   ' Create the reply message
   Set objMessageOut = g_objMessageDB.Create
 
   If( g_objMessageDB.LastError = 0 ) Then
     objMessageOut.Direction = g_objConstants.MESSAGEDIRECTION_OUT
     objMessageOut.Type      = g_objConstants.MESSAGETYPE_SMS 
     objMessageOut.Status    = g_objConstants.MESSAGESTATUS_PENDING
     objMessageOut.Recipient = objMessageIn.Sender
     objMessageOut.ChannelID = objMessageIn.ChannelID
     objMessageOut.Body      = strReplymessage
     g_objMessageDB.Save objMessageOut
   End If
   g_objDebugger.WriteLine "<< ProcessQuery"
End Function
 
' // ========================================================================
' // Query stock
' // ------------------------------------------------------------------------
' // Lookup the current price of the selected stock ( use ticker symbol )
' // ========================================================================
Function QueryStock( strSymbol, BYREF curCurrentPrice )
   Dim objConn, RS
   Dim strQuery
   QueryStock = False
   g_objDebugger.WriteLine ">> QueryStock"
   Set objConn = CreateObject("ADODB.Connection")
 
   objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_DATABASEFILE & ";"
   strQuery = "SELECT * FROM Stocks WHERE ID = '" & strSymbol &"'"
 
   Set RS = objConn.Execute ( strQuery )
 
   If( RS.EOF = False ) Then
     curCurrentPrice = RS( "Value" )
     QueryStock      = True
   Else
     QueryStock      = False
   End If
   ' Close database
   objConn.Close
   Set objConn = Nothing
  g_objDebugger.WriteLine "<< QueryStock"
End Function

 

[plog]

This is where I'm confused. I am not sure why its being concanetated twice.

I'm confused in what you don't understand. Where do you see anything concanetated twice?

strQuery = "SELECT * FROM Stocks WHERE ID = '" & strSymbol &"'"

strSymbol is a variable which holds the text of the ID to look up. If you were to print out strQuery on the next line you would see it actually looks something like this:

SELECT * FROM Stocks WHERE ID = 'AA'

strSymbol just throws in whatever it is holding. If I misunderstand what you are confused about let me know and I will try and explain that.

 

[and1_hotsauce]

hi
thanks for your reply!

But I'm not quite sure how strsymbol is storing the text message. For eg. if I send 'BB', I can't see in the code where strSymbol=BB for it to be used in the sql statement

The variable strSymbol is only used twice

Once as an argument for the Function QueryStock and then its used in an sql statement

 

[mdlueck]

But I'm not quite sure how strsymbol is storing the text message. For eg. if I send 'BB', I can't see in the code where strSymbol=BB for it to be used in the sql statement

Varaible strsymbol seems to be the the name of a variable accepting an arguement into the QueryStock() Function. It ends up containing what ever that argument was when QueryStock() gets called.

Then it is used in building your SQL statement.

Do you agree?

 

[plog]

The value in strSymbol gets passed around a couple times before it gets used. It starts in this line of code:

  If( QueryStock ( objMessageIn.Body, curCurrentPrice ) = False ) Then

That line calls the QueryStock function and passes it 2 arguments: objMessageIn.Body and curCurrentPrice.

When you look at the QueryStock function declaration:

Function QueryStock( strSymbol, BYREF curCurrentPrice )

you see that the first argument it is passed becomes 'strSymbol'. That means when the function was called, the first value it was passed went into strSymbol. Looking back at the call in your function you see that initially that value was in objMessageIn.Body.

 

[and1_hotsauce]

thanks to both of your replies. I understand now.. I'll read up on functions and function calling

regards,
and1_hotsauce