Hi Folks
I’ve just imported a solution that works fine in developer but results in the following error when run in server:
java.sql.SQLException: The data types ntext and varchar are incompatible in the equal to operator.
This is the query that fails:
select qf_filter_name from quick_filter_saved where qf_user_uuid = '4B28C120-6572-4BD3-979C-6713EA025D96' parameters: <null>
The reason I find this odd is because (besides the fact that this runs in Developer with no complaint and using the same Db model) there are no ntext columns in the db and the comparison is between the db column (nvarchar) and the encapsulated uuid text.
The nvarchar column has worked perfectly to store the uuid’s in the past but this time I am passing it in as a string? Why might this error and why only in server?
EDIT: I’ve now converted all of the columns to varchar and yet still on the server I have the same error?
EDIT 2: Seems the export from my local PC to the Server Import has - again, changed data types for two table that did not exist on the server from varchars to ntext. This is despite having the boxes below checked???
Override existing sequence type definitions (in repository) with the sequence types contained in the import file.
Override existing default values (in repository) with the default values contained in the import file.
Allow reserved SQL keywords as table or column names (will fail unless supported by the backend database)
Allow data model (database) changes
Suggestions for tracking this down welcomed.
Ian,
Look at the db model using a db tool, the qf_filter_name table probably has a ntext column.
How large is your column in the dev db?
When you export from/import into sqlserver and the length is less than 4000 it should translate in nvarchar(len), any larger translates to ntext.
Rob
rgansevles:
Ian,
Look at the db model using a db tool, the qf_filter_name table probably has a ntext column.
How large is your column in the dev db?
When you export from/import into sqlserver and the length is less than 4000 it should translate in nvarchar(len), any larger translates to ntext.
Rob
Rob thanks for the feedback. as suggested in my edits above - what I discovered is that the two table that caused the problem were actually not in the server Db but were included in the local export (no data).
Servoy created them in the Db as nvarchar and ntext instead of varchar and varchar(MAX) as they were in the local db???
I had to correct these data types in the server db before we could run without error - that’s why it worked locally but not remotely. I was under the impression from discussions we have had in the past about this same situation that checking the boxes shown above in the import page of the server would force the server to adopt the same data types as the import. It appears not to be the case???
Since this db has several clones we have now to change data types in 30 or so columns in all of those clones.
Has this been fixed elsewhere?
Some further feedback on this would be very welcome Rob as I need to export to another server - with 4 clones - how can I avoid that erroneous creation of nvarchars and ntext??
Ian,
Override existing sequence type definitions (in repository) with the sequence types contained in the import file.
Override existing default values (in repository) with the default values contained in the import file.
These settings control sequence/defaults, not datamodel changes
Allow reserved SQL keywords as table or column names (will fail unless supported by the backend database)
This is just a check
Allow data model (database) changes
If you have this one checked, datamodel changes (like add table/column) will be done, otherwise not.
Again, how large are your columns? if > 4000 the default mapping in servoy is to create ntext.
Note that Servoy uses the column type as reported by the source db and then applies a db-specific mapping, so a source varchar(5000) column will map to ntext in the target.
If you want full control uncheck the datamodel-changes checkbox and create the tables/columns before importing.
Rob
Again, how large are your columns? if > 4000 the default mapping in servoy is to create ntext.
Note that Servoy uses the column type as reported by the source db and then applies a db-specific mapping, so a source varchar(5000) column will map to ntext in the target.
If you want full control uncheck the datamodel-changes checkbox and create the tables/columns before importing.
I do have a few varchar(MAX) columns for sure. Why are these large columns mapped to ntext??? MSSQL suggests that ntext is depracated an should be replaced with varchar(Max) (or possibly nvarchar(Max) though I realise this is version specific - it is the case since MSSQL 2005.
It really defeats the purpose of us designing the tables correctly to then have Servoy change the data types. Why is there no option to directly map the column types from the source to the target Db.
Handling this manually is a real chore - when there are a number of Db’s marked as clones! Isn’t it one of the benefits of Servoy that it handles the structure changes on export?
Ian,
In Servoy 6 we upgraded to the latest hibernate3.
There we will use the varchar(max) column type in stead of ntext.
Rob