Access to MySQL Migration - Data Types

So last week I had a problem (I thought) with updates not happening, but
really the problem was that the WHERE clause was returning no records.
Thanks to all of you who answered and gave me suggestions on where to
look for my problem.

After a fair bit of trial and error testing I found out that the problem
was with the integer column. So say the table looks like this:

TableID VARCHAR(255)
UserID INT(10)
InputID INT(10)
Answer LONGTEXT
Updated DATETIME
WorksheetID VARCHAR(255)

IF I run: SELECT TableID FROM myTable WHERE WorksheetID='myWorksheetID'
AND InputID=5
I get no rows returned even though that record is in the table.
If I ask for a different record: SELECT TableID FROM myTable WHERE
WorksheetID='myWorksheetID' AND InputID=1 then I do get a record

So I did a test and I created a new column
temp INT and I copied the data from InputID into that column. Then I ran
SELECT TableID FROM myTable WHERE WorksheetID='myWorksheetID' AND temp=5
and it returned the desired record.

So then I tried changing the InputID field to type INT and running the
query and I still get no records.

*sigh*

I tried changing the data type in a new import and I wasn't able to do so.

Why is this a problem? My understanding is that the (10) is just a mask,
it doesn't actually affect the data.
How do I fix this? Do I really have to go through my entire application
and rename columns? We've got several development copies of this out
there so doing this for all of them would be a major pain, not to
mention increase the down time of our site when we do the live migration.

I couldn't find anything on this but searching for "data type" and
"access" on the web is a mostly futile endeavor.

Thanks
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: Access to MySQL Migration - Data Types

Hi,

Joelle Tegwen wrote:

> UserID INT(10)
> InputID INT(10)

to overcome this problem with all databases, I use CHAR fields for IDs
and right adjust them. This might take longer than processing an INT but
works always. It gives the additional effect that I am not limited to 32
bits.

Alternatively, just use the datatype representing the best way your
number range.

> How do I fix this? Do I really have to go through my entire application
> and rename columns? We've got several development copies of this out
> there so doing this for all of them would be a major pain, not to
> mention increase the down time of our site when we do the live migration.
>
If this is the case, just try plain INT.

I moved a lot between different databases in the past. Handling INTs was
always my main problem.

Erich

--
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: Access to MySQL Migration - Data Types

While this was food for thought on future database construction, it
really wasn't realistic for this application. It would have involved
changing 1000s of lines of code. Simply changing the type on the
existing column didn't help. I had to create a new column and copy the
data over to get the data to "fix" itself. I'm posting the answer here
so that it might come up on a search for other people. Part of this
process for me was learning about the SCHEMA tables and how they could
be used effectively. Though I imagine that would be a problem for anyone
coming out of Access

What I tried that failed:
Writing my own import tool in VBA.
Changing the INTs to TEXT in Access, importing the database then
changing them back.

What I tried that worked:
I migrated the data using the Migration tool.
Then I wrote a script (web/ASP) that did the following:

1) Queried the KEY_COLUMN_USAGE table to find all of the primary keys.
Moved these into an array variable massaging the data so that I only
had one row per table with all of the primary keys comma delimited in
the other column.

2) Looped the above array for table names with primary keys, dropped
the primary keys from the table ("ALTER TABLE myTable DROP PRIMARY KEY)

3). Queried the COLUMNS table to get all of the columns with INT(10)
COLUMN_TYPE
for each column in tables
ADD column2 INTEGER (etc) AFTER column
SET column2=column
DROP column
ADD column INTEGER (etc) AFTER column2 (note:I actually did some
data type cleanup here.)
SET column=column2
DROP column2

4) Then I restored the Primary keys from the array from step1

And with the exception of the data "Fixing" in step 3, this code is
totally exportable to another project.

Thanks everyone for all of your help/advise.

Joelle


Erich Dollansky wrote:

> Hi,
>
> Joelle Tegwen wrote:
>
>> UserID INT(10)
>> InputID INT(10)
>
>
> to overcome this problem with all databases, I use CHAR fields for IDs
> and right adjust them. This might take longer than processing an INT
> but works always. It gives the additional effect that I am not limited
> to 32 bits.
>
> Alternatively, just use the datatype representing the best way your
> number range.
>
>> How do I fix this? Do I really have to go through my entire
>> application and rename columns? We've got several development copies
>> of this out there so doing this for all of them would be a major
>> pain, not to mention increase the down time of our site when we do
>> the live migration.
>>
> If this is the case, just try plain INT.
>
> I moved a lot between different databases in the past. Handling INTs
> was always my main problem.
>
> Erich
>


--
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