Re: Creating new table with same structure as existing one
I have a small ec program, which will return the create table statement.
It is written for data types mentioned in array . Call this from
4gl with params (tabname,length(tabname)) returning l_create_stmt.
This is bit crude, but u can refine it.
=============================================================
#include<stdlib.h>
#include<stdio.h>
#include<sqltypes.h>
#include<ifxtypes.h>
#include<math.h>
#include<datetime.h>
char *myvals[24] = { " char\\("," smallint"," int","",""," decimal\\(","
serial","
date","",""," datetime","","","
varchar\\(","","","","","","","","","",""};
char *dtimes[16] =
{"YEAR","","MONTH","","DAY","","HOUR","","MINUTE","","SECOND"
,"fraction\\(1\\)","fraction\\(2\\)","fraction\\(3\\)","fraction\\(4\\)","fraction\\(5\\)"
};
int tabsel(int
nargs)
{
exec sql begin declare
section;
char
l_colname[50];
int
l_coltype;
int
l_colno;
int
l_collength;
char
l_mystmt[300];
exec sql end declare
section;
int
tmp;
int
tmp1;
char
tabname[50];
int
len;
int
i;
char
l_stmt[300];
char
selstmt[1000];
popint(&len);
popquote(tabname,(len + 1
));
sprintf(l_mystmt,"select trim(colname),coltype,colno,collength
from sys
columns s,systables t where s.tabid=t.tabid and t.tabname= \\"%s\\" order
by colno
",tabname);
/*exec sql database
euro50k;*/
exec sql prepare prep_my from :l_mystmt
;
if (sqlca.sqlcode != 0
)
{
retquote("");
retint(sqlca.sqlcode);
return(2);
}
exec sql declare d_cur cursor for
prep_my;
if (sqlca.sqlcode != 0
)
{
retquote("");
retint(sqlca.sqlcode);
return(2);
}
exec sql open
d_cur;
if (sqlca.sqlcode != 0
)
{
retquote("");
retint(sqlca.sqlcode);
return(2);
}
sprintf(selstmt,"create table %s
\\(",tabname);
while (sqlca.sqlcode ==
0){
exec sql fetch next d_cur into
:l_colname,:l_coltype,:l_colno,:l
_collength;
if ( sqlca.sqlcode == 100
)
break;
while ( l_coltype >= 256
){
l_coltype = l_coltype -
256;
}
if ( (2<l_coltype && l_coltype <5) || (7<l_coltype &&
l_coltype<
10) || (10< l_coltype && l_coltype<12 ) || (l_coltype >13)
){
retquote("");
retint(2);
return(2);
}
for(i=0;i<strlen(l_colname);i++)
{
if ( l_colname[i]=='
')
{
l_colname[i]='\\0';
break;
}
}
strcat(selstmt,l_colname);
strcat(selstmt,myvals[l_coltype]);
switch
(l_coltype){
case 0:
sprintf(l_stmt,"%d\\)\\,",l_collength);
break;
case 1:
sprintf(l_stmt,"\\,");
break;
case 2:
sprintf(l_stmt,"\\,");
break;
case 5:
tmp=l_collength/256;
tmp1= l_collength -
tmp*256;
sprintf(l_stmt,"%d\\,%d\\)\\,",tmp,tmp1);
break;
case 6:
sprintf(l_stmt,"\\,");
break;
case 7:
sprintf(l_stmt,"\\,");
break;
case 10: sprintf(l_stmt," %s to %s
\\,",dtimes[TU_START(l
_collength)],dtimes[TU_END(l_collength)]);
break;
case 13:
tmp=l_collength/256;
tmp1=remainder(l_collength,256);
sprintf(l_stmt,"%d\\,%d\\)\\,",tmp1,tmp);
break;
}
strcat(selstmt,l_stmt);
}
selstmt[strlen(selstmt) - 1] = ')';
retquote(selstmt);
retint(0);
return(2);
}
==========================================================
Hope this helps
Rgds
Preetinder
Nebojsa Sevo wrote:
>I need to create "archive" table that will have the same structure as
>"production" one.
>
>When I run my program it have to create table named "pronop_yyyymm" ( yyyymm is
>argument to my program representing date in format year(4)month(2)) if it
>doesn't exist. I will write rows from table "pronop" that are in given
>month/year to "pronop_yyyymm" and delete them from "pronop" (I know that DELETE
>is very slow but I don't have alternative).
>What I want is not to change program (or part of program where I create
>"pronop_yyyymm") when structure of table "pronop" is changed.
>I am using I4GL. I have an idea to do it with "dbschema" and some scripting.
>
>Is there any other way?
>
>nebojsa
>------------------------------------
>Remove spam block (DELETE_) to reply
>
>
>
sending to informix-list