Skip to Content.
Sympa Menu

freetds - Re: [freetds] Sybase DATE and TIME data types

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Matthew <matthew.green AT datamartcomputing.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Sybase DATE and TIME data types
  • Date: Mon, 21 Sep 2015 19:43:26 +0200

I took last night's build and it worked well and passed my tests!!!!

Thanks Frediano.

Next step is MS SQL Server but that'll be more complicated as "set fmtonly" is deprecated and doesn't show DATEs and TIMEs. Still, I'll give it a go.

Cheers,

Matthew.

On 18/09/15 20:46, Frediano Ziglio wrote:
Should work now. Was a problem with datacopy not with date/time patches
Il 15/Set/2015 14:33, <matthew.green AT datamartcomputing.com> ha scritto:

Hi Frediano,

I downloaded the latest release (freetds-dev.0.99.297) but it fails with:

datacopy -t -p32768 -v -S SOURECE/USER/PW/DBdate_time_source -D
DESTINATION/USER/PW/DB/date_time_destination -E -T200000
Error in dbnumcols
datacopy: table structures do not match. terminating
My working version still works and looks like:
Starting copy...

rows read : 5
rows written : 5
elapsed time (secs) : 0.002556
rows per second : 1956.181534

I just wan,ted to check that I'd got the right version before I start
investigating what's missing.

Thanks,

Matthew.

PS. Here's the SQL I use to test (as well as a visual check):

if exists (select 1 from sysobjects where name = "date_time_source" and
type = "U")
drop table date_time_source
go
if exists (select 1 from sysobjects where name = "date_time_destination"
and type = "U")
drop table date_time_destination
go

create table date_time_source (
dt_i int not
null ,
dt_d datetime not
null ,
dt_dt date not
null ,
dt_t time not
null ,
dt_i2 int
null ,
dt_dt2 date
null ,
dt_t2 time
null
)

create table date_time_destination (
dt_i int not
null ,
dt_d datetime not
null ,
dt_dt date not
null ,
dt_t time not
null ,
dt_i2 int
null ,
dt_dt2 date
null ,
dt_t2 time
null
)
go
INSERT INTO date_time_source select 1,'1970-06-13
11:15:23.223','1970-06-13','10:15:23',2,'1950-12-10','05:15:12'
INSERT INTO date_time_source select 2,'1980-08-15
12:15:23.009','1970-06-15','12:15:23',3,'1965-02-20',NULL
INSERT INTO date_time_source select 3,'1981-10-30
02:05:25.123','1966-01-01','07:11:17',4,NULL,'15:00:12:123'
INSERT INTO date_time_source select 3,'1977-11-05
19:17:03.003','1960-02-14','07:11:17',4,NULL,NULL
INSERT INTO date_time_source select 1,'2012-01-13
11:15:23.223','2030-03-25','21:35:23:176',2,'1958-10-17','09:05:50:840'
go
-- Pause here and run datacopy. Don't forget you will need select/into
turned on in the target database.

declare @source_rows int,
@destination_rows int,
@unique_rows int,
@message varchar(2000)

select @source_rows = count(*) from date_time_source
select @destination_rows = count(*) from date_time_destination

select dt_i, dt_d, dt_dt, dt_t, dt_i2, dt_dt2 , dt_t2 from date_time_source
union
select dt_i, dt_d, dt_dt, dt_t, dt_i2, dt_dt2 , dt_t2 from
date_time_destination

select @unique_rows = @@rowcount

if @unique_rows != @source_rows
select @message = "ERROR: "+convert(varchar,@source_rows)+" source
rows resulted in "+convert(varchar,@unique_rows)+" unqiue rows."
else if @destination_rows != @source_rows
select @message = "ERROR: "+convert(varchar,@source_rows)+" source
rows resulted in "+convert(varchar,@destination_rows)+" destination rows."

if @message is null
print "TEST PASSED."
else
print "TEST FAILED"

print @message
go

September 14 2015 2:37 PM, "Frediano Ziglio" wrote:The change for
tds_types.h is not necessary, the invalid constant is
actually 0. Also tds_types.h is auto generated from misc/types.cvs.
The change in include/sybdb.h is also in my patches to support these
types (with new bindings) in dblib.

So... please grab the last snapshot and try again, should work!

Actually there is no automatic tests for freebcp and datacopy (but the
base functions they use are tested).

Frediano

2015-09-14 13:13 GMT+01:00 :
Hi Frediano,

Yes, a diff is the thing to do :-) I've got access again so I ran it and
the results are (I
removedreferences to compiled objects and documentation):

diff -r ../compare/freetds-dev.0.99.220/include/sybdb.h ./include/sybdb.h
diff -r ../compare/freetds-dev.0.99.220/include/freetds/convert.h
./include/freetds/convert.h
diff -r ../compare/freetds-dev.0.99.220/include/freetds/proto.h
./include/freetds/proto.h
diff -r ../compare/freetds-dev.0.99.220/src/apps/datacopy.c
./src/apps/datacopy.c
diff -r ../compare/freetds-dev.0.99.220/src/tds/convert.c
./src/tds/convert.c
diff -r ../compare/freetds-dev.0.99.220/src/tds/mem.c ./src/tds/mem.c
diff -r ../compare/freetds-dev.0.99.220/src/tds/query.c ./src/tds/query.c
diff -r ../compare/freetds-dev.0.99.220/src/tds/tds_types.h
./src/tds/tds_types.h

So, I'd missed two files (in bold) which have now been updated in my
attached document.

It's still working for me :-)

Cheers,

Matthew.
September 14 2015 10:46 AM, "Frediano Ziglio" wrote:Thanks,
did patches yesterday. See

