Could i binary bulk load to a CERTAIN column?

Stefan Manegold Stefan.Manegold at cwi.nl
Sun Jul 21 12:14:21 CEST 2013


Hi,

debugging your own code in, say, gdb, would have shown you that the corruption happens in your own code:

a query string with 82 characters fits into an allocated array of 100 characters, while a query string of 132 characters does not:

$ echo 'copy binary into test3 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1')' | wc -c
82

$ echo 'copy binary into test4 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1','home/data1/writeBinary/2','home/data1/writeBinary/3')' | wc -c
132

NB, sprintf() is considered "dangerous", consider suing snprintf() instead.

Best,
Stefan

ps: in case you want to load fits files, you might want to consider using MonetDB's fits data vault ...

----- Original Message -----
> my table test4 is :
> create table test4(id bigint, a float, b float, c float);
> so i didnt selectively load to test4 but it crashed.
> 
> 
> my code is :
> #include <stdio.h>
> #include <stdlib.h>
> #include <time.h>
> #include <mapi.h>
> 
> 
> void itoa (int n,char s[])
> {
> int i,j,sign,k;
> char tmp;
> 
> 
> 
> 
> 
> 
> if((sign=n)<0)//记录符号
>         n=-n;//使n成为正数
>           i=0;
> do{
>         s[i++]=n%10+'0';//取下一个数字
> }while ((n/=10)>0);//删除该数字
> 
> 
> if(sign<0)
>         s[i++]='-';
> s[i]='\0';
> 
> 
> for(j=i-1,k=0;j>k;j--,k++)
>        {
>        tmp = s[k];
>         s[k] = s[j];
> 	 s[j] = tmp;
> }
> }
> 
> 
> int writetoFile() {
> unsigned int seed;
> int k,j,i=0;
> long al[200000];
> double af[200000];
> FILE *fp;
> char fname[5];
> 
> 
> fp = fopen("id","wb");  //创建二进制文件
> 
> 
> for(k=1;k<=200000;k++)  {
> 	al[k] = rand();
> }
> fwrite(al, sizeof(long), 200000,fp); //fwrite以二进制形式对文件操作
> fclose(fp);
> 
> 
> 
> 
> 
> 
> for(j=1;j<=20;j++)  {
> 	
> 	for(k=1;k<=200000;k++) {
> 	af[k] = (rand() /(double)(RAND_MAX));
> 	}
> 
> 
>        itoa(j,fname);
> 	
> 	fp=fopen(fname,"wb");
> 	fwrite(af, sizeof(double), 200000,fp);
> 	fclose(fp);
> }
> 
> 
> }
> 
> 
> 
> 
> void die(Mapi dbh, MapiHdl hdl)
>      {
>      	if (hdl != NULL) {
>      		mapi_explain_query(hdl, stderr);
>      		do {
>      			if (mapi_result_error(hdl) != NULL)
>      				mapi_explain_result(hdl, stderr);
>      		} while (mapi_next_result(hdl) == 1);
>      		mapi_close_handle(hdl);
>      		mapi_destroy(dbh);
>      	} else if (dbh != NULL) {
>      		mapi_explain(dbh, stderr);
>      		mapi_destroy(dbh);
>      	} else {
>      		fprintf(stderr, "command failed\n");
>      	}
>      	exit(-1);
>      }
>      
> 
> 
> MapiHdl query(Mapi dbh, char *q)
>      {
>      	MapiHdl ret = NULL;
>      	if ((ret = mapi_query(dbh, q)) == NULL || mapi_error(dbh) != MOK)
>      		die(dbh, ret);
>      	return(ret);
> }
>      
> void update(Mapi dbh, char *q)
> {
>      	MapiHdl ret = query(dbh, q);
>      	if (mapi_close_handle(ret) != MOK)
>      		die(dbh, ret);
> }
> 
> 
> int filetoMonetDB()  {
> Mapi dbh;
> MapiHdl hdl = NULL;
> 
> 
> dbh = mapi_connect("localhost",50000, "monetdb", "monetdb", "sql","mydb2" );
> if(mapi_error(dbh))
> 	die(dbh,hdl);
> 
> 
> char *sql = NULL;
> sql = (char *)malloc(100);
> memset(sql,0,100);
> 
> 
> //sprintf(sql, "copy binary into tmatch
> from('/home/data1/writeBinary/id','/home/data1/writeBinary/1','/home/data1/writeBinary/2','/home/data1/writeBinary/3','/home/data1/writeBinary/4')");
> //
> '/home/data1/writeBinary/5','/home/data1/writeBinary/6','/home/data1/writeBinary/7','/home/data1/writeBinary/8','/home/data1/writeBinary/9','/home/data1/writeBinary/10',
> \
> // '/home/data1/writeBinary/11', '/home/data1/writeBinary/12',
> '/home/data1/writeBinary/13', '/home/data1/writeBinary/14',
> '/home/data1/writeBinary/15', \
> // '/home/data1/writeBinary/16', '/home/data1/writeBinary/17',
> '/home/data1/writeBinary/18', '/home/data1/writeBinary/19',
> '/home/data1/writeBinary/20' )");
> // sprintf(sql,"insert into test2 values(2)");
> sprintf(sql, "copy binary into test3
> from('/home/data1/writeBinary/id','/home/data1/writeBinary/1')");
> //sprintf(sql,"copy binary into test4
> from('/home/data1/writeBinary/id','/home/data1/writeBinary/1','home/data1/writeBinary/2','home/data1/writeBinary/3')");
> //sprintf(sql,"copy binary into test4(id)
> from('/home/data1/writeBinary/id')");
> update(dbh, sql);
> 
> 
> mapi_destroy(dbh);
> 
> 
> free(sql);
> 
> 
> return 1;
> 
> 
> }
> 
> 
> 
> 
> 
> 
> int main()  {
> struct timeval tpstart,tpend;
> double timeuse;
> 
> 
> gettimeofday(&tpstart,NULL);
> 
> 
> writetoFile();
> 
> 
> gettimeofday(&tpend,NULL);
> timeuse=1000000*(tpend.tv_sec-tpstart.tv_sec)+tpend.tv_usec-tpstart.tv_usec;//unit:us
> timeuse /= 1000000;//unit:s
> printf("21 columns write to file used %f seconds.\n",timeuse) ;
> 
> 
> 
> 
> gettimeofday(&tpstart,NULL);
> 
> 
> filetoMonetDB();
> 
> 
> gettimeofday(&tpend,NULL);
> timeuse=1000000*(tpend.tv_sec-tpstart.tv_sec)+tpend.tv_usec-tpstart.tv_usec;//unit:us
> timeuse /= 1000000;//unit:s
> printf("Files load to MonetDB used %f seconds.\n",timeuse) ;
> 
> 
> return 1;
> }
> _______________________________________________
> users-list mailing list
> users-list at monetdb.org
> http://mail.monetdb.org/mailman/listinfo/users-list
> 

-- 
| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |




More information about the users-list mailing list