Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Fri Nov 24, 2017 8:10 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 20 posts ]  Go to page Previous  1, 2
Author Message
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Tue May 28, 2013 5:44 am 
Offline

Joined: Mon Oct 13, 2008 4:26 am
Posts: 63
They were in the extras section of the download page but seem not to have been put back since the breach.

I sent a PM with my email. I can send them to you if you contact me.


Top
 Profile  
Reply with quote  
 Post subject: [SOLVED] Prepared Statement - Update - ODBC - data not appl
PostPosted: Wed May 29, 2013 10:48 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
Just to end this thread, give it some sort of conclusion, I got the below from the NUG thread (thanks Jay). Damn, had I picked up on this early on, I probably wouldn't have persisted with trying to make it work.

Jay wrote:
I just noticed this statement in the docs (
http://docs.realsoftware.com/index.php? ... ldid=44277):
*
*
*"Note:* The *ODBCDatabase* class does not yet support the
PreparedSQLStatement database parameter binding interface."

But of course there is the ODBCPreparedStatement in the docs (
http://docs.realsoftware.com/index.php? ... ldid=44289)
which indicates that binding works. And Steve has proved that the prepared
statement/binding does work for everything except writing strings at this
point, at least for an Access database.


I've prepended the subject title with "SOLVED" - it's solved in that we now know why it doesn't work.

I will be submitting a feedback issue regarding this but in the interim I'm returning to the Recordset methods to update and add to a database (via ODBC).

ps. seems like my [SOLVED] is not showing in the subject title, oh well, you get the idea.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
Reply with quote  
 Post subject: Re: Prepared Statement - Update - ODBC - data not applied
PostPosted: Wed May 29, 2013 11:41 pm 
Offline

Joined: Wed Mar 22, 2006 11:15 am
Posts: 712
Location: Southern California
If it's any consolation, I missed it as well :x

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


Top
 Profile  
Reply with quote  
 Post subject: Re: [SOLVED] Prepared Statement - Update-ODBC- data not appl
PostPosted: Mon Jun 03, 2013 8:03 pm 
Offline

Joined: Mon Jan 02, 2006 11:43 am
Posts: 172
Location: Calgary, Alberta, Canada
Hello Steve,

It looks like you had many attempts without any success. I have a few thoughts and suggestions, this stumps me is as you mentioned that it works with the SELECT statement... Hmmm....

Here are a couple things to check:
1) Dates are an issue with access because there are many different defaults. Could you try and check the date format? Even though the date format may look correct, Access sometimes has an issue. Try and convert the date format to its decimal equivalent (Example: 2013-06-03 8:53:26 AM = 41428.37044). Access usually works with the decimal equivalent.

2) Try and get your prepared statement to work with only one field update, as an example, see if you can update only the string (without any integer or date field update). If it works with the one field, then try and add the other fields and check for errors.

Could you post an example of the SQL update, as an example db.SQLExecute("UPDATE Library SET Author = 'E. Atkin', Inventory = 9, Title = 'How to fix a Car', Cost = 94.99 WHERE ID = 5") Feel free to change the actual terms used to keep your info confidential.

Note: The commit command does not need to be added to updating Access databases.

Sincerely,

Eugene


Top
 Profile  
Reply with quote  
 Post subject: Re: [SOLVED] Prepared Statement - Update-ODBC- data not appl
PostPosted: Tue Jun 04, 2013 4:00 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
eugenedakin wrote:
Hello Steve,

It looks like you had many attempts without any success. I have a few thoughts and suggestions, this stumps me is as you mentioned that it works with the SELECT statement... Hmmm....

Here are a couple things to check:
1) Dates are an issue with access because there are many different defaults. Could you try and check the date format? Even though the date format may look correct, Access sometimes has an issue. Try and convert the date format to its decimal equivalent (Example: 2013-06-03 8:53:26 AM = 41428.37044). Access usually works with the decimal equivalent.

2) Try and get your prepared statement to work with only one field update, as an example, see if you can update only the string (without any integer or date field update). If it works with the one field, then try and add the other fields and check for errors.

Could you post an example of the SQL update, as an example db.SQLExecute("UPDATE Library SET Author = 'E. Atkin', Inventory = 9, Title = 'How to fix a Car', Cost = 94.99 WHERE ID = 5") Feel free to change the actual terms used to keep your info confidential.

Note: The commit command does not need to be added to updating Access databases.

Sincerely,

Eugene


Thanks Eugene, your comments are valid and is what I ended up doing isolating the offending/problematic issues of the prepared statement. A sample project including an Access mdb file is here : https://dl.dropboxusercontent.com/u/946 ... esting.zip. It reproduces what I highlight in the following paragraphs. One caveat, I now have the latest ODBC driver but this does not alter the result.

From_NUG_List wrote:
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 will be creating a feedback issue regarding this.

ps. With Xojo just about due we might have to pick this topic up in the new forum.

_________________
Steve
rs2012 r2.1 Windows 7.


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

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:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group