Text...EndText ile SQLe kayit gonderirken numeric alan sorunu

1) ASLA ve ASLA boyle hardcoded yapma, parametre kullan.

Visual Fox Pro

TEXT TO m.lcSQLInsert TEXTMERGE NOSHOW PRETEXT 15 

INSERT INTO <<m.LcDatabase>>..intsales

( 

  firmid,tarih,depokodu,

  ckodu,eskibakiye,skodu,

  miktar,fiyat,tahsilat,

  bakiye,ozelkod,aciklama,

  cadi,sadi,

  lat,lon,myguid

)

values

( 

  ?CrsBackupRecords.firmid, ?CrsBackupRecords.tarih, ?CrsBackupRecords.depokodu, 

  ?CrsBackupRecords.ckodu, ?CrsBackupRecords.eskibakiye, ?CrsBackupRecords.skodu, 

  ?CrsBackupRecords.miktar, ?CrsBackupRecords.fiyat, ?CrsBackupRecords.tahsilat, 

  ?CrsBackupRecords.bakiye, ?CrsBackupRecords.ozelkod, ?CrsBackupRecords.aciklama, 

  ?CrsBackupRecords.cadi, ?CrsBackupRecords.sadi, 

  ?CrsBackupRecords.lat, ?CrsBackupRecords.lon, ?CrsBackupRecords.myguid

) 

EndText

 

Local handle

handle = SQLStringConnect('Driver={SQL Server Native Client 11.0};Server=.\SQLExpress;Database=MyDb;Trusted_Connection=yes')

SQLPrepare(m.handle, m.lcSQLInsert)

 

Select crsBackupRecords

Scan

  SQLExec(m.handle)

EndScan

 

SQLDisconnect(0)

2) Dogrudan XML'in kendisini SQL'e gonderebilirsin.

Visual Fox Pro

Local lcSQL, lcXML, lcNodeName, lnHandle

 

TEXT to lcSQL textmerge noshow

DECLARE @hDoc int

exec sp_xml_preparedocument @hDoc OUTPUT, ?m.lcXML

 

Insert into <<m.LcDatabase>>..intsales

  (

    firmid int,tarih datetime,depokodu,ckodu,eskibakiye,skodu,

    miktar,fiyat,tahsilat,bakiye,ozelkod,aciklama,cadi,sadi,lat,lon,myguid

   )

Select firmid int,tarih datetime,depokodu,ckodu,eskibakiye,skodu,

       miktar,fiyat,tahsilat,bakiye,ozelkod,aciklama,cadi,sadi,lat,lon,myguid

 FROM OPENXML(@hDoc, ?m.lcNodename, 1)

 WITH (

   firmid varchar(50), tarih datetime,  depokodu varchar(50),

   ckodu varchar(50), eskibakiye Numeric(16,2), skodu varchar(50),

   miktar Numeric(16,2), fiyat Numeric(12,6), tahsilat Numeric(16,2),

   bakiye Numeric(16,2), ozelkod varchar(50), aciklama varchar(4000),

   cadi varchar(50), sadi varchar(50), 

   lat Numeric(9,6), lon Numeric(9,6), myguid varchar(50)

)

 

EXEC sp_xml_removedocument @hDoc

ENDTEXT

 

lnHandle = Sqlstringconnect('driver={SQL Server Native Client 10.0};server=.\SQLExpress;Trusted_Connection=yes;')

 

lcNodeName = '/VFPData/backuprecords' && VFP default naming in XML

lcXML = FileToStr(m.xmlfile)

 

SQLExec(m.lnHandle, m.lcSQL)

 

SQLDisconnect(0)

3) Tabii en kolay ve hizlisi sbchelper ile SQL bulk copy