MYSQL :: MyODBC - Updating Joins

MyODBC - Updating Joins
I am using MyODBC (35017) in VB and i get an error when I try and update a recordset which involves a join. Surely one isnt limited to only single tables?

Recordset.CursorLocation = adUseServer

Recordset.Open "SELECT pd.products_name 'Product Name', p.products_quantity 'Product Stock' FROM products p, products_description pd WHERE p.products_id = pd.products_id;", Connection, adOpenForwardOnly, adLockOptimistic

Recordset(1) = 5
Recordset.Update
Recordset.Close

Gives me the following error: "Query clause can not be updated because the FROM clause is not a single simple table name"

Natural Joins And Joins With USING
I was wondering if there is a way to make MySQL 5.0.15 ( final release ) able to use natural joins and joins, using old code that worked with 5.0.11 and earlier.

Since I am new to starting mysql and modifying it, more information the better.

MyODBC
I have a mySQL database hosted by a company. I installed the myODBC connector and need to connect to it in a vb6 program. I set up a user and password for the database i want to connect to. My string looks like this:

Dim conn As New ADODB.Connection

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=rentapar_System_Property;UID=rentapar_Client;PWD=password;"

it says it can't connect to mySQL server on 'localhost'
I am not getting where it is looking for the server. Do I need to set up a DSN? If so what do I use as server? Where do I point it to the hosted server? Please help!?? Do most hosting companies allow access from off the net like an application?

MyODBC
I have problem in using ODBC for Voice Guide (an IVR application). I can connect to MySQL database successfully. The only thing is that everytime the system tried to connect to MySQL database through ODBC the Connector/ODBC Configuration Windows Pops Up. I have tried to pass Option=16 and also tick "Don't Prompt Upon Connect" property on Flag 2 but the window still pops up.

Here is the details of my system:
OS: Windows XP Pro SP2
MySQL Server: version 4.1.11-log via TCP/IP
Client version: MySQL Client Version 5.0.11
ODBC version 3.51

I didn't have this problem before I reformat my harddrive and reinstall Windows.

MyODBC 3.51
I have a C# program which uses a remote MySQL database to store user information. It uses MyODBC 3.51. However, I find making people download and install MyODBC seperately from the application rather annoying. Is there any way I can package MyODBC with the installation package for my application

MyODBC To OpenOffice.org
I'm trying to connect OpenOffice.org to MySQL through MyODBC. MySQL is
set up, but I'm having problems setting up MyODBC. I checked the
documentation on the MySQL webpage, and tried to set it up that way, but
OpenOffice.org can't find MyODBC. It can't find the libodbc.so.1. I
tried to follow the set up PDF file on the OpenOffice.org webpage, but it
switches between MyODBC and UnixODBC.

I copied the libraries to /usr/lib, and made the link in /usr/local. I
set up the obdb.ini in /usr/etc. Is there something I'm missing to set up
the connection? Has anyone had luck with this?

Myodbc Error
------=_NextPart_000_000D_01C35CF0.20AC1200
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

i get this msg:
Microsoft.Data.Odbc.OdbcException: NO_DATA - no error information =
available
at Microsoft.Data.Odbc.OdbcConnection.HandleError(Int Ptr hHandle, =
SQL_HANDLE hType, RETCODE retcode)
at Microsoft.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, =
Int32 cb)
at Microsoft.Data.Odbc.OdbcDataReader.internalGetStri ng(Int32 i)
at Microsoft.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)
at Microsoft.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
at Microsoft.Data.Odbc.DbCache.AccessIndex(Int32 i)
at Microsoft.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
at Microsoft.Data.Odbc.OdbcDataReader.get_Item(String value)
at =
F=E6llesDialogbox.frmAddIngDialog.cboVaregruppe_Se lectedIndexChanged(Obje=
ct sender, EventArgs e) in C:Documents and SettingsAdministratorMy =
DocumentsVisual Studio =
ProjectsDatasystemKSF=E6llesDialogboxfrmAddIng Dialog.vb:line 357

then i try to get a row out fo the database mysql 3.23.54 run on redhat =
linux 9.0 i am acceessing it from vs.net via odbc.net.
i am using myodbc 3.51 and my code look like this:

Try
Dim plunr As Integer
Dim varenavn As String
Dim varegruppenr....................................................

MyODBC Driver
Trying to use MySql 5.0.27 with Dreamweaver MX & PHP & Apache

In "Inside Dreamweaver MX" the authors state 'If you're using MySQL as your DBMS, you need to use the MyODBC driver to create your DSN's See Appendix C for full coverage of obtaining & setting up MyODBC' Unfortunately Appendix C does not mention MyODBC although is does mention MySQL.

