Re: Sybase DATE and TIME data types

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, <[email protected]> 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
>> [email protected] (mailto:[email protected]) (mailto:
>> [email protected] (mailto:mailto:[email protected]))
>> (mailto:[email protected] (mailto:mailto:[email protected]
>> )
>> (mailto:mailto:[email protected] (mailto:mailto:mailto:
>> [email protected])))
>> 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
>> [email protected] (mailto:[email protected]) (mailto:
>> [email protected] (mailto:mailto:[email protected]))
>> 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
>> [email protected] (mailto:[email protected]) (mailto:
>> [email protected] (mailto:mailto:[email protected]))
>> 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
>> [email protected] (mailto:[email protected])
>> http://lists.ibiblio.org/mailman/listinfo/freetds (
>> http://lists.ibiblio.org/mailman/listinfo/freetds)
>>
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected] (mailto:[email protected])
>> http://lists.ibiblio.org/mailman/listinfo/freetds (
>> http://lists.ibiblio.org/mailman/listinfo/freetds)
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds