function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MilkovicMilkovic 

Downloading into table

What's the best way to download a salesforce table (i.e. Account), into a table in my database? The only way I see to do it is to get a QueryResult and create a insert statement based on it into my database, but this seems inefficient. Any other ideas?

DevAngelDevAngel

Hi WhiZa,

If you db supports a bulk import process then you could run a query to get the data, output it to the correct bulk import/copy format and run the bulk import.  You could also use a recordset that can update the database and add the records from the query to the recordset then update the server.

If you want to do this in a scheduled fashion, you can set up a standing request to have csv files generated on a weekly basis for batch import into a database.

Cheers
MilkovicMilkovic

Ok, but if I use a ResultSet you are assuming I already have the table in the database (I'm using SQL Server) to insert into. I would like to create a whole new table based on the returned query. Is the only way to do this is to create a csv file, then do a bulk insert?

Also, the SoapBindingStub.query method doesn't allow for all fields (*)? Whenever I use the *, instead of just field names, I receive a malformed query exception. How can I select all fields?
DevAngelDevAngel

Hi WhiZa,

Ok, so you are using SQL Server.  If you do a describe call on the object that you are retrieving, you can build the appropriate CREATE TABLE statement to generate the table.  I've done this and it's not hard.  Alternatively, you could create a table based on the field list portion of the query string, but since you want to gather all fields, I recommend the describe call.  Here is a VB .Net version:

    Private Function CreateTableSQL(ByVal ObjectName As String) As String

        Dim dsor As sForce25.DescribeSObjectResult = sfh25.webReference.describeSObject(ObjectName)

        'get the name of the table
        Dim tableName As String = "CREATE TABLE [dbo].[" & Me.tvDesc.SelectedNode.Text & "] ("
        'get the field names and types
        Dim fieldList As String
        Dim fields() As sForce25.Field = dsor.fields
        For i As Integer = fields.GetLowerBound(0) To fields.GetUpperBound(0)
            Dim fieldName As String = fields(i).name
            Dim fieldType As String = fields(i).type
            If fieldType.ToLower = "char" Then
                Dim fieldLen As String = fields(i).length
                If fieldType = "text" Then
                    fieldList += "[" & fieldName & "] [" & fieldType & "]"
                Else
                    fieldList += "[" & fieldName & "] [" & fieldType & "] (" & fieldLen & ")"
                End If
            Else
                fieldList += "[" & fieldName & "] [" & fieldType & "]"
            End If
            fieldList += ", "
        Next
        fieldList = fieldList.Substring(0, fieldList.Length - 2)
        Dim strSQL As String = tableName & " " & fieldList & ") ON [PRIMARY]"

        Return strSQL

    End Function

We do not allow a select * operaton because it would be misused and needlessly have a negative impact on performance acrosse the board.  You can select all fields by building a list of fields from the describe call.  It is very similar to the code above.

 
MilkovicMilkovic

Thanks Dave,

In the sforce examples to get the value of a field you use the getFieldName method. How do I get the value for a custom field that doesn't have a method to return its value?
DevAngelDevAngel

Hi WhiZa,

Re-import your wsdl.  Custom fields and tables will be represented in the WSDL.  When you make changes to your schema you will need to re-generate the Axis client code.
MilkovicMilkovic

Thanks Dave, I really appreciate it 

I am able to retrieve vales by using the getFieldName method(s). Is there a way to retrieve a field value by its index? In the code you provided to me before you get a field's name by its index "fields(i).name", I was wondering if it was possible when getting values as well. We have a system administrator who likes to add/remove fields quite often, so it would be much easier to change an index instead of amending method names.
DevAngelDevAngel

Hi WhiZa,

In this case you may want to look at using the partner version of the wsdl.  The partner wsdl is useful when you don't know the schema ahead of time or, as in your case, when the schema changes frequently.  It does this by not including any schema object types in the wsdl.  You provide the data in the form of xml that represents your objects.

We currently do not have the samples for the partner wsdl ready, but are busy producing them.