Converting SQL from Access to MySQL 5.0 Using ASP interface

We recently decided to migrate our site from Access to MySQL. I must say
I was very pleased with the installation process and I'm thrilled with
all of the support that MySQL gives over Access.

I'm running on XP if that's at all helpful and I'm using the 3.51
driver. (Is there any more information I should give?)

I think I've worked out most of the syntactical bugs for getting
information out of the database, so now I'm working on putting new
information in it (or updating it).

I've got a form with many fields and it's set up in the database so that
each user's answer to each question is it's own record. So when I
process the form I've got 30 or so update queries that run. The problem
I'm having is that some of them are successfully updating and others
aren't but my comm object [set
conn=server.createobject("adodb.connection")] isn't registering that
there's a problem.

So these succeed:

UPDATE LMAnswer SET LMAanswer= 'It''s nice to have 5' , LMAupdated=
now() WHERE UserMapID= '296692.1.6' AND LMinputID= 1

UPDATE LMAnswer SET LMAanswer= '1/1/07' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 51

but these fail:

UPDATE LMAnswer SET LMAanswer= 'I''m' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 5

UPDATE LMAnswer SET LMAanswer= 'here' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 6

UPDATE LMAnswer SET LMAanswer= 'there' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 7

UPDATE LMAnswer SET LMAanswer= 'and' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 8

LMAanswer is LONGTEXT, UserMapID is VARCHAR, LMAupdated is DATETIME, and
LMinputID is INT(10)

I would be less puzzled if none of them were succeeding, but some do and
so I'm confused. How can I get all of these update queries to run? Or,
is there a more efficient way to do this in MySQL that I should know
about? (I've seen references to being able to do multiple inserts of
values in one query. Maybe I can do multiple updates?)

As a side question, when I click refresh in the query browser the data
from the query doesn't refresh. I have to execute the query in a new
tab. Am I doing something wrong?

Thanks much.

Joelle



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Converting SQL from Access to MySQL 5.0 Using ASP interface

Where to begin. XP is not a server environment, so you should consider
server 2000 or 2003.

Good db design does not agree with a record per field. Generally the form is
a record with each field a part of the record allowing you to recover what
was give to you. That will also agree better with the submission process 1
submission many fields in 1 record.

The many record process should require that you look at a transactional
model or stored procedures for you control.

I strongly suggest that you step back and consider your design to correctly
align the data to the database for the ultimate results you seek.

Leif

Leif Johnston
Managing Partner
Technology Catalyst

-----Original Message-----
From: Joelle Tegwen [mailto:tegwe002@umn.edu]
Sent: Friday, October 21, 2005 1:12 PM
To: win32@lists.mysql.com
Subject: Converting SQL from Access to MySQL 5.0 Using ASP interface

We recently decided to migrate our site from Access to MySQL. I must say
I was very pleased with the installation process and I'm thrilled with
all of the support that MySQL gives over Access.

I'm running on XP if that's at all helpful and I'm using the 3.51
driver. (Is there any more information I should give?)

I think I've worked out most of the syntactical bugs for getting
information out of the database, so now I'm working on putting new
information in it (or updating it).

I've got a form with many fields and it's set up in the database so that
each user's answer to each question is it's own record. So when I
process the form I've got 30 or so update queries that run. The problem
I'm having is that some of them are successfully updating and others
aren't but my comm object [set
conn=server.createobject("adodb.connection")] isn't registering that
there's a problem.

So these succeed:

UPDATE LMAnswer SET LMAanswer= 'It''s nice to have 5' , LMAupdated=
now() WHERE UserMapID= '296692.1.6' AND LMinputID= 1

UPDATE LMAnswer SET LMAanswer= '1/1/07' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 51

but these fail:

UPDATE LMAnswer SET LMAanswer= 'I''m' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 5

UPDATE LMAnswer SET LMAanswer= 'here' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 6

UPDATE LMAnswer SET LMAanswer= 'there' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 7

UPDATE LMAnswer SET LMAanswer= 'and' , LMAupdated= now() WHERE
UserMapID= '296692.1.6' AND LMinputID= 8

