New topic: Best structure/methodology to create sql string
<http://forums.realsoftware.com/viewtopic.php?t=47276> Page 1 of 1 [ 3 posts ] Previous topic | Next topic Author Message tseyfarth Post subject: Best structure/methodology to create sql stringPosted: Tue Mar 12, 2013 11:39 pm Joined: Sat Dec 04, 2010 9:14 pm Posts: 843 Hello all, To date, I have used the SmartSQL object to create my SQL commands, and have used different methods for each 'type' of query. For example, if by person, one method. If by address, another method and so on. Function getTenant(fName As String, lName As String, D As Date, D1 As Date, UseDate As Boolean = False, UpdateProperties As Boolean = False) As Integer Function getPIN(PIN As Integer, D As Date, D1 As Date, UseDate As Boolean = False, UpdateProperties As Boolean = False) As Integer Function getTenant(fName As String, lName As String, D As Date, D1 As Date, UseDate As Boolean = False, UpdateProperties As Boolean = False) As Integer Now, I have to work with a Pervasive SQL database, that has some apparrantly unique syntax requirements that will not allow me to use the Smart SQL object. OK, not an entirely bad thing (although very frustrating when they don't work!) since it forces a new area of growth. With this new requirement, I think I need to organize the command building structure far better than I had with the easy way using SmartSQL. But how to do this and what is the best way? Having never seen code to do this, I am really feeling in the dark. I have several main elements that will be mixed and matched to create a query. They are: from/to date range person FirstName person LastName (two fields which CAN be combined in the results) PIN Bldg & Door (two fields which CAN be combined in the results) There are others too but this is enough. Plus, the results need to be ordered - normally by date. To date, I have created each string separately using string concatenation hard coded such as: Dim s As String = "select * from " s = s + """TRANSACTION""" s = s + " where " s = s + """TRANSACTION""" + "." + """Date In""" + ">= {d '" + D.SQLDate + "'} And " + """TRANSACTION""" + "." + """Date In""" + "<= {d '" + D1.SQLDate + "'} And " s = s + """TRANSACTION""" + "." + """bldgno""" + "= '" + Bldg + "' And " + """TRANSACTION""" + "." + """Door No""" + " = '" + Dr + "';" To make this a little easier and try to move in a different direction as I am desiring to do so, I have created several constants: Dim s As String = constSel s = s +constDate1 + d.SQLDate + constDate2 + D1.SQLDate + constAND + constFName + fName + constLName + lName + constEND Using the Constants seems to be somewhat better, but I am struggling with how to organize and therefore create a functionally easy/clear method to create multiple queries in a very flexible way. Can anyone share some example code or other information to help me? Thank you, Tim Top timhare Post subject: Re: Best structure/methodology to create sql stringPosted: Wed Mar 13, 2013 12:06 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 12144 Location: Portland, OR USA As a first pass, I'd create some helper methods to format the string the way Pervasive wants it. I usually go for short methods here, since you're going to be using a lot of them in the string creation and you don't want to make it verbose. function pfield(field as string) as string dim s() as string = split(field, ".") dim q as string = chrb(34) for i as integer = 0 to ubound(s) s(i) = q + s(i) + q next return join(s, ".") end function pdate(d as date) as string dim s as string = "{d '"+ d.SQLDate+ "'}" return s end dim s as string = "select * from "+ pfield("TRANSACTION") + " where " _ + pfield("TRANSACTION.Date In") + " >= " + pdate(D) + " and " _ + pfield("TRANSACTION.Date In") + " <= " + pdate(D1) + " and " _ + pfield("TRANSACTION.bldgno") + " = " + Bldg + " and " _ + pfield("TRANSACTION.Door No") + " = '"+ Dr + "'" The final ";" may not be necessary when using the RB database plugins. Tim Last edited by timhare on Wed Mar 13, 2013 12:19 am, edited 1 time in total. Top timhare Post subject: Re: Best structure/methodology to create sql stringPosted: Wed Mar 13, 2013 12:19 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 12144 Location: Portland, OR USA The next step would be to create a set of overloaded methods. function pwhere(field as string, oper as string, pdata as string) return pfield(field) + " " + oper + " '"+ pdata + "'" end function pwhere(field as string, oper as string, pdata as double) return pfield(field) + " " + oper + " " + str(pdata) end function pwhere(field as string, oper as string, pdata as date) return pfield(field) + " " + oper + " " + pdate(pdata) end dim s as string = "select * from " + pfield("TRANSACTION") + " where " _ + pwhere("TRANSACTION.Date In", ">=", D) + " and " _ + pwhere("TRANSACTION.Date In", "<=", D1) + " and " _ + pwhere("TRANSACTION.bldgno", "=", Bldg) + " and " _ + pwhere("TRANSACTION.Door No", "=", Dr) Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 3 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
