High performance database messaging or pushing
a. Fast updating one or more middle tier
caches for different client applications whenever backend database is updated.
To push messages from a database, SocketPro starts to build one client socket connection from database to a remote SocketPro server. Usually, we treat database as a server, but SocketPro thinks your database is one of its clients. Therefore, your database server is required to install a proper version of (32bit or 64bit) npUSocketPro.dll, and make it registered because it is a COM library.
SocketPro database push library is thread-safe. You can call any exposed functions under any conditions within a database system.
Although SocketPro database push library opens one socket connection only, it is very fast for its 100% non-blocking communication. Internally, it has a hidden memory queue which has very high communication rate without wasting network bandwidth.
When making the call Connect successfully, SocketPro DB push will automatically switch to chat service with given user id and password right after the client socket connection is established. Therefore, your remote SocketPro server must be enabled with built-in service chat. If this built-in chat service is not enabled, DB push will not work as expected.
You can call all of DB push methods anytime from anywhere within a database server. Internally, it just uses one instance of client socket only.
SocketPro DB push will periodically send base request DoEcho with receiving time out equal to 1 second. DB push uses this way to fast detect server abnormal shutdown.
Before using SocketPro database push, you need to make sure that a proper version of (32bit or 64bit) npUSocketPro.dll is registered by calling regsvr32 npUSocket.dll from DOS command as described here.
Afterwards, you need to load one of dll located within the directory of ..\UDAParts\SocketPro\DatabasePush so that your database can access it. Usually, you are required to have DBA privilege at least. Also, see the SQL script file named as registration.sql for each of database management systems.
Once we have loaded our SocketPro database push library into a database server, we need to register the five functions IsConnected, Connect, Disconnect, Notify, and SendUserMessage. We take MS Sql server as an example as shown below. All of other databases are similar. Please see the attached file registration.sql for each of database management systems.
Once the above functions are registered within your database, you can access them at your will. Again, please see the attached file registration.sql for each of database management systems. Connecting string to a remote server could be:
Note that the settings verify and strict are ignored if the encryption is 0 (no encryption, default). By default, zip is zero; encryption zero; verify string empty; and strict zero. The setting verify is used for verifying certificate subject, chains and validity. If the setting strict is not set to zero and encryption is either 3 (SSL3) or 5 (TLSv1), server authentication is fully tested through certificate before sending user id and password to a remote server.
To test SocketPro database push library, you need to start SocketPro HTTP push server as described here and here. The sample server is written for supporting all types (middle tier, http server, silverlight, desktop, device and database) of push demonstrations. Please compile the sample project inside the directory C:\Program Files\UDAParts\SocketPro\tutorial\CSharp (VBNet)\Chat. After copying all files in the directory C:\Program Files\UDAParts\SocketPro\tutorial\HTTPChatSampleFiles into the directory C:\Program Files\UDAParts\SocketPro\tutorial\CSharp (VBNet)\Chat\Server\bin\Release, you can see messages on your web browsers from your databases.
If you do everything correctly, you are expected to get the following two pictures:
The above three functions may return one of the below error codes:
At start, you may meet a number of problems to bother you. Here is a list of common errors.
When you enable CLR support for managed code, MS SQL server may emit the error, Msg 5847, Level 16, State 88, Line 2 Execution of .NET Framework code is disabled. Set "clr enabled" configuration option and restart the server.
When you send SQL command and ask for installing usocketnet.dll or SpMSSQLPush.dll, MS SQL server may response the error message, Msg 33009, Level 16, State 2, Line 1. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'Northwind'. You should correct this situation by resetting the owner of database 'Northwind' using the ALTER AUTHORIZATION statement.
When you register one of SocketPro DB push methods, MS SQL server may answer with the error message like Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
SocketPro database push library doesn't change any database objects or properties. It sends data from database onto a remote SocketPro server only after starting a client socket connection. Actually, there is absolutely no possibility to change any database objects or properties. Therefore, it is absolutely safe from security view.