Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sun Apr 30, 2017 11:24 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 20 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: [SOLVED] Prepared Statement - Update-ODBC- data not applied
PostPosted: Thu May 23, 2013 7:36 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
Hope someone can help. I'm converting my traditional SQL statements to Prepared statements. I'm using ODBC connected to a Microsoft Access database. I've successfully got the SQL SELECT prepared statement working. I'm having issues with the UPDATE prepared statement. I am not receiving any db errors but the data is not being applied. I navigate away from the record, return to it and the old data is there, no changes. Below is the method I use in creating the UPDATE prepared statement.

Function BindParametersUpdate(pStr_SQL_PreparedUpdate as string) As Boolean
Dim ps As ODBCPreparedStatement
Dim i as integer
dim var_Value as Variant
ps = ODBCPreparedStatement(db.Prepare(pStr_SQL_PreparedUpdate))

' First, cycle SaveFields array (data entered by user) for binding.

for i = 0 to UBound(c_arv_SQL_SaveFields)
var_Value = c_arv_SQL_SaveFields(i,2) // actual data value

Select Case c_arv_SQL_SaveFields(i,1) // this is the data type

case k_TYPE_TEXT
ps.BindType(i,ODBCPreparedStatement.ODBC_TYPE_STRING)
case k_TYPE_NUMBER
ps.BindType(i,ODBCPreparedStatement.ODBC_TYPE_INTEGER)
case k_TYPE_DATE
ps.BindType(i,ODBCPreparedStatement.ODBC_TYPE_DATE)

// further case statements maybe required
end select

ps.Bind(i,var_Value)

next i

' now work on key field parameter, the actual record to be updated.

ps.BindType(i,ODBCPreparedStatement.ODBC_TYPE_INTEGER)
ps.Bind(i,c_ari_RecKeyFields(c_int_RecPosition)) // Unique key - data value

ps.SQLExecute

if db.Error then
MsgBox "Binding Error " + db.ErrorMessage
return false
else
return true
end if

End Function


For debugging purposes, I've confirmed that the data values match up precisely with the parameters (?) in the prepared statement string (that is passed into the method).

Am I missing something? Any advice or suggestions are most welcome.

_________________
Steve
rs2012 r2.1 Windows 7.


Last edited by superjacent on Sun Jun 02, 2013 7:16 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Thu May 23, 2013 8:00 am 
Offline

Joined: Mon Oct 13, 2008 4:26 am
Posts: 63
Are you committing the update?


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Thu May 23, 2013 8:12 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
pfargo wrote:
Are you committing the update?

No, I wasn't. Inserting a db.commit just after the ps.SQLExecute does not alter things, data is still not saved.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Thu May 23, 2013 1:29 pm 
Offline

Joined: Wed Mar 22, 2006 11:15 am
Posts: 712
Location: Southern California
I ran into this myself just recently in a small project. Since it was a "one time" app where I was not concerned about security I just went ahead and executed SQL strings.

I've been busy so I didn't investigate further and file a Feedback case. I don't know if it's an issue with RB, ODBC, or Access.

_________________
Daniel L. Taylor
Custom Controls for Real Studio WE!
Visit: http://www.webcustomcontrols.com/


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Fri May 24, 2013 5:48 am 
Offline

Joined: Mon Oct 13, 2008 4:26 am
Posts: 63
I don't use ODBC prepared statements with Access but have done quite a bit with FileMaker. There are oddities that I have had to work around. Like Daniel said there are many layers to deal with (RS, ODBC Plugin, ODBC Driver, Access).

Are you not able to succeed with "any" prepared statements?

I ended up hand coding simple tests for each datatype and figured out what, if anything special, had to be done to make it work. I'd get out of your main app and the generic routines and try simple tests. You may already have?

If you can nail it down to the plugin Jonathan has been outstanding at fixing things (in my experience).


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Fri May 24, 2013 5:57 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
pfargo wrote:
I don't use ODBC with Access but have done quite a bit with FileMaker. There are oddities that I have had to work around. Like Daniel said there are many layers to deal with (RS, ODBC Plugin, ODBC Driver, Access).

Are you not able to succeed with "any" prepared statements?


Yes, I've got SELELCT prepared statements working. In that select statement I'm retrieving various data types (Text, Boolean, Date & Number). This is why it is confusing me, why it works for a SELECT and not UPDATE (and now I'm assuming INSERT though haven't tested).

pfargo wrote:
I ended up hand coding simple tests for each datatype and figured out what, if anything special, had to be done to make it work. I'd get out of your main app and the generic routines and try simple tests. You may already have?

Yes, I think you're right, will have to conduct some simple hard-coded examples and then report back with results.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Fri May 24, 2013 7:21 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
Further. I was able to purposely switch off updating of certain fields, which I based on different data types but the result was the same as first reported; no db error and no change in data at the db.

Next, I purposely altered the table field names attempting to force a db error and presto, no db error reported. According to my code the prepared statement executed correctly, which shouldn't have. Am I correct in assuming that the prepared statement never actually hit the db and if so, I must not be coding it incorrectly? Is there a step I'm missing?

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Mon May 27, 2013 8:38 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
An update on this saga. My development machine is a Windows 7, 64 bit system. I have Office 2010 installed. I have Microsoft Access ODBC driver version 14.00.6015.1000 dated 21 Dec 2010. This driver supposedly includes access to both the old .mdb databases and the newer .accdb databases. I do not have Office 2003 installed on this system.

