Real Software Forums
http://forums.realsoftware.com/

[SOLVED] Prepared Statement - Update-ODBC- data not applied
http://forums.realsoftware.com/viewtopic.php?f=3&t=47958
Page 1 of 2

Author:  superjacent [ Thu May 23, 2013 7:36 am ]
Post subject:  [SOLVED] Prepared Statement - Update-ODBC- data not applied

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.

Author:  pfargo [ Thu May 23, 2013 8:00 am ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

Are you committing the update?

Author:  superjacent [ Thu May 23, 2013 8:12 am ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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.

Author:  taylor-design [ Thu May 23, 2013 1:29 pm ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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.

Author:  pfargo [ Fri May 24, 2013 5:48 am ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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).

Author:  superjacent [ Fri May 24, 2013 5:57 am ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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.

Author:  superjacent [ Fri May 24, 2013 7:21 am ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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?

Author:  superjacent [ Mon May 27, 2013 8:38 am ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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.

Author:  pfargo [ Mon May 27, 2013 1:09 pm ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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

Author:  superjacent [ Mon May 27, 2013 4:07 pm ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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.

Author:  taylor-design [ Mon May 27, 2013 4:47 pm ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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.

Author:  superjacent [ Mon May 27, 2013 5:46 pm ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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.

Author:  pfargo [ Tue May 28, 2013 5:03 am ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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.

Author:  superjacent [ Tue May 28, 2013 5:20 am ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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.

Author:  superjacent [ Tue May 28, 2013 5:28 am ]
Post subject:  Re: Prepared Statement - Update - ODBC - data not applied

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?

Page 1 of 2 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/