1. Scott
  2. Valentina Database ADK
  3. Wednesday, January 14 2015, 10:16 PM
  4.  Subscribe via email
I am developing a DB using the Binary Link method. In VStudio its very easy to establish the links. But I need to insert data from my web app thru PHP to multiple tables using the Binary Link method and cannot figure it out.

I searched the Valentina website but I couldn't find an answer. Can you point me in the right direction?

I just want to insert numerous fields into several related tables that are using Binary Links.

I am beginning to enjoy Valentine DB, the more I learn how to use it. But the user documentation isn't that user friendly for someone like me: Not a beginner to SQL, but not an expert either.

Please guide me on how to insert records using the Binary Link method.

Thanks,

Scott

P.S. If anyone in the community is a VDB expert, please msg me. I am willing to pay for your time to teach me this DB API.
Comment
There are no comments made yet.
Ruslan Zasukhin Accepted Answer
About how to INSERT records linked by different types of links.

1) FK-link and ObjectPtr link.

They are similar because here we have

  • 2 Tables
  • Second table have FIELD, which keep value of KEY from the first Table
  • These links can establish 1:1 and 1:M links


To establish link between TWO records we just place VALUE of KEY into INSERT command for the second table.

This assumes that we have made INSERT into the first table BEFORE, and we have KEY value. To get the KEY value, the last_rec_id() function can be used for Valentina. Other DBs have similar function for PK/FK.

INSERT INTO T1( a1, a2, a3, ... ) VALUES ( va1, va2, va3, ... )

INSERT INTO T2( b1, b2, b3, T1_PTR ) VALUES ( vb1, vb2, vb3, last_rec_id() )


2) For BinaryLinks we DO NOT have any special field. BinaryLink is separate entity in database. BinaryLink can establish 1:1, 1:M and M:M links.

So we can compare BinaryLink of Valentina to additional third table in Relational model, to establish M:M links.
T12 ( T1_PTR, T2_PTR )

In relational model with ForeignKeys in such third table, you will need 3 INSERTs

INSERT INTO T1 ...
INSERT INTO T2 ...
INSERT INTO T12( T1_PTR, T2_PTR ) VALUES ( key1, key2 )

In similar way you will work with BinaryLink

INSERT INTO T1 ...
INSERT INTO T2 ...
LINK RECORD(key1) OF tbl1 WITH RECORD(key2) OF tbl2;


3) Important to notice, that in real life, you are not forced to have such 3 INSERTs one by one.

Yes this can happens, but on the other hand exists scenarios, when you have added today record into T1, tomorrow record into T2 and only week later you will need LINK them together.

So it is up to you how you design your code in your project.
* May be as Stored Procedures with few INSERTs following one another,
* may be separate functions that do 3 independent INSERTs
* may be else ...
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Ruslan Zasukhin Accepted Answer
Hi Scott,

The first answer:
* Good entry point about Links in Valentina Database model is here:
http://valentina-db.com/dokuwiki/doku.php?id=valentina:vcomponents:vkernel:vlink:vlink

It contains URLs to other pages important to read about links:
RecId, ObjectPtr, BinaryLink
API, and SQL commands to work with links.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
Scott Accepted Answer
As far as I can tell - the only way to do this efficiently is through a Stored Procedure.

Something like this:

CREATE OR REPLACE PROCEDURE "ProcedureName"( IN (variable) (type), etc....)
BEGIN
INSERT INTO tbl1 VALUES ( whatever u set as parameters in CREATE statement);
DECLARE firstRecID, secondRecID INTEGER;
SET firstRecID = last_recid_of_table('your table1');
INSERT INTO tbl2 VALUES (whatever you set as parameters in CREATE statement);
SET secondRecID = last_recid_of_table('tbl2');
LINK RECORD(firstRecID) OF tbl1 WITH RECORD(secondRecID) OF tbl2;
END

And so forth, and so forth. Add as many tables as you need - setting the parameters, and declaring the IDs of the respective tables.

Then in your SQL statement (using PHP in my instance) use:

$query = "CALL ProcedureName('parameter 1', and so forth)";

This inserts the info into the first table, pulls out the RecID, then sets a variable as this RecID and begins to insert data into related tables using this RecID as its Binary Link info.

Works like a champ for me. Very fast and very efficient. But I am not well versed in Valentina DB so if anyone has a better way to do this, please reply.

As a side note for Ruslan, the docs show the Binary Link statement to be:

LINK RECORD(1) of tbl1 TO RECORD(1) OF tbl2.


This errors out.

I had replace TO with WITH to make it work.

LINK RECORD(1) TO tbl1 WITH RECORD(1) OF tbl2.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 3
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories