Posted: Sun Oct 07, 2007 10:44 am Post subject: Данные из таблицы на сервере MS SQL во внутреннюю табличку
Коллеги, помогите плиз с задачей, мне нужно с внешнего MS SQL сервера получить содержимое некоторой таблички, при условии что программа будет запускаться в фоновом режиме автоматически по расписанию. большое спасибо.
В таблицу DBCON добавить запись об используемой базе и под каким юзером идет коннект типа:
SERVER NAME: SERVER
MSSQL DBname: DBNAME
CON_NAME - это логическое имя соединения будешь использовать в EXEC SQL, например 'TEST'
DBMS = 'MSS' (удаленная база MSSQL)
USERNAME = логин под которым коннектишься к удаленной базе
DBPassw - Password
Conn info: MSSQL_SERVER=SERVER;MSSQL_DBNAME=DBNAME
Читаете ноту 323151 "Several DB connections with Native SQL", чтобы понимать синтаксис. Если возникают проблемы с соединением, читаете связанные ноты: 178949,25383,339092,582772
DATA: F1(3), F2(3), F3(3).
F3 = ' 1 '.
EXEC SQL.
SELECT CLIENT, ARG1 INTO :F1, :F2 FROM AVERI_CLNT
WHERE ARG2 = :F3
ENDEXEC.
WRITE: / F1, F2.
Получение данных в таблицу.
Code:
* This internal table will need to be defined with the data types to match the column types in the Oracle table. In this case they are all VARCHAR2, which translates to (n) TYPE C in ABAP. Other types are also possible. Let me know your Oracle table structure and I will help if you need it. Use DESC <tablename> in SQLPlus to get the Oracle table structure.
DATA: BEGIN OF specdb_data OCCURS 0,
rc_code(1) TYPE C, “ VARCHAR2(1)
device_no(5) TYPE C, “ VARCHAR2(5)
pattern_no(3) TYPE C, “ VARCHAR2(3)
specdbtype(3) TYPE C, “ VARCHAR2(3)
sort_equip(5) TYPE C, “ VARCHAR2(5)
test_equip(5) TYPE C, “ VARCHAR2(5)
material(13) TYPE C, “ VARCHAR2(13)
part_type(3) TYPE C, “ VARCHAR2(3)
END OF specdb_data.
DATA: con_name LIKE dbcon-con_name.
con_name = 'TEST'.
* Open the database connection.
EXEC SQL.
set connection :con_name
ENDEXEC.
EXEC SQL.
connect to :con_name
ENDEXEC.
IF sy-subrc NE 0.
WRITE:/ 'Error: Unable to connect to SpecDB Oracle database!' COLOR
COL_NEGATIVE.
WRITE:/ ' SpecDB data *NOT* loaded!' COLOR COL_NEGATIVE.
WRITE:/.
EXIT.
ELSE.
WRITE:/ 'Connected to Oracle database!'.
ENDIF.
* Do the select. Your SQL SELECT will go here.
EXEC SQL.
OPEN c1 FOR
SELECT NVL(responsibility_code, '-') rc_code,
device_no,
pattern_no,
part_type specdbtype,
sort_equip,
test_equip,
NULL,
NULL
FROM masterspec.spec_acl_all
ENDEXEC.
IF sy-subrc EQ 0.
CLEAR specdb_data.
*
* here is the loop to load the Oracle table into the internal table
*
DO.
EXEC SQL.
fetch next c1 into :specdb_data.
ENDEXEC.
IF sy-subrc <> 0. "All data has been processed.
EXIT.
ENDIF.
APPEND specdb_data.
ENDDO.
ENDIF.
*
* Do your stuff with the interal table here
*
* *Close the cursor.
EXEC SQL.
close c1
ENDEXEC.
* *Disconnect the database.
EXEC SQL.
disconnect :con_name
ENDEXEC.
ENDFORM.
Code:
TABLES: dbcon.
DATA: BEGIN OF t_material_list OCCURS 0,
parent_part(20),
component_part(20),
ref_designator(10),
component_type(1),
component_quantity(16),
END OF t_material_list.
DATA: con_name LIKE dbcon-con_name VALUE 'S2M'.
PARAMETERS: p_matnr LIKE mara-matnr DEFAULT '54-30508-03'.
EXEC SQL.
CONNECT TO :con_name
ENDEXEC.
IF sy-subrc EQ 0.
EXEC SQL.
SET CONNECTION :con_name
ENDEXEC.
IF sy-subrc EQ 0.
EXEC SQL.
SELECT parent_part,
component_part,
ref_designator,
component_type,
component_quantity
INTO :t_material_list
FROM material_list
WHERE parent_part = :p_matnr.
ENDEXEC.
ENDIF.
ENDIF.
Code:
***********************************************************************
*
* Procedure: Get_ASN_Quantity
*
* Purpose: Takes a validated ASN and calculates outstanding
* quantitiesm
*
* Entry: pu_vlief - ASN Number
*
* Tables: GLOBAL: wt_asn_data. (Due to EXEC SQL)
*
* Exit: pc_subrc - value of subrc after exec.
*
*
* Called By: Perform Get_ASN_Quantity using w_vlief
* changing w_subrc.
*
* Calls:
*
*
Form Get_ASN_Quantity using pu_vlief like rm07m-vlief
Changing pc_subrc like sy-subrc.
*
Data: w_ebeln like ekbe-ebeln,
w_ebelp like ekbe-ebelp,
Begin of t_ekbe occurs 0.
Include structure ekbe.
Data: End Of t_ekbe.
*
* Used Exec SQL statement because SAP defined LIPS-VGPOS and
* EKPO-EBELP with conflicting Data Domain Chacteristics making the
* use of Inner Joins impossible.
*
Zap wt_asn_data.
EXEC SQL PERFORMING GET_ASN_DATA.
SELECT LIPS.VBELN,
LIPS.POSNR,
LIPS.LGORT,
LIPS.MATNR,
LIPS.VGBEL,
LIPS.VGPOS,
LIPS.LFIMG,
MARA.XCHPF,
EKPO.EBELN,
EKPO.EBELP,
EKPO.KTMNG,
EKPO.MENGE
INTO :WT_ASN_DATA-VBELN,
:WT_ASN_DATA-POSNR,
:WT_ASN_DATA-LGORT,
:WT_ASN_DATA-MATNR,
:WT_ASN_DATA-VGBEL,
:WT_ASN_DATA-VGPOS,
:WT_ASN_DATA-LFIMG,
:WT_ASN_DATA-XCHPF,
:WT_ASN_DATA-EBELN,
:WT_ASN_DATA-EBELP,
:WT_ASN_DATA-KTMNG,
:WT_ASN_DATA-MENGE
FROM LIPS,
MARA,
EKPO
WHERE LIPS.MANDT = :SY-MANDT AND
LIPS.MANDT = MARA.MANDT AND
LIPS.MATNR = MARA.MATNR AND
LIPS.MANDT = EKPO.MANDT AND
LIPS.VGBEL = EKPO.EBELN AND
LIPS.VGPOS = '0' || EKPO.EBELP AND
LIPS.VBELN = :PU_VLIEF AND
LIPS.LFIMG > 0
ENDEXEC.
LOOP AT WT_ASN_DATA.
CLEAR T_EKBE.
REFRESH T_EKBE.
SELECT EBELN EBELP MENGE SHKZG
FROM EKBE
INTO CORRESPONDING FIELDS OF T_EKBE
WHERE
EBELN = WT_ASN_DATA-VGBEL AND
EBELP = WT_ASN_DATA-VGPOS AND
BEWTP = 'E'.
COLLECT T_EKBE.
ENDSELECT.
LOOP AT T_EKBE.
IF T_EKBE-SHKZG = 'S'.
* wt_asn_data-menge = wt_asn_data-lfimg - t_ekbe-menge.
WT_ASN_DATA-MENGE = WT_ASN_DATA-MENGE - T_EKBE-MENGE.
ELSE.
WT_ASN_DATA-MENGE = WT_ASN_DATA-MENGE + T_EKBE-MENGE.
ENDIF.
ENDLOOP.
IF WT_ASN_DATA-MENGE <= 0.
DELETE WT_ASN_DATA
WHERE
VBELN = WT_ASN_DATA-VBELN AND
POSNR = WT_ASN_DATA-POSNR.
ENDIF.
ENDLOOP.
Move sy-subrc to pc_subrc.
EndForm.
*&---------------------------------------------------------------------*
*& Form GET_ASN_DATA
*&---------------------------------------------------------------------*
FORM GET_ASN_DATA.
APPEND WT_ASN_DATA.
ENDFORM. " GET_ASN_DATA
Note 323151 - Several DB connections with Native SQL
This note describes the option (available as of Basis Release 4.0B) of using Native SQL language elements to establish one or several database connections to database systems of the same manufacturer (homogeneous multi-connect) or to databases of other manufacturers (heterogeneous multi-connect).
Other terms
Multi-connect, DBCON, Native SQL, EXEC SQL, Connect
Solution
When you start an R/3 application server, a connection is opened by default from the R/3 kernel to the R/3 database. This connection will be called the R/3 default connection from here on. All SQL commands that are transmitted from the R/3 kernel or from ABAP programs - no matter whether they are Open or Native SQL commands - automatically refer to this default connection, that is, they run in the context of the active DB transaction on this connection. The connection data (such as the DB user name, password of the user or the database name) is taken either from the profile parameters or from the correspondingly set environment variables (this is DB-specific).
As of Release 4.0B, you have the option of opening other DB connections in addition to the default connection from within an ABAP program, and you can access these databases using Native SQL commands. These additional connections can be set up either
to the standard R/3 database or
to another database of the same DB manufacturer or
to a database of another DB manufacturer.
These databases do not necessarily have to contain SAP components. However, you can only set up connections to database systems that are also supported by the R/3 Basis system. These are DB2, Informix, MS SQL Server, Oracle and SAP DB. If you only want to set up connections to databases of the same manufacturer (that is, set up homogeneous connections only), this is already possible using the delivered R/3 software. However, if you want to set up a connection to a database that does not correspond to the DB platform of the R/3 database, you also need to install a DB platform-specific DLL via which accesses to this database from R/3 can be routed. These DLLs are not part of a standard R/3 installation. In this case, contact your SAP consultant. You can find information on potential platform-specific restrictions in the attached special notes.
Configuration: table DBCON
Before you can open an additional DB connection, all connection data that is required to identify the target database and for authentication against this database must be communicated to the ABAP runtime environment. To do this, create an entry in the table DBCON that will contain all the required connection data for each database connection that you want to set up in addition to the R/3 default connection. The table DBCON is in the R/3 default database and can be maintained using the table maintenance tool (Transaction SM30) or, as of Release 4.6, using Transaction DBCO. For each connection you must enter the following information in this table:
A logical connection name. The name entered here explicitly identifies a database connection.
The database type. This determines which DB platform will be used for this connection. In principle, you can enter all DB platforms supported by the R/3 System.
The database user under whose name you want to set up the connection.
The password of this user for authentication against the database. This password is stored in encrypted form.
The technical connection data required to set up the connection to the database. The connection data is DB platform-dependent and generally includes the database name as well as the DB host on which the database runs.
Native SQL language commands for administrating several database connections
Only Native SQL interfaces allow you to set up additional database connections and access non-R/3 databases. However, all Open SQL commands will continue to be transferred to the default connection, that is, to the R/3 database.
Commands for opening and closing a database connection, as well as setting it up, have been added to native SQL. If a new database connection is opened, a new DB transaction is started automatically on this connection. This transaction is independent of the transaction currently running on the R/3 default connection, that is, the transaction running there is not closed and all subsequent Open SQL commands will continue to be processed in this transaction (and not in the transaction started on the new DB connection). However, all subsequent Native SQL commands will be executed on the newly opened connection. No synchronization is carried out by the system between the transactions running on the different connections, that is, a 2-phase commit log is NOT supported.
Opening a database connection
EXEC SQL. CONNECT TO <con_name> [ AS <alias_name> ] ENDEXEC.
This command opens the database connection designated by <con_name>. In this context, <con_name> is a user-defined logical name, under which all the information required to open the database connection such as the user name, password, database name and so on must be stored in the table DBCON. You can specify <con_name> as a string-literal or as a variable in the form :<var>.
If the addition "AS <alias_name>" is entered, the open database connection is managed with the indicated alias name. If no alias name is entered, <con_name> is used as the name for the open connection. If you enter several alias names, you can open several connections to the same database simultaneously by using the same login name. This alias arrangement allows the execution of independent transactions on the same logical database connection (see example 2). The alias name can also be entered as a literal or as a variable.
If no entry is found in the table DBCON for the indicated connection name <con_name>, an ABAP runtime error occurs. If, on the other hand, the setup connection fails due to a DB error, for example, because the database in question is not online, then SY-SUBRC = 4 is set and the ABAP program assumes control.
After the connection has been successfully set up (SY-SUBRC = 0), the connection that was just opened automatically becomes the active connection, that is, from this point on, all subsequent Native SQL commands (and only these) will access the database associated with this connection. However, all subsequent Open SQL accesses still refer to the default connection. Nevertheless, the physical connection remains. If the connection is not closed explicitly with a "DISCONNECT" command (see below), it remains open until it is closed implicitly when the corresponding internal session is closed (the term "internal session" is explained in the section on "Data area and Modularization Unit Organization" in the ABAP online documentation). After that, you can only access this connection if it has been opened again explicitly with a new "CONNECT" command.
Comments
If a database connection that was opened using a "CONNECT" command is not explicitly closed again with a "DISCONNECT" command, it is invalidated (this means that it will have to be opened again with a "CONNECT" for further accesses) when the internal session is closed, but the physical connection remains in place. The advantage of this is that it will not have to be set up again if a new "CONNECT" is executed. However, "reusing" a connection that was previously opened only works if there are no open transactions on this connection, that is, if the last transaction on this connection was completed using COMMIT or ROLLBACK.
Setting the active database connection
EXEC SQL. SET CONNECTION { <alias_name> | DEFAULT } ENDEXEC.
This command sets the data base connection <alias_name> that has been opened to active. This means that all subsequent Native SQL statements are executed on this connection. If a transaction is still open on the previous connection, it remains open and can then be continued by reverting to this connection.
You can also enter the keyword DEFAULT as an alias name. This causes the R/3 default connection to become the active connection again.
Again, <alias_name> can also be entered directly as a literal or as a variable value in form of :<var>. However, the entered alias name must be known from a preceding "CONNECT" statement (that is, a statement executed within the same internal mode).If the database connection was opened without explicitly specifying an alias name, the connection name <con_name> must be used in the "SET" statement as the <alias_name>.
If there is no opened connection for the entered connection name, SY-SUBRC = 4 is set.
Closing a database connection
EXEC SQL. DISCONNECT <alias_name> ENDEXEC.
The "DISCONNECT" command explicitly closes a database connection opened under the <alias_name> name. As of Basis Release 6.20: The transaction running on this link is rolled back. Up to 6.20, the physical link is retained if the transaction running on this link was not completed by COMMIT/ROLLBACK. After a "DISCONNECT", you can no longer access this link until it is reopened explicitly by a "CONNECT". If the closed connection was also the connection that was just active, then the R/3 default connection is automatically the active connection again after that.
The "DISCONNECT" command also closes the physical connection to the database. If you are setting up a new connection, you must first re-establish this physical connection, which may be a rather time-consuming operation since the corresponding system resources will have to be requested on the client side as well as on the database server side. Therefore, you should only explicitly close a connection with the "DISCONNECT" command if the operations executed on this connection are only running occasionally. However, if you want to use this connection all the time, it should remain open.
If the <alias_name> does not correspond to any previously opened DB connection, an ABAP runtime error occurs.
Example 1: Accessing remote DB in a subroutine
FORM remote_access USING con_name LIKE dbcon-con_name.
* This form performs some database operations on connection 'con_name'.
* The form does not require that the caller has already opened this
* connection nor does it require that 'con_name' is "active". After
* the form has finished the "default connection" is active
* Test if connection 'con_name' has already been opened
EXEC SQL.
SET CONNECTION :con_name
ENDEXEC. IF SY-SUBRC <> 0. * Connection not yet opened.
EXEC SQL.
CONNECT TO :con_name
ENDEXEC. IF SY-SUBRC <> 0. * error handling
ENDIF.
ENDIF.
* Do something on connection 'con_name'
EXEC SQL.
...
ENDEXEC.
* Commit the changes on 'con_name'
EXEC SQL.
COMMIT
ENDEXEC.
* Reset to "default connection"
EXEC SQL.
SET CONNECTION DEFAULT
ENDEXEC.
ENDFORM.
Example 2: Implementing independent transactions
DATA con_name LIKE dbcon-con_name VALUE 'ABC'.
* Open a connection C1 to a database identified by the logical name
* 'ABC'. This implicitly starts a transaction T1.
EXEC SQL.
CONNECT TO :con_name AS 'C1'
ENDEXEC.IF SY-SUBRC <> 0. * error handling
ENDIF.
* Do some database operations on connection C1
EXEC SQL.
...
ENDEXEC.
* Open a second connection C2 to the same database 'ABC'. This
* implicitly starts transaction T2 which became the "active" one.
* Transaction T1 still remains open.
EXEC SQL.
CONNECT TO :con_name AS 'C2'
ENDEXEC.IF SY-SUBRC <> 0. * error handling
ENDIF.
* Do some operations on connection C2
EXEC SQL.
...
ENDEXEC.
* Commit transaction T2. This commit does not have any effect on
* transaction T1.
EXEC SQL.
COMMIT
ENDEXEC.
* Resume transaction T1 on connection C1
EXEC SQL.
SET CONNECTION 'C1'
ENDEXEC.
* Reset to the default connection
EXEC SQL.
SET CONNECTION DEFAULT
ENDEXEC.
Header Data
Release Status: Released for Customer
Released on: 03.11.2005 07:57:25
Priority: Recommendations/additional info
Category: Consulting
Primary Component: BC-DB-DBI DB Independant Database Interface
Releases
Software
Component Release From
Release To
Release And
subsequent
SAP_BASIS 46 46B 46B X
SAP_BASIS 46C 46C 46D X
SAP_BASIS 60 610 640 X
Related Notes
955670 - DB multiconnect with MaxDB as secondary database
829706 - MSSQL: DB Multiconnect in the MSSPROCS program
738371 - Creating DBCON multiconnect entries for SQL Server
606359 - FAQ: Oracle National Language Support (NLS)
597903 - Reconnect for secondary connection to Informix
592393 - FAQ: Oracle
585468 - NT/DB4: Authentication failure to iSeries Diagnose
518241 - DB Connect in BW for an external Oracle database
445872 - iSeries: DB multiconnect from Windows / LinuxPPC to iSeries
421026 - DB MultiConnect with Informix as a secondary database
339092 - DB MultiConnect with Oracle as secondary database
200164 - DB6: Database multiconnect with EXEC SQL
181989 - Multiconnect under EXEC SQL Informix
178949 - MSSQL: Database MultiConnect with EXEC SQL
160484 - DB2/390: Database multiconnect with EXEC SQL
146624 - AS/400: Database Multiconnect with EXEC SQL and ADBC
NOTE: Currently this DBSL library is only available for the Microsoft Windows Operating System. UNIX application servers cannot be used to connect to a remote MS SQL Server.
т.е. получается что если сервер приложений организован на *nix системе то подключиться к MSSQL не возможно, как тогда решить поставленную задачу?
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
