From SqlBase to MsSql

Discussion forum about all things SqlBase.
kjellv
Norway
Posts: 84
Joined: 05 Sep 2018, 07:12
Location: sandefjord/Norway

From SqlBase to MsSql

Post by kjellv » 22 Jun 2016, 23:05

hi

I have an application created against SqlBase, no I want to convert it o MsSql, what is the problems I can run into?

I'm using a lot of SqlImmidiate among other things.

As for the Rowid I know this is not valid in MsSql and will take this into account.

Thanks

FRBhote
India
Posts: 2176
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: From SqlBase to MsSql

Post by FRBhote » 23 Jun 2016, 06:21

We use The timestamp column as a substitute for rowid.

You need to create this column on every table.

Substitute=ROWID,TIMESTAMP

FRBhote
India
Posts: 2176
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: From SqlBase to MsSql

Post by FRBhote » 23 Jun 2016, 06:43

In fact we use all of these:

substitute=@UPPER,UPPER
substitute=@LOWER,LOWER
substitute=@left,left
substitute=@LEFT,LEFT
substitute=@SUBSTRING,SUBSTRING
substitute=@mod,MOD
substitute=@MOD,MOD
substitute=@nullvalue,ISNULL
substitute=@NULLVALUE,ISNULL
substitute=@right,RIGHT
substitute=@RIGHT,RIGHT
substitute=@ROUND,ROUND
substitute=@CHOOSE,dbo.SCHOOSE
substitute=@CODE,ASCII
substitute=||,+
substitute=@TRIM,dbo.TRIM
substitute=@ABS,ABS
substitute=@LENGTH,LEN
substitute=@SCAN,charindex
substitute=@FIND,dbo.FIND
substitute=SYSTABLES,MsysObjects
Substitute=ROWID,TIMESTAMP
substitute=@MID,SUBSTRING
substitute=@VALUE,VAL
substitute=@DATEVALUE,TO_DATE
substitute=not null with default,NOT NULL DEFAULT '0'
substitute=NOT NULL WITH DEFAULT,NOT NULL DEFAULT '0'
substitute=pctfree,
substitute=PCTFREE,
;substitute=AUTOINCREMENT,IDENTITY(1,1)
substitute=SYSTABLES,SYS.TABLES
substitute=pctfree 10,
substitute=PCTFREE 10,
substitute=DOUBLE PRECISION,FLOAT
substitute=SET SPOOL,SPOOL
substitute=@DATE, DateSerial

substitute=SYSDATETIME, GETDATE()
substitute=SYSDATE, GETDATE()

The unfortunate thin is that the is no DATE in MSSql, so you have to use DATETIME. Need to be very careful that the time part is set to zeros.

DECODE has to be rewritten:

Code: Select all

Set sDecodeCashOnlyP = " @DECODE( CB_CRDR, 'P', CB_AMOUNT ) "
If nBrand = DBV_BRAND_ODBC
     Set sDecodeCashOnlyP = " CASE CB_CRDR WHEN 'P' THEN CB_AMOUNT END "
If you use old style outer joins then this is needed:

Code: Select all

Set sOuterJoinSubCat = ' P_CAT = SG_CAT(+) AND P_SUB_CAT = SG_CODE(+)  '
If nBrand = DBV_BRAND_ODBC
    Set sOuterJoinSubCat = ' P_CAT *= SG_CAT AND P_SUB_CAT *= SG_CODE '
SUBSTRING starts from 1 in SqlServer, so:

Code: Select all

If nBrand = DBV_BRAND_ODBC
    Set sZero = '1'
If nBrand = DBV_BRAND_SQL
    Set sZero = '0'

AND @SUBSTRING(CO_OPT, " || sZero || ", 1)
And finally Sqlbase accepts a date as 2016-06-23, but in SqlServer it has to be in quotes '2016-06-23'

I would suggest that you convert all NUMBER fields to DECIMAL(18, n).

All the best!

FRBhote
India
Posts: 2176
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: From SqlBase to MsSql

Post by FRBhote » 23 Jun 2016, 07:10

Also set the collation to SQL_Latin1_General_CP1_CI_AS before you create the database otherwise upper & lower case will not be distinguished.

There is some setting for IS NULL and not = NULL, but I forget what...

kjellv
Norway
Posts: 84
Joined: 05 Sep 2018, 07:12
Location: sandefjord/Norway

Re: From SqlBase to MsSql

Post by kjellv » 23 Jun 2016, 13:41

thanks a lot :D

kjellv
Norway
Posts: 84
Joined: 05 Sep 2018, 07:12
Location: sandefjord/Norway

Re: From SqlBase to MsSql

Post by kjellv » 10 Aug 2016, 10:53

hi

My application which I made under SqlBase is now working against MSSQL. I had to make some small corrections, but another thing I notice and which I need to correct is that the behavior of fields and columns.

In MSSQL I have difficulties to cange inputs in certain columns, I have to mark the column (left mouse and drag across) then I can enter or change values. In other columns the cursor does not place itself at the end of the content of the column.

Could you also explain where I have to do this setting, SQL_Latin1_General_CP1_CI_AS when I create the database or is it when I install MSSQL?

FRBhote
India
Posts: 2176
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: From SqlBase to MsSql

Post by FRBhote » 10 Aug 2016, 14:21

Can't understand the cursor stuff - it should be the same.
Untitled.jpg
You do not have the required permissions to view the files attached to this post.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests