FreeTDS Development Group

[Konrad J Hambrick <konrad AT jax01.payplus.com>]

All --

I mucked about in the freebcp.c source code to make the
freebcp -h (hints) work

see my previous post 're: freebcp -h TABLOCK syntax'.

A .patch file against freetds-0.65.dev.20060512 is attached.

I found if I moved the call to bcp_options() out of the
login_to_database() function and copied it into each of

   file_character(),
   file_native() and
   file_formatted()

just after the bcp_init() calls in each function, the hints
were accepted by SQL Server 2000.

IOW, these changes eliminated the

   Msg 20076, Level 7
   bcp_init must be called before any other bcp routines
   db-lib: Unable to set hint "TABLOCK"
   bcp reports error 1

Errors when trying to pass hints to SQL Server via freebcp.

I also noticed that the

   if (erc != SUCCEED)

block inside the if (pdata->hint){ } block seemed to be
missing a curly brace ...

I added that to each of the bcp_options(){ } blocks in the
file_character(), file_native() and file_formatted() procs.

Finally, just because I like immediate gratification <G>,
I changed

   printf("\nStarting copy...\n\n")          to

   fprintf(stderr, "\nStarting copy...\n\n")

and

   printf("%d rows copied.\n", li_rowsread)  to

   fprintf(stderr, "%d rows copied.\n", li_rowsread)

so that the messages are dumped immediately to stderr rather
than being buffered to stdout.

Do these changes seem reasonable ?

Thanks for freetds guys !  I would be stuck trying to script
my data warehouse loads on Win2005 without it !

-- kjh

*** freebcp~01.c	Tue Mar 14 23:44:41 2006
--- freebcp.c	Sun May 14 07:00:00 2006
***************
*** 370,386 ****
  		return (FALSE);
  	}
  
! 	/* set hint if any */
! 	if (pdata->hint) {
! 		int erc = bcp_options(*pdbproc, BCPHINTS, (BYTE *) pdata->hint, strlen(pdata->hint));
! 
! 		if (erc != SUCCEED)
! 			fprintf(stderr, "db-lib: Unable to set hint \"%s\"\n", pdata->hint);
! 		return FALSE;
! 	}
  
  	return (TRUE);
- 
  }
  
  int
--- 370,389 ----
  		return (FALSE);
  	}
  
! // - kjh A60513 
! //
! //  	/* set hint if any */
! //  	if (pdata->hint) {
! //  		int erc = bcp_options(*pdbproc, BCPHINTS, (BYTE *) pdata->hint, strlen(pdata->hint));
! //  
! //  		if (erc != SUCCEED)
! //		{
! //  			fprintf(stderr, "db-lib: Unable to set hint \"%s\" (erc = %d)\n", pdata->hint, erc);
! //  			return FALSE;
! //  		}
! //  	}
  
  	return (TRUE);
  }
  
  int
***************
*** 422,427 ****
--- 425,443 ----
  	if (FAIL == bcp_init(dbproc, pdata->dbobject, pdata->hostfilename, pdata->errorfile, dir))
  		return FALSE;
  
+ 	/* kjh was here A60513 */
+ 
+   	/* set hint if any */
+   	if (pdata->hint) {
+   		int erc = bcp_options(dbproc, BCPHINTS, (BYTE *) pdata->hint, strlen(pdata->hint));
+   
+   		if (erc != SUCCEED)
+ 		{
+   			fprintf(stderr, "db-lib: Unable to set hint \"%s\" (erc = %d)\n", pdata->hint, erc);
+   			return FALSE;
+   		}
+   	}
+ 
  	if (pdata->Eflag) {
  
  		bcp_control(dbproc, BCPKEEPIDENTITY, 1);
***************
*** 464,477 ****
  
  	bcp_control(dbproc, BCPBATCH, pdata->batchsize);
  
! 	printf("\nStarting copy...\n\n");
  
  	if (FAIL == bcp_exec(dbproc, &li_rowsread)) {
  		fprintf(stderr, "bcp copy %s failed\n", (dir == DB_IN) ? "in" : "out");
  		return FALSE;
  	}
  
! 	printf("%d rows copied.\n", li_rowsread);
  
  	return TRUE;
  }
--- 480,495 ----
  
  	bcp_control(dbproc, BCPBATCH, pdata->batchsize);
  
