T
tbergman
Guest
I presented a demo that does almost exactly what you're asking for at the recent Pug Challenge conference. It's just a demo, it doesn't deal with indexes, field lengths etc. but it should get you started. It uses .Net, and has dependencies on the Microsoft SQL Management objects. If you have SQL Server installed on your PC, you probably already have these, otherwise you'll need to install them. Since the code has not yet been loaded to the Pug Challenge site, I've pasted it below. Tom /*------------------------------------------------------------------------ File : SqlDbDemo.p Purpose : Syntax : Description : Author(s) : Created : Fri Mar 11 07:13:14 EST 2016 Notes : ----------------------------------------------------------------------*/ BLOCK-LEVEL ON ERROR UNDO, THROW. DEFINE VARIABLE hDataSet AS HANDLE. DEFINE VARIABLE oDataBase AS Microsoft.SqlServer.Management.Smo.Database. DEFINE VARIABLE oServer AS Microsoft.SqlServer.Management.Smo.Server. DEFINE VARIABLE DotNetDS AS System.Data.DataSet. DEFINE VARIABLE oConnection AS Microsoft.SqlServer.Management.Common.ServerConnection. ETIME (TRUE). oServer = NEW Microsoft.SqlServer.Management.Smo.Server ("localhost,1433"). oDatabase = NEW Microsoft.SqlServer.Management.Smo.Database (oServer, "PugSports8"). oDatabase:Create(). RUN FillProDataSet (OUTPUT hDataset). RUN MakeDbSchema(oDataBase, hDataset). RUN FillDotNetDataset(hDataset, OUTPUT DotNetDs). RUN fillSqlDb(oServer, DotNetDs, hDataset). MESSAGE hDataset:NUM-BUFFERS "Buffers" SKIP ETIME "Milliseconds" VIEW-AS ALERT-BOX. PROCEDURE FillDotNetDataSet: DEFINE INPUT PARAMETER hDataSet AS HANDLE. DEFINE OUTPUT PARAMETER DotNetDS AS System.Data.DataSet. DEFINE VARIABLE xmlData AS LONGCHAR. DotNetDs = NEW System.Data.DataSet(). hDataset:WRITE-XML("longchar", xmlData, TRUE, ?, ?, TRUE ). DotNetDS:ReadXml(NEW System.IO.StringReader(xmlData)). END PROCEDURE. PROCEDURE FillProDataSet: DEFINE OUTPUT PARAMETER hDataSet AS HANDLE. DEFINE VARIABLE hDataSource AS HANDLE. DEFINE VARIABLE Htt AS HANDLE. DEFINE VARIABLE hCust AS HANDLE. DEFINE VARIABLE htableBuff AS HANDLE. CREATE DATASET hDataSet. FOR EACH _file WHERE _file._file-Number GT 0 AND _file._file-Number LE 32000, _index WHERE RECID(_index) = _file._prime-index, FIRST _Index-field WHERE _index-field._Index-recid = recid(_index), _field WHERE RECID(_field) = _index-field._field-recid: CREATE BUFFER htableBuff FOR TABLE _file._file-name. CREATE DATA-SOURCE hDataSource. CREATE TEMP-TABLE htt. hTT:CREATE-LIKE(hTableBuff). htt:TEMP-TABLE-PREPARE(hTableBuff:NAME). hDataSource:ADD-SOURCE-BUFFER(hTableBuff, _field._field-name). hDataSet:ADD-BUFFER(hTT
EFAULT-BUFFER-HANDLE). hTTEFAULT-BUFFER-HANDLE:Attach-Data-Source(hDataSource). END. hDataSet:FILL(). END PROCEDURE. PROCEDURE FillSqlDb: DEFINE INPUT PARAMETER oServer AS Microsoft.SqlServer.Management.Smo.Server. DEFINE INPUT PARAMETER DotNetDs AS System.Data.Dataset. DEFINE INPUT PARAMETER PDataset AS HANDLE. DEFINE VARIABLE oBulkCopy AS System.Data.SqlClient.SqlBulkCopy. DEFINE VARIABLE i AS INTEGER. oConnection = NEW Microsoft.SqlServer.Management.Common.ServerConnection ("localhost,1433"). oConnectionatabaseName = oDatabase:Name. oBulkCopy = NEW System.Data.SqlClient.SqlBulkCopy (oConnection:SqlConnectionObject). oConnection:SqlConnectionObject:Open(). DO i = 1 TO pDataset:NUM-BUFFERS: /* Bulkcopy is sensitive to sql keywords so table names may need to be quoted. For the Sports database, it choked on the table name "Order" */ oBulkCopyestinationTableName = '"' + pDataset:GET-BUFFER-HANDLE(i):Name + '"'. oBulkCopy:WriteToServer( DotNetDs:Tables[pDataset:GET-BUFFER-HANDLE(i):Name]). END. oConnection:SqlConnectionObject:Close(). END PROCEDURE. PROCEDURE GetDataType: DEFINE INPUT PARAMETER ProDataType AS CHARACTER. DEFINE OUTPUT PARAMETER SqlDataType AS Microsoft.SqlServer.Management.Smo.DataType. CASE ProDataType: WHEN "character" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:NVarCharMax. WHEN "clob" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:NVarCharMax. WHEN "Integer" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:Int. WHEN "Int64" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:BigInt. WHEN "Decimal" THEN /* For some reason, precision and scale are reversed from what is documented and from what ends up in the DB. This setting will results in a SQL database column of Decimal(38,10) */ SqlDataType = Microsoft.SqlServer.Management.Smo.DataTypeecimal(10,38). WHEN "Logical" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:Bit. /* Needs to be Datetime or Excel won't recognize as date but will import the data as character. */ WHEN "Date" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataTypeateTime. WHEN "DateTime" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataTypeateTime. WHEN "DateTime-TZ" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataTypeateTime. WHEN "blob" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:VarBinaryMax. END CASE. END PROCEDURE. /* This doesn't attempt to do anything with indexes or to set the proper size of fields. */ PROCEDURE MakeDbSchema: DEFINE INPUT PARAMETER oDataBase AS Microsoft.SqlServer.Management.Smo.Database. DEFINE INPUT PARAMETER hDataset AS HANDLE. DEFINE VARIABLE SqlTable AS Microsoft.SqlServer.Management.Smo.Table. DEFINE VARIABLE oDataType AS Microsoft.SqlServer.Management.Smo.DataType. DEFINE VARIABLE hBuff AS HANDLE. DEFINE VARIABLE hField AS HANDLE. DEFINE VARIABLE i AS INTEGER. DEFINE VARIABLE TableCount AS INTEGER. DEFINE VARIABLE oColumn AS Microsoft.SqlServer.Management.Smo.Column. DO TableCount = 1 TO hDataSet:NUM-BUFFERS: hBuff = hDataset:GET-BUFFER-HANDLE(TableCount). SqlTable = NEW Microsoft.SqlServer.Management.Smo.Table(oDatabase, hBuff:NAME). DO i = 1 TO hBuff:NUM-FIELDS: hField = hBuff:BUFFER-FIELD(i). /* The SQL DB */ oColumn = NEW Microsoft.SqlServer.Management.Smo.Column(SqlTable, hField:NAME). RUN GetDataType(INPUT hFieldATA-TYPE, OUTPUT oDataType). oColumnataType = oDataType. SqlTable:Columns:Add(oColumn). END. SqlTable:Create(). END. END PROCEDURE.
Continue reading...
EFAULT-BUFFER-HANDLE). hTTEFAULT-BUFFER-HANDLE:Attach-Data-Source(hDataSource). END. hDataSet:FILL(). END PROCEDURE. PROCEDURE FillSqlDb: DEFINE INPUT PARAMETER oServer AS Microsoft.SqlServer.Management.Smo.Server. DEFINE INPUT PARAMETER DotNetDs AS System.Data.Dataset. DEFINE INPUT PARAMETER PDataset AS HANDLE. DEFINE VARIABLE oBulkCopy AS System.Data.SqlClient.SqlBulkCopy. DEFINE VARIABLE i AS INTEGER. oConnection = NEW Microsoft.SqlServer.Management.Common.ServerConnection ("localhost,1433"). oConnectionatabaseName = oDatabase:Name. oBulkCopy = NEW System.Data.SqlClient.SqlBulkCopy (oConnection:SqlConnectionObject). oConnection:SqlConnectionObject:Open(). DO i = 1 TO pDataset:NUM-BUFFERS: /* Bulkcopy is sensitive to sql keywords so table names may need to be quoted. For the Sports database, it choked on the table name "Order" */ oBulkCopyestinationTableName = '"' + pDataset:GET-BUFFER-HANDLE(i):Name + '"'. oBulkCopy:WriteToServer( DotNetDs:Tables[pDataset:GET-BUFFER-HANDLE(i):Name]). END. oConnection:SqlConnectionObject:Close(). END PROCEDURE. PROCEDURE GetDataType: DEFINE INPUT PARAMETER ProDataType AS CHARACTER. DEFINE OUTPUT PARAMETER SqlDataType AS Microsoft.SqlServer.Management.Smo.DataType. CASE ProDataType: WHEN "character" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:NVarCharMax. WHEN "clob" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:NVarCharMax. WHEN "Integer" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:Int. WHEN "Int64" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:BigInt. WHEN "Decimal" THEN /* For some reason, precision and scale are reversed from what is documented and from what ends up in the DB. This setting will results in a SQL database column of Decimal(38,10) */ SqlDataType = Microsoft.SqlServer.Management.Smo.DataTypeecimal(10,38). WHEN "Logical" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:Bit. /* Needs to be Datetime or Excel won't recognize as date but will import the data as character. */ WHEN "Date" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataTypeateTime. WHEN "DateTime" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataTypeateTime. WHEN "DateTime-TZ" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataTypeateTime. WHEN "blob" THEN SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:VarBinaryMax. END CASE. END PROCEDURE. /* This doesn't attempt to do anything with indexes or to set the proper size of fields. */ PROCEDURE MakeDbSchema: DEFINE INPUT PARAMETER oDataBase AS Microsoft.SqlServer.Management.Smo.Database. DEFINE INPUT PARAMETER hDataset AS HANDLE. DEFINE VARIABLE SqlTable AS Microsoft.SqlServer.Management.Smo.Table. DEFINE VARIABLE oDataType AS Microsoft.SqlServer.Management.Smo.DataType. DEFINE VARIABLE hBuff AS HANDLE. DEFINE VARIABLE hField AS HANDLE. DEFINE VARIABLE i AS INTEGER. DEFINE VARIABLE TableCount AS INTEGER. DEFINE VARIABLE oColumn AS Microsoft.SqlServer.Management.Smo.Column. DO TableCount = 1 TO hDataSet:NUM-BUFFERS: hBuff = hDataset:GET-BUFFER-HANDLE(TableCount). SqlTable = NEW Microsoft.SqlServer.Management.Smo.Table(oDatabase, hBuff:NAME). DO i = 1 TO hBuff:NUM-FIELDS: hField = hBuff:BUFFER-FIELD(i). /* The SQL DB */ oColumn = NEW Microsoft.SqlServer.Management.Smo.Column(SqlTable, hField:NAME). RUN GetDataType(INPUT hFieldATA-TYPE, OUTPUT oDataType). oColumnataType = oDataType. SqlTable:Columns:Add(oColumn). END. SqlTable:Create(). END. END PROCEDURE.Continue reading...