Hi,
Could you please suggest me steps for ODBC conenction from Progress 11.6 to SQL Server 2008 r2.
Thanks.
OpenEdge DataServer for MS SQL: This makes the MS SQL database look like an OpenEdge database to your 4GL code. All you have to do is "FOR EACH <sql table name> ...". This is a separate product that you have to purchase.
For simple stuff, there are some new ODBC classes in the language that you can use to make a direct ODBC connection from your 4GL code to an ODBC datasource. This is a little more complex and inflexible but it's free.
Third option: you can use MS SQL Integration Services as the client connecting to the OpenEdge DB via ODBC. This is if you need to push and pull data between the two databases and don't require access to MSSQL from the user.
There is a whitepaper on the SQL server dataserver,
Hi Paul Koufalis,
Thank you for your time and help....
Could you please suggest me detail guide about second approach that you suggest me ie " there are some new ODBC classes in the language that you can use to make a direct ODBC connection " .
What I want to do is :
I have sports2000 db in progress and going to create same schema in MS SQL Server 2008.
Now on click of a button called "Load data" load all the data from Progress to MS SQL Server.
Can I do this using ODBC Connection That you guide me.
Thanks a lot.
John.
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:DEFAULT-BUFFER-HANDLE).
hTT:DEFAULT-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").
oConnection:DatabaseName = 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" */
oBulkCopy:DestinationTableName = '"' +
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.DataType:Decimal(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.DataType:DateTime.
WHEN "DateTime" THEN
SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:DateTime.
WHEN "DateTime-TZ" THEN
SqlDataType = Microsoft.SqlServer.Management.Smo.DataType:DateTime.
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 hField:DATA-TYPE, OUTPUT oDataType).
oColumn:DataType = oDataType.
SqlTable:Columns:Add(oColumn).
END.
SqlTable:Create().
END.
END PROCEDURE.