https://github.com/FreeTDS/freetds/commit/235019b7342cea9bd200e7a7b00bb1bc26e37096
(
https://github.com/FreeTDS/freetds/commit/235019b7342cea9bd200e7a7b00bb1bc26e37096
)
(
https://github.com/FreeTDS/freetds/commit/235019b7342cea9bd200e7a7b00bb1bc26e37096
(
https://github.com/FreeTDS/freetds/commit/235019b7342cea9bd200e7a7b00bb1bc26e37096
))
and
https://github.com/FreeTDS/freetds/commit/526c035f66cc297943690fd4096444921a764206
(
https://github.com/FreeTDS/freetds/commit/526c035f66cc297943690fd4096444921a764206
)
(
https://github.com/FreeTDS/freetds/commit/526c035f66cc297943690fd4096444921a764206
(
https://github.com/FreeTDS/freetds/commit/526c035f66cc297943690fd4096444921a764206)
).

Is not easier to do a diff?

Currently only ODBC is missing to the list.

Frediano

2015-09-14 9:19 GMT+01:00 :
I've found one file I modified which wasn't in my document... sorry about
that.

The change is to include/sybdb.h, around line 154:

enum
{
SYBCHAR = 47, /* 0x2F */
#define SYBCHAR SYBCHAR
SYBVARCHAR = 39, /* 0x27 */
#define SYBVARCHAR SYBVARCHAR
SYBINTN = 38, /* 0x26 */
#define SYBINTN SYBINTN
SYBINT1 = 48, /* 0x30 */
#define SYBINT1 SYBINT1
SYBINT2 = 52, /* 0x34 */
#define SYBINT2 SYBINT2
SYBINT4 = 56, /* 0x38 */
#define SYBINT4 SYBINT4
SYBINT8 = 127, /* 0x7F */
#define SYBINT8 SYBINT8
SYBFLT8 = 62, /* 0x3E */
#define SYBFLT8 SYBFLT8
SYBDATETIME = 61, /* 0x3D */
#define SYBDATETIME SYBDATETIME
SYBDATE = 49, /* 0x31 */
#define SYBDATE SYBDATE
SYBTIME = 51, /* 0x33 */
#define SYBTIME SYBTIME
SYBBIT = 50, /* 0x32 */
#define SYBBIT SYBBIT
SYBBITN = 104, /* 0x68 */

I'll keep looking in case there's anything else.....

Cheers,

Matthew.
September 13 2015 12:19 PM, "Matthew" wrote:Thanks for the kind words. It
seems I must have
missedsomething as
datacopy is working for me when copying DATE and TIME columns from ASE
15.7 to ASE 15.7.

A lot of what I did was trial and error and I tried to remember to leave
a comment so I could find what I'd changed in order to write it up at
the end.

Any idea which file I would have changed to get it working with dblib?
If not I'll untar the package somewhere else and run a find and diff to
see if I can spot it.

Cheers,

Matthew.

On 13/09/15 11:55, Frediano Ziglio wrote:
2015-09-11 10:04 GMT+01:00 :Dear All,

I'm not really a C programmer (or C++ for that matter) but I wanted to be
able to use datacopy
tocopy tables with DATE and TIME data types. So, I decided to have a crack
at adding them to
FreeTDS.My testing has been successful, although I'm not sure the way I've
coded it is correct so
any
help/fixes would be appreciated.

I've attached a rich text document with the details. The first part
proposes a quick and dirty
fix,while the second is my best guess at the "correct" way of doing things.

Bare in mind that this is currently only for Sybase to Sybase. I'm going
to look at doing this
forSQL Server too but that's a bit more complicated!

Cheers,

Matthew.

Patch and effort are really appreciated.
So appreciated that I already pushed some changes and is actually
testing deeply (but I don't think there will be any regression).

datacopy still does not work as SYBDATE/SYBTIME are not supported by
dblib (you added support for libTDS not upper libraries and datacopy
is based on dblib).

Currently converting from string will use datetime format in all
cases. ODBC has its standard so format strings are well defined for
it.

I added some improvements too based on previously patches on some other
types.

Yes, adding a new type in FreeTDS is really a nightmare !!!

Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org (mailto:FreeTDS AT lists.ibiblio.org) (mailto:
FreeTDS AT lists.ibiblio.org (mailto:mailto:FreeTDS AT lists.ibiblio.org))
(mailto:FreeTDS AT lists.ibiblio.org (mailto:mailto:FreeTDS AT lists.ibiblio.org
)
(mailto:mailto:FreeTDS AT lists.ibiblio.org (mailto:mailto:mailto:
FreeTDS AT lists.ibiblio.org)))
http://lists.ibiblio.org/mailman/listinfo/freetds (
http://lists.ibiblio.org/mailman/listinfo/freetds)
(http://lists.ibiblio.org/mailman/listinfo/freetds (
http://lists.ibiblio.org/mailman/listinfo/freetds))
(http://lists.ibiblio.org/mailman/listinfo/freetds (
http://lists.ibiblio.org/mailman/listinfo/freetds)
(http://lists.ibiblio.org/mailman/listinfo/freetds) (
http://lists.ibiblio.org/mailman/listinfo/freetds)))
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org (mailto:FreeTDS AT lists.ibiblio.org) (mailto:
FreeTDS AT lists.ibiblio.org (mailto:mailto:FreeTDS AT lists.ibiblio.org))
http://lists.ibiblio.org/mailman/listinfo/freetds (
http://lists.ibiblio.org/mailman/listinfo/freetds)
(http://lists.ibiblio.org/mailman/listinfo/freetds (
http://lists.ibiblio.org/mailman/listinfo/freetds))

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org (mailto:FreeTDS AT lists.ibiblio.org) (mailto:
FreeTDS AT lists.ibiblio.org (mailto:mailto:FreeTDS AT lists.ibiblio.org))
http://lists.ibiblio.org/mailman/listinfo/freetds (
http://lists.ibiblio.org/mailman/listinfo/freetds)
(http://lists.ibiblio.org/mailman/listinfo/freetds (
http://lists.ibiblio.org/mailman/listinfo/freetds))

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org (mailto:FreeTDS AT lists.ibiblio.org)
http://lists.ibiblio.org/mailman/listinfo/freetds (
http://lists.ibiblio.org/mailman/listinfo/freetds)

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org (mailto:FreeTDS AT lists.ibiblio.org)
http://lists.ibiblio.org/mailman/listinfo/freetds (
http://lists.ibiblio.org/mailman/listinfo/freetds)
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds





Archive powered by MHonArc 2.6.24.

Top of Page