Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Fri Nov 16, 2018 5:52 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Is there a way... SQL string
PostPosted: Wed Nov 28, 2012 6:31 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
Hello all,

I want to assign a SQL command string to a single string variable. The string is LONG, so it would be best to have it in several lines, rather than one LONG line. How to do this?!

The first example is the same as the second, except that the first is really one long line in code. The second would be far better, but I cannot get RS to recognize the use of the "_" to extend the code to the next line.

Ideas?
Thanks,

Tim

q = "select site_listing.acc acc, site_listing.siteno siteno, site_listing.sitename sitename, site_listing.usrint_lic_qty usrint_lic_qty, site_listing.usrint_lic_used usrint_lic_used, site_listing.lic_paid lic_paid, site_listing.email email,site_listing.address address, site_listing.address1 address1, site_listing.city city, site_listing.countrycode countrycode, site_listing.phone phone, site_listing.postcode postcode, site_listing.state state, site_listing.country country, site_listing.start_date start_date, site_listing.exp_date exp_date, site_listing.install_date_axcys install_date_axcys, site_listing.install_date_embedded install_date_embedded, site_listing.subscription_support_yrending subscription_support_yrending, site_listing.last_purch_date last_purch_date, site_listing.last_purchase_item last_purchase_item, site_listing.subscription_support_purchase_date subscription_support_purchase_date, site_listing.last_purchase_buyer last_purchase_buyer from site_listing site_listing"



q = select
site_listing.acc acc,
site_listing.siteno siteno,
site_listing.sitename sitename,
site_listing.usrint_lic_qty usrint_lic_qty,
site_listing.usrint_lic_used usrint_lic_used,
site_listing.lic_paid lic_paid,
site_listing.email email,
site_listing.address address,
site_listing.address1 address1,
site_listing.city city,
site_listing.countrycode countrycode,
site_listing.phone phone,
site_listing.postcode postcode,
site_listing.state state,
site_listing.country country,
site_listing.start_date start_date,
site_listing.exp_date exp_date,
site_listing.install_date_axcys install_date_axcys,
site_listing.install_date_embedded install_date_embedded,
site_listing.subscription_support_yrending subscription_support_yrending,
site_listing.last_purch_date last_purch_date,
site_listing.last_purchase_item last_purchase_item,
site_listing.subscription_support_purchase_date subscription_support_purchase_date,
site_listing.last_purchase_buyer last_purchase_buyer
from
site_listing site_listing


Top
 Profile  
Reply with quote  
 Post subject: Re: Is there a way... SQL string
PostPosted: Wed Nov 28, 2012 6:51 pm 
Offline
User avatar

Joined: Mon Feb 05, 2007 5:21 pm
Posts: 600
Location: New York, NY
dim l() as string = Array( _
"Select ", _
"site_listing.acc acc,", _
"site_listing.siteno siteno,", _
"site_listing.sitename sitename,", _
... _
"from ", _
"site_listing site_listing" _
)

q = join( l, " " )

Or:
dim l() as string
l.Append "Select "
l.Append "site_listing.acc acc,"
l.Append "site_listing.siteno siteno,"
...
l.Append "from "
l.Append "site_listing site_listing"

q = join( l, " " )

Or:
q = _
"select " + _
"site_listing.acc acc," + _
"site_listing.siteno siteno," + _
... _
"from " + _
"site_listing site_listing"

These are in order from fastest to slowest.

_________________
Kem Tekinay
MacTechnologies Consulting
http://www.mactechnologies.com/

Need to develop, test, and refine regular expressions? Try RegExRX.


Top
 Profile  
Reply with quote  
 Post subject: Re: Is there a way... SQL string
PostPosted: Thu Nov 29, 2012 5:00 am 
Offline

Joined: Mon May 30, 2011 12:56 am
Posts: 702
Silly (?) question:

Is
q = "select site_listing.acc acc, site_listing.siteno siteno, site_listing.sitename sitename, site_listing.usrint_lic_qty usrint_lic_qty, site_listing.usrint_lic_used usrint_lic_used, site_listing.lic_paid lic_paid, site_listing.email email,site_listing.address address, site_listing.address1 address1, site_listing.city city, site_listing.countrycode countrycode, site_listing.phone phone, site_listing.postcode postcode, site_listing.state state, site_listing.country country, site_listing.start_date start_date, site_listing.exp_date exp_date, site_listing.install_date_axcys install_date_axcys, site_listing.install_date_embedded install_date_embedded, site_listing.subscription_support_yrending subscription_support_yrending, site_listing.last_purch_date last_purch_date, site_listing.last_purchase_item last_purchase_item, site_listing.subscription_support_purchase_date subscription_support_purchase_date, site_listing.last_purchase_buyer last_purchase_buyer from site_listing site_listing"

much different from
"Select * from site_listing"

..how many fields does site_listing actually have?
Using select * , you can get the values of the field you want by using syntax like .Fields("phone").value


Even if it isn't the entire field list, you only have one source table.
So prefixing every field with the table name is also overkill.


q = "select acc, siteno, sitename, usrint_lic_qty, usrint_lic_used, lic_paid, email, address, address1,city, countrycode, phone, postcode, state, country, start_date, exp_date, install_date_axcys, install_date_embedded , subscription_support_yrending, last_purch_date, last_purchase_item, subscription_support_purchase_date, last_purchase_buyer from site_listing"

..same result.


Top
 Profile  
Reply with quote  
 Post subject: Re: Is there a way... SQL string
PostPosted: Thu Nov 29, 2012 10:20 am 
Offline
Real Software Engineer

Joined: Sat Dec 24, 2005 8:18 pm
Posts: 7858
Location: Canada, Alberta, Near Red Deer
kermit wrote:
Silly (?) question:

Is
q = "select site_listing.acc acc, site_listing.siteno siteno, site_listing.sitename sitename, site_listing.usrint_lic_qty usrint_lic_qty, site_listing.usrint_lic_used usrint_lic_used, site_listing.lic_paid lic_paid, site_listing.email email,site_listing.address address, site_listing.address1 address1, site_listing.city city, site_listing.countrycode countrycode, site_listing.phone phone, site_listing.postcode postcode, site_listing.state state, site_listing.country country, site_listing.start_date start_date, site_listing.exp_date exp_date, site_listing.install_date_axcys install_date_axcys, site_listing.install_date_embedded install_date_embedded, site_listing.subscription_support_yrending subscription_support_yrending, site_listing.last_purch_date last_purch_date, site_listing.last_purchase_item last_purchase_item, site_listing.subscription_support_purchase_date subscription_support_purchase_date, site_listing.last_purchase_buyer last_purchase_buyer from site_listing site_listing"

much different from
"Select * from site_listing"


Yes
You should not use select * in production code as any change in a table could suddenly return more or less columns

You're way better off to explicitly list them

_________________
Norman Palardy (Real Software)


Top
 Profile  
Reply with quote  
 Post subject: Re: Is there a way... SQL string
PostPosted: Thu Nov 29, 2012 11:44 am 
Offline

Joined: Mon May 30, 2011 12:56 am
Posts: 702
Quote:
You should not use select * in production code as any change in a table could suddenly return more or less columns


OK, but if you refer to the returned fields by name rather than by ordinal, it should work even if you got 60 new fields.
And if someone removes a field, it's going to break regardless?

I use explicit fields when I want way less than the full set of fields.
Dont know how true it still is, but it used to be that a subset of data came back faster than select *


Top
 Profile  
Reply with quote  
 Post subject: Re: Is there a way... SQL string
PostPosted: Thu Nov 29, 2012 3:32 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
So anyway it is looked at, all the elements still need to be in a long string.... maybe the best way is to use ktekinay methods, but make generic so they only have to be typed once. Then they can be added or modified later to suit.

I have been using the osql methods which is a bit cheating, but works! In this new case, I need to pass a SQL string to Valentina Reports and have not tried using the osql methods yet....

Thanks everyone for the replies!
Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: Is there a way... SQL string
PostPosted: Thu Nov 29, 2012 3:59 pm 
Offline

Joined: Wed May 20, 2009 4:43 pm
Posts: 958
Another option that might be helpful, if the string won't change, would be to put the query string into a string constant. That will show up as entered, line breaks and all, and can be edited as is in the edit window for that constant. You just won't be able to edit it in the actual code window.

_________________
RB 2009r4 Windows XP


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