Development Matters Blog

During 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.

During my work with a temporary table linked to the scrolling window, I came across a few problems. The first one was using the temporary table in pass through SQL code and the second one was the range of scripts and forms where the records from temporary table could be reached. I will explain the situations through the example below.

The mxCustomerContactsTempTable is a dexterity temporary table linked to the scrolling window. All necessary data for the mxCustomerContactsTempTable table are loaded from the mxCustomerContacts table, but this table needs one more field - ‘Customer Name’ – to be filled. As we have a ‘Customer Number’ in the mxCustomerContactsTempTable table, we can load data from table RM_Customer_MSTR table. If we go through the table record by record, lookup will be pretty slow. The better idea is to use the pass through SQL code.

Instead of using physicalname(table mxCustomerContactsTempTable) for retrieving a table name, we use the command Table_GetOSName(table CustomerContactsTempTable) for retrieving a name for the temporary table. The previous command will return only TEMP as a physical name and SQL would not recognize it.
The problem can occur if we have global script with pass-through SQL code and form which use that table by calling this script. In that case, records in the table will not be recognized and script will work with the empty table. The better option is to put all code related to temporary table in the same form. This way the script will fill the table with all needed data.