Database

BOOL CMyDoc::ImportPic(LPCTSTR filePath)
{
	CFile file;
	file.Open(filePath,CFile::modeRead);
	DWORD len=file.GetLength();
	BYTE *pBuf=new BYTE[len+1];
	file.ReadHuge(pBuf,len);
	
	VARIANT			varBLOB;
	SAFEARRAY		*psa;
	SAFEARRAYBOUND	rgsabound;
	
	_RecordsetPtr pRs;
	pRs.CreateInstance(__uuidof(Recordset));
	pRs->Open(_T("picdata"),m_pConn.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdTable);
	pRs->AddNew();
	pRs->PutCollect(_T("name"),_T("a"));
	rgsabound.lLbound=0;
	rgsabound.cElements=len;
	psa=SafeArrayCreate(VT_UI1,1,&rgsabound);
	for (LONG i=0;i<(LONG)len;++i)
		SafeArrayPutElement(psa,&i,pBuf++);
	varBLOB.vt=VT_ARRAY|VT_UI1;
	varBLOB.parray=psa;
	pRs->GetFields()->GetItem("pic0")->AppendChunk(varBLOB);
	pRs->Update();
	pRs->Close();
	pRs.Release();
	return TRUE;
}

UPDATE t SET num=num+1
  WHERE name='abc'
  AND age=20
  AND job='worker'
IF (@@ROWCOUNT = 0) BEGIN
  INSERT INTO t(id,name,age,job,num)
     VALUES(NEWID(),'abc',20,'worker',1)
END

CREATE UNIQUE INDEX t_idx1 ON t (name, age, job)

CREATE PROCEDURE dbo.Insert_t
@Name VARCHAR(40),
@Age SMALLINT,
@Job VARCHAR(40)
AS BEGIN
  SET NOCOUNT ON
  UPDATE t SET num = num+1
    WHERE name = @Name
    AND age = @Age
    AND job = @Job
  IF (@@ROWCOUNT = 0) BEGIN
    INSERT INTO t (id, name, age, job, num)
      VALUES (NEWID(), @Name, @Age, @Job, 1)
  END
  RETURN 0
END