On my VM, it's Windows XP with Office 2003 installed. Microsoft Access ODBC Driver is version 4.00.6305.00 dated 14 Apr 2008. This is the same configuration as my employer's desktop computers.

To recap the issue:

I can execute normal UPDATE SQL statements in both environments.

This is not the case for UPDATE prepared statements. I have detected two problem issues:

1. When attempting to bind STRING / TEXT data the only ODBC data type constants available are STRING and LONGSTRING. Only the LONGSTRING constant partially works for binding. That is, only the first two characters of the text data are actually updated to the database. When binding using the STRING data type no update takes place. Regardless, no database error is thrown.

2. When attempting to send a NULL value for a date field an error is thrown by the database. If a date is valid an update occurs normally. The date field in the database can accept nulls or be empty.

As mentioned, the above two issues are not problems if executing an UPDATE statement normally (non prepared). I am getting the same results on my company computers.

I also discovered that even when I purposely entered incorrect field names in the prepared statement that no database error was thrown. Checking the SQL Tracing log, the invalid field names are clearly referenced and within the log there are numerous SQL errors but these are not thrown back to the RS application.

I'm now re-thinking my approach.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Mon May 27, 2013 1:09 pm 
Offline

Joined: Mon Oct 13, 2008 4:26 am
Posts: 63
What is the version of your odbc plugin? There were many updates in the last year or so.

You can get it by entering the following statement after making a successful odbc connection.

MsgBox db.GetInfo(1058)

It should be Jan 17 2013 14:28:12


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Mon May 27, 2013 4:07 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
pfargo wrote:
What is the version of your odbc plugin? There were many updates in the last year or so.

You can get it by entering the following statement after making a successful odbc connection.

MsgBox db.GetInfo(1058)

It should be Jan 17 2013 14:28:12

I'm getting a compiler error with this.

I don't see this in the list of auto populate properties of the DB property.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Mon May 27, 2013 4:47 pm 
Offline

Joined: Wed Mar 22, 2006 11:15 am
Posts: 712
Location: Southern California
superjacent wrote:
I'm now re-thinking my approach.


Have you considered just editing a RecordSet rather then using a prepared update statement? You should get the same protection against SQL injection. This doesn't work of course if you're trying to efficiently update multiple rows.

_________________
Daniel L. Taylor
Custom Controls for Real Studio WE!
Visit: http://www.webcustomcontrols.com/


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Mon May 27, 2013 5:46 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
taylor-design wrote:
superjacent wrote:
I'm now re-thinking my approach.


Have you considered just editing a RecordSet rather then using a prepared update statement? You should get the same protection against SQL injection. This doesn't work of course if you're trying to efficiently update multiple rows.


Looks like this is what I might have to do. It's certainly an option.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Tue May 28, 2013 5:03 am 
Offline

Joined: Mon Oct 13, 2008 4:26 am
Posts: 63
superjacent wrote:
pfargo wrote:
What is the version of your odbc plugin? There were many updates in the last year or so.

You can get it by entering the following statement after making a successful odbc connection.

MsgBox db.GetInfo(1058)

It should be Jan 17 2013 14:28:12

I'm getting a compiler error with this.

I don't see this in the list of auto populate properties of the DB property.


Do you possibly have your "db" wrapped? You may have to cast it in order to see these items.

ODBCDatabase(db).GetInfo

If you look up ODBCDatabase in the Language Reference you will see GetInfo in the Methods section.

ODBCDatabase.GetInfo ( Attribute as Integer ) as Variant
Returns general information about the driver and data source associated with a connection.
Returns a Variant that contains either a String or an Integer, based on the attribute.


Last edited by pfargo on Tue May 28, 2013 5:45 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Tue May 28, 2013 5:20 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
pfargo wrote:
superjacent wrote:
pfargo wrote:
What is the version of your odbc plugin? There were many updates in the last year or so.

You can get it by entering the following statement after making a successful odbc connection.

MsgBox db.GetInfo(1058)

It should be Jan 17 2013 14:28:12

I'm getting a compiler error with this.

I don't see this in the list of auto populate properties of the DB property.


Do you possible have your "db" wrapped? You may have to cast it in order to see these items.

ODBCDatabase(db).GetInfo

If you look up ODBCDatabase in the Language Reference you will see GetInfo in the Methods section.

ODBCDatabase.GetInfo ( Attribute as Integer ) as Variant
Returns general information about the driver and data source associated with a connection.
Returns a Variant that contains either a String or an Integer, based on the attribute.


Connection Success: ODBCDatabase(db).GetInfo(1058) = Dec 21 2012 18:11:57

Will have a look at the RS site, I'm assuming I should be able to download the latest version.

Thanks,

Steve.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Tue May 28, 2013 5:28 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
Can't seem to find Database Plugins to download at RS site.

Do I wait less than a week, download Xojo which should have the latest version of the plugins?

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 20 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 3 guests


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