LMAanswer is LONGTEXT, UserMapID is VARCHAR, LMAupdated is DATETIME, and
LMinputID is INT(10)

I would be less puzzled if none of them were succeeding, but some do and
so I'm confused. How can I get all of these update queries to run? Or,
is there a more efficient way to do this in MySQL that I should know
about? (I've seen references to being able to do multiple inserts of
values in one query. Maybe I can do multiple updates?)

As a side question, when I click refresh in the query browser the data
from the query doesn't refresh. I have to execute the query in a new
tab. Am I doing something wrong?

Thanks much.

Joelle



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=leif@technologycatalyst.c om



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

When the conversion goes live it will be on 2003. I'm just running it on
my local host to get all the bugs worked out before I roll things out.
Our test server is 2003 also.

I've got pretty clear instructions to get this site out of Access ASAP.
Redesigning the architecture is only an option if it *won't work the way
it does. We're planning some pretty major changes down the road but this
is the way it is set up right now.

I am running this with transactions - at least I think I am. I was doing
the transactions in ASP because access didn't have a mechanism and I
rolled them over to MySQL, I think, but I don't know how to test if it
works because I'm not sure how MySQL sends an error back to me to let me
know there was an error (that doesn't stop the code).

Thanks
Joelle

Leif Johnston wrote:

>Where to begin. XP is not a server environment, so you should consider
>server 2000 or 2003.
>
>Good db design does not agree with a record per field. Generally the form is
>a record with each field a part of the record allowing you to recover what
>was give to you. That will also agree better with the submission process 1
>submission many fields in 1 record.
>
>The many record process should require that you look at a transactional
>model or stored procedures for you control.
>
>I strongly suggest that you step back and consider your design to correctly
>align the data to the database for the ultimate results you seek.
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Converting SQL from Access to MySQL 5.0 Using ASP interface

--=_alternative 00612C97852570A1_=
Content-Type: text/plain; charset="US-ASCII"

I think I disagree with you Leif. If I read your answer correctly, you are
suggestion that she create one row of answers where each answer sits in
it's own column. A test with 20 answers would take up 20 columns. A test
with only 5 answers would use only 5 columns. That is not a very flexible
or normalized design as it requires you to change the design of your
answer table if you ever administered a test with more than 20 questions.

Her design allows for storing any number of questions and answers
regardless of test size. I guest that there is one table to hold the
metadata for each test (who made it, what it's called, the date it was
administered), one table for all of the questions from all of the tests,
and one table for all of the answers. This is quite flexible and is highly
normalized. More questions/more answers = more rows not more columns.

My question for Joelle is: Why are you UPDATE-ing to log each answer?
Shouldn't you be INSERT-ing? You can only UPDATE an answer if a row for
that answer already exists. If you didn't pre-load your answer table with
all blank answers, your UPDATE-as-you-go strategy will fail as there will
be nothing to UPDATE.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Leif Johnston" wrote on 10/21/2005 01:19:33
PM:

> Where to begin. XP is not a server environment, so you should consider
> server 2000 or 2003.
>
> Good db design does not agree with a record per field. Generally the
form is
> a record with each field a part of the record allowing you to recover
what
> was give to you. That will also agree better with the submission process
1
> submission many fields in 1 record.
>
> The many record process should require that you look at a transactional
> model or stored procedures for you control.
>
> I strongly suggest that you step back and consider your design to
correctly
> align the data to the database for the ultimate results you seek.
>
> Leif
>
> Leif Johnston
> Managing Partner
> Technology Catalyst
>
> -----Original Message-----
> From: Joelle Tegwen [mailto:tegwe002@umn.edu]
> Sent: Friday, October 21, 2005 1:12 PM
> To: win32@lists.mysql.com
> Subject: Converting SQL from Access to MySQL 5.0 Using ASP interface
>
> We recently decided to migrate our site from Access to MySQL. I must say

> I was very pleased with the installation process and I'm thrilled with
> all of the support that MySQL gives over Access.
>
> I'm running on XP if that's at all helpful and I'm using the 3.51
> driver. (Is there any more information I should give?)
>
> I think I've worked out most of the syntactical bugs for getting
> information out of the database, so now I'm working on putting new
> information in it (or updating it).
>
> I've got a form with many fields and it's set up in the database so that

> each user's answer to each question is it's own record. So when I
> process the form I've got 30 or so update queries that run. The problem
> I'm having is that some of them are successfully updating and others
> aren't but my comm object [set
> conn=server.createobject("adodb.connection")] isn't registering that
> there's a problem.
>
> So these succeed:
>
> UPDATE LMAnswer SET LMAanswer= 'It''s nice to have 5' , LMAupdated=
> now() WHERE UserMapID= '296692.1.6' AND LMinputID= 1
>
> UPDATE LMAnswer SET LMAanswer= '1/1/07' , LMAupdated= now() WHERE
> UserMapID= '296692.1.6' AND LMinputID= 51
>
> but these fail:
>
> UPDATE LMAnswer SET LMAanswer= 'I''m' , LMAupdated= now() WHERE
> UserMapID= '296692.1.6' AND LMinputID= 5
>
> UPDATE LMAnswer SET LMAanswer= 'here' , LMAupdated= now() WHERE
> UserMapID= '296692.1.6' AND LMinputID= 6
>
> UPDATE LMAnswer SET LMAanswer= 'there' , LMAupdated= now() WHERE
> UserMapID= '296692.1.6' AND LMinputID= 7
>
> UPDATE LMAnswer SET LMAanswer= 'and' , LMAupdated= now() WHERE
> UserMapID= '296692.1.6' AND LMinputID= 8
>
> LMAanswer is LONGTEXT, UserMapID is VARCHAR, LMAupdated is DATETIME, and

> LMinputID is INT(10)
>
> I would be less puzzled if none of them were succeeding, but some do and

> so I'm confused. How can I get all of these update queries to run? Or,
> is there a more efficient way to do this in MySQL that I should know
> about? (I've seen references to being able to do multiple inserts of
> values in one query. Maybe I can do multiple updates?)
>
> As a side question, when I click refresh in the query browser the data
> from the query doesn't refresh. I have to execute the query in a new
> tab. Am I doing something wrong?
>
> Thanks much.
>
> Joelle
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=leif@technologycatalyst.c om
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 00612C97852570A1_=--

Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

On 10/21/05, Leif Johnston wrote:
> Where to begin. XP is not a server environment, so you should consider
> server 2000 or 2003.

I agree with Leif, XP is not designed for high demand systems and is
likely to behave "bad" in situations like yours (too many updates for
a single operation like form submission).

> Good db design does not agree with a record per field. Generally the form=
is
> a record with each field a part of the record allowing you to recover wha=
t
> was give to you. That will also agree better with the submission process =
1
> submission many fields in 1 record.
>
> The many record process should require that you look at a transactional
> model or stored procedures for you control.
>
> I strongly suggest that you step back and consider your design to correct=
ly
> align the data to the database for the ultimate results you seek.

You'll probably find yourself pretty confused when accessing data the
way you're trying to insert into, considering a better design or
better ways of getting the job done will save time and probably save
your server from lack of resources or random failures. Consider that
MySQL 5 has its advantages (stored procedures, views) but its not
recommended for production systems.

>
> -----Original Message-----
> From: Joelle Tegwen [mailto:tegwe002@umn.edu]
> Sent: Friday, October 21, 2005 1:12 PM
> To: win32@lists.mysql.com
> Subject: Converting SQL from Access to MySQL 5.0 Using ASP interface
>
> We recently decided to migrate our site from Access to MySQL. I must say
> I was very pleased with the installation process and I'm thrilled with
> all of the support that MySQL gives over Access.

Wise decision!!!!!

> I'm running on XP if that's at all helpful and I'm using the 3.51
> driver. (Is there any more information I should give?)
>
> I think I've worked out most of the syntactical bugs for getting
> information out of the database, so now I'm working on putting new
> information in it (or updating it).
>
> I've got a form with many fields and it's set up in the database so that
> each user's answer to each question is it's own record. So when I
> process the form I've got 30 or so update queries that run. The problem
> I'm having is that some of them are successfully updating and others
> aren't but my comm object [set
> conn=3Dserver.createobject("adodb.connection")] isn't registering that
> there's a problem.

