Robert,
Thanks for your prompt response.
I have added answers and comments to the response below
Eric Whitchurch
35 Thompson Road UPWEY VIC 3158
Ph: 03 9754 6378 Mobile: 0408 172 789
Email: ewh...@bi...
--------------------------------------------------
From: "Robert Manning" <rob...@gm...>
Sent: Friday, January 25, 2008 11:43 AM
To: "Eric Whitchurch" <ewh...@bi...>
Cc: <squ...@li...>
Subject: Re: [Squirrel-sql-users] VARCHAR conversion error - Ingres 2006
tables to Microsoft SQL Server 2000 and 2005
> On Jan 24, 2008 5:05 PM, Eric Whitchurch <ewh...@bi...> wrote:
>>
>>
>>
>> Hi,
>>
>> I have just subscribed to your mailing list.
>>
>> I am trialing your product as a means of converting many ingres databases
>> to
>> SQL Server for quite a number of customers of the firm for whom I am
>> primarily consulting.
>>
>> Their product normally uses an Ingres database as that is where their
>> skills
>> lie. However, they have converted to be able to satisfy an increasing
>> number
>> of requests to have their database on SQL Server - both 2000 and 2005 and
>> undoubtedly soon 2008..
>>
>> At least for my first tests everything seems OK EXCEPT that ALL
>> VARCHAR(anything) end up as VARCHAR(8000).
>
> Well, because that is a bug. The SQLServer dialect (my own, not
> Hibernate's) hard-coded 8000 as the length for all VARCHAR type
> columns. So, I wrote that up here:
>
> http://sourceforge.net/tracker/index.php?func=detail&aid=1879349&group_id=28383&atid=393414
>
> My resolution would be to use the length of the source column as the
> length of the destination column and switchover to TEXT type beyond
> 8000. Sound reasonable?
Yes that would be reasonable and solves the other problem of varchars
greater than 8000.
>
>> I also note that like most similar products, you do not seem to recognize
>> column defaults. Even though they are set for almost every column in
>> every
>> table in the Ingres database, you report them as NULL in the column
>> property
>> display. This may be a problem with Ingres JDBC, but certainly INGRES
>> .NET
>> provider reports the default values in its schema retrieval facility.
>>
>> If the value was valid. would you pass it through to the converted table?
>
> No, unfortunately. That is not currently implemented for copy
> operations, even if the JDBC driver correctly implements it. But it
> sounds like a nice thing to have so I have added a feature request for
> you:
>
> http://sourceforge.net/tracker/index.php?func=detail&aid=1879353&group_id=28383&atid=393417
Thanks for that. It is actually a real problem in the way my client uses
columns - they expect the default value of date fields in particular to be
empty string, not null or a "funny" date. They also have tests for zero on
integer fields and crash if null.
>
>> I am getting a bit desperate and maybe should have written my own
>> converter,
>> but I have found nothing that works completely, although yours comes
>> closest. Microsoft imports do only half the job.
>
> By this, do you mean that the import tool only creates the schema and
> doesn't copy in the data? If that were the case, and assuming you
> like the schema that it produces better than what is produced by
> DBCopy in SQuirreL, then perhaps you can let it create the schema and
> then use SQuirreL to copy the data into the (newly created) tables.
> The DBCopy plugin in SQuirreL supports copying data into existing
> empty tables without modifying their structure.
Once again, you have a nice product.
No the problem with using SQL Server's import is that it does not generate
correct schema as far as my client is concerned. This is largely because of
the way they (my client) define their Ingres database tables. In Ingres, you
get to define a table's structure as (among others) heap, btree and isam.
For the latter two, you define unique keys for the structure which Ingres
then assumes constitute the Primary key for the table and the unique
constraints. My client has never defined Primary keys specifically. Ingres
has accounted for this in their JDBC (they also have a Hibernate driver).
Somehow Microsoft in their import routines miss this and do not generate the
Primary Key constraints, so effectively the import is not very good.
Microsoft also does not transfer the default values.
I have my own .NET app that copies in the default values, so have that
covered.
>
> Rob
>
Eric
|
