+ Start a Discussion
msimondsmsimonds 

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
DevAngelDevAngel
Um, yes, why?  I think you are missing some key concepts about developing with our API.
msimondsmsimonds
Not sure I really understand your comment. I was merely asking a question on the type of database used.  What concepts am I missing exactly!

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?
Mike LeachMike Leach
Oracle is the "database", but the API provides access to a "metabase". It helps to differentiate between the 2 and set your expectations on working with the metabase only, as the physical table names are designed to support multiple tenants. Not a single org.
msimondsmsimonds
Thanks Mike for the quick response!  I apprecaite you taking the time to do so! 

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?
SuperfellSuperfell
All the metadata about your schema is available via the describeSObject(s) API call, and i believe the Developerside kick along with other tools like SForce Explorer and SoqlXplorer can show you all the details including field types, lengths, crud access etc.
Mike LeachMike Leach

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.

msimondsmsimonds
thanks to all of you that have been helping me on this, I truly appreciate it!

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?
DevAngelDevAngel
Hmm...  Are you using the same login in each case?  The reason I ask is that field level security is honored in the API in the same way it is in the app.  Be sure to use a login with sufficient access to all the fields.


msimondsmsimonds
yes the login does not change at all.  I have also not modified the any of the source code on the data loader, I am using it out of the box!
msimondsmsimonds

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

DevAngelDevAngel
Hi msimonds,

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
msimondsmsimonds
Thanks Dave that will really help.  If I am using the data loader on unix, where do I check this or where do I add/change it!
Gareth DaviesGareth Davies

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.

msimondsmsimonds
email sent !!   Thanks Gareth :smileywink:
DevAngelDevAngel
The sforce explorer allows you to set the endpoint as a setting.  Click tools/options and on the login tab modify the endpoint to be 7.0 leaving other settings alone.

Cheers.
msimondsmsimonds
Thanks Dave for the reply!  Not on the desktop application version, I am referring to the CLI on linux.   Is there a file or path I can put in the config.properties to change the URL to the correct version?
DevAngelDevAngel
For the dataloader I'm not sure.  I'll try to find out.
msimondsmsimonds
Gareth I just wanted to say thanks and that your application > the Developers Sidekick is just an amazing piece of software and I cannot wait until it is out of beta.  The ability to use the report function to create a database schema is just grand!
 
I will definatly purchase this software once it is released!!
 
Regards,
Mike
Maxim Integrated Products
Gareth DaviesGareth Davies

Our pleasure. Glad we could help.

Cheers

Gareth.

Gareth DaviesGareth Davies
Just an addendum to the post:

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.