Re: Track database changes
Well, there is some info about SQLExecDirect and SQL_DATA_AT_EXEC here: https://docs.microsoft.com/en-us/sql...ql-server-2017
Re: Track database changes
VictorN,
I'm passing SQL_NTS to the call. Here is the code:
Code:
std::wstring query8 = L"IF EXISTS(SELECT * FROM sys.databases WHERE name = \'" + pimpl->m_dbName + L"\' AND is_broker_enabled = 0) ALTER DATABASE " + pimpl->m_dbName + L" SET ENABLE_BROKER";
std::wstring query9 = L"CREATE QUEUE dbo.EventNotificationQueue";
SQLWCHAR *query;
query = new SQLWCHAR[query8.size() + 2];
memset( query, '\0', query8.size() + 2 );
uc_to_str_cpy( query, query8 );
ret = SQLExecDirect( m_hstmt, query, SQL_NTS );
delete query;
query = NULL;
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
}
The ret value becomes "-2".
As you can see there is no binding, just direct query execution.
I can try to use parameter for the database name , but it looks like the link I'm following suggests that this query should be run from the master database.
So I can try both ways.
Thank you.
Re: Track database changes
I'd first start out by running the scripts in your sample with SSMS. There is quite a bit to get the service broker up and running in sql, so you'll want to make sure it's set up on your sql server instance before you attempt to programmatically attempt to use it.
Any reason you are using sqlexecdirect rather something object oriented and more modern?
Re: Track database changes
Hi,
Quote:
Originally Posted by
Arjay
I'd first start out by running the scripts in your sample with SSMS. There is quite a bit to get the service broker up and running in sql, so you'll want to make sure it's set up on your sql server instance before you attempt to programmatically attempt to use it.
OK, is there any other way track the schema changes from the C++ program?
I heard about DDL triggers, but the link I quoted was the first one I found.
Quote:
Originally Posted by
Arjay
Any reason you are using sqlexecdirect rather something object oriented and more modern?
AFAIK, ODBC is the only portable interface between all 3 major platforms - Windows, *nix and OSX.
In Windows its native, on *nix/Mac you choose between unixODBC, iODBC and some commercial ODBC driver managers.
All those stuff like ADO are completely not portable.
Thank you.
Re: Track database changes
OneEyeMan, the article you linked to is tracking data changes, not schema changes. At any rate, I suggested that you run the scripts inside SQLServer Management Studio (SSMS) before trying to execute them in c++ code. You should resolve any issues in the scripts in SSMS. That way it will be easier when you run the script in c ++ if you know it works to begin with. Be aware that certain database permissions are required to manipulate the service broker. The user that you connect to SSMS or ODBC with will need these permissions.
Re: Track database changes
Hi,
Quote:
Originally Posted by
Arjay
OneEyeMan, the article you linked to is tracking data changes, not schema changes.
I beg to differ.
It is tracking CREATE/ALTER TABLE, which is schema changes, and not the INSERT/UPDATE which is data.
Quote:
Originally Posted by
Arjay
At any rate, I suggested that you run the scripts inside SQLServer Management Studio (SSMS) before trying to execute them in c++ code. You should resolve any issues in the scripts in SSMS. That way it will be easier when you run the script in c ++ if you know it works to begin with. Be aware that certain database permissions are required to manipulate the service broker. The user that you connect to SSMS or ODBC with will need these permissions.
Yes, I understand that.
I did execute that in the SSMS and it looked like everything was working fine.
Now out of curiosity - is there a better way to notify about the table creation/deletion or working with this script is the only way?
If you don't know - that's fine. I will just stick with this solution.
Thank you.
Re: Track database changes
You are correct about the sample tracking schema changes.
Just want to mention that I found that I needed to reregister for events after receiving an event. In other words once I got notified, I wouldn't retrieve more events until I reregistered the table. It seemed odd that I would have needed to do that. YMMV.
As far as other options, the service broker sits on top of message queing (msmq). So at least on Windows, you have an alternative for listening for changes - you can connect to the queue directly through c++ rather than going through sql. Of course, that approach isn't going to portable, and it is doubtful it will be simpler.
Re: Track database changes
Hi,
Quote:
Originally Posted by
Arjay
You are correct about the sample tracking schema changes.
Just want to mention that I found that I needed to reregister for events after receiving an event. In other words once I got notified, I wouldn't retrieve more events until I reregistered the table. It seemed odd that I would have needed to do that. YMMV.
Which version of SQL Server do you have?
Quote:
Originally Posted by
Arjay
As far as other options, the service broker sits on top of message queing (msmq). So at least on Windows, you have an alternative for listening for changes - you can connect to the queue directly through c++ rather than going through sql. Of course, that approach isn't going to portable, and it is doubtful it will be simpler.
I'm actually curious if its possible to do that with ct-lib call. That way I can work with freeTDS and then make it portable. Have any idea how to do that?
Thank you.
Re: Track database changes
Quote:
Originally Posted by
OneEyeMan
Hi,
Which version of SQL Server do you have?
I'm actually curious if its possible to do that with ct-lib call. That way I can work with freeTDS and then make it portable. Have any idea how to do that?
Thank you.
I've used all versions of sql server including 2016. The broker stuff I did was on sql 2008. Don't know anything about ct-lib or freeTDS.