They are referring to an earlier version of MySQL (3.23) and show setting up the DSN in windows.

My problem is I cannot see the MyODBC driver. Is this a separate download?

MyODBC And Citrix
My client (and hence I!) need to install MyODBC on a Citrix server, but
when the MyODBC installation .exe is run, it fails. The .msi also fails.

Investigation on the web indicates that this might have something to do
with the Citrix environment not liking "hardcoded" paths in installation
programs, but naive fumbling with Orca on the .msi didn't really help.

Has anyone succeeded in installing MyODBC in a Citrix environment, and
if so, how?

Uninstall MyODBC
How do I uninstall myODBC from wind98? everything was working, then badness at the shop so... I have reloaded wind98,mySQL now mySQL works OK but I cant setup myODBC (setup says myODBC installed ok but when I try to make data source it says drivers no good, reinstall...

looks to me like I need to uninstall, but myODBC has no uninstall Alas! does anyone know whaich registry entries to delete?

MyODBC Time
recenlty I wanted to migrate my app from MySQL 3 to MySQL4.1.19, conector: MyODBC 3.51.06 into MyODBC 3.51.11-1win.

Now I have a problem with time values from my database. In a field I have the value '00:00:01'. When I retrieve this value in my app (via MyODBC) i get a null value. I don't want that null value, I vant my '00:00:01'.

Before the migration, I hadn't this problem ... everything worked fine. Is there a setting in MyODBC that I must check to solve this? If not, how can I solve this problem ... quick?

MyODBC Mapping
I have an VB frontend that can't be changed for various reasons, and have
been attempting to move the database (access) out to MySQL (for various
other reasons ;). I haven't been able to successfully read True/False
fields, and after much research it appears that I need to map any true/false
or yes/no to 1/0 in the MyODBC source code.

I've downloaded the source code, but I haven't been able to determine what
and how to change the mapping. Any assistance would be GREATLY appreciated!

FYI: I tried treating the MySQL side as an enum field, but the VB front end
won't accept the result.

MyODBC VB6 Problem
I have made a program that runs off the task scheduler. It connects to a web database using MyODBC. This works most of the time, but once in a while i'll get the MyODBC DSN connection window. I'm not sure why this pops up. should i upgrade the MyODBC from version 2.5 to the newest version? If this is not the case, is there a way to hide this in code or through settings.

MyODBC Driver
I have winxp as OS & my php folder is located in c:XXXXphp.

I am getting these errors while running a php code-

Warning: mysqli::mysqli() [function.mysqli-mysqli]: (28000/1045): Access denied for user 'ODBC'@'localhost' (using password: NO) in C:wampwwwprosenjitOODatabase.class on line 16

Warning: mysqli::query() [function.mysqli-query]: Couldn't fetch mysqli in C:wampwwwprosenjitOODatabase.class on line 32
Database is not available. Try again later

Will installing 'MyODBC Driver' solve my problem? What other things should i ensure so as not to get these error.I have WAMP5 installed.

Connector MyODBC
Can I using MyODBC to connect to MySQL Server through A Proxy Server ?

MySQL / MyODBC
I installed MySQL v4.0.20d and MyODBC v2.50.39 on a Windows 2000 workstation SP4. When I go to StartProgramsAdministrative Tools I don't see the Data Sources menu option. When I installed the same software on a Windows 2000 Server, I do see the Data Sources menu option.

MyODBC And DB Access
I setup Apache, PHP, and Mysql on our internal server probably 5 years ago. It works so good I NEVER have to do anything so I've gotten really rusty. The problem is we use UPS through MyODBC to add shipping data to our DB. We recently got a new computer to run the UPS stuff. I can't get MyODBC to connect from the new computer to save my life.

I joined our domain restarted a bunch of time and still when I setup the system I get an error to the effect of "Matt@MASTER" password =yes access denied. Now here is the kicker. the computer name isn't master its been renamed. I removed the 3.5 MyODBC driver and installed the 2.5 like the old computer had just to be sure. It is doing the same thing. So I found a way thanks to google to grant access to that username in a range of IP addresses.

here it is: "GRANT ALL PRIVILEGES ON db.* TO matt@'192.168.0.0/255.255.255.0';

which has a result of 0 rows effected!?

I have tried a different user when using the mysql program on the server machine and been able to login with it and the matt user mentioned above. However still nothing at the MyODBC end with the UPS system.

Configuring MyODBC
I am using MyODBC 3.51 with Borland BDE, and was having a problem whereby Blob result fields were being restricted to a maximum size of 32k. Now, in the BDE control panel, I can set configuration variables for the MyODBC driver, and I was able to change the value of BLOB SIZE from 32 to 128, which solved the problem. However, there are many other configuration variables there (TRACE MODE, BATCH COUNT, BLOBS TO CACHE, ENABLE SCHEMA CACHE, ROWSET SIZE, etc.) for which I have not been able to find any documentation. Does anybody know where I can find documentation for these settings?

MyODBC Install
For the fourth day I'm trying to install MyODBC. I installed the old 2.5 ver as well as the newest 3.5.The install completes successfully but when I go to the ODBC admin and try to add a new DSN, mysql is not in the driver list. I checked the registry and it is there- but not in the list.

Please someone help, I've never been pushed this far back in development by something that seems so simple / are there any 3rd part MySQL drivers I can use?

MyODBC And Remote
I'm conected to server and my app works just fine, and I was connecting to MySQL over ODBC (my app is running on winXP)! It was good while I was using only USERNAME with blank PASSWORD in MySQL! But when I put password, I can't connect, and of course my USER/PASS combination is OK! I try to search for known MySQL bug in this version but with no results!

Views And MyODBC
well i just installed MySQL "5.0.1-alpha-nt-max" to play around with the new Features on my local PC.reating and accessing Views seems to work, but as it seems MyODBC 3.51.09 cant handle Views.
Are there any plans to support Views also via MyODBC ?
If so, is that Feature currently in the Bitkeeper-Repository of "MySQL Connector/ODBC 3.52" ?

Retaining Control From MyODBC
I'm developing a system with an Excel front-end that uses ADODB and MyODBC to fire off instructions to a (local machine) MySQL server. As I step through my VBA code, it sends an instruction to MySQL (e.g. gconMySQL.Execute "DROP TABLE IF EXISTS MyData;" ), waits for the instruction to be executed, before returning control to the code and moving on to the next VBA statement. All well and good; this works fine for the majority of the processing.

However there is some initialisation of the MySQL database, particularly loading some data tables into MEMORY, that takes up to half a minute to complete. I'd rather fire off the instruction(s) to MySQL to do this so that MySQL initialises invisibly in the background, whilst the user continues playing around with the front-end themselves.

Linking MyODBC Drivers
Could someone please help me with the linker options for linking the MySQL ODBC drivers. I'm running on Redhat Linux, with gcc 3.4.6.

Myodbc 3.51 Connector Freezes
I just installed mysql server 5.0 and myodb driver 3.51. Now I'm trying to make a DSN on my system. When I enter the Data Source Name, Server, User and Password, then when I click the Database drop-down menu, the program freezes. It does not do anything, just sits there. I set up the database properly, and it is configured and ready to go but I cannot get past this.

Slow Performance Using MyODBC
I am creating a pair of applications that essentially transfer data from a remote client to a local server. The trick is that "remote" in this application means "way out in the mountains where it takes an hour by four wheel drive to reach the nearest paved road and don't even talk to me about a cell tower for another hour or so of driving".

Because of this, we are using shoe-leather-net to communicate between the client and the server. The client is collecting data into a MySQL table. Every so often, a person will use the client application to copy the data onto a thumb drive and carry it down the mountain to the server, where the partner application will copy the data into an identical MySQL table. These are distinctly non-technical people I am talking about here - typing a file name is a high-skill activity - so this needs to be a dead-simple user interface.

The data is sensitive, so it is encrypted going on to the thumb drive and decrypted coming off.

Here is the question: The copy process that I have programmed is very slow - on average 2 seconds per row - on both ends of the transaction and this does not seem right. What can I do to improve it?

When the process is running, Task Manager shows that mysqld-nt is using as much of the CPU as it can get and the actual application is only using CPU time once in a while.

The basic client task is this:
-- Read a row from the local table
-- Encrypt the row
-- Write the encrypted row to the file
-- Repeat until all of the unexported data has been copied

The basic server task is the inverse:
-- Read a line from the file
-- Decrypt the line and construct a corresponding INSERT statement
-- Execute the INSERT
-- Repeat until end-of-file

Other possibly pertinent information:
-- Windows XP Pro
-- Visual Basic.Net
-- MySQL 4.1.14-nt
-- MyODBC 3.51.13 Dave Gee

MyODBC Mapping Question
I have an VB frontend that can't be changed for various reasons, and have
been attempting to move the database (access) out to MySQL (for various
other reasons ;). I haven't been able to successfully read True/False
fields, and after much research it appears that I need to map any true/false
or yes/no to 1/0 in the MyODBC source code.

I've downloaded the source code, but I haven't been able to determine what
and how to change the mapping. Any assistance would be GREATLY appreciated!

FYI: I tried treating the MySQL side as an enum field, but the VB front end
won't accept the result.

MyODBC Command Syntax
I'm trying to do what I thought was a fairly simple thing, select a group of
records from a mySQL database.
I'm not much of an SQL guy. The syntax I would use for Microsoft SQL is
shown on the "query =" line.
A myODBC error message indicates this is not supported syntax.
Can anyone help me?

{ ADO Startup Values and Creation using the .Open method}

{Create a COM object reference to ADO Recordset.}
recordset = COM_CreateObject("ADODB.Recordset");

{ Open a Recordset }
query = "SELECT * FROM t_course_date
WHERE(modified>=CONVERT(DATETIME,'1999-07-31 00:00:00',102))";
("modified" field above is a mySQL field name)

recordset.Open(query, 'connection id', adOpenDynamic,adLockOptimistic); <--
This statement fails.

C++ Wrapper For UnixODBC/MyODBC

I used an odbc-wrapper connectting mysql with unixODBC.
But it didnt work well, often some exception occured.
So I need one new wrapper for unixODBC, it must run stably.

MS Access, MySQL & MyODBC .....
I am using MySQL ODBC 3.51 Driver, with MS Access 2003 and MySQL 4.1.11
standard log.

I created my tables in MS Access, then exported them via ODBC to an
externally hosted MySQL database (fasthosts) . I then import-linked
them back into my MS Access database.
Before I import linked them back, I edited the tables in MySQL so that
the PK & Autonumber was set and added a timestamp field.

The problem I seem to have is that when I use the Timestamp field in
MySQL, I cannot seem to update current records on my Access forms
without getting the error 'Reserved Error (-7776)'. If I remove the
Timestamp field, the error goes away, but then I get the usual #Deleted
problem when adding records etc.

I know the timestamp is required, but I cannot seem to get arround this
unknown error ?

MyODBC 3.51 To MySQL 5.0 Alpha
I am recently testing out MySQL 5.0.0_2 Alpha on a FreeBSD 5.2 server.
Also, I am experimenting with MS Windows connectivity, and although the MySQL Administrator application works beautifully, I seem unable to establish an ODBC DSN to the database.
I installed MyODBC 3.51.06 but it fails upon authentication with the following message:
[MySQL][ODBC 3.51 Driver] Client does not support authentication protocol requested by server; consider upgrading MySQL client
And am I considering, but it doesn't help. Any good suggestions or is it not yet possible to connect through ODBC

MyODBC And Hierarchical Datasets
Has anyone tried (and succeded?) in specifying the appropriate connect
string for a hierarchical dataset using MyODBC?
It's where you specify MSDATASHAPE as your "Provider" but then need to
designate MyODBC as the "Data" Provider.

How To Set Multiple Statement In MyODBC Connectionstring
http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-connection-parameters.html
If I want to set multiple command lines in "stmt" parameter, how to do that?

My current connection string
strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; CharSet=utf8; Port=3306; Option=0; Socket=; Stmt=SET names 'utf8' Database=yyy; Uid=xxx; Pwd=zzz;"

Need To Install MyODBC 3.51.14 On Windows Client
We have recently upgraded the MySQL client on a Windows machine to 5.0.38. In concert with that upgrade, we must ensure that MyODBC 3.51.14 is properly installed, as well.

After executing searches on the mysql.com website, I cannot locate guidance for the following issues:

1) To what link do I navigate to download the MyODBC 3.51.14 installation files?

