MySQLTalk.org MYSQL discussions groups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  VB, MyODBC, MySQL problems          MySQLTalk.org Forum Index -> MySQL ODBC View previous topic :: View next topic   Author Message Schuetz, David Guest Posted: Mon Jun 28, 1999 3:04 pm    Post subject: VB, MyODBC, MySQL problems [posted to USENET, I hope somebody here can help me, too... ] I've developed a rough prototype app using MySQL/Linux with MyODBC on NT using an Access/VBA front-end. It works pretty well, but I've run into limits as to what I can do with Access, so I've been moving into a VB development mode. Unfortuately, no matter what I try I can't seem to get VB to write to my database. I've been using ADO, but getting "Insuficcient base table information" errors when I try to do a RecordSet.Update call. MS has this article on the problem: http://support.microsoft.com/support/kb/articles/q190/1/08.asp, saying that I basically need to use server-side cursors or straight-through SQL. Well, I can't seem to get server-side to work with MyODBC (I think it's not supported yet), and straight-through SQL was giving me fits since some of the fields I'm updating contain large blocks of HTML code, and I'd get SQL errors. I've even tried reverting to RDO or DAO, but still have cryptic error messages that don't really help me out, and I still can't get the update to happen. I've seen a lot of conversation here, but not much good advice about how to make this work. I've also seen a couple people claiming to have things working great, but I've been unable to get through to them in email... Does *anyone* have any good suggestions or magic answers on this one? I'm about at the end of my rope, and am considering (<gasp!>) moving to an all-MS solution, including IIS, MS-SQL, ASP, and NT (to replace Apache, MySQL, PHP, and Linux). I'd rather not do this... :-( I'd really like to get ADO working "seamlessly" (that is, like all the examples say), but if I have to, a method using straight SQL commands is good, too...anything to avoid having to re-engineer my entire back-end... Thanks!! david. Back to top Phillip Grant Guest Posted: Mon Jun 28, 1999 3:21 pm    Post subject: RE: VB, MyODBC, MySQL problems Hi David, I'm using Visual FoxPro, but had similar problems using local cursors and views(snapshots and recordsets in your terms), so I went to a SQL Passthrough methodology. You will probably have to go that route as well. You will run into some problems going that way, too, unfortunately. Pay close attention to the MySQL documentation on creating strings and escaping special characters with the "" character for your "INSERT" commands, and you should be OK. Phillip Grant "Getting there is half the battle, but most of the fun..." -----Original Message----- From: Schuetz, David [mailto:David_Schuetz (AT) tds (DOT) com] Sent: Monday, June 28, 1999 10:05 AM To: 'myodbc (AT) lists (DOT) mysql.com' Subject: VB, MyODBC, MySQL problems [posted to USENET, I hope somebody here can help me, too... ] I've developed a rough prototype app using MySQL/Linux with MyODBC on NT using an Access/VBA front-end. It works pretty well, but I've run into limits as to what I can do with Access, so I've been moving into a VB development mode. Unfortuately, no matter what I try I can't seem to get VB to write to my database. I've been using ADO, but getting "Insuficcient base table information" errors when I try to do a RecordSet.Update call. MS has this article on the problem: http://support.microsoft.com/support/kb/articles/q190/1/08.asp, saying that I basically need to use server-side cursors or straight-through SQL. Well, I can't seem to get server-side to work with MyODBC (I think it's not supported yet), and straight-through SQL was giving me fits since some of the fields I'm updating contain large blocks of HTML code, and I'd get SQL errors. I've even tried reverting to RDO or DAO, but still have cryptic error messages that don't really help me out, and I still can't get the update to happen. I've seen a lot of conversation here, but not much good advice about how to make this work. I've also seen a couple people claiming to have things working great, but I've been unable to get through to them in email... Does *anyone* have any good suggestions or magic answers on this one? I'm about at the end of my rope, and am considering (<gasp!>) moving to an all-MS solution, including IIS, MS-SQL, ASP, and NT (to replace Apache, MySQL, PHP, and Linux). I'd rather not do this... :-( I'd really like to get ADO working "seamlessly" (that is, like all the examples say), but if I have to, a method using straight SQL commands is good, too...anything to avoid having to re-engineer my entire back-end... Thanks!! david. --------------------------------------------------------------------- Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before posting. To request this thread, e-mail [email]myodbc-thread356 (AT) lists (DOT) mysql.com[/email] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [email]myodbc-unsubscribe (AT) lists (DOT) mysql.com[/email] instead. Back to top Schuetz, David Guest Posted: Mon Jun 28, 1999 7:00 pm    Post subject: RE: VB, MyODBC, MySQL problems Quote: For goodness sake, don't switch to an all-MS solution! I figured that'd get SOMEBODY's attention. :-) I've had three quick responses, all of which suggested I escape some of the more problematic characters (' and "). That seems to be working now, with a straight command execution (cmd.commandtext = "UPDATE ....", cmd.execute), but I've already discovered at least one extra character that needed escaping (?, for some reason). This approach always makes me nervous, mostly 'cause I'm never certain whether i've found all the possible problems or whether I'm just introducing new ones. Does anyone know how the "Visual Data Manager" does things? That Add-In seems to have no problems updating the DB, as long as I use "Dynaset" (instead of "Snapshot"). I'm gonna revisit my straight-ADO code and see if I can't figure out how to work some "dynaset" option into it... I'd really like to go to ADO, just 'cause it's a lot cleaner (rs!content = dhtml1.document.html, rs.update) than doing SQL stuff.. Thanks for all the help so far! david. Back to top Peter Carter Guest Posted: Tue Jun 29, 1999 2:33 pm    Post subject: Re: VB, MyODBC, MySQL problems I created a site to help with these sorts of problems. It is an online VB code wizzard for MySQL. It uses existing MyODBC and ADO/RDO. Yes, it's free...... ;) http://www.pbc.ottawa.on.ca At 08:04 AM 6/28/99 -0700, you wrote: Quote: [posted to USENET, I hope somebody here can help me, too... ] I've developed a rough prototype app using MySQL/Linux with MyODBC on NT using an Access/VBA front-end. It works pretty well, but I've run into limits as to what I can do with Access, so I've been moving into a VB development mode. Unfortuately, no matter what I try I can't seem to get VB to write to my database. I've been using ADO, but getting "Insuficcient base table information" errors when I try to do a RecordSet.Update call. MS has this article on the problem: http://support.microsoft.com/support/kb/articles/q190/1/08.asp, saying that I basically need to use server-side cursors or straight-through SQL. Well, I can't seem to get server-side to work with MyODBC (I think it's not supported yet), and straight-through SQL was giving me fits since some of the fields I'm updating contain large blocks of HTML code, and I'd get SQL errors. I've even tried reverting to RDO or DAO, but still have cryptic error messages that don't really help me out, and I still can't get the update to happen. I've seen a lot of conversation here, but not much good advice about how to make this work. I've also seen a couple people claiming to have things working great, but I've been unable to get through to them in email... Does *anyone* have any good suggestions or magic answers on this one? I'm about at the end of my rope, and am considering (<gasp!>) moving to an all-MS solution, including IIS, MS-SQL, ASP, and NT (to replace Apache, MySQL, PHP, and Linux). I'd rather not do this... :-( I'd really like to get ADO working "seamlessly" (that is, like all the examples say), but if I have to, a method using straight SQL commands is good, too...anything to avoid having to re-engineer my entire back-end... Thanks!! david. --------------------------------------------------------------------- Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before posting. To request this thread, e-mail [email]myodbc-thread356 (AT) lists (DOT) mysql.com[/email] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [email]myodbc-unsubscribe (AT) lists (DOT) mysql.com[/email] instead. --- Peter B. Carter (peterc (AT) pbc (DOT) ottawa.on.ca) Pager: 613-751-4660 http://www.pbc.ottawa.on.ca Flames are a waste of cyberspace, don't bother....... Back to top Schuetz, David Guest Posted: Tue Jun 29, 1999 6:45 pm    Post subject: RE: VB, MyODBC, MySQL problems Quote: I created a site to help with these sorts of problems. It is an > online VB code wizzard for MySQL. It uses existing MyODBC and ADO/RDO. http://www.pbc.ottawa.on.ca Excellent! I was trying to get through there yesterday, but something was broken. I ran your wizard thingie and the output's pretty interesting. Seems, though, that you're still not really taking full advantage of ADO, as far as the idea of a recordset entry with all selected fields available automagically, with semi-automatic updates and such. This is basically a class providing a nice wrapper around straight SQL stuff... ....that said, however, it does look like much of what's in there I can reuse, in "simpler" configurations (my model doesn't really require a consistent open connection--I just do a query--process--close, or an update of a single field). Also, you gave me a much faster, fairly complete-looking escape function. How would you recommend storing large blocks of text (in my case, HTML code generated by the MS DHTML widget, in all its microsoft bloated html code glory)? You've got both a "put literal" function (doing the escaping bit for special chars) and a "put binary" function, for turning everything into pure hex bitstreams. I can see the appeal of the latter, but you have some warnings in your code about buffer sizes and such... Which approach is likely to be "best" (easiest, fastest, least trouble)? If the binary method, could you help me out a little with those warnings? (what do I have to watch out for, etc.)? Thanks again! david. Back to top Peter Carter Guest Posted: Tue Jun 29, 1999 10:42 pm    Post subject: RE: VB, MyODBC, MySQL problems At 11:45 AM 6/29/99 -0700, you wrote: Quote: Excellent! I was trying to get through there yesterday, but something was broken. I ran your wizard thingie and the output's pretty interesting. YOYO FreeBSD/MySQL due to overheating...... problem solved ;) Quote: Seems, though, that you're still not really taking full advantage of ADO, as far as the idea of a recordset entry with all selected fields available automagically, with semi-automatic updates and such. This is basically a class providing a nice wrapper around straight SQL stuff... The object was to create a consistant interface for any object. MySQL is not the only target and VB is not going to be the only code generated. VBS is about half done and then I will be on to C++. My connector of choice is RDO, as it is much cleaner and leaner. I put the ADO support in due to licence restrictions of RDO. If I let ADO go on autopilot, I would run into the same problems as everyone else and be as consistant as the version numbers. I like stability. I have plans to write a native VB driver for MySQL, so I must also leave the door open for that as well. Quote: ...that said, however, it does look like much of what's in there I can reuse, in "simpler" configurations (my model doesn't really require a consistent open connection--I just do a query--process--close, or an update of a single field). Also, you gave me a much faster, fairly complete-looking escape function. Check out the function 'DBClose'. This effictively allows you to keep your recordset, close the connection and re-open on demand. The advantage here is that you do not have to re-select. There is even a hook for a whatch-dog for idle applications. Quote: How would you recommend storing large blocks of text (in my case, HTML code generated by the MS DHTML widget, in all its microsoft bloated html code glory)? You've got both a "put literal" function (doing the escaping bit for special chars) and a "put binary" function, for turning everything into pure hex bitstreams. I can see the appeal of the latter, but you have some warnings in your code about buffer sizes and such... Which approach is likely to be "best" (easiest, fastest, least trouble)? If the binary method, could you help me out a little with those warnings? (what do I have to watch out for, etc.)? The reason I use both, is that some data types can not accept a binary hex equivalent. I found the escapes just did not work properly due to os conversions, lack of adiquite documentation and version issues. If you look at the MySQL docs, they list more escapes than are actually supported. You will be lucky to find them at all in the MicrosoftSQL docs. If you use TEXT type for the html code, use the deliminators. It's just faster. If you are concerned about conversion errors, use the hex approach. If you are using the VARCHAR or CHAR types, I do not think you can use hex at all? (Maybe this is Microsoft....) Buffer sizes are important. However, my updates chunk the data. This means, with stock buffers, they will be properly stored. You may have to open them up to read all the data though. My choice is based on the use. Steel all the code you want. I designed it to DO and TO LEARN. If you can find a better / faster way to do the job, let me know...... ;) --- Peter B. Carter (peterc (AT) pbc (DOT) ottawa.on.ca) Pager: 613-751-4660 http://www.pbc.ottawa.on.ca Flames are a waste of cyberspace, don't bother....... Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         MySQLTalk.org Forum Index -> MySQL ODBC All times are GMT Page 1 of 1   Jump to: Select a forum MySQL----------------MySQL and JavaMySQL AnnouncementsMySQL++MySQL and .NETMySQL ODBCMySQL and PerlDatabase BenchmarksMySQL BugsMySQL Cluster DiscussionMySQL CommunityMySQL DocumentationEventum DevelopmentEventum General DiscussionMySQL General DiscussionMySQL GUI ToolsMySQL InternalsMySQL CommitsMaxDBMySQL PackagersMySQL Replication DiscussionBrisbane MySQL Users GroupMySQL on Win32  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 You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.