Table Creation taking a lot of time in SAP 2007...

[Former Member]

I have an SAP Addon which is created in SAP2005...Right now i changed the code to SAP 2007...But the Problem i am facing is, it takes a lot of time to created the usertabels thorugh SAP 2007 code...Around 5 minutes it takes....But i created the usertables through SAP 2005 less than 2 minutes....I ahve to create 18 usertables thorugh this code....

can anyone help me to solve this issue.. as well as i want to know whether any change is there in codes in SAP 2005 or SAP 2007.for creating Usertables....

My Code:

Private Sub AddUserTable(ByVal Name As String, ByVal Description As String, ByVal Type As SAPbobsCOM.BoUTBTableType)

Try

oUserTablesMD = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserTables)

If oUserTablesMD.GetByKey(Name) = False Then

oUserTablesMD.TableName = Name

oUserTablesMD.TableDescription = Description

oUserTablesMD.TableType = Type

lRetCode = oUserTablesMD.Add

If lRetCode 0 Then

'oCompany.GetLastError(lRetCode, sErrMsg)

'MsgBox(sErrMsg)

End If

End If

Catch ex As Exception

'pEve_Application.StatusBar.SetText(ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error)

Finally

System.Runtime.InteropServices.Marshal.ReleaseComObject(oUserTablesMD)

End Try

End Sub

Waiting for Positive Reply...

Thanks

Hari

[Former Member · Accepted solution]

the only thing you may try instead of

System.Runtime.InteropServices.Marshal.ReleaseComObject(oUserTablesMD)

add

oUserTablesMD= Nothing

GC.Collect()

BTW, 5 min for 19 tables is normal. You create it onetimes only and in seccond run you may check if table exists and in case that exists, you cannot try to add table and fields.

[Former Member · Accepted solution]

Thank you...You helped me to solve the issue..Now i am checking whthere the usertables as well as userfields are there or not..it reduced the time to less than 2 minutes....

[Former Member]

I have using the code listed in the forum for creating tables and for creating user fields. I want also to create an UDO for the table but i have no ideea how to buid the XML for an UDO. could you please help me if you have any ideea? Thnks

Dragos C

[Former Member]

Thank you Vitor Vieira...I will go thorugh the code that you send me...

[Former Member]

Hi hari,

I suggest you create you user tables, fields, keys, permissions through XML.

Here's a sample:

<?xml version="1.0" encoding="UTF-16"?>
<BOM>
	<BO>
		<AdmInfo>
			<Object>153</Object>
		</AdmInfo>
		<OUTB>
			<row>
				<TableName>TABLE_ID</TableName>
				<Descr>TABLE_DESCRIPTION</Descr>
				<ObjectType>1</ObjectType>
			</row>
		</OUTB>
	</BO>
	<BO>
		<AdmInfo>
			<Object>152</Object>
		</AdmInfo>
		<CUFD>
			<row>
				<TableID>@TABLE_ID</TableID>
				<AliasID>FIELD_NAME</AliasID>
				<Descr>FIELD_DESCRIPTION</Descr>
				<TypeID>A</TypeID>
				<EditType></EditType>
				<Dflt></Dflt>
				<EditSize>100</EditSize>
			</row>
		</CUFD>
		<UFD1>
		</UFD1>
	</BO>
</BOM>

If you have already created the fields before (through code or through B1 UI), you can query the OUTB, CUFD and UFD1 tables to check what values you need to put in the XML file.

