Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sun Sep 22, 2019 10:14 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: Escape textual data function problem
PostPosted: Tue Nov 27, 2012 2:04 pm 
Offline

Joined: Tue Nov 27, 2012 1:04 pm
Posts: 3
I followed Mike Bailey's post of Sep 15, 2006 regarding Escape Textual data building a SQL statement as I had an apostrophy that would stop the search. The code works well in curing the apostraphy problem but now I can only Update once before having to leave the window and then come back it to do another Update. On another window with the same code except for the EscapeQuote SQL statement I can Update multiple times without any problem.

The reworked SQL statement is:
rs = App.InvestorsDB.SQLSelect("SELECT * FROM Sectors WHERE Topic = '" + EscapeQuotes ( sectorsTopic) + "'")
The function I am using is:
return replaceAll ( data, "'", "''" )

The working SQL statement is:
rs = App.InvestorsDB.SQLSelect("SELECT * FROM Regions WHERE Topic = '" + regionsTopic + "'")

Would anyone be able to explain why the first SQL statement only allows one Update and then stops while the second permits multiple Updates?


Top
 Profile  
Reply with quote  
 Post subject: Re: Escape textual data function problem
PostPosted: Tue Nov 27, 2012 2:28 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
From what you posted, there is no reason it shouldn't work. You'll need to snoop around a little more and get more info about what's going wrong. The debugger should provide a lot of good info for you. To help see what's up, create your sql statement in a string variable. Also, check App.InvestorsDB.ErrorMessage after the SQLSelect call.


Top
 Profile  
Reply with quote  
 Post subject: Re: Escape textual data function problem
PostPosted: Tue Nov 27, 2012 2:55 pm 
Offline
User avatar

Joined: Mon Feb 05, 2007 5:21 pm
Posts: 600
Location: New York, NY
Also look into Prepared Statements as that is the preferred way for doing this.

_________________
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: Escape textual data function problem
PostPosted: Tue Nov 27, 2012 4:24 pm 
Offline

Joined: Tue Nov 27, 2012 1:04 pm
Posts: 3
There is no error message; I press on the push button, it moves as expected and that is all that occurs. Without the EscapeQuotes the push button moves and an update occurs and I can make another change and the Update will work instead of "lock-up". I appreciate your giving this your time.


Top
 Profile  
Reply with quote  
 Post subject: Re: Escape textual data function problem
PostPosted: Tue Nov 27, 2012 6:08 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
Well, that's a little more info at least. So you say your code locks up? Do you have to force quit the app? Again, have you stepped through this code in the debugger?


Top
 Profile  
Reply with quote  
 Post subject: Re: Escape textual data function problem
PostPosted: Wed Nov 28, 2012 4:37 pm 
Offline

Joined: Tue Nov 27, 2012 1:04 pm
Posts: 3
I am currently doing some further reading; I placed the EscapeQuote in the App section and had an adverse effect in that the problem has spread to all windows. Fortunately I can go back to previously saved versions saved on different drives. Could anyone tell me what other symbols, letters such as the apostrophy are propriertary (only usable) to SQL so I can avoid this problem in the future? I have looked through several texts but none address this issue.

Again, I appreciate your time and effort.


Top
 Profile  
Reply with quote  
 Post subject: Re: Escape textual data function problem
PostPosted: Wed Nov 28, 2012 4:44 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
Can you post some code? There's nothing in what you've provided that would account for what you're seeing.

Apostrophe is really the only one you need to worry about.


Top
 Profile  
Reply with quote  
 Post subject: Re: Escape textual data function problem
PostPosted: Wed Nov 28, 2012 6:06 pm 
Offline
User avatar

Joined: Mon Feb 05, 2007 5:21 pm
Posts: 600
Location: New York, NY
I'll say again, use a PreparedStatement instead.

Your code:
rs = App.InvestorsDB.SQLSelect("SELECT * FROM Sectors WHERE Topic = '" + EscapeQuotes ( sectorsTopic) + "'")

With a PreparedStatement:
dim ps as RealSQLPreparedStatement = App.InvestorsDB.Prepare( "SELECT * FROM Sectors WHERE Topic = ?" )
ps.BindType( 0, RealSQLPreparedStatement.SQLITE_TEXT )
ps.Bind( 0, sectorsTopic )
rs = ps.SQLSelect

If this is a statement you use all the time, you can prepare it once, then rebind new values to it every time you use it.

_________________
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: Escape textual data function problem
PostPosted: Wed Nov 28, 2012 7:02 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
I completely agree with the recommendation to use a prepared statement, but I'm also curious about the bizarre results you're encountering.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 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