MS SqlServer2000

Hi

Is there any way to use a DataBase from MS SQL Server 2000?

Thanks in advance.

Hi.

Following “4.3.1.1. Support for Other DBMSs” on the developer manual, is there any way to user jTDSDriver with cuba platform 6.5+?

Thanks

Hi,
As it was mentioned before, there is no smooth way to use jTDS driver in CUBA Studio 6.5+.
But you can try the following workaround:
Set Microsoft SQL Server Database type via Studio. Close project in Studio.
Manually set jTDS driver in your datasource definition (context.xml). You’ll need to change driver class name, url and add validation query.

driverClassName="net.sourceforge.jtds.jdbc.Driver"
url="jdbc:jtds:sqlserver://{host}/{dbName}"
validationQuery="select 1"

In project build.gradle file change the following:
Set

def mssql = 'net.sourceforge.jtds:jtds:1.2.4'

instead of

def mssql = 'com.microsoft.sqlserver:mssql-jdbc:6.1.0.jre8'

remove curly braces (exclude) blocks after jdbc(mssql) and testRuntime(mssql) in coreModule dependencies section:

jdbc(mssql)
testRuntime(mssql)

instead of

jdbc(mssql) { exclude(group: 'com.microsoft.azure', module: 'azure-keyvault') }
testRuntime(mssql) { exclude(group: 'com.microsoft.azure', module: 'azure-keyvault') }

Add masterUrl, driver, dbUrl and timestamp values in createDb task:

dbms = 'mssql'
host = 'localhost'
dbName = 'test'
dbUser = 'test'
dbPassword = 'testPass'
masterUrl = "jdbc:jtds:sqlserver://$host/master$connectionParams"
driver = 'net.sourceforge.jtds.jdbc.Driver'
dbUrl = "jdbc:jtds:sqlserver://$host/$dbName$connectionParams"
timeStampType = 'datetime'

Add driver, dbUrl and timestamp values in updateDb task:

dbms = 'mssql'
host = 'localhost'
dbName = 'test'
dbUser = 'test'
dbPassword = 'testPass'
driver = 'net.sourceforge.jtds.jdbc.Driver'
dbUrl = "jdbc:jtds:sqlserver://$host/$dbName$connectionParams"
timeStampType = 'datetime'

Please note that CUBA Studio will rewrite context.xml file and set Microsoft driver every time you save project properties.
Also https://doc.cuba-platform.com/manual-6.6/db_update_server.html automatic database update by server won’t work in this case.
cuba.automaticDatabaseUpdate application property should be set to false (as it is by default)

If you use this workaround, you don’t need follow “4.3.1.1. Support for Other DBMSs” instructions.

We have created YouTrack issues for jTDS support

Hi,

Have you tried to select Microsoft SQL Server in Project properties > Database type field in Studio?

Hi

Configured SQL Server 2000 connection on a sample project and get the next error when testing connection (using standar SQL Server driver).

[08:29:32.863] Trying to connect to jdbc:sqlserver://XXXXXXXX;databaseName=xxxxxxxxxxxxxxxxxx
[08:29:32.939] Connection failed
SQL Server version 8 is not supported by this driver. ClientConnectionId:e3d1ae94-37ed-4c6e-b110-a260c1d70753

Followed the indications of the developer manual “4.3.1.1. Support for Other DBMSs” to use jTDS driver with no luck.

Think than i need a step by step guide on how to connect to other DBMS.

Thanks

I’m afraid you won’t be able to work with your SQLServer from Studio 6.5+. But Studio 6.4 uses jTDS driver and works with older SQLServer.

You can try using a SQLServer 2005+ locally to work from Studio, and just change driver and connection URL to jTDS in testing and production environment to work with your SQLServer 2000.

If you need an initial model generation from the SQLServer 2000 database, install Studio 6.4, create new project on platform 6.4, generate model, then switch to the latest Studio and upgrade your project to the latest platform.

:ticket: See the following issue in our bug tracker:

https://youtrack.cuba-platform.com/issue/PL-9467

Hi.
I have a new issue with this driver.

It’s correctly installed and configured. Test connection is OK.

I try to ‘generate model’ from an existing database. When click on ‘Show tables’, an empty list is the result.
In the SQL Server Analyzer i can see the next Stored Procedure execution

exec sp_tables N’%’, N’DBO’, NULL, N’"’‘TABLE’’,’‘VIEW’’"’

but sql Server is installed with a collation ‘Case Sensitive’, so this stored procedure returns an empty table list.

Nevertheless using next call

exec sp_tables N’%’, N’dbo’, NULL, N’"’‘TABLE’’,’‘VIEW’’"’

returns the correct table list.

Is there any workaround.

Thanks?

Hi!

You can try to explicitly specify schema name in lowercase.
Add
;currentSchema=dbo
to the connection url in your context.xml file

Hi!.

Thanks for the help.

Now i have a new problem, when executing script “10-cuba/init/mssql/create-db.sql”, i get the next exception:

Failed to execute:
create table SYS_SERVER (
ID uniqueidentifier not null,
CREATE_TS datetime,
CREATED_BY varchar(50),
UPDATE_TS datetime,
UPDATED_BY varchar(50),
NAME varchar(255),
IS_RUNNING tinyint,
DATA varchar(max),
primary key nonclustered (ID)
) because: Línea 10: sintaxis incorrecta cerca de ‘max’.
:app-core:updateDb FAILED
FAILURE: Build failed with an exception.

  • What went wrong:
    Execution failed for task ‘:app-core:updateDb’.

java.sql.SQLException: Línea 10: sintaxis incorrecta cerca de ‘max’.

  • Try:

Any help?

Thank again

Hello!

Unfortunatly MS SQL Server 2000 doesn’t support varchar(max) type, which is used in CUBA Platform init scripts.
You need to replace ‘varchar(max)’ with ‘text’ in /modules/core/build/10-cuba/init/mssql/create-db.sql script in your project folder.

Hi.
I don’t know how to make this change permanent.
When i modify this script from IDE, and run ‘update database’, studio undo all the changes.

Any ideas?

Thanks

Hi,

I’m sorry but it seems that you can’t make this change permanent.
CUBA Platform createDb and updateDb gradle tasks assemble platform db scripts each time they executed. So any changes being overwritten.

You can manually execute failing script and add record to SYS_DB_CHANGELOG to mark it executed for the platform.
It looks like this:

SCRIPT_NAME                          CREATE_TS                        IS_INIT
10-cuba/init/mssql/create-db.sql     2017-10-30 12:00:00.000          1

Another option is to use your legacy database as additional data store, so platform script won’t be executed on SQL Server 2000. And for main data store, which contains platform tables use DBMS that is supported out of the box.

P.S.
We just added support for jTDS driver in release 6.7 which is coming very soon (You can already download release candidate).
For the 6.8 release we are working on the custom database support from Studio.