You can use the following function to process a single XML file with the definition of tables, fields, keys and permissions.

    Public Function LoadBDFromXML(ByRef oCompany As SAPbobsCOM.Company, ByRef oApplication As SAPbouiCOM.Application, _
            ByRef Directory As String, ByRef FileName As String) As Boolean

        Dim sXmlFileName As String
        Dim iElementCount As Long
        Dim iCounter As Long

        LoadBDFromXML = False

        Try
            '// Inicializar a barra de progresso, caso seja nada.
            If BarraDeProgresso Is Nothing Then
                BarraDeProgresso = oApplication.StatusBar.CreateProgressBar(UpdatingDB, 1, False)
            End If

            sXmlFileName = Directory & "" & FileName
            iElementCount = oCompany.GetXMLelementCount(sXmlFileName)
            BarraDeProgresso.Maximum = iElementCount
            BarraDeProgresso.Value = 0

            For iCounter = 0 To iElementCount - 1
                oCompany.XmlExportType = BoXmlExportTypes.xet_ExportImportMode

                Select Case oCompany.GetXMLobjectType(sXmlFileName, iCounter)

                    '// Tabela de Utilizador
                    Case BoObjectTypes.oUserTables
                        Dim oTable As SAPbobsCOM.UserTablesMD = oCompany.GetBusinessObjectFromXML(sXmlFileName, iCounter)

                        Try
                            BarraDeProgresso.Value += 1
                            BarraDeProgresso.Text = "UDT: " & oTable.TableDescription
                            If oTable.Add <> 0 Then
                                oApplication.MessageBox(TranslateStr(oApplication, ErrAddUDT) & vbCrLf & _
                                TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & _
                                oCompany.GetLastErrorDescription & vbCrLf & TranslateStr(oApplication, Element) & _
                                iCounter & ", " & oTable.TableDescription)
                                Exit Function
                            End If
                        Catch ex As Exception
                            oApplication.MessageBox(TranslateStr(oApplication, Error_) & _
                            TranslateStr(oApplication, ErrUDT) & ", " & ex.ToString)
                        Finally
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(oTable)
                            oTable = Nothing
                            GC.Collect()
                        End Try


                        '// Campos de Utilizador
                    Case BoObjectTypes.oUserFields
                        Dim oFields As SAPbobsCOM.UserFieldsMD = oCompany.GetBusinessObjectFromXML(sXmlFileName, iCounter)

                        Try
                            BarraDeProgresso.Value += 1
                            BarraDeProgresso.Text = "UDF: " & oFields.TableName & "." & oFields.Name & " - " & oFields.Description
                            If oFields.Add <> 0 Then
                                oApplication.MessageBox(TranslateStr(oApplication, ErrAddUDF) & vbCrLf & _
                                TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & _
                                oCompany.GetLastErrorDescription & vbCrLf & TranslateStr(oApplication, Element) & _
                                iCounter & ", " & oFields.Name)
                                Exit Function
                            End If
                        Catch ex As Exception
                            oApplication.MessageBox(TranslateStr(oApplication, Error_) & _
                            TranslateStr(oApplication, ErrUDF) & ", " & ex.ToString)
                        Finally
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(oFields)
                            oFields = Nothing
                            GC.Collect()
                        End Try


                        '// Chaves de Utilizador
                    Case BoObjectTypes.oUserKeys
                        Dim oKeys As SAPbobsCOM.UserKeysMD = oCompany.GetBusinessObjectFromXML(sXmlFileName, iCounter)

                        Try
                            BarraDeProgresso.Value += 1
                            BarraDeProgresso.Text = "UDKey: " & oKeys.KeyName & "(" & oKeys.TableName & ")"
                            If oKeys.Add <> 0 Then
                                oApplication.MessageBox(TranslateStr(oApplication, ErrAddUDK) & vbCrLf & _
                                TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & _
                                oCompany.GetLastErrorDescription & vbCrLf & TranslateStr(oApplication, Element) & _
                                iCounter & ", " & oKeys.KeyName)
                                Exit Function
                            End If
                        Catch ex As Exception
                            oApplication.MessageBox(TranslateStr(oApplication, Error_) & _
                            TranslateStr(oApplication, ErrUDK) & ", " & ex.ToString)
                        Finally
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(oKeys)
                            oKeys = Nothing
                            GC.Collect()
                        End Try


                        '// Árvore de Autorizações
                    Case BoObjectTypes.oUserPermissionTree
                        Dim Tree As SAPbobsCOM.UserPermissionTree = oCompany.GetBusinessObjectFromXML(sXmlFileName, iCounter)

                        Try
                            BarraDeProgresso.Value += 1
                            BarraDeProgresso.Text = PermTree & Tree.Name
                            If Tree.Add <> 0 Then
                                oApplication.MessageBox(TranslateStr(oApplication, ErrAddUPT) & vbCrLf & _
                                TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & _
                                oCompany.GetLastErrorDescription & vbCrLf & TranslateStr(oApplication, Element) & _
                                iCounter & ", " & Tree.Name)
                                Exit Function
                            End If
                        Catch ex As Exception
                            oApplication.MessageBox(TranslateStr(oApplication, Error_) & _
                            TranslateStr(oApplication, ErrUPT) & ", " & ex.ToString)
                        Finally
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(Tree)
                            Tree = Nothing
                            GC.Collect()
                        End Try


                        '// Objectos de Utilizador
                    Case BoObjectTypes.oUserObjectsMD
                        Dim oUDO As SAPbobsCOM.UserObjectsMD = oCompany.GetBusinessObjectFromXML(sXmlFileName, iCounter)

                        Try
                            BarraDeProgresso.Value += 1
                            BarraDeProgresso.Text = "UDO: " & oUDO.Code & "(" & oUDO.Name & ")"
                            If oUDO.Add <> 0 Then
                                oApplication.MessageBox(TranslateStr(oApplication, ErrAddUDO) & vbCrLf & _
                                TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & _
                                oCompany.GetLastErrorDescription & vbCrLf & TranslateStr(oApplication, Element) & _
                                iCounter & ", " & oUDO.Name)
                                Exit Function
                            End If
                        Catch ex As Exception
                            oApplication.MessageBox(TranslateStr(oApplication, Error_) & _
                            TranslateStr(oApplication, ErrUDO) & ", " & ex.ToString)
                        Finally
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(oUDO)
                            oUDO = Nothing
                            GC.Collect()
                        End Try
                End Select
            Next iCounter

            If Not BarraDeProgresso Is Nothing Then
                BarraDeProgresso.Stop()
                BarraDeProgresso = Nothing
            End If

            LoadBDFromXML = True
        Catch ex As Exception
            If oCompany.InTransaction = True Then oCompany.EndTransaction(BoWfTransOpt.wf_RollBack)
            oApplication.MessageBox("LoadBDFromXML(" & FileName & "): " & oCompany.GetLastErrorCode.ToString & ", " & ex.ToString)
        End Try
    End Function

Note: BarraDeProgresso is my progress bar.

You can also register UDO's through XML, but I prefer to do it by code.

Regards,

Vítor Vieira