Database

The text, ntext, and image data types are invalid for local variables

Hi All,

We are working on a Windows desktop application and which interacts with a MS-Access as end database. We are writing some rows into tables of Ms-Access database by binding the parameters using SQLBindParameter.

 At the time of binding a double and a string separately using SQLBindParameter(), it is successful and after SQL_Execute statement getting "Program type out of range" and "SQL_NEED_DATA" error correspondingly.

The SQLBindParameter() calls as below:

Scenario 1 - binding double parameter:

              double pValue;

             SQLINTEGER iResult = 0;

              iRetCode = SQLBindParameter(
              this->m_hStatement
              , 1
              , SQL_PARAM_INPUT
              , SQL_C_DOUBLE
              , SQL_DOUBLE
              , 8
              , 5
              , (SQLPOINTER*)pValue
              , 0
              , (SQLLEN*)&iResult
          );

and it is producing  [Microsoft][ODBC Driver Manager] Program type out of range  error.

Scenario 2 - binding string parameter(Uni code character set):

              CString pValue;
             SQLINTEGER iResult = SQL_NTS;

              iRetCode = SQLBindParameter(
              this->m_hStatement
              , 1
              , SQL_PARAM_INPUT
              , SQL_C_WCHAR
              , SQL_WCHAR
              , 32
              , 0
              , (SQLPOINTER*)pValue
              , 32
              , (SQLLEN*)&iResult
          );

and it is producing SQL_NEED_DATA  error.

Surprisingly, when I ran the above one by using sample program it is successfully writing record into the table, but when it comes to application, its giving SQL_NEED_DATA error.

I am trying to fix them for couple of days, but no luck. I appreciate your contribution to this question.

Thanks in advance,