That many updates for a single form submission is likely to have
troubles, you could always get the number of affected records for each
querie, and if its not "1" then retry it, but its trully a matter of
design.

> So these succeed:
>
> UPDATE LMAnswer SET LMAanswer=3D 'It''s nice to have 5' , LMAupdated=3D
> now() WHERE UserMapID=3D '296692.1.6' AND LMinputID=3D 1
>
> UPDATE LMAnswer SET LMAanswer=3D '1/1/07' , LMAupdated=3D now() WHERE
> UserMapID=3D '296692.1.6' AND LMinputID=3D 51
>
> but these fail:
>
> UPDATE LMAnswer SET LMAanswer=3D 'I''m' , LMAupdated=3D now() WHERE
> UserMapID=3D '296692.1.6' AND LMinputID=3D 5
>
> UPDATE LMAnswer SET LMAanswer=3D 'here' , LMAupdated=3D now() WHERE
> UserMapID=3D '296692.1.6' AND LMinputID=3D 6
>
> UPDATE LMAnswer SET LMAanswer=3D 'there' , LMAupdated=3D now() WHERE
> UserMapID=3D '296692.1.6' AND LMinputID=3D 7
>
> UPDATE LMAnswer SET LMAanswer=3D 'and' , LMAupdated=3D now() WHERE
> UserMapID=3D '296692.1.6' AND LMinputID=3D 8
>
> LMAanswer is LONGTEXT, UserMapID is VARCHAR, LMAupdated is DATETIME, and
> LMinputID is INT(10)
>
> I would be less puzzled if none of them were succeeding, but some do and
> so I'm confused. How can I get all of these update queries to run? Or,
> is there a more efficient way to do this in MySQL that I should know
> about? (I've seen references to being able to do multiple inserts of
> values in one query. Maybe I can do multiple updates?)

You can update all the fields of the row at a time, simply:

UPDATE table SET field=3D'value', field2=3D'value2', etc WHERE xxx=3Dxxx

> As a side question, when I click refresh in the query browser the data
> from the query doesn't refresh. I have to execute the query in a new
> tab. Am I doing something wrong?

Never tried that, but it should... Maybe check the help to see if
there's another way of doying it.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

--=_alternative 0061CF20852570A1_=
Content-Type: text/plain; charset="US-ASCII"

Assuming you are using the ADODB library:

a) check the Errors collection of the Connection object for database
errors.

b) In order to run transactions in MySQL, make all of your tables InnoDB.
Then the ADODB transaction control interface will be able to START
TRANSACTION, COMMIT, and ROLLBACK as intended. If you are using MyISAM
tables, the ADODB library will still issue the transaction control
statements but the database will be ignoring them as MyISAM does not
support transactions.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Joelle Tegwen wrote on 10/21/2005 01:38:07 PM:

> When the conversion goes live it will be on 2003. I'm just running it on

> my local host to get all the bugs worked out before I roll things out.
> Our test server is 2003 also.
>
> I've got pretty clear instructions to get this site out of Access ASAP.
> Redesigning the architecture is only an option if it *won't work the way

> it does. We're planning some pretty major changes down the road but this

> is the way it is set up right now.
>
> I am running this with transactions - at least I think I am. I was doing

> the transactions in ASP because access didn't have a mechanism and I
> rolled them over to MySQL, I think, but I don't know how to test if it
> works because I'm not sure how MySQL sends an error back to me to let me

> know there was an error (that doesn't stop the code).
>
> Thanks
> Joelle
>
> Leif Johnston wrote:
>
> >Where to begin. XP is not a server environment, so you should consider
> >server 2000 or 2003.
> >
> >Good db design does not agree with a record per field. Generally the
form is
> >a record with each field a part of the record allowing you to recover
what
> >was give to you. That will also agree better with the submission
process 1
> >submission many fields in 1 record.
> >
> >The many record process should require that you look at a transactional
> >model or stored procedures for you control.
> >
> >I strongly suggest that you step back and consider your design to
correctly
> >align the data to the database for the ultimate results you seek.
> >
> >
> >
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 0061CF20852570A1_=--

Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

That is very close to what we're doing. Except instead of having tests,
they're "worksheets" We have about 70 of them (so yes, they're all
different lengths and this allows for the most flexible data storage,
thanks for helping me explain this.). But since they're worksheets, not
tests, they can change the answers. In fact, some of them are designed
to be updated over time. Thus we both insert and update.

