I have to pass List from java to JDBC query( which i have written in xml file).  List  transactionTypeStringList which can have 3 or 6   Before passing it to query, i have joined all the list value using following code to concatenate   transactionTypeString=  String.join("',' ", transactionTypeStringList);  transactionTypeString= "'"+transactionTypeString+ "'";   Query:  select count(*) from transaction where transactiontype in (?);   I am using db2 db and when i run the program, i am getting following exception:  Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.64.96  When i googled this error code, it states that "Data being inserted into the database table column is larger than the assigned column length." which i understand since i am passing list of value to replace a single ? But now I don't how to pass multiple value from java code to jdbc to replace a single ?.  I don't want to create multiple queries(one with 3 and other with 6 value). Please help me to fix this issue.
6749
What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? placeholder represents one value, rather than a list of values.  Consider the following SQL statement:  SELECT my_column FROM my_table where search_column IN (?)   Using preparedStatement.setString( 1, "'A', 'B', 'C'" ); is essentially a non-working attempt at a workaround of the reasons for using ? in the first place.    What workarounds are available?
48923
When using JDBC, I often come across constructs like    ResultSet rs = ps.executeQuery(); while (rs.next()) {     int id = rs.getInt(1);     // Some other actions }   I asked myself (and authors of code too) why not to use labels for retrieving column values:    int id = rs.getInt("CUSTOMER_ID");   The best explanation I've heard is something concerning performance. But actually, does it make processing extremely fast? I don't believe so, though I have never performed measurements. Even if retrieving by label would be a bit slower, nevertheless, it provide better readability and flexibility, in my opinion. So could someone give me good explanation of avoiding to retrieve column values by column index instead of column label? What are pros and cons of both approaches (maybe, concerning certain DBMS)?
63506
This is a part of my .  I have the below logic to split a string value from a column in table tblProjects as below:  SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID  SET @Docs = @Docs + '$' SET @pos = 0 SET @len = 0  WHILE CHARINDEX('$', @Docs, @pos + 1)&gt;0 BEGIN   SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos   SET @value = SUBSTRING(@Docs, @pos, @len)    IF CHARINDEX('|', @value) &gt;0   BEGIN     SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),             @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)     INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)   END       SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1 END   A sample input would be   insert into tblProjects(ID, Documents)  values('P_1', 'gdgfg|jkkgk$bkgkkj|kjgkgk$vjffj|Khkgjhg$jkgkvghv|kbkghhkgk');   I need to split the Documents string and insert the values into a separate table. Values delimited by | should go into separate columns of the same row, whereas values for a particular row are delimited by $.   Is there a single query method to complete this operation, for all rows in the table, in the minimum possible time?
2705
The upsert() DML operation requires a specific List based on a specific sObject type, for instance:List&lt;Account&gt; lstAccount = new List&lt;Account&gt;();   However, we are trying to generate a generic method which allows us to pass by an sObject list containing all data records to be upserted for a specific sObject. An insert() operation allows us to use a List&lt;sObject&gt; to be inserted but the upsert() function is rejecting it:  List&lt;sObject&gt; listWRecords = new List&lt;sObject&gt;(); listWRecords.add(new Foo__c(Name="Record1")); listWRecords.add(new Foo__c(Name="Record2"));  upsert(listWRecords, 'ExternalIdFieldFrom_Foo__c', false);   Which will result in an error saying that the upsert must be performed on a concrete entity type.   Dynamically casting an sObject list is also not possible; I've seen solutions to 'hard code' or create Wrapper classes for each sObject on your org. But we would like to create a generic option here.  Another possibility would be to create some logic mimicking an upsert by splitting it into an update() and an insert() operation. Keeping in mind Salesforce limits, I do not see an option to go this way.
60323
My first post,pretty excited.I want to make a soap api call which insert multiple records. I am using 2 different salesforce instances. destination side  global with sharing class SoapDemo1 {    global class AccountWrapper    {       webservice string Name;       AccountWrapper(Account a)       {         this.Name=a.Name;       }    }     webservice static List&lt;Account&gt; CreateMultipleAccounts(List&lt;Account&gt; accs)    {        List&lt;Account&gt; FinalList=new List&lt;Account&gt;();         for(Account a:accs)        {           Account a1=new Account();           a1.Name=a.Name;            FinalList.add(a1);           }         if (FinalList.size() &gt; 0)        {           Database.Insert(FinalList);        }         return FinalList;     }     }   I generated the class wsdl file and also partner wsdl file  ****source side:**** I upload the class wsdl file generated from destination, when I parse no errors, but when I click Generated Apex code, it shows error as:     Unable to find complexType for   {}anyType    I changed anyType to string and again repeated the process but it shows error as:     Unable to find complexType for   {}JunctionIdListNames   Please help me out
14862
I am trying to query two tables and get results like the following:  Section    Names shoes      AccountName1, AccountName2, AccountName3 books      AccountName1   The tables are:  CREATE TABLE dbo.TableA(ID INT, Section varchar(64), AccountId varchar(64));  INSERT dbo.TableA(ID, Section, AccountId) VALUES (1 ,'shoes','A1'), (2 ,'shoes','A2'), (3 ,'shoes','A3'), (4 ,'books','A1');  CREATE TABLE dbo.TableB(AccountId varchar(20), Name varchar(64));  INSERT dbo.TableB(AccountId, Name) VALUES ('A1','AccountName1'), ('A2','AccountName2'), ('A3','AccountNAme3');   I saw a few questions answered saying to use "XML PATH" and "STUFF" to query the data to get the results I am looking for, but I think there is something missing.  I have tried the below query and get the error message:     Column 'a.AccountId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.   I don't have it in the SELECT clause of either query, but I assume the error is because AccountId is not unique in TableA.  Here is the query I am currently trying to get working correctly.  SELECT section, names= STUFF((     SELECT ', ' + Name FROM TableB as b  WHERE AccountId = b.AccountId FOR XML PATH('')), 1, 1, '') FROM TableA AS a GROUP BY a.section
37183
Possible Duplicate:          I have seen  in SQL. I wonder how would you approach the opposite problem: splitting coma delimited string into rows of data:  Lets say I have tables:  userTypedTags(userID,commaSeparatedTags) 'one entry per user tags(tagID,name)   And want to insert data into table  userTag(userID,tagID) 'multiple entries per user   Inspired by  question  EDIT  Thanks for the answers, actually more then one deserves to be accepted but I can only pick one, and the  with recursions seems pretty clean to me. It works on SQL Server 2005 and above.   For earlier version of SQL Server the solution  can be used.  For working with text data type  will be helpful. Thanks again.
4991
Using SQLite3 with Python 2.5, I'm trying to iterate through a list and pull the weight of an item from the database based on the item's name.  I tried using the "?" parameter substitution suggested to prevent SQL injections but it doesn't work. For example, when I use:  for item in self.inventory_names:     self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", item)     self.cursor.close()   I get the error:     sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 8 supplied.   I believe this is somehow caused by the initial creation of the database; the module I made that actually creates the DB does have 8 bindings.  cursor.execute("""CREATE TABLE Equipment      (id INTEGER PRIMARY KEY,      name TEXT,     price INTEGER,      weight REAL,      info TEXT,      ammo_cap INTEGER,      availability_west TEXT,     availability_east TEXT)""")   However, when I use the less-secure "%s" substitution for each item name, it works just fine. Like so:  for item in self.inventory_names:     self.cursor.execute("SELECT weight FROM Equipment WHERE name = '%s'" % item)     self.cursor.close()   I can't figure out why it thinks I have 8 bindins when I'm only calling one. How can I fix it?
40938
After I posted this question, I found a similar post that seemed to answer it: . I'll leave this in place for now, as I am not sure how to mark my own question as a duplicate  In attempting to debug a SOQL query error, I encountered a strange behavior with Database.query and bind variables. Specifically, when I run this code:  String userQuery = 'SELECT Id FROM User'; if (true) {     List&lt;String&gt; firstNames = new List&lt;String&gt;{'James','Joe'};     userQuery += ' WHERE FirstName IN :firstNames'; } Database.query(userQuery);   the Database.query statement executes with no errors, despite the fact that the variable firstNames does not exist in its context. However, when I add an else clause:  String userQuery = 'SELECT Id FROM User'; if (true) {     List&lt;String&gt; firstNames = new List&lt;String&gt;{'James','Joe'};     userQuery += ' WHERE FirstName IN :firstNames'; } else {     List&lt;String&gt; firstNames; } Database.query(userQuery);   I receive a null object reference exception. It seems like Salesforce is checking to see if the variable is only declared once beforehand and, if so, uses its value in the context it was declared. This seems strange to me, so I was wondering if anybody here has any insight into what is going on under the hood.
41171
So I have the following query that I am running through Batch Apex. I get the error:  System.LimitException: Too many query rows: 50001  I am receiving this because COUNT_DISTINCT() is subject to the same governor limits as other SOQL Queries. Basically I cannot think of a solution here, which is why I am posting this. The goal of the query is to see how many unique accounts an SDR is in contact with over a given time period. In this case, it is over a 120 day period. Would love some advice on how to achieve this.  String Contacts_Reached_TR_120_Query = 'SELECT Ownerid SDR_ID, COUNT_DISTINCT( whoid ) result FROM Task Where ActivityDate = LAST_N_DAYS:120 AND OwnerId in :ListOfIDs GROUP BY Ownerid';  DynamicBatchApexAR batch3_TR120      =   new DynamicBatchApexAR('SDR_ID', 'result', 'Contacts_Reached_TR_120__c', Contacts_Reached_TR_120_Query, SDR_IDs);   Then here is the batch class  global class DynamicBatchApexAR implements Database.Batchable&lt;AggregateResult&gt; {  public String sObjectIdKey; public String sObjectResultKey; public String sObjectFieldToUpdate; public String query; public List&lt;Id&gt; ListOfIDs;   global DynamicBatchApexAR(String arIdKey, String arResultKey, String fieldToUpdate, String soqlQuery, List&lt;Id&gt; whereInClause )  {       sObjectIdKey            = arIdKey;     sObjectResultKey        = arResultKey;     sObjectFieldToUpdate    = fieldToUpdate;     query                   = soqlQuery;     ListOfIDs               = whereInClause; }  global DynamicBatchApexAR(String arIdKey, String arResultKey, String fieldToUpdate, String soqlQuery )  {       sObjectIdKey            = arIdKey;     sObjectResultKey        = arResultKey;     sObjectFieldToUpdate    = fieldToUpdate;     query                   = soqlQuery; }  global Iterable&lt;AggregateResult&gt; start(Database.BatchableContext BC)  {     return ListOfIDs == null ? new BulkIterable(query) : new BulkIterable(query, ListOfIDs); }  global void execute(Database.BatchableContext BC, List&lt;sObject&gt; scope)  {     DynamicSObjectUpdater sObjectUpdater = new DynamicSObjectUpdater();      for(sObject sObj: scope)     {          AggregateResult ar      = (AggregateResult)sObj;         ID sObjectId            = (ID)ar.get(sObjectIdKey);         Decimal arResult        = (Decimal)ar.get(sObjectResultKey);         Map&lt;String, Object&gt; sObjectFieldsToUpdate = new Map&lt;String, Object&gt;{             sObjectFieldToUpdate =&gt; arResult         };          sObjectUpdater.getUpdateSObject(sObjectId, sObjectFieldsToUpdate);     }      sObjectUpdater.updateSObjects();   } global void finish(Database.BatchableContext BC)  {  }   }  Here is the iterable    global with sharing class BulkIterable implements Iterable&lt;AggregateResult&gt; {         String query;         List&lt;Id&gt;   ListOfIDs;      global BulkIterable(String soql, List&lt;Id&gt; WhereInClause)     {         query = soql;         ListOfIDs = WhereInClause;     }      global BulkIterable(String soql)     {         query = soql;     }      global Iterator&lt;AggregateResult&gt; Iterator(){         return ListOfIDs == null ? new BulkIterator(query) :  new BulkIterator(query, ListOfIDs);     }  }   Here is the iterator      global class BulkIterator implements Iterator&lt;AggregateResult&gt; {        AggregateResult[] results { get;set; }       Integer index { get;set; }   List&lt;Id&gt; ListOfIDs;      global BulkIterator(String query, List&lt;Id&gt; WhereInClause)      {     index = 0;     ListOfIDs = WhereInClause;     results = Database.query(query);        }    global BulkIterator(String query) {     index = 0;     results = Database.query(query);      }    global Boolean hasNext(){     return results != null &amp;&amp; !results.isEmpty() &amp;&amp; index &lt; results.size();    }        global AggregateResult next() {      return results[index++];   }      }   Is the error because I am running too many batches?
96022
I had the following exception thrown at me when trying to process more than one selected records via a custom button in the list view.    Class Code  global class MerchandiseInventoryClass {      webservice static void ConverttoInventory(String[] merchandise_id)    {        //Convert Merchandise collection into Inventory Collection...         Merchandise__c[] merchandise_collection = [SELECT ID,Author__c,Book_Title__c,Edition__c,Publisher__c,Total_Merchandise_QtyTotal_Qty__c,Status__c FROM Merchandise__c WHERE ID = :merchandise_id];          Inventory__c i = new Inventory__c(); //Line 1        List&lt;Inventory__c&gt; newInventory = new List&lt;Inventory__c&gt;();        for(Merchandise__c m : merchandise_collection )        {            if(m.Status__c !='Fully Converted')            {            i.Author__c = m.Author__c ;            i.Book_Title__c = m.Book_Title__c;            i.Edition__c = m.Edition__c;            i.Publisher__c = m.Publisher__c;            i.Qty_Available__c = m.Total_Merchandise_QtyTotal_Qty__c;            i.Merchandise__c = m.Id;            m.Uninventoried_Qty__c = 0;            m.Status__c = 'Fully Converted';            newInventory.add(i);            }        }        insert newInventory;        update merchandise_collection;    }  }   By moving the Line 1 to within the for loop solved the issue for me.  Thanks to this post (refer the Best Answer in that post)    What puzzles me is that WHY i have to instantiate "i" for every iteration within the loop.  Why the list collection ("newInventory") is complaining that are duplicate elements and why should it even confuse in the first place when I am performing an "insert" even ?
76988
To query a single record we have below two approach :  Approach 1. Assign record to a single Account variable as shown below:  Account accountToGet = [SELECT Name From Account where name = 'UniqueAccount' LIMIT 1];   With above line, it throws below error if no record founds : System.QueryException: List has no rows for assignment to SObject  Approach 2. Assign single record to a List variable as mention below :   List&lt;Account&gt; lstAccount = [SELECT Name From Account where name = 'Unknown Account'];    Even if SOQL returns no records, above line doesn't throws an error. We can use below line to avoid NULL errors.  if(!lstAccount.IsEmpty())     System.debug('No Error');   To handle NULL exception in Approach 1, we need to handle NULL exception in try....catch. It looks like an approach where we will error to occur and then handle it in try....catch.  With Approach 2, we can avoid exception to occur and without using try..catch.. also the code will work.  Based on above approaches, I think approach 2 is fine to prevent errors. Can anyone give me suggestion that why should not we use approach 2?
63512
Am not doing anything fancy, just trying to use  method of list.      List&lt;Id&gt; myIdList = new List&lt;Id&gt;(); myIdList.add('0060E000008PdMk');  Id oppId ='0060E000008PdMk';      System.debug(myIdList.contains(oppId)); //Returns false   To my surprise, I spent a few hours debugging and came with this problematic code. Are we not supposed to use contains for List&lt;Id&gt; ?  Changing the type from List&lt;Id&gt; to List&lt;String&gt; makes my code run, but I dont have any clue why it does?   Can anyone shed some light?  UPDATE : As per the suggestion of sfdcfox I have raised a case with SF support. Case number "19522568". I will keep you guys updated with the results.  UPDATE 2 : Salesforce reached out to me for this,   A bug W-4884673 already exist for this issue. SF R&amp;D team is working on the same. Known issue link for reference:   There isn't any ETA for this to be fixed, so the case will be closed as "bug fix submitted"  UPDATE 3 : This has now been resolved
18114
I have a method with an out parameter that tries to do a type conversion. Basically:  public void GetParameterValue(out object destination) {     object paramVal = "I want to return this. could be any type, not just string.";      destination = null; // default out param to null     destination = Convert.ChangeType(paramVal, destination.GetType()); }   The problem is that usually someone would call this like:  string output; GetParameterValue(output);   This will fail because of:  destination.GetType()   destination is null, so we can't call .GetType() on it. We also can not call:  typeof(destination)   because destination is a variable name not a type name.  So is there any way to get the type of an object that is set to null? I would think there would have to be a way to know what type a storage location is without it being assigned anything.    Just to give a bit more info, I am trying to make a utility method that will grab the output parameters of an Oracle stored procedure. The issue is that DbParameter.Value is of type object.  What would be ideal would be for the developers to do something like:  string val = GetParameterValue("parameterName");   The notable thing is that there is no casting of types. In practice, you don't know the lparam of the "equals", so I went with:  string val; GetParameterValue("parameterName", out val);   And figured within the method, I would know the destination type of the output variable. I guess that was a bad assumption. As an alternative, I also wrote the method:  public T GetParameterValue&lt;T&gt;(string paramName)   So the developers can do:  string val = GetParameterValue&lt;string&gt;("parameterName");   I find the explicit "string" declaration to be repetitive, especially since in practice, the destination if probably an object property and the oracle data type could change (think ORM):  MyObj.SomeProp = GetParameterValue&lt;MyObj.SomeProp.GetType()&gt;("parameterName");   But again, if MyObj.SomeProp is null, that .GetType() call fails. The VM has to know the type of MyObj.SomeProp, even when its null, right? or else how would it catch cast exceptions?    To partially solve my own problem, I can do:  MyObj.SomeProp = GetParameterValue&lt;typeof(MyObj).GetField("SomeProp").GetType()&gt;("parameterName");   The whole idea was to not have to explicitly use the Type in more than one place, so that if the data type changes, it only has to be changed in the destination object (MyObj.SomeProp) and in the DB. There has to be a better way...
118306
I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values.  This is identical to , but for Oracle rather than SQL Server or MySQL.
68403
I have this controller but I am getting this error      System.QueryException: List has no rows for assignment to SObject   public class MyController {     private final Account account;      public MyController() {         account = [SELECT Id, Name, Site FROM Account                     WHERE Id = :ApexPages.currentPage().getParameters().get('id')];             }      public Account getAccount() {         return account;     }      public PageReference save() {         update account;         return null;     } }
50351
I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office.  INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "London Office"); INSERT INTO MyTable VALUES ("Miranda", 126, "Bristol Office");   Can I insert all 4 rows in a single SQL statement?