RE: Converting SQL from Access to MySQL 5.0 Using ASP interface - SGr...@unimin.com - com.mysql.lists.win32

13 messages in com.mysql.lists.win32RE: Converting SQL from Access to MyS...

From	Sent On	Attachments
Joelle Tegwen	21 Oct 2005 10:11
Leif Johnston	21 Oct 2005 10:19
Joelle Tegwen	21 Oct 2005 10:37
SGr...@unimin.com	21 Oct 2005 10:41
Daniel da Veiga	21 Oct 2005 10:46
SGr...@unimin.com	21 Oct 2005 10:48
Joelle Tegwen	21 Oct 2005 10:58
Joelle Tegwen	21 Oct 2005 11:05
SGr...@unimin.com	21 Oct 2005 11:30
Joelle Tegwen	21 Oct 2005 12:33
SGr...@unimin.com	21 Oct 2005 12:55
Joelle Tegwen	21 Oct 2005 13:46
jbon...@sola.com.au	25 Oct 2005 15:44

Subject:	RE: Converting SQL from Access to MySQL 5.0 Using ASP interface
From:	SGr...@unimin.com (SGr...@unimin.com)
Date:	10/21/2005 10:41:07 AM
List:	com.mysql.lists.win32

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" <Le...@TechnologyCatalyst.com> 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:tegw...@umn.edu] Sent: Friday, October 21, 2005 1:12 PM To: win...@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