使用ODBC和SQL连接到使用Cobol的Quick Books数据库
问题描述：
我们正在尝试打开与QB DB的连接并从中获取任何数据。我们设法用C＃实现它，但是转化为CoBOL（一种对我们来说是陌生的语言）证明非常困难。这里是C＃代码：使用ODBC和SQL连接到使用Cobol的Quick Books数据库
class Program {
static void Main(string[] args)
{
var odbcConnection = new OdbcConnection("DSN=QuickBooks Data;DFQ=C:\\Users\\Public\\Documents\\Intuit\\QuickBooks\\Company Files\\" +
"ourQuickBooksFile.qbw;OLE DB Services=-2;OpenMode=S");
odbcConnection.Open();
var odbcDataAdapter = new OdbcDataAdapter("SELECT ListID, FullName, CompanyName FROM Customer", odbcConnection);
var dataSet = new DataSet();
odbcDataAdapter.Fill(dataSet);
DataRowCollection dataRowCollection = dataSet.Tables[0].Rows;
foreach (DataRow dataRow in dataRowCollection)
{
Console.WriteLine(dataRow["ListID"] + " " + dataRow["FullName"] + " " + dataRow["CompanyName"]);
}
Console.ReadLine();
odbcConnection.Close();
}
}
我们对其他方法开放，但COBOL部分是必不可少的。
我们使用Windows 10和GnuCOBOL。
答
您没有提及您的Cobol所在的编译器/操作系统。如果您使用的是IBM的Enterprise Cobol，那么您可以将您的C＃代码（这实际上只是Java的一对一翻译）并将其扩展到Cobol程序中。或者因为你显然是一个.NET商店，你可以使用Cobol的.NET风格，并扩展你上面的C＃代码。
但没有关于您的环境的更多细节，很难给您一个具体的答案。
答
而不是只发布链接（底部，你需要，以掌握编译选项），这里是GnuCOBOL FAQ有关使用esqlOC预处理器访问ODBC的一些框架代码通过Sergey Kashyrin。
IDENTIFICATION DIVISION.
PROGRAM-ID. esqlOCGetStart1.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC.
01 HOSTVARS.
05 BUFFER PIC X(1024).
05 hVarD PIC S9(5)V99.
05 hVarC PIC X(50).
05 hVarN PIC 9(12).
EXEC SQL
END DECLARE SECTION
END-EXEC.
PROCEDURE DIVISION.
MAIN SECTION.
*-----------------------------------------------------------------*
* CONNECT TO THE DATABASE
* also possible with DSN: 'youruser/[email protected]_DSN'
*-----------------------------------------------------------------*
STRING 'DRIVER={MySQL ODBC 5.2w Driver};'
'SERVER=localhost;'
'PORT=3306;'
'DATABASE=test;'
'USER=youruser;'
'PASSWORD=yourpasswd;'
* example for DB specific ODBC parameter:
* no compressed MySQL connection (would be the DEFAULT anyway)
'COMRESSED_PROTO=0;'
INTO BUFFER.
EXEC SQL
CONNECT TO :BUFFER
END-EXEC.
PERFORM SQLSTATE-CHECK.
*-----------------------------------------------------------------*
* CREATE TABLEs
*-----------------------------------------------------------------*
* TESTPERSON
MOVE SPACES TO BUFFER.
STRING
'CREATE TABLE TESTPERSON('
'ID DECIMAL(12,0), '
'NAME CHAR(50) NOT NULL, '
'PRIMARY KEY (ID))'
INTO BUFFER.
EXEC SQL
EXECUTE IMMEDIATE :BUFFER
END-EXEC
IF SQLSTATE='42S01'
DISPLAY ' Table TESTPERSON already exists.'
ELSE
PERFORM SQLSTATE-CHECK
DISPLAY ' created Table TESTPERSON'
PERFORM INSDATAPERSON.
* TESTGAME
MOVE SPACES TO BUFFER.
STRING
'CREATE TABLE TESTGAME('
'ID DECIMAL(12,0), '
'NAME CHAR(50) NOT NULL, '
'PRIMARY KEY (ID))'
INTO BUFFER.
EXEC SQL
EXECUTE IMMEDIATE :BUFFER
END-EXEC
IF SQLSTATE='42S01'
DISPLAY ' Table TESTGAME already exists.'
ELSE
PERFORM SQLSTATE-CHECK
DISPLAY ' created Table TESTGAME'
PERFORM INSDATAGAME.
* TESTPOINTS
MOVE SPACES TO BUFFER.
STRING
'CREATE TABLE TESTPOINTS('
'PERSONID DECIMAL(12,0), '
'GAMEID DECIMAL(12,0), '
'POINTS DECIMAL(6,2), '
'CONSTRAINT POINTS_CONSTRAINT1 FOREIGN '
'KEY (PERSONID) REFERENCES TESTPERSON(ID), '
'CONSTRAINT POINTS_CONSTRAINT2 FOREIGN '
'KEY (GAMEID) REFERENCES TESTGAME(ID),'
'PRIMARY KEY (PERSONID, GAMEID))'
INTO BUFFER.
EXEC SQL
EXECUTE IMMEDIATE :BUFFER
END-EXEC
IF SQLSTATE='42S01'
DISPLAY ' Table TESTPOINTS already exists.'
ELSE
PERFORM SQLSTATE-CHECK
DISPLAY ' created Table TESTPOINTS'
PERFORM INSDATAPOINTS.
*-----------------------------------------------------------------*
* SELECT SUM of POINTS for persons >1
*-----------------------------------------------------------------*
EXEC SQL
SELECT
SUM(POINTS)
INTO
:hVarD
FROM
TESTPERSON, TESTPOINTS
WHERE PERSONID>1 AND PERSONID=ID
END-EXEC
PERFORM SQLSTATE-CHECK
IF SQLCODE NOT = 100
DISPLAY 'SELECTED '
DISPLAY ' SUM of POINTS for persons >1 ' hVarD
ELSE
DISPLAY ' No points found'
END-IF.
*-----------------------------------------------------------------*
* SELECT ALL with CURSORS
*-----------------------------------------------------------------*
EXEC SQL
DECLARE CUR_ALL CURSOR FOR
SELECT
TESTPERSON.NAME,
POINTS
FROM
TESTPERSON, TESTPOINTS
WHERE PERSONID=ID
END-EXEC
PERFORM SQLSTATE-CHECK
EXEC SQL
OPEN CUR_ALL
END-EXEC
PERFORM SQLSTATE-CHECK
PERFORM UNTIL SQLCODE = 100
EXEC SQL
FETCH CUR_ALL
INTO
:hVarC,
:hVarD
END-EXEC
PERFORM SQLSTATE-CHECK
IF SQLCODE NOT = 100
DISPLAY 'FETCHED '
DISPLAY ' person ' hVarC ' points: ' hVarD
ELSE
DISPLAY ' No points found'
END-IF
END-PERFORM.
*-----------------------------------------------------------------*
* DROP TABLEs
*-----------------------------------------------------------------*
* MOVE 'DROP TABLE TESTPOINTS' TO BUFFER.
* EXEC SQL
* EXECUTE IMMEDIATE :BUFFER
* END-EXEC
* PERFORM SQLSTATE-CHECK.
* MOVE 'DROP TABLE TESTGAME' TO BUFFER.
* EXEC SQL
* EXECUTE IMMEDIATE :BUFFER
* END-EXEC
* PERFORM SQLSTATE-CHECK.
* MOVE 'DROP TABLE TESTPERSON' TO BUFFER.
* EXEC SQL
* EXECUTE IMMEDIATE :BUFFER
* END-EXEC
* PERFORM SQLSTATE-CHECK.
* DISPLAY ' dropped Tables '
*-----------------------------------------------------------------*
* COMMIT CHANGES
*-----------------------------------------------------------------*
EXEC SQL
COMMIT
END-EXEC.
PERFORM SQLSTATE-CHECK.
*-----------------------------------------------------------------*
* DISCONNECT FROM THE DATABASE
*-----------------------------------------------------------------*
EXEC SQL
CONNECT RESET
END-EXEC.
PERFORM SQLSTATE-CHECK.
STOP RUN.
.
*-----------------------------------------------------------------*
* CHECK SQLSTATE AND DISPLAY ERRORS IF ANY
*-----------------------------------------------------------------*
SQLSTATE-CHECK SECTION.
IF SQLCODE < 0
DISPLAY 'SQLSTATE=' SQLSTATE,
', SQLCODE=' SQLCODE
IF SQLERRML > 0
DISPLAY 'SQL Error message:' SQLERRMC(1:SQLERRML)
END-IF
MOVE SQLCODE TO RETURN-CODE
STOP RUN
ELSE IF SQLCODE > 0 AND NOT = 100
DISPLAY 'SQLSTATE=' SQLSTATE,
', SQLCODE=' SQLCODE
IF SQLERRML > 0
DISPLAY 'SQL Warning message:' SQLERRMC(1:SQLERRML)
END-IF
END-IF.
.
INSDATAPERSON SECTION.
*-----------------------------------------------------------------*
* INSERT Data
*-----------------------------------------------------------------*
* TESTPERSON
MOVE 0 TO hVarN.
PERFORM UNTIL hVarN > 2
COMPUTE hVarN = hVarN + 1
STRING 'Testpers '
hVarN
INTO hVarC
EXEC SQL
INSERT INTO TESTPERSON SET
ID=:hVarN,
NAME=:hVarC
END-EXEC
PERFORM SQLSTATE-CHECK
DISPLAY 'INSERTED '
DISPLAY ' Person ' hVarN ' NAME ' hVarC
END-PERFORM.
INSDATAGAME SECTION.
* TESTGAME
MOVE 0 TO hVarN.
PERFORM UNTIL hVarN > 3
COMPUTE hVarN = hVarN + 1
STRING 'Testgame '
hVarN
INTO hVarC
EXEC SQL
INSERT INTO TESTGAME SET
ID=:hVarN,
NAME=:hVarC
END-EXEC
PERFORM SQLSTATE-CHECK
DISPLAY 'INSERTED '
DISPLAY ' Game ' hVarN ' NAME ' hVarC
END-PERFORM.
INSDATAPOINTS SECTION.
* TESTPOINTS
MOVE 0 TO hVarN.
MOVE 0 TO hVarD.
PERFORM UNTIL hVarN > 2
COMPUTE hVarN = hVarN + 1
COMPUTE hVarD = hVarN + 0.75
EXEC SQL
INSERT INTO TESTPOINTS SET
PERSONID=:hVarN,
GAMEID=:hVarN,
POINTS=:hVarD
END-EXEC
PERFORM SQLSTATE-CHECK
DISPLAY 'INSERTED '
DISPLAY ' POINTS for person/game ' hVarN ' : ' hVarD
END-PERFORM.
然后进行预处理
esqlOC.exe -static -o c:\Temp\esqlOCGetStart1.cob c:\Temp\esqlOCGetStart1.sqb
然后编译
SET OC_RUNTIME=c:\OpenCobol_bin
SET esqlOC_RUNTIME=c:\esqlOC\release
SET COB_CFLAGS=-I %OC_RUNTIME%
SET COB_LIBS=%OC_RUNTIME%\libcob.lib %OC_RUNTIME%\mpir.lib %esqlOC_RUNTIME%\ocsql.lib
SET COB_CONFIG_DIR=%OC_RUNTIME%\config\
set PATH=C:\WINDOWS\system32;%OC_RUNTIME%
call "%PROGRAMFILES%\Microsoft Visual Studio 10.0\VC\vcvarsall.bat"
%OC_RUNTIME%\cobc.exe -fixed -v -x -static -o c:\Temp\esqlOCGetStart1.exe c:\Temp\esqlOCGetStart1.cob
然后运行
SET OC_RUNTIME=c:\OpenCobol_bin
SET esqlOC_RUNTIME=c:\esqlOC\release
set PATH=%OC_RUNTIME%;%esqlOC_RUNTIME%
c:\Temp\esqlOCGetStart1.exe
您的详细信息会有所不同，当然。更多信息请点击：
http://open-cobol.sourceforge.net/faq/#getting-started-with-esqloc
什么编译器/操作系统？ –
忘记“翻译”，只是找出如何在COBOL中做到这一点。尽管如果COBOL对你来说是新的，那么使用它似乎不是一个好主意。搜索引擎的0.218可以在“COBOL”中找到示例，但是对于这种类型的事件，确切需要知道哪些COBOL。 –
Cobc for Cobol，Windows是OS – mrjigglejam