SET DELETED off
SET MULTILOCKS ON
SET TEXTMERGE ON
LOCAL lcStringConn, lnHandle, lcServer, lcDatabase, lcUser, lcPassWord, lcString, lcVar

* устанавливаем соединение с сервером
lcServer = "COMP1\MYSQLSERVER"
lcDatabase = "master"
lcUser = "sa"
lcPassWord = "555"
text to lcStringConn noshow pretext  15  
Driver=SQL Server; 
SERVER=<<lcServer>>; 
DATABASE=<<lcDatabase>>; 
uid=<<lcUser>>; 
pwd=<<lcPassWord>>; 
endtext 
lnHandle=SQLSTRINGCONNECT(lcStringConn)
if lnHandle<= 0  
  messagebox("Соединение не установлено!" + CHR( 10 ) + "Строка соединения:" + CHR( 10 ) + lcStringConn) 
  return 
endif 

* проверка наличия базы данных
lcString = [SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME = 'TESTXXX']
res = SQL_EXEC("Запрос не выполнился:  " + lcString, lnHandle, lcString, "curs")
if res<= 0  
  SQLDISCONNECT(lnHandle)
  return
endif

*================= создание базы данных
IF EOF("curs")
text to lcString noshow pretext  15  
CREATE DATABASE TESTXXX ON (NAME = 'TESTXXX_db', 
	FILENAME = 'C:\TEST_XXX\TESTXXX.MDF')
endtext 
res = SQL_EXEC("Запрос на создание БД не выполнился!", lnHandle, lcString, "")
if res<= 0  
  SQLDISCONNECT(lnHandle)
  return
endif
text to lcString noshow pretext  15  
use TESTXXX
create table tMaster
(  tMaster_id int identity not null primary key,
   ndok int not null, 
   info varchar( 30 ) )
create table tDetail
(  tDetail_id int identity not null primary key,
   tMaster_id int not null foreign key references tMaster (tMaster_id),
   info1 varchar( 30 ),
   info2 varchar( 30 ) )
endtext 
res = SQL_EXEC("Запрос на создание таблиц БД не выполнился!", lnHandle, lcString, "")
if res<= 0  
  SQLDISCONNECT(lnHandle)
  return
endif
ENDIF 

*================= ввод первичных данных
text to lcString noshow pretext  15 
DECLARE @s int
use TESTXXX
DELETE FROM tDetail
DELETE FROM tMaster
INSERT into tMaster (ndok, info) values ( 1 , 'dokument N1')
SET @s = @@identity
INSERT into tDetail (tMaster_id, info1) values (@s, 'dok N1 - stroka N1')
INSERT into tDetail (tMaster_id, info1) values (@s, 'dok N1 - stroka N2')
INSERT into tDetail (tMaster_id, info1) values (@s, 'dok N1 - stroka N3')
INSERT into tMaster (ndok, info) values ( 2 , 'dokument N2')
SET @s = @@identity
INSERT into tDetail (tMaster_id, info1, info2) values (@s, 'dok N2 - stroka N1', 'info2')
INSERT into tDetail (tMaster_id, info1, info2) values (@s, 'dok N2 - stroka N2', 'info2')
INSERT into tDetail (tMaster_id, info1, info2) values (@s, 'dok N2 - stroka N3', 'info2')
INSERT into tMaster (ndok, info) values ( 3 , 'dokument N3')
SET @s = @@identity
INSERT into tDetail (tMaster_id, info1) values (@s, 'dok N3 - stroka N1')
INSERT into tDetail (tMaster_id, info1) values (@s, 'dok N3 - stroka N2')
INSERT into tDetail (tMaster_id, info1) values (@s, 'dok N3 - stroka N3')
endtext
res = SQL_EXEC("Запрос на ввод первичных данных в таблицы БД не выполнился!", lnHandle, lcString, "")
if res<= 0  
  SQLDISCONNECT(lnHandle)
  return
endif

*================= чтение документа
text to lcString noshow pretext  15 
DECLARE @mid int
use TESTXXX
SELECT * FROM tMaster WHERE ndok =  2 
SET @mid = (SELECT tMaster_id FROM tMaster WHERE ndok =  2 )
SELECT * FROM tDetail WHERE tMaster_id = @mid
endtext
res = SQL_EXEC("Запрос на чтение документа не выполнился!", lnHandle, lcString, "curs")
if res<= 0  
  SQLDISCONNECT(lnHandle)
  return
endif

*================= редактирование документа
SELECT curs
= CURSORSETPROP('Buffering',  5 )
browse
SELECT curs1
= CURSORSETPROP('Buffering',  5 )
browse
GO  2 
delete
GO  3 
REPLACE info1 WITH RTRIM(info1) + ">>update"
APPEND blank
REPLACE info1 WITH 'dok N2 - stroka XX>>append'
browse

