You need to sign in to do that
Don't have an account?
msimonds
Database Type?
I did not know where to post this, sorry if this is the wrong place!
Does salesforce use Oracle as the database?
If so could someone please confirm this?
Thanks,
Mike
Maxim Int Products
Your API documentation is outstanding to say the least but I have one small problem with it! This is an example. On page 206 is the Opportunity table layout, fields, field names, and descriptions. It does not tell me what the max length is, no where in the API documentation does it say what type of field it is, such as varchar or numeric, or alphanumeric
Now if I am wrong, please point me in the right direction.
also why cannot we, as clients, ask from salesforce to have a database scheme of our tables and their structures? Especially if your using Oracle and so are we (in this case)! It would really make it easier for us to create our database?
I understand the concept differences between the 'database" and the "metabase".
Here is my question though. The following is an example of our acutal schema from salesforce using an appexchange tool > The developers sideKick:
Object: Opportunity
Opportunity.Id
Opportunity.AccountId
Opportunity.Name
Opportunity.Description
Opportunity.StageName
Opportunity.Amount
Opportunity.ExpectedRevenue
Opportunity.CloseDate
Opportunity.Type
Opportunity.NextStep
Opportunity.LeadSource
Opportunity.IsClosed
Opportunity.IsWon
I am trying to create an Oracle table based off of this schema, problem to me is that I do not know what the atrributes of these fields are. that is what I am trying to figure out or a way to figure out a way to obtain these, such as varchar (18) or clob, etc...
Does that make sense?
The describeSObject API call will give you the type info for each Field (such as Text(255) or Integer ).
Force Amp will replicate your meta model into a physical database model using SQL Server. Not sure about Oracle replication though.
Okay I am running into a bump in the road here.
If I use the dataloader as a desktop application to extract the data into a CSV file and select all fields from the opportunity table, here is the query that comes directly cut and pasted from that app:
Select Id, AccountId, Name, Description, StageName, Amount, ExpectedRevenue, TotalOpportunityQuantity, CloseDate, Type, NextStep, LeadSource, IsClosed, IsWon, ForecastCategory, CampaignId, HasOpportunityLineItem, PricebookId, Pricebook2Id, OwnerId, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, RSM_Original_Project_ID_Score__c, Pool_FAE__c, Avg_Annual_Volume__c, Priority__c, Design_Start_Date__c, Design_Proto_Date__c, Production_Years__c, Production_Life_months__c, Production_Months__c, Design_Freeze_Date__c, Production_Proto_Date__c, Mass_Production_Date__c, FAE_Status__c, FAE_Assigned_Date__c, FAE_Accepted_Date__c, Potential__c, FAE_Reject_Reason__c, FAE_Project_Complexity__c, Influence_or_Revenue__c, BU_Project_Status__c, Design_Phase__c, maxim_id__c, Objective_Status_Next_Qtr__c, Quality_of_Relationship__c, Targeted_PO_Date__c, Part_No_Involved__c, First_PO_Number__c, Lost_Reason__c, FAE_Estimate_of_Business__c, Baseband_Partner__c, Project_Number__c, Active__c FROM Opportunity
but when I use this exact select statement either in the dataloader that I have working on one of our linux servers or from the sforce explorer, i receive errors on the field names like:
TotalOpportunityQuantity,
or
2006-11-28 14:32:35,225 ERROR [main] progress.NihilistProgressAdapter doneError (NihilistProgressAdapter.java:51) - INVALID_FIELD:
RSM_Original_Project_ID_Score__c, Pool_FAE__c, Avg_Annual_Volume__c
^
ERROR at Row:1:Column:336
No such column 'Pool_FAE__c' on entity 'Opportunity'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.
Tue Nov 28 14:32:35 CST 2006 ==> done
any ideas on this?
Here is a synopsis of the error that is happening with the Opportunity table/object
I am using various Salesforce.com Appexchange tools to run my export test so I can create mappings so we can create a mirror image of our data from SFDC.
The tools I am using are:
Salesforce Data Loader
Developers Side Kick
Sforce Explorer
Salesforce Data Loader:
Used in two ways:
1) Desktop Application on Windows XP - I start this application, choose the Opportunity table to export. I choose all fields to unload the data into a CSV file locally. Here is the query:
Select Id, AccountId, Name, Description, StageName, Amount, ExpectedRevenue, TotalOpportunityQuantity, CloseDate, Type, NextStep, LeadSource, IsClosed, IsWon, ForecastCategory, CampaignId, HasOpportunityLineItem, PricebookId, Pricebook2Id, OwnerId, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, RSM_Original_Project_ID_Score__c, Pool_FAE__c, Avg_Annual_Volume__c, Priority__c, Design_Start_Date__c, Design_Proto_Date__c, Production_Years__c, Production_Life_months__c, Production_Months__c, Design_Freeze_Date__c, Production_Proto_Date__c, Mass_Production_Date__c, FAE_Status__c, FAE_Assigned_Date__c, FAE_Accepted_Date__c, Potential__c, FAE_Reject_Reason__c, FAE_Project_Complexity__c, Influence_or_Revenue__c, BU_Project_Status__c, Design_Phase__c, maxim_id__c, Objective_Status_Next_Qtr__c, Quality_of_Relationship__c, Targeted_PO_Date__c, Part_No_Involved__c, First_PO_Number__c, Lost_Reason__c, FAE_Estimate_of_Business__c, Baseband_Partner__c, Project_Number__c, Active__c FROM Opportunity
The export runs perfectly and creates the data as expected
2) Linux automated process of Data Loader:
If I copy and paste the exact same SELECT statement into our automated scripts and schedule it to run. It fails on the TotalOpportunityQuantity field. If I modify the SELECT statement and remove this field the query fails on other custom fields. This is the following error:
No such column ' TotalOpportunityQuantity ' on entity 'Opportunity'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.
Here is an example of another field that it is failing on:
No such column 'Pool_FAE__c' on entity 'Opportunity'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.
Developers Sidekick:
This tool shows a schema of the tables we currently have in Salesforce. Here is the schema of the opportunity table/object:
Object: Opportunity
Opportunity.Id
Opportunity.AccountId
Opportunity.Name
Opportunity.Description
Opportunity.StageName
Opportunity.Amount
Opportunity.ExpectedRevenue
Opportunity.CloseDate
Opportunity.Type
Opportunity.NextStep
Opportunity.LeadSource
Opportunity.IsClosed
Opportunity.IsWon
Opportunity.ForecastCategory
Opportunity.CampaignId
Opportunity.HasOpportunityLineItem
Opportunity.PricebookId
Opportunity.Pricebook2Id
Opportunity.OwnerId
Opportunity.CreatedDate
Opportunity.CreatedById
Opportunity.LastModifiedDate
Opportunity.LastModifiedById
Opportunity.SystemModstamp
Opportunity.RSM_Original_Project_ID_Score__c
Opportunity.Avg_Annual_Volume__c
Opportunity.Priority__c
Opportunity.Design_Start_Date__c
Opportunity.Design_Proto_Date__c
Opportunity.Production_Years__c
Opportunity.Production_Life_months__c
Opportunity.Production_Months__c
Opportunity.Design_Freeze_Date__c
Opportunity.Production_Proto_Date__c
Opportunity.Mass_Production_Date__c
Opportunity.FAE_Status__c
Opportunity.FAE_Assigned_Date__c
Opportunity.FAE_Accepted_Date__c
Opportunity.Potential__c
Opportunity.FAE_Reject_Reason__c
Opportunity.FAE_Project_Complexity__c
Opportunity.Influence_or_Revenue__c
Opportunity.BU_Project_Status__c
Opportunity.Design_Phase__c
Opportunity.maxim_id__c
Opportunity.Objective_Status_Next_Qtr__c
Opportunity.Quality_of_Relationship__c
Opportunity.Targeted_PO_Date__c
Opportunity.Part_No_Involved__c
Opportunity.First_PO_Number__c
Opportunity.Lost_Reason__c
Opportunity.FAE_Estimate_of_Business__c
Opportunity.Baseband_Partner__c
Opportunity.Project_Number__c
Opportunity.Active__c
As you see the field, ' TotalOpportunityQuantity ' or 'Pool_FAE__c', is not in the schema from this view. So there is some sort of difference coming from Salesforce. I am not sure if this is related to this being a custom field or a problem in Salesforce itself. I also logged into Salesforce and ran an export on the Opportunity table, both of these fields/objects showed up on that export too.
SForce Explorer:
Same error is happening as the Sidekick
I hope this all makes sense.
Message Edited by msimonds on 11-29-2006 10:15 AM
Well, I believe I've discovered at least part of the solution. Make sure that for any api client (sforce explorer, developer sidekick, dataloader) that you are using the 7.0 end point (https://www.salesforce.com/services/Soap/u/7.0). I can duplicate the missing TotalOpportunityQuantity field by using an endpoint less than 7.0.
This may also resolve the user lookup field Pool_FAE__c.
Cheers
Hi there
The version you are using of Dev Sidekick is API 6. drop me an email to gdavies@upside-outcomes.com and I'll send you the latest version (not yet released) which is on version 7. I also might be able to help you out with a SQL CREATE TABLE generator that we have somewhere in the labs if we can find it.
Cheers
Gareth.
Cheers.
Our pleasure. Glad we could help.
Cheers
Gareth.
We released Developers' Sidekick here and created a documentation generator with an Oracle SQL output which you can find here. Both programs are free for developers' edition use and have 10 day free trial for use on non-developers edition accounts.
Gareth.