Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Nov 21, 2017 12:46 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: SQL with two parameters
PostPosted: Tue Feb 24, 2009 2:07 pm 
Offline

Joined: Fri Sep 14, 2007 5:00 pm
Posts: 408
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?


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL with two parameters
PostPosted: Tue Feb 24, 2009 3:11 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
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


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL with two parameters
PostPosted: Tue Feb 24, 2009 4:21 pm 
Offline

Joined: Fri Sep 14, 2007 5:00 pm
Posts: 408
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:


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL with two parameters
PostPosted: Fri May 03, 2013 2:47 pm 
Offline

Joined: Fri Sep 14, 2007 5:00 pm
Posts: 408
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL with two parameters
PostPosted: Fri May 03, 2013 2:58 pm 
Offline
User avatar

Joined: Fri Nov 10, 2006 4:10 pm
Posts: 1855
Location: Michigan, USA
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.

_________________
Windows 8 x64
Windows XP Pro SP3
Ubuntu 11.04 via Virtual Box
RS Enterprise 2012r1.1

Programming Tutorials & Free Projects: http://www.JasonTheAdams.com
"Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL with two parameters
PostPosted: Fri May 03, 2013 4:55 pm 
Offline

Joined: Fri Sep 14, 2007 5:00 pm
Posts: 408
Thanks! Using you method I was able to see I was missing a closing single quote!~ Werks now!


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL with two parameters
PostPosted: Fri May 03, 2013 5:04 pm 
Offline
Real Software Engineer

Joined: Sat Dec 24, 2005 8:18 pm
Posts: 7858
Location: Canada, Alberta, Near Red Deer
A perfect reason to use a prepared statement and mostly skip that kind of error

_________________
Norman Palardy (Real Software)


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group