CN110968625A - Method and system for interaction between SQLserver database and third-party platform - Google Patents
Method and system for interaction between SQLserver database and third-party platform Download PDFInfo
- Publication number
- CN110968625A CN110968625A CN201910959228.6A CN201910959228A CN110968625A CN 110968625 A CN110968625 A CN 110968625A CN 201910959228 A CN201910959228 A CN 201910959228A CN 110968625 A CN110968625 A CN 110968625A
- Authority
- CN
- China
- Prior art keywords
- http request
- creating
- storage process
- trigger
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 72
- 230000003993 interaction Effects 0.000 title claims abstract description 14
- 230000008569 process Effects 0.000 claims abstract description 38
- 230000004048 modification Effects 0.000 claims abstract description 7
- 238000012986 modification Methods 0.000 claims abstract description 7
- 238000004806 packaging method and process Methods 0.000 claims abstract description 5
- 238000012545 processing Methods 0.000 claims description 10
- 230000004044 response Effects 0.000 claims description 8
- 238000012795 verification Methods 0.000 claims description 4
- 238000001914 filtration Methods 0.000 claims description 2
- 230000000977 initiatory effect Effects 0.000 abstract 1
- 230000001960 triggered effect Effects 0.000 description 9
- 238000004590 computer program Methods 0.000 description 3
- 238000011161 development Methods 0.000 description 3
- 238000010586 diagram Methods 0.000 description 3
- 230000006872 improvement Effects 0.000 description 3
- 230000008859 change Effects 0.000 description 2
- 230000004075 alteration Effects 0.000 description 1
- 230000005540 biological transmission Effects 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 230000002093 peripheral effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/252—Integrating or interfacing systems involving database management systems between a Database Management System and a front-end application
-
- H—ELECTRICITY
- H04—ELECTRIC COMMUNICATION TECHNIQUE
- H04L—TRANSMISSION OF DIGITAL INFORMATION, e.g. TELEGRAPHIC COMMUNICATION
- H04L67/00—Network arrangements or protocols for supporting network services or applications
- H04L67/01—Protocols
- H04L67/02—Protocols based on web technology, e.g. hypertext transfer protocol [HTTP]
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computer Networks & Wireless Communication (AREA)
- Signal Processing (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application discloses a method and a system for interaction between an SQLserver database and a third-party platform, wherein the method comprises the following steps: the SQLserver is allowed to send out a call through a storage process; creating a storage process for realizing http request sending; creating a trigger on a database table; and responding to the modification of the data in the database table, triggering the trigger, packaging the data to be sent, and calling the storage process to send the http request to a third-party platform. According to the invention, the third-party interface is called by directly initiating the http request in the SQLserver database, so that the interaction between the old system of which the database is the SQLserver and the third-party system is easy and the consumed time is less.
Description
Technical Field
The application relates to the field of electric digital data processing, in particular to a method and a system for interaction between an SQLserver database and a third-party platform.
Background
With the continuous development of the internet, some traditional old systems with the database being the SQLserver need to interact with other third-party platforms/systems so as to adapt to the development needs of the times, and particularly, hospital information systems are most obvious.
One of the existing solutions is to modify and implement the old system again, but due to historical reasons, the difficulty of re-development is often very high. The other scheme is to use a third-party system to periodically query the needed data through an old system without modifying the old system, but the scheme is poor in timeliness and needs to adjust the structure of a database table to distinguish the processed data from the unprocessed data.
Disclosure of Invention
In order to overcome the defects in the prior art, the invention aims to solve the technical problem of providing the method and the system for interaction between the SQLserver database and the third-party platform, which are easy to realize, consume less time and can realize the interaction with the third-party platform without greatly changing the old system.
To solve the above technical problem, according to a first aspect of the present invention, a method for interaction between an SQLserver database and a third party platform is provided, where the method includes:
the SQLserver is allowed to send out a call through a storage process;
creating a storage process for realizing http request sending;
creating a trigger on a database table;
and responding to the modification of the data in the database table, triggering the trigger, packaging the data to be sent, and calling the storage process to send the http request to a third-party platform.
As an improvement of the method of the present invention, the storing process comprises:
checking parameters transmitted when the storage process is called;
in response to the verification passing, creating a serverxmlhttp object;
creating a connection to a third party platform target address using a serverxmlhttp object;
setting timeout time, media type information in an http request header and the size of an http request entity;
sending data to be sent;
and processing the information returned by the http request and returning the information to a trigger for calling the storage process to inform whether the http request is successful or not.
As another improvement of the method of the present invention, the storing process further includes: in response to failure of the http request, an error prompt for failure is given.
As a further improvement of the method of the present invention, the flip-flop further comprises: filtering data that does not satisfy the condition is also included before encapsulating the data to be transmitted.
To solve the above technical problem, according to a second aspect of the present invention, there is provided a system for implementing interaction between an SQLserver database and a third party platform, the system comprising:
the configuration module is used for enabling the SQLserver to allow a call to be sent out through a storage process;
the storage process creating module is used for creating a storage process for realizing http request sending;
the trigger creating module is used for creating a trigger on the database table;
and the trigger module is used for responding to the modification of the data in the database table, triggering the trigger, packaging the data to be sent, and calling the storage process to send the http request to a third-party platform.
To solve the above technical problem, the tangible computer readable medium of the present invention includes computer program code for executing the method for interaction between the SQLserver database of the present invention and a third party platform.
To solve the above technical problem, the present invention provides an apparatus, comprising at least one processor; and at least one memory including computer program code, the at least one memory and the computer program code configured to, with the at least one processor, cause the apparatus to perform at least some of the steps of the method of the present invention for interacting a SQLserver database with a third party platform.
According to the invention, by establishing the trigger in the database of the old system, the business operation in the old system can change the data in the corresponding business table in the database, at the moment, the trigger established on the business table can be triggered, the triggered data can be processed, the data which do not meet the conditions can be filtered, then the business data structure can be packaged, and the HTTP request can be directly initiated to connect with the third-party platform/system by calling the storage process. Compared with the prior art, the method is simple and convenient to implement, and consumes less time when interacting with a third-party platform.
Other features and advantages of the present invention will become more apparent from the detailed description of the embodiments of the present invention when taken in conjunction with the accompanying drawings.
Drawings
FIG. 1 is a flow chart of an embodiment of a method according to the present invention.
FIG. 2 is a schematic diagram of an embodiment of a system according to the present invention.
For the sake of clarity, the figures are schematic and simplified drawings, which only show details which are necessary for understanding the invention and other details are omitted.
Detailed Description
Embodiments and examples of the present invention will be described in detail below with reference to the accompanying drawings.
The scope of applicability of the present invention will become apparent from the detailed description given hereinafter. It should be understood, however, that the detailed description and the specific examples, while indicating preferred embodiments of the invention, are given by way of illustration only.
Figure 1 illustrates a flow diagram of one embodiment of a method for interaction of a SQLserver database with a third party platform in accordance with the present invention.
In step S102, change the SQLserver aspect- > peripheral application configuration- > ole automation enable lower attribute from false to true to allow the SQLserver to make external calls through the storage process.
In step S104, a stored procedure is created to implement the specific processing of http request transmission, for example, expressed as CREATE proc [ dbo ] in SQL statement. In an embodiment, the specific processing of the stored procedure includes: in the first step, parameters transferred when the storage process is called, such as the address of a third-party platform, a request mode (for example, post request), requested media type information (for example, application/json), encapsulated content of data to be sent, a prompt to be returned by the storage process, and the like, are simply checked. For example, it is checked whether the request mode is get or post, and other contents cannot be checked, and it is checked that the address of the request, the media type information of the request, the content of the request, etc. cannot be empty. And if the condition is met, the operation is carried out to the second step, otherwise, a specific error prompt is returned. And step two, calling a windows operating system ServerXMLHTTP.3.0 component to create a serverxmllhttp object, if the creation is successful, performing the next step, and if not, returning a specific error prompt. And thirdly, establishing connection to a target address of the third-party platform by using a serverxmllhttp object in a post or get mode, if the establishment is successful, performing the next step, and if not, returning a specific error prompt. And fourthly, setting the timeout time, and setting the media Type information (Content-Type) and the size (Content-Length) of the request entity in the http request header. And fifthly, calling a send method at the bottom layer to send the data (body). And sixthly, processing the information returned by the http request, judging success or failure according to the state code returned by the http, returning to a trigger for calling the storage process to inform the success or failure of the request, and if the request fails, returning an error prompt of the failure.
In step S106, a trigger is created on the corresponding database table. Triggers include insert/add triggers, delete triggers, and/or update/modify sensors. The insert trigger is triggered when data is inserted into the table, the delete trigger is triggered when data is deleted from the table, and the update trigger is triggered when data in the table is modified. In the case where the trigger is triggered, the triggered data (inserted data, updated data, or deleted data) is processed according to the type of the triggered trigger, data that does not satisfy the prescribed condition is filtered, data that satisfies the condition is data-encapsulated, and the storage procedure created in step S104 is invoked.
In step S108, in response to modifying the data in the database table, a trigger created on the database table is triggered, data that does not satisfy the condition is filtered, the data to be sent is packaged, and a storage process is invoked to send an http request to the third party platform to be docked with the third party platform. Sending an http request may be expressed in SQL statements as:
EXEC spHTTPRequest@urlStr,'POST','application/json',@body,@responseText OUTPUT
that is, the stored procedure (spHTTPRequest) created in step S104 is called and the following parameters are passed: the address of the third party platform (@ urlStr), the request mode (post request), the requested media type information (application/json), the encapsulated content of the data to be sent (@ body), and the prompt (@ responseText) to be returned by the storage process.
Fig. 2 is a schematic structural diagram of a system for implementing interaction between the SQLserver database and a third-party platform according to the present invention. The system comprises: a configuration module 202, configured to enable the SQLserver to make a call to the outside through a storage process; a storage procedure creating module 204, configured to create a storage procedure for implementing http request sending; a trigger creation module 206 for creating a trigger on a database table; and the triggering module 208 is configured to trigger the trigger in response to modification of the data in the database table, encapsulate the data to be sent, and call the storage process to send the http request to the third-party platform. In an embodiment, the stored procedure creation module 204 may include: the checking submodule is used for checking parameters transmitted when the storage process is called; the first creating submodule is used for responding to the verification passing and creating a serverxmllhttp object; the second creating submodule is used for creating connection to a target address of the third-party platform by using a serverxmllhttp object; the setting submodule is used for setting the overtime time, the media type information in the http request header and the size of the http request entity; the sending submodule is used for sending data to be sent; the processing submodule is used for processing information returned by the http request and returning the information to the trigger for calling the storage process to inform whether the http request is successful or not; and the error prompt submodule is used for responding to the failure of the http request and giving a failed error prompt.
The particular features, structures, or characteristics of the various embodiments described herein may be combined as suitable in one or more embodiments of the invention. Additionally, in some cases, the order of steps depicted in the flowcharts and/or in the pipelined process may be modified, as appropriate, and need not be performed exactly in the order depicted. In addition, various aspects of the invention may be implemented using software, hardware, firmware, or a combination thereof, and/or other computer implemented modules or devices that perform the described functions. Software implementations of the present invention may include executable code stored in a computer readable medium and executed by one or more processors. The computer readable medium may include a computer hard drive, ROM, RAM, flash memory, portable computer storage media such as CD-ROM, DVD-ROM, flash drives, and/or other devices, for example, having a Universal Serial Bus (USB) interface, and/or any other suitable tangible or non-transitory computer readable medium or computer memory on which executable code may be stored and executed by a processor. The present invention may be used in conjunction with any suitable operating system.
As used herein, the singular forms "a", "an" and "the" include plural references (i.e., have the meaning "at least one"), unless the context clearly dictates otherwise. It will be further understood that the terms "has," "includes" and/or "including," when used in this specification, specify the presence of stated features, steps, operations, elements, and/or components, but do not preclude the presence or addition of one or more other features, steps, operations, elements, components, and/or groups thereof. The term "and/or" as used herein includes any and all combinations of one or more of the associated listed items.
The foregoing describes some preferred embodiments of the present invention, but it should be emphasized that the invention is not limited to these embodiments, but can be implemented in other ways within the scope of the inventive subject matter. Various modifications and alterations of this invention will become apparent to those skilled in the art without departing from the spirit and scope of this invention.
Claims (10)
1. A method for interaction between an SQLserver database and a third-party platform is characterized by comprising the following steps:
the SQLserver is allowed to send out a call through a storage process;
creating a storage process for realizing http request sending;
creating a trigger on a database table;
and responding to the modification of the data in the database table, triggering the trigger, packaging the data to be sent, and calling the storage process to send the http request to a third-party platform.
2. The method of claim 1, wherein the storing the procedure comprises:
checking parameters transmitted when the storage process is called;
in response to the verification passing, creating a serverxmlhttp object;
creating a connection to a third party platform target address using a serverxmlhttp object;
setting timeout time, media type information in an http request header and the size of an http request entity;
sending data to be sent;
and processing the information returned by the http request and returning the information to a trigger for calling the storage process to inform whether the http request is successful or not.
3. The method of claim 2, wherein the storing further comprises:
in response to failure of the http request, an error prompt for failure is given.
4. The method of claim 2, wherein the communicated parameters comprise: the address of the third-party platform, the http request mode, the media type information of the http request, the packaged content of the data to be sent and the prompt to be returned in the storage process.
5. The method of claim 1, wherein the trigger further comprises: filtering data that does not satisfy the condition is also included before encapsulating the data to be transmitted.
6. The method of claim 2, further comprising:
and returning a specific error prompt in response to failure of creating the serverxmlhttp object.
7. The method of claim 2, further comprising:
and returning a specific error prompt in response to the failure of establishing the connection to the target address of the third-party platform.
8. A system for realizing interaction between an SQLserver database and a third-party platform is characterized by comprising:
the configuration module is used for enabling the SQLserver to allow a call to be sent out through a storage process;
the storage process creating module is used for creating a storage process for realizing http request sending;
the trigger creating module is used for creating a trigger on the database table;
and the trigger module is used for responding to the modification of the data in the database table, triggering the trigger, packaging the data to be sent, and calling the storage process to send the http request to a third-party platform.
9. The system of claim 8, wherein the stored procedure creation module comprises:
the checking submodule is used for checking parameters transmitted when the storage process is called;
the first creating submodule is used for responding to the verification passing and creating a serverxmllhttp object;
the second creating submodule is used for creating connection to a target address of the third-party platform by using a serverxmllhttp object;
the setting submodule is used for setting the overtime time, the media type information in the http request header and the size of the http request entity;
the sending submodule is used for sending data to be sent;
and the processing submodule is used for processing the information returned by the http request and returning the information to the trigger for calling the storage process to inform whether the http request is successful or not.
10. The system of claim 9, wherein the stored procedure creation module further comprises:
and the error prompt submodule is used for responding to the failure of the http request and giving a failed error prompt.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910959228.6A CN110968625A (en) | 2019-10-10 | 2019-10-10 | Method and system for interaction between SQLserver database and third-party platform |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910959228.6A CN110968625A (en) | 2019-10-10 | 2019-10-10 | Method and system for interaction between SQLserver database and third-party platform |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN110968625A true CN110968625A (en) | 2020-04-07 |
Family
ID=70029768
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910959228.6A Pending CN110968625A (en) | 2019-10-10 | 2019-10-10 | Method and system for interaction between SQLserver database and third-party platform |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110968625A (en) |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108255967A (en) * | 2017-12-26 | 2018-07-06 | 平安科技（深圳）有限公司 | Call method, device, storage medium and the terminal of storing process |
-
2019
- 2019-10-10 CN CN201910959228.6A patent/CN110968625A/en active Pending
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108255967A (en) * | 2017-12-26 | 2018-07-06 | 平安科技（深圳）有限公司 | Call method, device, storage medium and the terminal of storing process |
Non-Patent Citations (4)
| Title |
|---|
| 夏凌云 等: ""基于存储过程和 HTTP 协议的消息主动推送系统实现"", 《微型电脑应用》 * |
| 摄念子: ""Sql server2012中触发器发送http请求"", 《HTTPS://BLOG.CSDN.NET/LONGTINGJING/ARTICLE/DETAILS/46533423》 * |
| 申永芳: ""数据库中存储过程的研究与应用"", 《南方农机》 * |
| 赵永霞 等: "《数据库原理与应用》", 31 July 2017, 华中科技大学出版社 * |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10742773B2 (en) | Protocol conversion method, platform, and protocol conversion gateway | |
| CN113568686B (en) | Asynchronous processing method and device for Lua language, computer equipment and storage medium | |
| CN108733720B (en) | On-line checking method and checking system for SQL (structured query language) query of multiple database types | |
| CN111813724B (en) | HIDL interface adapting system, method, corresponding equipment and storage medium | |
| WO2019104974A1 (en) | Dubbo platform-based automatic server starting and stopping method , server, and storage medium | |
| KR20210040304A (en) | Image recognizing method, apparatus, electronic device, storage medium, and program | |
| US20180253313A1 (en) | Operation instruction response control method and terminal for human-machine interface | |
| CN106648875A (en) | Application switching method and device | |
| CN104636154B (en) | Application program installation method and device | |
| CN110881224B (en) | Network long connection method, device, equipment and storage medium | |
| CN110968625A (en) | Method and system for interaction between SQLserver database and third-party platform | |
| CN112835615B (en) | Plug-in processing method and device for software development kit and electronic equipment | |
| US8510426B2 (en) | Communication and coordination between web services in a cloud-based computing environment | |
| CN113791792A (en) | Application calling information acquisition method and device and storage medium | |
| CN116561179A (en) | Data query method, device, equipment and program product | |
| CN111294377B (en) | Dependency network request sending method, terminal device and storage medium | |
| CN113760318A (en) | Information processing method, information processing apparatus, server, and storage medium | |
| CN113703793B (en) | Application installation method, device, equipment and medium for cloud mobile phone | |
| CN107632893A (en) | Message queue processing method and processing device | |
| CN112948098B (en) | Service unit offline method based on micro-service unit architecture and related device | |
| CN114925120A (en) | Insurance business information query method and related equipment | |
| CN113760487B (en) | Service processing method and device | |
| CN103294527A (en) | Method, system, and server for processing network task | |
| CN110990000B (en) | Data request processing method, device and equipment of MVC pattern design model layer | |
| CN113434572A (en) | Data query method, query system, device, storage medium and program product |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20200407 |