Re: Sybase DATE and TIME data types

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
>