SGreen@unimin.com wrote:

>I think I disagree with you Leif. If I read your answer correctly, you are
>suggestion that she create one row of answers where each answer sits in
>it's own column. A test with 20 answers would take up 20 columns. A test
>with only 5 answers would use only 5 columns. That is not a very flexible
>or normalized design as it requires you to change the design of your
>answer table if you ever administered a test with more than 20 questions.
>
>Her design allows for storing any number of questions and answers
>regardless of test size. I guest that there is one table to hold the
>metadata for each test (who made it, what it's called, the date it was
>administered), one table for all of the questions from all of the tests,
>and one table for all of the answers. This is quite flexible and is highly
>normalized. More questions/more answers = more rows not more columns.
>
>My question for Joelle is: Why are you UPDATE-ing to log each answer?
>Shouldn't you be INSERT-ing? You can only UPDATE an answer if a row for
>that answer already exists. If you didn't pre-load your answer table with
>all blank answers, your UPDATE-as-you-go strategy will fail as there will
>be nothing to UPDATE.
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

Yes, I am using the ADODB library.
a) I do check the errors collection with [If conn.Errors.Count > 0 Then
LetMeKnow()]
b) I did set this up to be InnoDB because I wanted transaction support
(especially with these worksheets and the multiple updates) and I
converted ...
conn.beginTrans --> conn.execute "SET AUTOCOMMIT=0;" ; conn.execute
"START TRANSACTION;"
conn.commitTrans --> conn.execute "COMMIT;"
conn.rollback --> conn.execute "ROLLBACK;"

That was what I understood from the Reference Manual. But I've only
every worked in access, so I could be doing this all wrong :)

But it doesn't create an error even though some of the records don't
update (and I'm not sure why those aren't updating when they are just
simple text).

Thanks!
Joelle

SGreen@unimin.com wrote:

>Assuming you are using the ADODB library:
>
>a) check the Errors collection of the Connection object for database
>errors.
>
>b) In order to run transactions in MySQL, make all of your tables InnoDB.
>Then the ADODB transaction control interface will be able to START
>TRANSACTION, COMMIT, and ROLLBACK as intended. If you are using MyISAM
>tables, the ADODB library will still issue the transaction control
>statements but the database will be ignoring them as MyISAM does not
>support transactions.
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

--=_alternative 0065B550852570A1_=
Content-Type: text/plain; charset="US-ASCII"

I think you translated correctly. Now, understanding that you do have
active transactions, is it possible that the "missing" updates may be
occurring in a separate transaction than the one you are attempting to
verify the updates from? Are you sure you commit each transaction or could
you miss a few and not allow those updates to become "public"?

What I am driving towards is that your problems may not be with the
database but with your program logic and flow. If I query a database from
one connection, start a transaction from another connection, change 5 rows
(within the transaction), then requery the database from the original
connection, I will not see those 5 changes as they are still wrapped in
the transaction. If I now commit the tx and requery the database, all 5
changes should appear (finally). Verify that your code is COMMIT-ing each
change properly.

One point of grammar. When issuing direct statements through ADODB, you do
not need the semicolon as a command terminator. You only need to use a
semicolon if you are in mutli-statement mode (which I am not sure the
ADODB library supports. I may, I have just never checked,) or if you are
using the CLI (command line interface).

conn.execute "SET AUTOCOMMIT=0" ; conn.execute "START TRANSACTION"; ...
etc.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Joelle Tegwen wrote on 10/21/2005 02:06:02 PM:

