I�m building a web application which will be used primarily for users to log on and print certain financial reports. Because of the complexity of some of the reports, data is taken from several tables and appended to 2 temporary tables and the report is made from a set derived from linking the 2 temp tables. Since, each time a report is run, the 2 temp tables are zapped and new data appended, I am concerned about the wait time to generate the report if several people try running the same report at the same time.
So, what I am trying to accomplish is, instead of directly using the temp tables and set, to create 2 new randomly named tables, then link them to form a new set, then duplicate the existing temp set to the newly created set so that the newly created set will have the report layout from the temp set, run the report in pdf or html format, then delete the newly created set and tables. This way there is never a conflict except when the duplication happens which shouldn�t be a problem.
I first created the code and tested in in a desktop application and it works fine. I won�t go into detail of the many ways I�ve tried to get this done. However, at one point I got it to work successfully in the interactive window of the page I want it to run on but it would hang up when it ran live. Below is the code I�m using at this point in time.
When the page runs, the 2 new tables are created, the first table is added to the controlpanel then the page hangs up.
<%a5
''SESSION VARIABLE TO BE USED FOR UDF PAGES
session.headertbl=""
session.datatbl=""
session.set_name=""
session.Ptbl_Name=""
session.Ctbl_Name=""
session.PSet_Name=""
''CREATE PARENT TABLE NAME AND LOCATION
dim shared Pname as c
Pname = "A"+rand_string(6)+".dbf"
session.headertbl="[PathAlias.ADB_Path]"+"\\"+Pname
session.Ptbl_Name=A5.GET_PATH()+"\\"+Pname
''CREATE CHILD TABLE NAME AND LOCATION
dim shared Cname as c
Cname = "B"+rand_string(6)+".dbf"
session.datatbl="[PathAlias.ADB_Path]"+"\\"+Cname
session.Ctbl_Name=A5.GET_PATH()+"\\"+Cname
''CREATE SET NAME AND LOCATION
dim shared Sname as c
Sname = left(Pname,7)+"_"+left(Cname,7)+".set"
session.set_name = "[PathAlias.ADB_Path]"+"\\"+Sname
session.PSet_Name = A5.GET_PATH()+"\\"+Sname
''CODE TO CREATE NEW PARENT TABLE
fields = <<%a%
Clientno,c,10
Code,c,10
Company,c,50
My1,c,8
My2,c,8
My3,c,8
My4,c,8
My5,c,8
My6,c,8
My7,c,8
My8,c,8
My9,c,8
My10,c,8
My11,c,8
My12,c,8
%a%
create_table(session.Ptbl_Name, fields)
''CODE TO CREATE NEW CHILD TABLE
fields = <<%a%
Clientno,c,10
Indicator,c,35
M1,c,18
M2,c,18
M3,c,18
M4,c,18
M5,c,18
M6,c,18
M7,c,18
M8,c,18
M9,c,18
M10,c,18
M11,c,18
M12,c,18
%a%
create_table(session.Ctbl_Name, fields)
''CREATE PARENT TABLE INDEX
Dim t as p
t=table.open(session.Ptbl_Name)
t.index_create_begin("ZClientno","Clientno")
t.index_create_end()
t.close()
''CREATE CHILD TABLE INDEX
Dim t as p
t=table.open(session.Ctbl_Name)
t.index_create_begin("ZClientno","Clientno")
t.index_create_end()
t.close()
''ADD PARENT TABLE TO CONTROLPANEL
dim ptr_Ptbl as p
dim Ptbl as c
ptr_Ptbl = table.open(session.Ptbl_Name)
Ptbl = ptr_Ptbl.filename_get()
file_add_to_db(Ptbl)
ptr_Ptbl.close()
''ADD CHILD TABLE TO CONTROLPANEL
dim ptr_Ctbl as p
dim Ctbl as c
ptr_Ctbl = table.open(session.Ctbl_Name)
Ctbl = ptr_Ctbl.filename_get()
file_add_to_db(Ctbl)
ptr_Ctbl.close()
''LINK TABLES TO CREATE SET
dim parent_tbl as P
dim child_tbl as P
dim child_index as P
parent_tbl = table.open(session.Ptbl_Name)
child_tbl = table.open(session.Ctbl_Name)
relation.link_type = LINK_MANY
child_index = child_tbl.index_get("ZClientno")
relation.index_child = child_index
relation.order_parent = "Clientno"
relation.ref_integrity = 0
parent_tbl.relation_add(session.Ctbl_Name)
parent_tbl.set_create(A5.GET_PATH()+"\\"+session.PSet_Name)
child_tbl.close()
parent_tbl.close()
''DUPLICATE TEMP SET TO NEWLY CREATED SET
A5_DUPLICATE_SET_FILES(A5.GET_PATH()+"\\"+"ind_mavg_set", A5.GET_PATH()+"\\"+session.PSet_Name)
''ADD NEW SET TO TO CONTROLPANEL
dim PSet as c
dim ptr_PSet as P
ptr_PSet = set.open(session.PSet_Name)
PSet = ptr_PSet.filename_get()
file_add_to_db(PSet)
ptr_PSet.close()
%>
First of all, does what I�m trying to do make sense, and if so is it doable?? Is there a better way to handle the problem?? Am I paranoid about the possible wait time if I go ahead and design it using the original Temp Table setup??
I�m using A5V9 Plat.
By the way, the reports that I�m concerned about have 2 UDF pages that are included on the reports select page and each has over 2000 lines of code that are used to empty and repopulate the tables in the report set. Yet the reports run in about 3 seconds (locally). Again, my concern is what is going to happen when 2 or more users attempt to run the same report at the same time.
I�ve spent so much time on this that I can�t even think straight about it any more!!
Any and all help will be greatly appreciated.
JAS
So, what I am trying to accomplish is, instead of directly using the temp tables and set, to create 2 new randomly named tables, then link them to form a new set, then duplicate the existing temp set to the newly created set so that the newly created set will have the report layout from the temp set, run the report in pdf or html format, then delete the newly created set and tables. This way there is never a conflict except when the duplication happens which shouldn�t be a problem.
I first created the code and tested in in a desktop application and it works fine. I won�t go into detail of the many ways I�ve tried to get this done. However, at one point I got it to work successfully in the interactive window of the page I want it to run on but it would hang up when it ran live. Below is the code I�m using at this point in time.
When the page runs, the 2 new tables are created, the first table is added to the controlpanel then the page hangs up.
<%a5
''SESSION VARIABLE TO BE USED FOR UDF PAGES
session.headertbl=""
session.datatbl=""
session.set_name=""
session.Ptbl_Name=""
session.Ctbl_Name=""
session.PSet_Name=""
''CREATE PARENT TABLE NAME AND LOCATION
dim shared Pname as c
Pname = "A"+rand_string(6)+".dbf"
session.headertbl="[PathAlias.ADB_Path]"+"\\"+Pname
session.Ptbl_Name=A5.GET_PATH()+"\\"+Pname
''CREATE CHILD TABLE NAME AND LOCATION
dim shared Cname as c
Cname = "B"+rand_string(6)+".dbf"
session.datatbl="[PathAlias.ADB_Path]"+"\\"+Cname
session.Ctbl_Name=A5.GET_PATH()+"\\"+Cname
''CREATE SET NAME AND LOCATION
dim shared Sname as c
Sname = left(Pname,7)+"_"+left(Cname,7)+".set"
session.set_name = "[PathAlias.ADB_Path]"+"\\"+Sname
session.PSet_Name = A5.GET_PATH()+"\\"+Sname
''CODE TO CREATE NEW PARENT TABLE
fields = <<%a%
Clientno,c,10
Code,c,10
Company,c,50
My1,c,8
My2,c,8
My3,c,8
My4,c,8
My5,c,8
My6,c,8
My7,c,8
My8,c,8
My9,c,8
My10,c,8
My11,c,8
My12,c,8
%a%
create_table(session.Ptbl_Name, fields)
''CODE TO CREATE NEW CHILD TABLE
fields = <<%a%
Clientno,c,10
Indicator,c,35
M1,c,18
M2,c,18
M3,c,18
M4,c,18
M5,c,18
M6,c,18
M7,c,18
M8,c,18
M9,c,18
M10,c,18
M11,c,18
M12,c,18
%a%
create_table(session.Ctbl_Name, fields)
''CREATE PARENT TABLE INDEX
Dim t as p
t=table.open(session.Ptbl_Name)
t.index_create_begin("ZClientno","Clientno")
t.index_create_end()
t.close()
''CREATE CHILD TABLE INDEX
Dim t as p
t=table.open(session.Ctbl_Name)
t.index_create_begin("ZClientno","Clientno")
t.index_create_end()
t.close()
''ADD PARENT TABLE TO CONTROLPANEL
dim ptr_Ptbl as p
dim Ptbl as c
ptr_Ptbl = table.open(session.Ptbl_Name)
Ptbl = ptr_Ptbl.filename_get()
file_add_to_db(Ptbl)
ptr_Ptbl.close()
''ADD CHILD TABLE TO CONTROLPANEL
dim ptr_Ctbl as p
dim Ctbl as c
ptr_Ctbl = table.open(session.Ctbl_Name)
Ctbl = ptr_Ctbl.filename_get()
file_add_to_db(Ctbl)
ptr_Ctbl.close()
''LINK TABLES TO CREATE SET
dim parent_tbl as P
dim child_tbl as P
dim child_index as P
parent_tbl = table.open(session.Ptbl_Name)
child_tbl = table.open(session.Ctbl_Name)
relation.link_type = LINK_MANY
child_index = child_tbl.index_get("ZClientno")
relation.index_child = child_index
relation.order_parent = "Clientno"
relation.ref_integrity = 0
parent_tbl.relation_add(session.Ctbl_Name)
parent_tbl.set_create(A5.GET_PATH()+"\\"+session.PSet_Name)
child_tbl.close()
parent_tbl.close()
''DUPLICATE TEMP SET TO NEWLY CREATED SET
A5_DUPLICATE_SET_FILES(A5.GET_PATH()+"\\"+"ind_mavg_set", A5.GET_PATH()+"\\"+session.PSet_Name)
''ADD NEW SET TO TO CONTROLPANEL
dim PSet as c
dim ptr_PSet as P
ptr_PSet = set.open(session.PSet_Name)
PSet = ptr_PSet.filename_get()
file_add_to_db(PSet)
ptr_PSet.close()
%>
First of all, does what I�m trying to do make sense, and if so is it doable?? Is there a better way to handle the problem?? Am I paranoid about the possible wait time if I go ahead and design it using the original Temp Table setup??
I�m using A5V9 Plat.
By the way, the reports that I�m concerned about have 2 UDF pages that are included on the reports select page and each has over 2000 lines of code that are used to empty and repopulate the tables in the report set. Yet the reports run in about 3 seconds (locally). Again, my concern is what is going to happen when 2 or more users attempt to run the same report at the same time.
I�ve spent so much time on this that I can�t even think straight about it any more!!
Any and all help will be greatly appreciated.
JAS
Comment