! 	// printf("\nStarting copy...\n\n");
! 	fprintf(stderr, "\nStarting copy...\n\n");
  
  	if (FAIL == bcp_exec(dbproc, &li_rowsread)) {
  		fprintf(stderr, "bcp copy %s failed\n", (dir == DB_IN) ? "in" : "out");
  		return FALSE;
  	}
  
! 	// printf("%d rows copied.\n", li_rowsread);
! 	fprintf(stderr, "%d rows copied.\n", li_rowsread);
  
  	return TRUE;
  }
***************
*** 517,522 ****
--- 535,553 ----
  	if (FAIL == bcp_init(dbproc, pdata->dbobject, pdata->hostfilename, pdata->errorfile, dir))
  		return FALSE;
  
+ 	/* kjh was here A60513 */
+ 
+   	/* set hint if any */
+   	if (pdata->hint) {
+   		int erc = bcp_options(dbproc, BCPHINTS, (BYTE *) pdata->hint, strlen(pdata->hint));
+   
+   		if (erc != SUCCEED)
+ 		{
+   			fprintf(stderr, "db-lib: Unable to set hint \"%s\" (erc = %d)\n", pdata->hint, erc);
+   			return FALSE;
+   		}
+   	}
+ 
  	if (pdata->Eflag) {
  
  		bcp_control(dbproc, BCPKEEPIDENTITY, 1);
***************
*** 553,559 ****
  		}
  	}
  
! 	printf("\nStarting copy...\n\n");
  
  
  	if (FAIL == bcp_exec(dbproc, &li_rowsread)) {
--- 584,591 ----
  		}
  	}
  
! 	// printf("\nStarting copy...\n\n");
! 	fprintf(stderr, "\nStarting copy...\n\n");
  
  
  	if (FAIL == bcp_exec(dbproc, &li_rowsread)) {
***************
*** 561,567 ****
  		return FALSE;
  	}
  
! 	printf("%d rows copied.\n", li_rowsread);
  
  	return TRUE;
  }
--- 593,600 ----
  		return FALSE;
  	}
  
! 	// printf("%d rows copied.\n", li_rowsread);
! 	fprintf(stderr, "%d rows copied.\n", li_rowsread);
  
  	return TRUE;
  }
***************
*** 575,580 ****
--- 608,626 ----
  	if (FAIL == bcp_init(dbproc, pdata->dbobject, pdata->hostfilename, pdata->errorfile, dir))
  		return FALSE;
  
+ 	/* kjh was here A60513 */
+ 
+   	/* set hint if any */
+   	if (pdata->hint) {
+   		int erc = bcp_options(dbproc, BCPHINTS, (BYTE *) pdata->hint, strlen(pdata->hint));
+   
+   		if (erc != SUCCEED)
+ 		{
+   			fprintf(stderr, "db-lib: Unable to set hint \"%s\" (erc = %d)\n", pdata->hint, erc);
+   			return FALSE;
+   		}
+   	}
+ 
  	if (pdata->Eflag) {
  
  		bcp_control(dbproc, BCPKEEPIDENTITY, 1);
***************
*** 599,613 ****
  	if (FAIL == bcp_readfmt(dbproc, pdata->formatfile))
  		return FALSE;
  
! 	printf("\nStarting copy...\n\n");
! 
  
  	if (FAIL == bcp_exec(dbproc, &li_rowsread)) {
  		fprintf(stderr, "bcp copy %s failed\n", (dir == DB_IN) ? "in" : "out");
  		return FALSE;
  	}
  
! 	printf("%d rows copied.\n", li_rowsread);
  
  	return TRUE;
  }
--- 645,660 ----
  	if (FAIL == bcp_readfmt(dbproc, pdata->formatfile))
  		return FALSE;
  
! 	// printf("\nStarting copy...\n\n");
! 	fprintf(stderr, "\nStarting copy...\n\n");
  
  	if (FAIL == bcp_exec(dbproc, &li_rowsread)) {
  		fprintf(stderr, "bcp copy %s failed\n", (dir == DB_IN) ? "in" : "out");
  		return FALSE;
  	}
  
! 	// printf("%d rows copied.\n", li_rowsread);
! 	fprintf(stderr, "%d rows copied.\n", li_rowsread);
  
  	return TRUE;
  }