Colon in M/D SQL - nube question

JamesBrown · Post by **JamesBrown** » Fri 09 Dec 2011 16:59

What does the colon do in this (working) SQL statement?
SELECT * FROM Hits WHERE Scan_ID = :Scan_ID

I'm using Delphi XE

AndreyZ · Post by **AndreyZ** » Mon 12 Dec 2011 08:54

Hello,

Colon means that Scan_ID is a parameter. In master/detail relationship the value for this parameter is taken from the master table. For more information about using master/detail relationship in MyDAC, please read the "Working with Master/Detail Relationships" topic of the MyDAC documentation.

JamesBrown · Post by **JamesBrown** » Mon 12 Dec 2011 15:50

Thanks for the reply.
I had found the Working with M/D in the help and managed to construct a working example fine. The help example uses the colon but doesn't say how it works.

I guess what I didn't find was the SQL definition of the colon. Anyone know how I can get at that?

Thanks....... Jim

AndreyZ · Post by **AndreyZ** » Tue 13 Dec 2011 10:42

The value for the parameter is taken from a field of the master table that has the same name as the parameter does. For example, you have the following master table:

Code: Select all

CREATE TABLE DEPT(
  DEPTNO INT(11) NOT NULL AUTO_INCREMENT,
  DNAME VARCHAR(14) DEFAULT NULL,
  LOC VARCHAR(13) DEFAULT NULL,
  PRIMARY KEY (DEPTNO)
)
ENGINE = MYISAM
AUTO_INCREMENT = 1;

INSERT INTO DEPT(DNAME, LOC) VALUES('ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT(DNAME, LOC) VALUES('RESEARCH', 'DALLAS');

, and the following detail table:

Code: Select all

CREATE TABLE EMP(
  EMPNO INT(11) NOT NULL AUTO_INCREMENT,
  ENAME VARCHAR(10) DEFAULT NULL,
  JOB VARCHAR(9) DEFAULT NULL,
  MGR INT(11) DEFAULT NULL,
  HIREDATE DATETIME DEFAULT NULL,
  SAL FLOAT DEFAULT NULL,
  COMM FLOAT DEFAULT NULL,
  DEPTNO INT(11) DEFAULT NULL,
  PRIMARY KEY (EMPNO)
)
ENGINE = MYISAM
AUTO_INCREMENT = 1;

, and you are using the following code:

Code: Select all

procedure TMainForm.BitBtn1Click(Sender: TObject);
begin
  MyQuery1.SQL.Text := 'select * from dept';
  MyDataSource1.DataSet := MyQuery1;
  MyQuery2.SQL.Text := 'select * from emp where deptno=:deptno';
  MyDataSource2.DataSet := MyQuery2;
  MyQuery2.MasterSource := MyDataSource1;
  MyQuery1.Open; // here the 'select * from dept' SQL statement is executed
  MyQuery2.Open; // here the 'select * from emp where deptno=:deptno' SQL statement is executed. The value (for example, 1) for the deptno parameter is taken from the deptno field of the master table (MyQuery1).
  MyQuery1.Next; // here the master table goes to the next record and the 'select * from emp where deptno=:deptno' SQL statement is executed for the detail table again, but it uses the deptno field value (for example, 2) from the current record of the master table (MyQuery1).
end;

JamesBrown · Post by **JamesBrown** » Fri 23 Dec 2011 16:12

Thanks Much.
I live by examples....