Real Software Forums
http://forums.realsoftware.com/

SQL with two parameters
http://forums.realsoftware.com/viewtopic.php?f=3&t=26608
Page 1 of 1

Author:  McDian [ Tue Feb 24, 2009 2:07 pm ]
Post subject:  SQL with two parameters

First, yes I know the code is a bit nasty, it's a work in progress so please understand it's not final but a work in progress.

Okay so I have a query that needs two input parameters in order to extract the required data but I am not getting a return at all or at least I am not getting what I expect.

This is the exact query in question.
' This query is to pull a single record based on meter id and year period. I want the last reading for the meter in process.
recSetFour = dbMmacs.SQLSelect("SELECT currRead FROM tblMeterHistory WHERE meterId='" + recSetTwo.Field("meterId").StringValue + "' AND YYYYPP='" + stYyyyPp + "'")

If recSetFour = Nil Then
Beep
MsgBox "Connected to database but the record set is empty." + EndOfLine + EndOfLine + "Error Located: frmLoadExports.mthImportFileCreate."
Else
stLastRead = recSetFour.Field("currRead").StringValue
MsgBox stLastRead
Exit Sub
End If


And this is the entire scope of what I am attempting to do.
' First thing is take the last Export File and move it from tblExportStore and move it into tblArchiveExportStore then move tblMeterTest into tblExportStore then
' delete tblMeterTest.
Dim dbMmacs As New REALSQLDatabase
Dim dbRec As New DatabaseRecord
Dim recSet As RecordSet ' Holds route data
Dim recSetTwo As RecordSet ' Holds historical data
Dim recSetThree As RecordSet ' Holds meter data
Dim recSetFour As RecordSet ' Holds last read for a meter
Dim dbFile As FolderItem
Dim txtOut As TextOutputStream
Dim dblHighVal, dblLowVal, dblSecHigh,dblSecLow As Double
Dim intIndexMax As Integer
Dim stMsgOut, stErrMsg, stRteNum, stLastRead As String

' Set the database location
dbFile = GetFolderItem("Database").Child("dbUcscMMDB.rsd")

' Test to make sure the folder and the database actually exist.
If (dbFIle) <> Nil Then
If (dbFile.Exists) Then
dbMmacs.DatabaseFile = dbFile
Else
MsgBox "Database is not there."
Return
End If
Else
MsgBox "Folder is not there."
Return
End If

' Test to make sure I am connected to the database.
If dbMmacs.Connect = True Then
recSet = dbMmacs.SQLSelect("SELECT meterId,rteNum,seqNum FROM tblRoutes") ' Get meter ids to be tested.

If recSet = Nil Then
// Test for an empty record set.
Beep
MsgBox "Hmmm, connected but the RecordSet is empty?!" + EndOfLine+ EndOfLine + "Error Located: frmLoadExports.mthImportFileCreate.recSet."
Exit Sub
Else
While NOT recSet.EOF
stRteNum = recSet.Field("rteNum").StringValue ' This will be used later to test for changes. If there is a change then start a new text output file.

' This query pulls data from the meter history table based on the current meter id. This is used to perform high low calculations
recSetTwo = dbMmacs.SQLSelect("SELECT meterId,startDate,endDate,usage,YYYYPP FROM tblMeterHistory WHERE meterId='" + recSet.Field("meterId").StringValue + "'")

' This query pulls information from tblMeters to be used in high low calculations and final text file output.
recSetThree = dbMmacs.SQLSelect("SELECT meterId,meterMulti FROM tblMeters WHERE meterId='" + recSet.Field("meterId").StringValue + "'")

If recSetTwo = Nil Then
// Testing for an empty RecordSet
Beep
MsgBox "Connected to the database but the record set is empty." + EndOfLine + EndOfLine + "Error Located: frmLoadExports.mthCalcError.recSetTwo."
Else

' Set up my arrays and variables in this scope.
Dim aryHighVals() As Double
Dim stYyyyPp As String
Dim aryYyyyPp(), intYyyyPp As Integer

' Load each array with requisite data to be sorted through later.
While NOT recSetTwo.EOF
aryHighVals.Append CDbl(recSetTwo.Field("usage").StringValue)
aryYyyyPp.Append CDbl(recSetTwo.Field("YYYYPP").StringValue)
recSetTwo.MoveNext
Wend

' Sort both arrays.
aryHighVals.Sort
aryYyyyPp.Sort

' This For Loop is used to get the last Year Period of the last read.
For intCnt As Integer = 0 To UBound(aryYyyyPp)
If intYyyyPp < aryYyyyPp(intCnt) Then
intYyyyPp = aryYyyyPp(intCnt)
intIndexMax = intCnt
End If
Next
stYyyyPp = CStr(intYyyyPp)

' This query is to pull a single record based on meter id and year period. I want the last reading for the meter in process.
recSetFour = dbMmacs.SQLSelect("SELECT currRead FROM tblMeterHistory WHERE meterId='" + recSetTwo.Field("meterId").StringValue + "' AND YYYYPP='" + stYyyyPp + "'")

