VBscript example using VI.DB.Entities.Query class with a join

What is the error?

Where or better in what kind of statement are you trying to use the Query object?

Hi Markus,

I am create a custom task for SAP connector

I would like to have the SAP the "ParamID" on table SAPParameter of a SAPUser.

Obviously I can retrive before all UID_SAPParameter from "SAPUserHasParameter" and after retrive all ParamID from "SAPParameter"

but If I can retrvie all value on a single query it is more efficient.

I would like to have a collection like:

SAPUserHasParameter.UID_SAPParameter - SAPParameter.ParamID -  SAPUserHasParameter.ParameterValue

Thank you very much and best regards

Ermes

I am still not understanding where are trying to use your Query object and what the error message is.

Are you trying to use the Query object in a scripted property of an SAP sync project? Can you post the error message?

This is a possible solution, but this bypasses objectlayer and permissions model so be careful with it. But it is superfast and quite easy to do. Hope it helps.

----

Public Function testQuery() As String

      ' Degine output file

      Dim outputfile As String = "c:\temp\myreport.csv"

      ' Setup database connection

      Dim ConnectionString As String = Connection.GetSingleProperty("dialogDatabase", "connectionstring", "")

      Dim conData As ConnectData = DbApp.Instance.Connect(ConnectionString)

      ' Dim the SQL query as you would run in using ObjectBrowser for example, watch the quotes into the string !

      Dim myQuery As String = "select centralaccount,internalname from person p where p.centralaccount like 'aa%'"

      ' Define return var and run the query

      Dim returnValue As iDatareader

      Dim cSQL As SqlExecutor = conData.Connection.CreateSqlExecutor(conData.PublicKey)

      returnValue = cSQL.SqlExecute(String.Format(myQuery))

      ' Write a header to the output file

      returnValue2csv(outputfile,"centralaccount;internalname",False)

      ' Looping over the query results and append lines to logfile

      While returnValue.Read()

        Dim record As IDataRecord = CType(returnValue, IDataRecord)

            Dim csvLine As String = String.Format("{0};{1};", record(0), record(1))

            returnValue2csv(outputfile,csvLine,True)

      End While

      ' Return done

      Return "Done."

End Function

' Sub function to write a logfile

Public Sub returnValue2csv(ByVal filename As String, ByVal csvLine As String, ByVal appendfile As Boolean)

    ' definestreamwriter object - append or not to append

      Using swFile As New StreamWriter(filename,appendfile)

      ' write

      swFile.WriteLine(csvLine)

      End Using

End Sub