2) What must I do to the client machine to prepare it for the 3.51.14 installation? (e.g. uninstall the old MyODBC driver?)

3) What specific steps do I follow to successfully install MyODBC 3.51.14 on the client machine?

MyODBC With Access Update Issue
I am having a problem with using MSAccess (2003) on the front end of a MySQL DB (4.1.10a). When I add a timestamp field, the Access table will allow me to add the record and provides a proper refresh without a #deleted# record. When I attempt to update the table in Access, I get an error of SQL data out of range error. If I delete the timestamp field, I can update the record, but then get the dreaded #deleted# record information. MyODBC version is 3.51. I have upgraded the MDAC to the latest version.

MyODBC 3.51 Executing Multiple SQL Statements
I am using MyODBC from VB and I want to submit a batch of insert statements
in one call from my App. This is more efficient than making multiple calls
from code because of the communication overhead.

If I send a batch multiple statements separated by ; or ; + newline I get
syntax errors pointing at the start of the next statement..
If I fire each statement one at a time with the ; at the end there's no
problem.

I cannot be the first person with this problem. Is it common practice to
make multiple calls from you application or is there a magic trick I am
missing? Code:

Getting Rid Of The Failed Setups In MyODBC Driver?
Anyone with any experience with MyODBC 3.51 linking MySQL tables to MS
Access 97 please tell me how I can clear the miscellaneous, failed
setup attempts from under the Machine Data Source tab on the Select
Data Source window?