> Yes, I am using the ADODB library.
> a) I do check the errors collection with [If conn.Errors.Count > 0 Then
> LetMeKnow()]
> b) I did set this up to be InnoDB because I wanted transaction support
> (especially with these worksheets and the multiple updates) and I
> converted ...
> conn.beginTrans --> conn.execute "SET AUTOCOMMIT=0;" ; conn.execute
> "START TRANSACTION;"
> conn.commitTrans --> conn.execute "COMMIT;"
> conn.rollback --> conn.execute "ROLLBACK;"
>
> That was what I understood from the Reference Manual. But I've only
> every worked in access, so I could be doing this all wrong :)
>
> But it doesn't create an error even though some of the records don't
> update (and I'm not sure why those aren't updating when they are just
> simple text).
>
> Thanks!
> Joelle
>
> SGreen@unimin.com wrote:
>
> >Assuming you are using the ADODB library:
> >
> >a) check the Errors collection of the Connection object for database
> >errors.
> >
> >b) In order to run transactions in MySQL, make all of your tables
InnoDB.
> >Then the ADODB transaction control interface will be able to START
> >TRANSACTION, COMMIT, and ROLLBACK as intended. If you are using MyISAM
> >tables, the ADODB library will still issue the transaction control
> >statements but the database will be ignoring them as MyISAM does not
> >support transactions.
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >
> >
>

--=_alternative 0065B550852570A1_=--

Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

This is what I did.
First. I created a code error after all of the updates were done, but
before the commit. None of the changes were successful. So it seems that
the transaction is working correctly.

The updates all happen inside of a loop of the form object and there are
no other transaction statements within there so I can't see how they
could end up in separate ones.

I then tried copy/pasting the errant update statements into the Query
Browser and they generated no error but the data is still unchanged.

So then I tried Daniel's advice to check the records affected and it
said 0. So I fiddled in the Query Browser until I could get a SELECT
statement that would return the desired record. In the process I found
out that if I only WHERE-ed on one of the two criteria I could get rows
(including the one I was looking for) but when I added the other
component it would fail. But only for this handful of records. (Most of
them succeed.)

I'm not sure what the difference is between records 1 and 5 (other than
the obvious) that make the WHERE fail and that concerns me. Is there
something flaky with having two conditions on an UPDATE? Could there be
an extra space that Access ignored by MySQL doesn't?

While I found a solution to this particular one, I do have UPDATE-s and
INSERT-s with multiple WHERE-s that I don't think I can do that for. So
I would really like to get to the bottom of this.

Thanks tons for your time and knowledge.
Joelle

SGreen@unimin.com wrote:

>I think you translated correctly. Now, understanding that you do have
>active transactions, is it possible that the "missing" updates may be
>occurring in a separate transaction than the one you are attempting to
>verify the updates from? Are you sure you commit each transaction or could
>you miss a few and not allow those updates to become "public"?
>
>What I am driving towards is that your problems may not be with the
>database but with your program logic and flow. If I query a database from
>one connection, start a transaction from another connection, change 5 rows
>(within the transaction), then requery the database from the original
>connection, I will not see those 5 changes as they are still wrapped in
>the transaction. If I now commit the tx and requery the database, all 5
>changes should appear (finally). Verify that your code is COMMIT-ing each
>change properly.
>
>One point of grammar. When issuing direct statements through ADODB, you do
>not need the semicolon as a command terminator. You only need to use a
>semicolon if you are in mutli-statement mode (which I am not sure the
>ADODB library supports. I may, I have just never checked,) or if you are
>using the CLI (command line interface).
>
>conn.execute "SET AUTOCOMMIT=0" ; conn.execute "START TRANSACTION"; ...
>etc.
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

--=_alternative 006D6ED1852570A1_=
Content-Type: text/plain; charset="US-ASCII"

Joelle Tegwen wrote on 10/21/2005 03:33:43 PM:

> This is what I did.
> First. I created a code error after all of the updates were done, but
> before the commit. None of the changes were successful. So it seems that

> the transaction is working correctly.
>
> The updates all happen inside of a loop of the form object and there are

