Could i binary bulk load to a CERTAIN column?

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


NB: in fact, your code would not require any s[n]printf() at all.

Also, even while developing your code, you might want to keep you code clean of too much commented out ("dead") code ("noise") and use clean and proper indenting --- the cleaner the code (and its formatting/layout), the easier it is to debug it and spot errors / bugs "visually" ...

Just another  ¥ 0,02 of mine ... ;-)

Stefan

----- Original Message -----
> 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) |
> 
> _______________________________________________
> 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