|
Hi everybody,
Updating complex structures in a remote systems like in an object databases is not an easy task. This is even more true for pure SQL databases. -Here I assume that also the modifications are complex.
Therefore I tried to develop a general declarative method for updating object databases (and object/relational databases), or more generally remote memories or even more generally, remote directed graphs, without any artificial limitations.
The name of the method is “embed” and it is called from a Java client program in the following simple way:
db.embed(s);
,where s is the root object of a modified object structure in the run-time memory. Here modifications of an object structure s can be totally arbitrary and complex. For example, before calling the embed method, the client program can change the overall topology of s by removing objects from it or by adding new objects to it, some of them created by the client program, some of them loaded originally from the database. In addition, scalar fields of objects can be modified.
The embed method and the theory behind it is described in my article:
Blog: http://hvirkkun.blogspot.fi/2016/04/a-new-communication-theory-on-complex.html
Do you think that the embed method works algorithmically (and practically)?
Some properties:
-The embed method is based on a new communication theory for complex information represented as a directed graph of nodes. In short, the theory states that modified complex information (represented as a directed graph of nodes) can always be transferred back to its original system in an exact and meaningful way.
-The embed method models the object database and a modified object structure in the run-time memory as directed graphs of nodes without any artificial limitations.
-Persistence in the object database is defined in a well known and accepted way, by reachability from persistent root objects.
-The embed method does not only transfer modified information into the object database but also removes structures of garbage objects from the object database if any appear during the update operation, thus leaving the database in a consistent state. Garbage objects may easily appear if some objects in the object database lose references to themselves during the update operation.
-The embed method understands local topology of the object database, avoiding examining unrelated objects in the database which is one reason for its efficiency.
If the embed method works, it makes updating of object databases as easy as manipulating object structures in the run-time memory. Perhaps updating can not be more easy?
The algorithm of the embed method (directly from the Java demo implementation, see my article above):
(--Sorry I had first a wrong fake code below.I forgot to replace it with the right code when I tested formatting possibilities of the Java code. )
I try later clarify my question.
//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);
}
Heikki Virkkunen
|
|
|
|
|
Heikki welcome to CP, you have missed the usage of the forums, they are for asking questions and discussions. What you have posted is more suited to an article or Tip/Trick.
An article gets peer vetted and released, it stays around and may be useful for a long time. A forum post will stay on the from page for only a short time and will not be a long term benefit. An article may attract comment, it can be downloaded and modified and it enhances your reputation (if it is worthy).
I suggest you post this as an article or T/T, it will need some more structure and additional work but it will give much better value.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
How is this possible?
"orders" has an FK ao_campaign_id to "ao_campaigns".
=# 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)
(types are correct, all ids are integers).
The last query should return at least one result - 2851 , right? What am I missing?
Greetings, Jacek
|
|
|
|
|
Got it!
Correct query:
select * from ao_campaigns where ao_campaigns.id not in (select orders.ao_campaign_id from orders where ao_campaign_id IS NOT NULL);
Some of ao_campaign_id in orders were NULL, which are treated as "unknown" and therefore uncomparable. It is not intuitive in this particular case, though.
Reference: sql null logic - Szukaj w Google[^]
|
|
|
|
|
Or get used to use EXISTS instead of IN .
EXISTS does not use three valued logic.
SELECT *
FROM ao_campaigns
WHERE NOT EXISTS (
SELECT *
FROM orders
WHERE orders.ao_campaign_id = ao_campaigns.id
)
|
|
|
|
|
I am adding up combo boxes to array of combobox and assigning combo boxes to them like this
ComboBox[] arrSize = new ComboBox[5];
arrSize[0] = cbSize_00;
arrSize[1] = cbSize_01;
arrSize[2] = cbSize_02;
arrSize[3] = cbSize_03;
arrSize[4] = cbSize_04;
I would like to know if there is a way to delegate the array as a whole instead of me delegating each combo box manually.
Please help. Thanks in advance.
|
|
|
|
|
You've posted this in the wrong forum - this question has nothing to do with databases.
What do you mean by "delegate the array"?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
If you had posted this in the correct forum - C# Discussion Boards - CodeProject[^] or even Quick Answers - CodeProject[^] then you would have probably had an answer much earlier. Please take more care in future.
You could do it this way
var arrSize = (from object c in Controls where c.GetType() == typeof(ComboBox) select c as ComboBox).ToArray();
|
|
|
|
|
Dear All,
We have below requirement kindly suggest the best solutions.
Requirement :
Extract specific data from the production database and need to load those data to test environment database.
For example, in production database has 100 tables, need to exact 40 tables with specific data ( Don't want to extract all data only few data based on input rule).
Kindly suggest best solution to avoid huge effort.
Thank you in advance.
Best Regards
|
|
|
|
|
Well, it would help to know what database you are using....
Don't you have any kind of database backup in place? Can't you just sue that to populate your test database? But anyway, most databases have admin tools that allow you to export data in one form or another... and failing that, you could always write your own script to create a bunch of SQL INSERT statements....
|
|
|
|
|
Is this MS SQL Server you are referring to? If so you can use the SSMS Wizard to set up a package to export the data - very easy to use and once the package is in place you can tweak it if you need to use it again... We had a similar set up on our Test database (so that we could change it without having to have update permissions on Production)
|
|
|
|
|
We are planning to use datastax cassandra as database for sturctured data. Also we are evaluating the cassandra whether we can use it to store files such as email attachments. while i am able to store the files into cassandra file system (cfs:/// or cfs-archive:///) through linux terminal commands. example: $DSE_HOME/dse hadoop fs -put /home/user/test.txt cfs:///test.txt
I am looking for a RESTful API which allows me to put the files to cassandra file sytem from a remote machine, it can be either linux or windows machine. I am looking for an API which is similar to WebHDFS, which is a RESTful API for hdfs file system. I know that CFS compatabile to HDFS and all APIs of HDFS works for CFS as per datastax'x document Redirect Notice[^]
It would be a great help, if anyone posts a link to CFS API or a working sample application code.
Thanks.
|
|
|
|
|
What is schema, dimention, fact?
|
|
|
|
|
This will explain it much better than I can -> clickety[^].
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Hi All,
I am writing a Dynamic Sql in a while loop which loop through all Tables and then columns in ever table, I have written a general Dynamic sql to read the columns but some tables do not have CreatedBy and ModifiedBy columns and when it is getting executed the Dynamic Sql is failing for some of the scenarios.
Can somebody suggest me how to write that Dynamic Sql to fit for both the scenarios. Any suggestion is helpful thanks in advance.
Here is my Dynamic sql
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
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Query the sys.columns table for @TbleName and add if the CreatedBy, ModifiedBy columns exist on that table add that in to the dynamic SQL
|
|
|
|
|
Hi All.
Firstly.I use Database link from my SQL to Oracle to get Data.In The Oracle the field sta_datetime is date type.
Now I use
convert(varchar(10),sta_datetime,111) as Sdate,
convert(time,sta_datetime) as S_Time,
To get data to Sdate field date type and S_Time field is time(0)type.
Now I show them in the gridview and then get them back when user lick on the row to show on another child gridview.
So many convert time.So it is so difficult to get them back.
|
|
|
|
|
Your question is also confusing. So much so that I'm not sure what your question is!
However, Don't convert date, datetime or time to varchar(anything) ... use the appropriate types. It is only in the final presentation of the data that you need to worry about how it looks
|
|
|
|
|
Original type of field in Oracle Database is date.Now I select this and insert to SQL server.Then show in gridview.
The question is
How to get exactly data from oracle database link?
And How to show exactly data to the user?
|
|
|
|
|
I'm moving my sql server db to oracle. But oracle seems not to support Date Name with date data type.
Looking for solution as changing name from Date to Vdate or bDate requires lot of changes in app code.
|
|
|
|
|
"Date" will probably be a reserved word which is why you get this issue.
Outside of changing the column name,which I strongly recommend doing(calling a column "date" is asking for trouble), which will involve quite a bit of work, the only workaround is to use whatever Oracle uses to delimit column names ([],"" etc)
My experience is that it is best to fix these sorts of issues early i.e. rename the column as the problems you currently experience will be magnified in a few months time when you have more that depends on a column called "date". The amount of work it will take you to fix this issue now will be far less than later.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Guy is spot on on all accounts, Date isn't just a reserved word in Oracle but also by ANSI, you have a list here.
As a delimiter in Oracle you use the ANSI standard "". But also here I agree with Guy, don't do it, it'll save you a lot of trouble later.
|
|
|
|
|
Another benefit of changing the column name will be the opportunity to give it a semantically meaningful name. Just "Date" could be any sort of date anyone might ever think of. Calling it what it actually is (like "CreatedDate", "PurchaseDate", etc.) will make much more sense.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Hello All,
I would like to convert reports in the format .rdlc into format .rdl using Visual Studio 2015.
What I did: I've created a Reporting project(existent under Visual C# templates) where I added two reports. Those reports come with the extension .rdlc. After creating the project I checked its properties and the type of project is windows application. Further I decided to use SQl Server Reporting Services to do my reports. I installed Data Tool preview for Visual Studio 2015 and created a Reporting Services project under Business Intelligence template.
Issue:
The reports I initially created using the Reporting project have the extension .rdlc and the ones created with Reporting Services .rdl. I imported the .rdlc ones into my Reporting Services project. However if I change the extension from .rdlc to .rdl i can design but not preview. When trying to preview, Visual Studios says "an error occurred during local report processing. error in the application. Object reference not set to an instance of an object".
I would not want to loose those two reports as the design took me one day to do.If anyone knows a solution that I could use to import/convert .rdlc reports into .rdl I would appreciate.
|
|
|
|
|
|