If recSetFour = Nil Then
Beep
MsgBox "Connected to database but the record set is empty." + EndOfLine + EndOfLine + "Error Located: frmLoadExports.mthImportFileCreate."
Else
stLastRead = recSetFour.Field("currRead").StringValue
MsgBox stLastRead
Exit Sub
End If

' Load the high values array
For intCnt As Integer = 0 To UBound(aryHighVals)
If dblHighVal < aryHighVals(intCnt) Then
dblSecHigh = dblHighVal
dblHighVal = aryHighVals(intCnt)
intIndexMax = intCnt
End If
Next

' Load the low values array
For intCnt As Integer = 0 To UBound(aryHighVals)
If dblHighVal > aryHighVals(intCnt) Then
dblSecLow = dblLowVal
dblLowVal = aryHighVals(intCnt)
intIndexMax = intCnt
End If
Next

Dim stA As String
Dim dblUsge As Double

' Set High Limit
dblUsge = (dblSecHigh * 1.25)/ CDbl(recSetThree.Field("meterMulti").StringValue) + CDbl(stLastRead)

stMsgOut = stMsgOut + "Meter ID: " + recSet.Field("meterId").StringValue + " High Read = " + CStr(dblUsge) + " High = " + CStr(dblSecHigh) + " Low = " + CStr(dblSecLow) + " Last Meter Read = " + stLastRead + EndOfLine

recSet.MoveNext
dblHighVal = 0
intIndexMax = 0

End If
Wend

recSet.Close
recSetTwo.Close

dbFile = GetFolderItem("Dump.txt")
If dbFile <> Nil Then
txtOut = dbFile.CreateTextFile
txtOut.Write ConvertEncoding(stMsgOut, Encodings.UTF8)
txtOut.Close
End If

End If
Else
If dbMmacs.Error Then
// Testing for issues in the DB tissues!
Beep
MsgBox "Database Error: " + str(dbMmacs.ErrorCode) + EndOfLine + EndOfLine +dbMmacs.ErrorMessage
Exit Sub
End If
MsgBox "I do not believe it is connected! " + str(dbMmacs.ErrorCode) + EndOfLine + EndOfLine + dbMmacs.ErrorMessage
End If


I probably could just eliminate this particular query, but I can't quite figure out how to do it yet so...

Anyhow what is happening is I have to pull data from three different tables in order to create an output file. The first query pulls the source from one table. And that in turn provides the search parameter for the next three queries that pull data from their respective tables. What I am trying to do is take data from the appropriate tables and create a .csv file that will be used to load a hand held meter reading tool.

I have the idea worked out but am stuck on getting one last piece of the puzzle and that is the last reading for each meter in the loop. There are 1365 meters that have to be processed this way and that in turn will create 16 text files. But first I want to get the calculation complete but I can't since I am not able to get the one piece of data I need.

So in my detailed and long winded way, how do I write a query with two search parameters?

Author:  timhare [ Tue Feb 24, 2009 3:11 pm ]
Post subject:  Re: SQL with two parameters

The query is correct, perhaps the data that you're putting in to the query is to blame? The normal steps for debugging an sqlselect involve

Print out the error message returned from the query. In this case, dbMmacs.ErrorMessage.
Create the query in a string so you can view it in the debugger.
Copy the contents of the string in the debugger and paste it into a query browser and run the query.

HTH,
Tim

Author:  McDian [ Tue Feb 24, 2009 4:21 pm ]
Post subject:  Re: SQL with two parameters

Hmmm, I got it fixed. Instead of using the previous query I used a variable instead and now it's working. Why is it you beat on something for three or more days, finally give up ask for help then 30 minutes later figure it out? Does anyone else get that? :roll:

Author:  McDian [ Fri May 03, 2013 2:47 pm ]
Post subject:  Re: SQL with two parameters

Hmmm, having troubles again with two parameter queries...

Dim bStte As String = "BLG"
Dim recYerp As RecordSet = dbFaserBase.SQLSelect("SELECT MAX(yerPer) AS yPerd FROM tblHistory WHERE metId='" + stMetId + " AND blState='" + bStte + "'")
Dim sYerp As String = recYerp.IdxField(1).StringValue


recYerp is Coming back Nil. If I take the second parameter our (blState) then it works fine in that it will return a value... just potentially not the correct value.

Author:  Jason_Adams [ Fri May 03, 2013 2:58 pm ]
Post subject:  Re: SQL with two parameters

May I suggest adding another variable? I always do something like this:

Dim SQL As String = "SELECT somecolumn FROM mytable WHERE idcolumn=" + Str(nIDVariable)
Dim rs As RecordSet = MyDatabase.SQLSelect(SQL)


That way, if there's an issue I can stop the method in the debugger and view the SQL string to make sure I agree with what it became before executing the query. If it doesn't look right, there's something wrong with my code; if it does, there's something wrong with my database.

Author:  McDian [ Fri May 03, 2013 4:55 pm ]
Post subject:  Re: SQL with two parameters

Thanks! Using you method I was able to see I was missing a closing single quote!~ Werks now!

Author:  npalardy [ Fri May 03, 2013 5:04 pm ]
Post subject:  Re: SQL with two parameters

A perfect reason to use a prepared statement and mostly skip that kind of error

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/