Everytime I clicked "NEW", trying variations of configuration options
in hopes of making it work resulted in a new Machine Data Source being
set up. Now I have to look at all the failed ones along with the one
successful configuration everytime I use it to renew my attachments.
Ideas on how to get rid of 'em?

MyODBC Connection Dies During Data Transfer
I've got a table of about 400,000 records but when I transfer it from Access
to MySQL, the ODBC connection dies around 3,000 records or so (or about
3-5mins) with this error:

ODBC -- call failed

[MySQL][ODBC 3.51 Driver][mysqld-3.23.41] Lost connection to MySQL server
during query (#2013)

Once this error occurs, the table and uploaded records are removed from
MySQL.

But I've transferred other tables with a few thousand records just fine.
One query even runs for about 10 minutes or more with no problem.

Any ideas?

Using MyODBC (Linux Version) - Bracketed UNION
I've come across a strange issue when using UNION via MyODBC on my
Fedora linux box (I'm using MySQL 4.1.3-standard-beta)

**This doesn't work:

(select a from tbl_name where a=10 and b=1 order by a)
union
(select a from tbl_name where a=11 and b=2 order by a)
order by 1

(took this straight from the MySQL manual)

**Removing parentheses doesn't work either:

select a from tbl_name where a=10 and b=1 order by a
union
select a from tbl_name where a=11 and b=2 order by a
order by 1

**But this does work:

select a from tbl_name where a=10 and b=1
union
select a from tbl_name where a=11 and b=2
order by 1

- so it appears that ORDER BY is not allowed in an individual select
when it is being amalgamated with UNION (or UNION ALL).

Interestingly, the first statement works fine when executing from the
MySQL command line.

Has anyone else spotted this, and is there a workaround (other than
not ordering select clauses that are UNIONed together)?

MyODBC Error On Windows Server 2003 - Unable To Set Up DSN
I recently installed MySQL on my Windows Server 2003 and got to know that one needs MyODBC also, in order to make ASP webpages able to access the MySQL database.

Now, here is my problem.
I installed MyODBC-3.51.06.exe on my server. The installation went OK. But when I want to make a User DSN or System DSN i get the following errormessage:

"The setup routines for the MySQL ODBC 3.51 Driver ODBC driver could not be loaded due to system error code 126." What do I do?

When clicking OK, the following warningbox appears:

"Could not load the setup or translator library."

Command Line Parameters For MyODBC 3.51 Windows Installer
i want to install the MyODBC driver for windows.
i donwloaded the newest installer from the mysql website.

-> MyODBC-3.51.06.exe

now i know the command line parameter for a silent installation (/s),
but i wonder if there are more.Logfile.

MyODBC And Hierarchical Datasets (aka "SHAPE" Commands)
Has anyone tried (and succeded?) in specifying the appropriate connect
string for a hierarchical dataset using MyODBC?

It's where you specify MSDATASHAPE as your "Provider" but then need to
designate MyODBC as the "Data" Provider.

Updating From 4.0 To 4.1
My web host will update the server to MySQL 4.1 this week.
What's the difference? Should I look for something special in my code that might not work after that?
They will probably update to ver 5 later as well. But not yet.

Updating
If I have a bunch of cells that say: ,4,

and i want to update them with another number (5, and 6, -> $var) so that I would get results like ,4,5, and ,4,6, how would I go about updating that?

I notice, if I defined $var, and just use a normal update command, it just replaces it, but I want to keep the 4 in there (not necessarily always 4, but this is an example, as that too is a different number each time).

I tried using variables as well, but if I did anything within the while method, I couldn't bring it to the query. Any suggestions?

Updating A Row ....
I have an application for employee punch in/out. My table structure is, ID,Date,Employee Name,In-time,Out-time. Now when on the first punch in of the day, it will add a new record for that employee for that date only in in-time field. Next time, when same employee punches out, it should update the same existing record and put time value in out-time field. Next time when again, same employee tries to punch in, a new record gets added with value in only in-time field and so on..

Now, a totally weird thing happens. Whenever employee tries to punch out (so there is a value in in-time field), it updates that record as well as it adds a new record with same out-time value in in-time field. So in nutshell, it updates the existing record and adds a new record. I checked my script 50 times and put debugging code, but i don't find any errors.