Process of Creating and Deleting tables from SQL through Dexterity
March 30th, 2010During a table upgrade, we are met with the task of creating and deleting tables on SQL using Dexterity code.
The process of deleting tables requires deleting both the table and its stored procedures, but the process of creating tables does at the same time create a table and its stored procedures on SQL. Also, if we try to create the stored procedures after creating a table, the result will be failed.
The whole process of creating a table is done in one line:
open table TableName;
The following few lines will try to create stored procedures:
l_result = Table_CreateProcedures(ProductID, table CSCIVCatalogNotes);
if l_result <> 0 then
error “Stored procedures were not successfully created for table CSCIVCatalogNotes.”;
end if;
However, if we try to create stored procedures after we create the table on SQL, we will always get the error message. The main reason is that stored procedures are already created.
The process of deleting a table from SQL requires deleting stored procedures:
local long status;
status = Table_DropProcedures(ProductID, table NameOfTheTable);
If the value of the variable status is equal to 0, stored procedures are successfully deleted and now we need to delete the table from SQL. We can do it this way:
Local text sCode;
Local long status;
status = SQL_Connect(SQL_connection);
if (status = 0) then
sCode = ” use ” + DatabaseName;
sCode = sCode + ” drop table PhisicalNameOftheTable “;
status = SQL_Execute(SQL_connection, sCode);
{— If status is equal to 0 the table is successfully deleted. —}
end if;
status = SQL_Terminate(SQL_connection);
The whole story is important especially for a process of upgrading tables, because if we do not delete stored procedures and just drop and create tables, some of the old stored procedures can stay and mix with the new ones, so real damage can be made.