Lakshmi NC.

  //The embed method is called for a modified object structure s
  //(with its root node) in the run-time memory. In the beginning
  //of the embed method non-null nodes in s are separated in two
  //groups, white nodes and gray nodes. A non-null node p in s is
  //a white node if its id is zero, i.e. if p does not yet have a
  //corresponding node in the the database. Otherwise, if id>0, p
  //already has a corresponding node in the database with that id
  //and p is a gray node. White nodes are collected in the list
  //whiteNodes and gray nodes are collected in the list GrayNodes.
  ArrayList<Object> whiteNodes;
  ArrayList<Object> grayNodes;

  //As a side effect, the update phase of the embed method
  //collects ids of potential garbage nodes, it finds, in the
  //set seedGarbageIds. When the update phase has been finished,
  //all possible garbage nodes belong to set or graph Z
  //consisting of the nodes reachable from the nodes having their
  //ids in seedGarbageIds. It is possible that only some, or none
  //of the nodes in Z are garbage. Z may also be empty.
  HashSet<Integer> seedGarbageIds;

  //The map countOfInternalReferencesInZ is used to count the
  //internal references inside Z. The key is id of a node and the
  //value is count of internal references to that node in Z.
  HashMap<Integer,Integer> countOfInternalReferencesInZ;

  //In Z, nodes which are referred to from outside the Z,
  //cannot be garbage. Ids of those nodes are collected in the
  //set idsOfNodesRefOutsideZ.
  HashSet<Integer> idsOfNodesRefOutsideZ;
  
		//.
		//.
		//.

  //This is the embed method which a client program calls to
  //update the object database with a modified object structure s
  //in the run-time memory. The method is called with the root
  //object s of the modified object structure. The embed method
  //consists of two co-operating phases, the update phase and the
  //garbage collection phase.
  public void embed(Object s)
  throws Exception
  {
    update(s);
    garbageCollection();
  }

		//.
		//.
		//.

  //The embed method calls the update method with the root node s
  //of the modified object data structure in the run-time memory.
  //After the update method has been finished, content of the
  //object structure s exists in the database.
  //
  //As a side effect, the update method produces the set of ids of
  //potential garbage nodes, it finds, into the set seedGarbageIds.
  //The set seedGarbageIds is input for the garbage collection
  //phase of the embed method.
  //
  //Below the terms "step 1", "step 2", "step 3" and "step 4"
  //refer to the steps described in the article, in section 4.
  private void update(Object s)
  throws Exception
  {
    //Global lists for the white nodes and the gray nodes in s.
    whiteNodes = new ArrayList<Object>();
    grayNodes = new ArrayList<Object>();

    //This boolean value dscribes whether the root node s is
    //white. This information is used in the step 4 of this
    //method.
    boolean rootIsWhite =
      getId(s) == ID_ZERO;

    //Create the global set seedGarbageIds.
    seedGarbageIds = new HashSet<Integer>();

//Step 1 and step 2 of the update method:
    //Step1: Collect white nodes in object the structure s in the
    //list whiteNodes and gray nodes in s in the list grayNodes.
    //Step 2: For each white node in s: Allocate the same type of
    //empty node in the database.
    collectWhiteAndGrayNodes(s);

//Step 3 of the update method:
    //Handle changes of internal reference counts of the nodes
    //caused by updating the database with the white nodes.
    handleReferencesFromWhiteNodesInDB();

    //Handle changes of internal reference counts of the nodes
    //caused by updating the database with the gray nodes.
    handleReferencesFromGrayNodesInDB();

    //Update the database with the white nodes.
    copyContentsOfWhiteNodesToDB();

    //Update the database with the gray nodes.
    copyContentsOfGrayNodesToDB();


//Step 4 of the update method:
    //For selected white nodes in s make their corresponding nodes
    //in the database persistent root nodes by setting them orc=1.
    //
    //We simply select only the root node s, if it is a white node.
    //Otherwise no nodes are selected.
    //
    //When a root node s is a white node it is obvious that the
    //user wants to make s' a persistent root node to make s' and
    //all the reachable nodes from it persistent in the database.
    //
    //There exist rare cases where a user may want that for a
    //white root node s the corresponding node s' gets orc=0 and
    //does not not become a persistent root node in the database.
    //For example if the run-time object structure s is a circular
    //structure s -> p -> s, where s is a white node and p is a
    //gray node, a user may want that persistence of s' depends on
    //the persistence of p'. Therefore orc could be zero for s in
    //this case.
    //
    //If the embed method is called for a gray node s, then the
    //orc of s' is kept as it was. This is a natural decision.
    //However, this can cause a strange but perhaps correct
    //effects in some cases; the node s', and perhaps some
    //reachable nodes from it can disappear from the object
    //database as a consequence of an executed embed method! The
    //following example describes this.
    //
    //Let s' originally refer to some node p' and p' refer back to
    //s' and suppose that no-one else in the database is referring
    //to s', and that orc=0 for s'. After that circular structure
    //s -> p -> s is modified in the run-time memory by setting p
    //to refer to a null node, i.e. not to s anymore. After that
    //the embed method is called for s. As a consequence s'
    //becomes garbage, because now irc=orc=0 for s'.
    if (rootIsWhite)
      incrORC(getId(s));

    //Free the lists reserved for the white nodes and the gray nodes.
    whiteNodes = null;
    grayNodes = null;
  }


  //Method collectWhiteAndGrayNodes is called from the method
  //update. This method separates white nodes and gray nodes in the
  //object structure s. White nodes are collected in the list
  //whiteNodes and gray nodes in the list grayNodes.
  //
  //In addition, the method allocates for each white node p in s a
  //corresponding empty node p' (having a corresponding type) in
  //the object database. The node p gets the id of p' into its
  //id field.
  private void collectWhiteAndGrayNodes(Object p)
  throws Exception
  {
    //Null nodes are note collected.
    if (p==null)
      return;

    //The same node instance is not collected twice.
    if (bufferContainsNode(whiteNodes,p)
        || bufferContainsNode(grayNodes,p))
      return;

    int id = getId(p);

    //If p is a white node, collect p and allocate a corresponding empty
    //node p' in the database. Assign id of p' to p.
    if (id == ID_ZERO)
    {
      id = allocateNodeInDB(p);
      setId(p,id);
      whiteNodes.add(p);
    }
    else //Collect a gray node p.
      grayNodes.add(p);

    //Collect non-null child nodes of p if not yet collected.
    ArrayList<FieldT> pointerFields = getFields(p.getClass(),p,1);
    for (FieldT f : pointerFields)
      collectWhiteAndGrayNodes(f.value);
  }


  //Handle changes of internal reference counts caused by updating
  //the object database with white nodes.
  private void handleReferencesFromWhiteNodesInDB()
  throws Exception
  {
    for(Object p:whiteNodes)
      handleReferencesFromWhiteNodeInDB(p);
  }
  //Here p is a white node. Updating p' with the p can increase
  //internal reference counts of some nodes in the database. These
  //changes are updated in this method.
  //
  //The following rule gives the result:
  //If a white node p refers with a pointer field f to a non-null
  //node q then the internal reference count of the node q'
  //increments by one. If p refers to q many times (with many
  //pointer fields), then the internal reference count of q'
  //increments as many times.
  private void handleReferencesFromWhiteNodeInDB(Object p)
  throws Exception
  {
    ArrayList<Integer> C1 = getIdsOfNonNullChildNodes(p);
    for(Integer id:C1)
      incrIRC(id);
  }


  //Handle changes of internal reference counts caused by updating
  //the object database with gray nodes.
  private void handleReferencesFromGrayNodesInDB()
  throws Exception
  {
    for(Object p:grayNodes)
      handleReferencesFromGrayNodeInDB(p);
  }
  //Here p is a gray node. Updating p' with the p can change
  //internal reference counts of some nodes in the database. These
  //changes are updated in this method.
  //
  //The following rules give the result:
  //
  //1)
  //If, before updating the node p' with a gray node p, the node
  //p' refers with a pointer field f' to a non-null node q' then
  //the internal reference count of q' decrements by one. If p'
  //refers to q' many times (with many pointer fields), then the
  //internal reference count of q' decrements as many times.
  //
  //2)
  //If a gray node p refers with a pointer field f to a non-null
  //node q then the internal reference count of the node q'
  //increments by one. If p refers to q many times (with many
  //pointer fields), then the internal reference count of q'
  //increments as many times.
  //
  //The algorithm does incrementing/decrementing in such a way
  //that the internal reference count of a node is only
  //incremented or decremented, not both.
  //
  //If, after updating, the node p' does not any more refer to a
  //non-null node q', then q' may be garbage. In this case the id
  //of q' is added conditionally to set seedGarbageIds, if it is
  //not yet there.
  private void handleReferencesFromGrayNodeInDB(Object p)
  throws Exception
  {

    //Construct the list C1 = (id(q1),..,id(qn)) of the ids of
    //non-null child nodes of the node p. If p refers several
    //times to the same non-null child node q then the id of q is
    //as many times in the list C1.
    ArrayList<Integer> C1 =
      getIdsOfNonNullChildNodes(p);


    //Construct the list C2 = (id(q'1),..,id(q'm)) of the ids of
    //non-null child nodes of the node p' (before p' has been
    //updated with p). If p' refers several times to the same
    //non-null child node q' then the id of q' is as many times
    //in the list C2.
    ArrayList<Integer> C2 =
      getIdsOfNonNullChildNodesOfDBNode(getId(p));

    //The set I will contain the intersection of C1 and C2. (The
    //same id is not twice in I).
    HashSet<Integer> I = new HashSet<Integer>();

    //Make lists C1 and C2 disjoint. The intersection of C1 and C2
    //is collected in the set I. Note that C1 can contain the
    //same id several times, before and after making C1 and C2
    //disjoint. The same is true for the list C2.
    //For example: Let
    //C1 = (1,2,1,3,4,1,2,2,2,3,3)
    //C2 = (1,1,2,2,3,2,2,2,3,3,5,5)
    //Then, after making C1 and C2 disjoint, C1, C2 and I are:
    //C1 = (4,1)
    //C2 = (2,5,5)
    //I = {1,2,3}
    int i=0;
    while (i < C1.size())
    {
      int id = C1.get(i);
      if (C2.remove((Integer)id))
      {
        C1.remove(i);
        I.add(id);
      }
      else
       ++i;
    }

    //Decrement the internal reference count, irc, of the nodes
    //that have their ids in C2. If id is not in set I, add id
    //conditionally to set seedGarbageIds.
    for(Integer id2:C2)
    {
      decrIRC(id2);

      //If set I contains the id2 then p' will still refere to the
      //node having the id value id2.
      if (I.contains(id2))
        continue;

      //Node p' does not any more refer to a node that has the id
      //value id2. If we are not certain that the node is not
      //garbage add the id2 to the set seedGarbageIds if it is not
      //yet there.
      if (!isNodeCertainlyNotGarabge(id2))
        seedGarbageIds.add(id2);

    }

    //Increment the internal reference counts, irc, of the nodes
    //having their ids in C1.
    for(Integer id1:C1)
      incrIRC(id1);
  }


  //This method updates the object database with the white nodes
  //in a flat way. If a field of a white node p is a pointer field
  //then the id of a node in the field is copied to p', not the
  //node itself.
  private void copyContentsOfWhiteNodesToDB()
  throws Exception
  {
    for(Object p:whiteNodes)
      copyContentOfNodeToDB(p);
  }


  //This method updates the object database with the gray nodes
  //in a flat way. If a field of a gray node p is a pointer field
  //then the id of a node in the field is copied to p', not the
  //node itself. For simplicity, contents of all fields are
  //copied, i.e. not only the changed fields. Also, to make
  //implementing easy, for a list node p the p' is first cleared
  //by making it an empty list.
  private void copyContentsOfGrayNodesToDB()
  throws Exception
  {
    for(Object p:grayNodes)
      copyContentOfGrayNodeToDB(p);
  }
  private void copyContentOfGrayNodeToDB(Object p)
  throws Exception
  {
    //This is a null operation for a fixed node.
    removeFieldsOfNodeInDB(p);

    copyContentOfNodeToDB(p);
  }


 //After the embed method has called the update method, it
 //calls the garbageCollection method to remove possible garbage
 //nodes from the object database.
 //
 //Real garbage nodes belong to the graph Z consisting of the
 //nodes reachable from nodes having their ids in the set
 //seedGarbageIds. Typically, only some or none of the nodes in Z
 //are garbage. Z may also be an empty set.
 //
 //The graph Z is examined by walking (traversing) it, each edge
 //in Z once. During walking, references for the reached nodes are
 //calculated. Walking produces for each node in Z the count of
 //incoming references in Z. By using this information and
 //reference count information (irc and orc) stored in the nodes
 //of Z it is determined which nodes in Z are referred to from
 //outside the Z. These nodes and reachable nodes from them are
 //not garbage. The remaining nodes in Z are real garbage.
 //
 //In some cases only part of the Z is needed to walk, i.e. Z can
 //be shrunk.
 private void garbageCollection()
  throws Exception
  {
    //The map used to count incoming internal references in Z.
    countOfInternalReferencesInZ
      = new HashMap<Integer,Integer>();
    idsOfNodesRefOutsideZ = new HashSet<Integer>();

    //Walk the Z and calculate incoming internal references in Z.
    calculateReferencesProducedByWalkingInZ();

    //Determine the nodes in Z referred to from outside the Z.
    collectIdsOfNodesReferecedOutsideZ();

    //Determine non-garbage nodes in Z. Remaining nodes in Z are
    //real garbage nodes to be removed from the object database.
    removeIdsOfNonGarbageNodesInZ();

    //Remove garbage nodes from the database.
    removeGarbageNodesFromDB();

    //Free the global structures.
    countOfInternalReferencesInZ = null;
    idsOfNodesRefOutsideZ = null;
    seedGarbageIds = null;
  }


  //Calculate for each node in Z the count of incoming references
  //in Z. For that we walk (traverse) the graph Z in the database,
  //each edge once. The counts of incoming references in Z are
  //collected in the map countOfInternalReferencesInZ where the
  //key is the id of the node and the value is the count of the
  //incoming references to that node in the Z.
  private void calculateReferencesProducedByWalkingInZ()
  throws Exception
  {
    for (Integer seedGarbageId:seedGarbageIds)
    {
      //A trick:
      //Let p' be the node having id value seedGarbageId. If the
      //method call "walk(seedGarbageId)" walks to p' then the
      //count of incoming references for p' must be decreased
      //afterward by one because the node p' is not reached through
      //a real edge in Z.
      if (walk(seedGarbageId))
        addToInternalReferencesInZ(seedGarbageId,-1);
    }
  }
  private boolean walk(Integer id)
  throws Exception
  {
    //Here we try to make the Z smaller, i.e. to the node having
    //"id" is not walked to if we are sure that this node is not
    //garbage.
    if (isNodeCertainlyNotGarabge(id))
      return false;

    boolean nodeReachedBefore =
      countOfInternalReferencesInZ.containsKey(id);

    if (nodeReachedBefore) //The node has been seen before.
    {
      addToInternalReferencesInZ(id,1);
      return true;
    }

    //The node has not been seen before.
    countOfInternalReferencesInZ.put(id,1);

    //Walk to non-null child nodes.
    ArrayList<Integer> childIds =
      getIdsOfNonNullChildNodesOfDBNode(id);
    for(Integer idChild : childIds)
    {
      walk(idChild);
    }
    return true;
  }


  //This method returns true if we are sure that the node having
  //the id is not garbage. However, in this demo implementation
  //this method returns always false. Some checkings
  //could be done in real implementations. Some suggestions are in
  //comments.
  private boolean isNodeCertainlyNotGarabge(Integer id)
  throws Exception
  {
    //Possible checkings, for example:
    //1)
    //if "readORC(id) > 0" then the node is a persistent root node
    //and it cannot be garbage.
    //
    //2)
    //The node having the id can not be garbage if some node in the
    //node structure s has the same id and the root node s is a
    //white node. In this case the corresponding node s' is a
    //persistent root node and all reachable nodes from it are
    //persistent.
    //
    //3)
    //It is possible to implement an embed method which takes as a
    //parameter a list of ids of nodes which can not be garbage.
    //(The child program can know strategic nodes which are not
    //garbage) The parameter id of this method could be compared
    //to these ids.

    return false;
  }


  void addToInternalReferencesInZ(Integer id, int value)
  {
    int oldValue = countOfInternalReferencesInZ.get(id);
    countOfInternalReferencesInZ.put(id,oldValue+value);
  }


  //Determine in the Z the nodes, their ids, which are referred to
  //from outside the Z. These ids are collected in the set
  //idsOfNodesRefOutsideZ.
  private void collectIdsOfNodesReferecedOutsideZ()
  throws Exception
  {
    for (Map.Entry<Integer, Integer> e :
      countOfInternalReferencesInZ.entrySet())
    {
      int id = e.getKey();
      int countOfInternalReferences = e.getValue();
      int irc = readIRC(id);
      int orc = readORC(id);

      //Here we test if a node is referred to from outside the Z,
      //i.e. if a node is a persistent root node (orc >= 1) or
      //it is referred to from some node outside the Z
      //(countOfInternalReferences < irc). Note that always
      //countOfInternalReferences <= irc.
      //
      //Also note that if would filter (not done) in the method
      //isNodeCertainlyNotGarabge the nodes that have orc > 0 then
      //the test below ccould be replaced with the test
      //"if (countOfInternalReferences < irc)"
      if (countOfInternalReferences < orc + irc)
        idsOfNodesRefOutsideZ.add(id);
    }
  }


  //In the Z nodes reachable from nodes having they ids in the set
  //idsOfNodesRefOutsideZ are not garbage. In this method ids of
  //these nodes are removed from the map
  //countOfInternalReferencesInZ. The remaining nodes, having
  //their ids in the map countOfInternalReferencesInZ, are the
  //real garbage nodes.
  private void removeIdsOfNonGarbageNodesInZ()
  throws Exception
  {
    for(Integer id : idsOfNodesRefOutsideZ)
      removeIdOfNonGarbageNodeInZ(id);
  }
  private void removeIdOfNonGarbageNodeInZ(Integer id)
  throws Exception
  {
    if (countOfInternalReferencesInZ.remove(id) == null)
      return;

    ArrayList<Integer> childIds
      = getIdsOfNonNullChildNodesOfDBNode(id);
    for(Integer idChild : childIds)
      removeIdOfNonGarbageNodeInZ(idChild);
  }


  //Remove real garbage nodes from the database. These are the
  //nodes having their ids in the map
  //countOfInternalReferencesInZ.
  private void removeGarbageNodesFromDB()
  throws Exception
  {
    Set<Integer> keys = countOfInternalReferencesInZ.keySet();
    for(Integer id : keys)
      removeGarbageNodeFromDB(id);
  }
  private void removeGarbageNodeFromDB(int id)
  throws Exception
  {
    //Internal reference counts of (non-null) non-garbage
    //child nodes must be decremented accordingly.
    ArrayList<Integer> childIds =
      getIdsOfNonNullChildNodesOfDBNode(id);
    for(Integer idChild : childIds)
      if (!countOfInternalReferencesInZ.containsKey(idChild))
        decrIRC(idChild);

    Class<?> c = getClassOfDBNode(id);

    if (c != ListNode.class)
      removeFixedNodeFromDB(c,id);
    else
      removeListNodeFromDB(id);
  }


  //Remove a fixed node from the database, in a flat way.
  private void removeFixedNodeFromDB(Class<?> c, int id)
  throws Exception
  {
    String tableName =
      getFixedTableNameFromClassName(c.getName());
    executeDelete(tableName, "instanceId=?", id);
    executeDelete("nodeInstances", "id=?", id);
  }


  //Remove a list node from the database, in a flat way.
  private void removeListNodeFromDB(int id)
  throws Exception
  {
    int rowIdOfList = (Integer)
      readSingleValue("lists","id","instanceId=?",id);
    executeDelete("listItems", "parent=?", rowIdOfList);
    executeDelete("lists", "id=?", rowIdOfList);
    executeDelete("nodeInstances", "id=?", id);
  }