**************** формирование команд для редактирования документа на сервере *****************
LOCAL lltMaster_Changed, lltDetail_Changed
SELECT curs
lltMaster_Changed = TableChanged()
SELECT curs1
lltDetail_Changed = TableChanged()
IF lltMaster_Changed OR lltDetail_Changed
text to lcString noshow pretext  7 
use TESTXXX
BEGIN TRANSACTION
ENDTEXT
SELECT curs
lndok_id = tMaster_id
IF lltMaster_Changed
text to lcVar noshow pretext  15 
UPDATE tDetail
SET info1 = '<<info1>>'
WHERE tMaster_id = <<lndok_id>>
ENDTEXT
lcString = lcString + CHR( 10 ) + lcVar
ENDIF
IF lltDetail_Changed
SELECT curs1
GO top
SCAN
DO case
case DELETED() AND !EMPTY(tDetail_id)
lcVar = "DELETE FROM tDetail WHERE tDetail_id = " + ALLTRIM(STR(tDetail_id))
lcString = lcString + CHR( 10 ) + lcVar
case EMPTY(tDetail_id)
text to lcVar noshow pretext  15 
INSERT into tDetail (tMaster_id, info1, info2) values (<<lndok_id>>, '<<info1>>', '<<info2>>')
ENDTEXT
lcString = lcString + CHR( 10 ) + lcVar
case RecordChanged()
text to lcVar noshow pretext  15 
UPDATE tDetail
SET info1 = '<<info1>>', info2 = '<<info2>>'
WHERE tDetail_id = <<tDetail_id>>
ENDTEXT
lcString = lcString + CHR( 10 ) + lcVar
ENDCASE
ENDSCAN
lcString = lcString + CHR( 10 ) + "COMMIT TRANSACTION"
messagebox("На сервер посылается набор строк:"+CHR( 10 )+lcString)
res = SQL_EXEC("Запрос на редактирование не выполнился!", lnHandle, lcString, "curs")
if res<= 0  
  SQLDISCONNECT(lnHandle)
  return
endif
ENDIF
ENDIF
**********************************************************************************************

SQLDISCONNECT(lnHandle)
RETURN

PROCEDURE SQL_EXEC(pmessage, pHandle, pString, pCurs)
LOCAL res
res = SQLEXEC(pHandle, pString, pCurs)
if res<= 0  
  AERROR(a)
  messagebox(pmessage+CHR( 10 )+"Ошибка номер"+STR(a( 1 ))+CHR( 10 )+a( 2 ), 0 ," ")
endif 
return res
ENDPROC
PROCEDURE TableChanged
IF ISNULL(GETFLDSTATE(- 1 ))
  RETURN .F.
ELSE
  RETURN ("2" $ GETFLDSTATE(- 1 ) OR ;
          "4" $ GETFLDSTATE(- 1 ))
ENDIF
ENDPROC
PROCEDURE RecordChanged 
LOCAL i , m_Field 
FOR i= 1  TO FCOUNT()
  m_Field = FIELD(i) 
  IF !(EVALUATE(m_Field) == OldVal(m_Field)) 
    RETURN .T. 
  ENDIF 
ENDFOR 
RETURN .F. 
ENDPROC

case DELETED() AND !EMPTY(tDetail_id)
lcVar = "DELETE FROM tDetail WHERE tDetail_id = " + ALLTRIM(STR(tDetail_id))
lcString = lcString + CHR( 10 ) + lcVar
case EMPTY(tDetail_id)
text to lcVar noshow pretext  15 
INSERT into tDetail (tMaster_id, info1, info2) values (<<lndok_id>>, '<<info1>>', '<<info2>>')
ENDTEXT
lcString = lcString + CHR( 10 ) + lcVar
case RecordChanged()
text to lcVar noshow pretext  15 
UPDATE tDetail
SET info1 = '<<info1>>', info2 = '<<info2>>'
WHERE tDetail_id = <<tDetail_id>>
ENDTEXT
lcString = lcString + CHR( 10 ) + lcVar
ENDCASE 

IF DELETED()      && запись удалена
  IF !EMPTY(tDetail_id)
    lcVar = "DELETE FROM tDetail WHERE tDetail_id = " + ALLTRIM(STR(tDetail_id))
    lcString = lcString + CHR( 10 ) + lcVar
  ENDIF
ELSE
  IF EMPTY(tDetail_id)      && запись добавлена
    text to lcVar noshow pretext  15 
    INSERT into tDetail (tMaster_id, info1, info2) values (<<lndok_id>>, '<<info1>>', '<<info2>>')
    ENDTEXT
    lcString = lcString + CHR( 10 ) + lcVar
  ELSE
    IF RecordChanged()      && запись изменена
      text to lcVar noshow pretext  15 
      UPDATE tDetail
      SET info1 = '<<info1>>', info2 = '<<info2>>'
      WHERE tDetail_id = <<tDetail_id>>
      ENDTEXT
      lcString = lcString + CHR( 10 ) + lcVar
    ENDIF
  ENDIF
ENDIF