> no other transaction statements within there so I can't see how they
> could end up in separate ones.
>
> I then tried copy/pasting the errant update statements into the Query
> Browser and they generated no error but the data is still unchanged.
>
> So then I tried Daniel's advice to check the records affected and it
> said 0. So I fiddled in the Query Browser until I could get a SELECT
> statement that would return the desired record. In the process I found
> out that if I only WHERE-ed on one of the two criteria I could get rows
> (including the one I was looking for) but when I added the other
> component it would fail. But only for this handful of records. (Most of
> them succeed.)
>
> I'm not sure what the difference is between records 1 and 5 (other than
> the obvious) that make the WHERE fail and that concerns me. Is there
> something flaky with having two conditions on an UPDATE? Could there be
> an extra space that Access ignored by MySQL doesn't?
>
> While I found a solution to this particular one, I do have UPDATE-s and
> INSERT-s with multiple WHERE-s that I don't think I can do that for. So
> I would really like to get to the bottom of this.
>
> Thanks tons for your time and knowledge.
> Joelle
>
> SGreen@unimin.com wrote:
>
> >I think you translated correctly. Now, understanding that you do have
> >active transactions, is it possible that the "missing" updates may be
> >occurring in a separate transaction than the one you are attempting to
> >verify the updates from? Are you sure you commit each transaction or
could
> >you miss a few and not allow those updates to become "public"?
> >
> >What I am driving towards is that your problems may not be with the
> >database but with your program logic and flow. If I query a database
from
> >one connection, start a transaction from another connection, change 5
rows
> >(within the transaction), then requery the database from the original
> >connection, I will not see those 5 changes as they are still wrapped
in
> >the transaction. If I now commit the tx and requery the database, all 5

> >changes should appear (finally). Verify that your code is COMMIT-ing
each
> >change properly.
> >
> >One point of grammar. When issuing direct statements through ADODB, you
do
> >not need the semicolon as a command terminator. You only need to use a

> >semicolon if you are in mutli-statement mode (which I am not sure the
> >ADODB library supports. I may, I have just never checked,) or if you
are
> >using the CLI (command line interface).
> >
> >conn.execute "SET AUTOCOMMIT=0" ; conn.execute "START TRANSACTION"; ...

> >etc.
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >
> >
>
Just to be thorough (It's one of those things I just need to check to
cover all of the bases) you do not open and close the connection from
within the loop do you? You don't sound like a person that would code that
but I can't always tell from an email. You must use the same, continuous
connection in order to keep the transaction alive. If it closes before you
COMMIT, it will ROLLBACK for you to avoid messing anything up.

I would look next at your special character escaping, the single quote is
properly escaped as \' not as ''. There are other characters that also
need to be escaped:
http://dev.mysql.com/doc/refman/4.1/en/string-syntax.html

That means if you wanted to store the response:

Plato wasn't the Greek that said "Eureka" and
went running naked through town.


You would need to write the MySQL UPDATE SQL statement as:

UPDATE responses SET response='Plato wasn\'t the Greek that said
\"Eureka\" and\nwent running naked through town.' WHERE wksheet_id = www
AND student_id=xxx AND question=yyy

No, there is no problem with multiple logical terms in an UPDATE statement
(or any other statement I can think of).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 006D6ED1852570A1_=--

Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

I appreciate you being thorough. I'm finding out there are lots of
things I didn't even think of in the conversion. Like it never occurred
to me to look deeper at escaping characters when I found out that '' and
"" were accepted escapes for ' and ". I've adjusted my function - Thanks.

Nah, I'm to lazy to open and close the connection every time. :)

I didn't think there would be a problem, but it was worth a shot. I'll
continue to look.

Thanks much.
Joelle

>Just to be thorough (It's one of those things I just need to check to
>cover all of the bases) you do not open and close the connection from
>within the loop do you? You don't sound like a person that would code that
>but I can't always tell from an email. You must use the same, continuous
>connection in order to keep the transaction alive. If it closes before you
>COMMIT, it will ROLLBACK for you to avoid messing anything up.
>
>I would look next at your special character escaping, the single quote is
>properly escaped as \' not as ''. There are other characters that also
>need to be escaped:
>http://dev.mysql.com/doc/refman/4.1/en/string-syntax.html
>
>That means if you wanted to store the response:
>
>Plato wasn't the Greek that said "Eureka" and
>went running naked through town.
>
>
>You would need to write the MySQL UPDATE SQL statement as:
>
>UPDATE responses SET response='Plato wasn\'t the Greek that said
>\"Eureka\" and\nwent running naked through town.' WHERE wksheet_id = www
>AND student_id=xxx AND question=yyy
>
>No, there is no problem with multiple logical terms in an UPDATE statement
>(or any other statement I can think of).
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Converting SQL from Access to MySQL 5.0 Using ASP interface