=# select id from ao_campaigns where id=2851;                                                                                                          
  id  
------
 2851
(1 row)

=# select id from orders where ao_campaign_id=2851;
 id 
----
(0 rows)

=# select * from ao_campaigns where id not in (select ao_campaign_id from orders);
 id | id_tvn | order_id | start_date | end_date | label 
----+--------+----------+------------+----------+-------
(0 rows)

SET @sql =N'INSERT INTO TrackUpdatedColumnNamesFlatTable (TableName, ColumnName, PrimaryKeyColumnName, PrimaryKeyValue, TransactionId
    , TransactionType, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate, IsSoftDelete, IsCmsActive, ColumnValueAfter)
    select ''' + @TblName + ''', ''' +  @ColName + ''', ''' +  @PKColName + '''
    , ' +  @PKColName + ', $start_lsn , CASE WHEN ($operation=1) THEN ''Delete''
    WHEN $operation=2 THEN ''Insert''
    WHEN __$operation=4 THEN ''Update''
    END TransactionType, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate
    , ' + CAST(@IsDeleted AS varchar(max)) + '
    , ' + CAST(@IsCmsActive AS varchar(max)) + '
    , CASE WHEN __$operation=1 THEN NULL ELSE CAST(' + @ColName + ' AS nvarchar(max)) END<br />
FROM [cdc].' + @cdcinstancename
+ ' WHERE CAST(sys.fn_cdc_map_lsn_to_time($start_lsn) AS DATE)=CAST(''' + @DateToTrack + ''' AS DATE)
AND sys.fn_cdc_is_bit_set(sys.fn_cdc_get_column_ordinal(''' + @captureinstance +
''',''' + '' + @ColName + '''), __$update_mask) = 1
AND __$operation IN (1, 2, 4)';

exec sp_executesql @sql, N'@IsColumnModified int out', @IsColumnModified out