As Shawn suggests it is good to check what is in the Errors collection
of the connection object. I use a routine like this when working in VB
to submit all my SQL.

Public Function RunSQL(SQL As String) As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim Cmd As String
Dim Msg As String
Dim E As ADODB.Error
=20
On Local Error GoTo RunSQL_Err
=20
Cmd =3D UCase(Left(SQL, 4))
If Cmd =3D "SELE" Or Cmd =3D "SHOW" Then
' SELECT or SHOW can return a recordset
Set rs =3D New ADODB.Recordset
rs.Open SQL, gCon, adOpenStatic, adLockReadOnly, adCmdText
Set RunSQL =3D rs
Else
' An action query, don't worry about recordset
gCon.Execute SQL, gRecordsAffected, adCmdText
End If

RunSQL_Exit:
Exit Function
=20
RunSQL_Err:
If gCon.Errors.Count <> 0 Then
Msg =3D "SQL Error " & CStr(Err.Number) & ": " & Err.Description
For Each E In gCon.Errors
Msg =3D Msg & vbCrLf & E.Description
Next E
gCon.Errors.Clear
Msg =3D Msg & vbCrLf & "SQL: " & SQL
MsgBox Msg, , "RunSQL"
Else
MsgBox "Error " & CStr(Err.Number) & ": " & Err.Description, ,
"RunSQL"
End If
Resume RunSQL_Exit
End Function

The error trap usually tells me all I need to know to fix any problems
that happen.

I think someone mentioned earlier in this thread that you can't do
transactions in Access. Access does support transactions and you can
improve performance by using them.

John B.

-----Original Message-----
From: SGreen@unimin.com [mailto:SGreen@unimin.com]=20
Sent: Saturday, 22 October 2005 3:18 AM
To: Joelle Tegwen
Cc: win32@lists.mysql.com
Subject: Re: Converting SQL from Access to MySQL 5.0 Using ASP interface

Assuming you are using the ADODB library:

a) check the Errors collection of the Connection object for database=20
errors.

b) In order to run transactions in MySQL, make all of your tables
InnoDB.=20
Then the ADODB transaction control interface will be able to START=20
TRANSACTION, COMMIT, and ROLLBACK as intended. If you are using MyISAM=20
tables, the ADODB library will still issue the transaction control=20
statements but the database will be ignoring them as MyISAM does not=20
support transactions.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Joelle Tegwen wrote on 10/21/2005 01:38:07 PM:

> When the conversion goes live it will be on 2003. I'm just running it
on=20

> my local host to get all the bugs worked out before I roll things out.

> Our test server is 2003 also.
>=20
> I've got pretty clear instructions to get this site out of Access
ASAP.=20
> Redesigning the architecture is only an option if it *won't work the
way=20

> it does. We're planning some pretty major changes down the road but
this=20

> is the way it is set up right now.
>=20
> I am running this with transactions - at least I think I am. I was
doing=20

> the transactions in ASP because access didn't have a mechanism and I=20
> rolled them over to MySQL, I think, but I don't know how to test if it

> works because I'm not sure how MySQL sends an error back to me to let
me=20

> know there was an error (that doesn't stop the code).
>=20
> Thanks
> Joelle
>=20
> Leif Johnston wrote:
>=20
> >Where to begin. XP is not a server environment, so you should
consider
> >server 2000 or 2003.
> >
> >Good db design does not agree with a record per field. Generally the=20
form is
> >a record with each field a part of the record allowing you to recover

what
> >was give to you. That will also agree better with the submission=20
process 1
> >submission many fields in 1 record.
> >
> >The many record process should require that you look at a
transactional
> >model or stored procedures for you control.
> >
> >I strongly suggest that you step back and consider your design to=20
correctly
> >align the data to the database for the ultimate results you seek.
> >
> >=20
> >
>=20
>=20
> --=20
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=3Dsgreen@unimin.com
>=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org