|
I have to get the user's acces permission for a specified database!
For that I use the command...PERMISSIONS()
I use ADO for establishing a connection to the database..my code is something like this now..
void main(void)
{
ADODB::_ConnectionPtr m_pConnection = NULL;
ADODB::_RecordsetPtr pRecordset = NULL;
VARIANT *vRecordsAffected = NULL;
char ConStr[500];
char str1[50];
BOOL m_bIsConnectionOpen;
// Create an instance of _Connection
HRESULT hr ;
hr = m_pConnection.CreateInstance(__uuidof(ADODB::Connection));
try
{
if (SUCCEEDED(hr))
{
//Open a connectionConStr[0] = '\0';
strcat(ConStr, "Provider=sqloledb;Server=ie10DT2KCO1473;Initial Catalog=Shree;");
strcat(ConStr,"User Id=sa;Password= ;");
m_pConnection->Open(ConStr, "", "", 0);
//If database opened successfully then set IsConnectionOpen to TRUE
if (SUCCEEDED(hr)) m_bIsConnectionOpen = TRUE;
}
/* sprintf(str1,"PERMISSIONS ( OBJECT_ID('TABLE1') )");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);
}
..
and then the catch function !
Now..
How do i display the return value of the PERMISSIONS function??
kindly help me in this regard!!
ranjani
|
|
|
|
|
I was playing with this right now and in SQL Query Analyzer I had to use it like this (This is from Northwind):
USE Northwind<br />
GO<br />
SELECT PERMISSIONS(OBJECT_ID('Customers'))
This would return a value of 1881108543. This means that in your code you forgot to add SELECT before PERMISSIONS funtion in order to get the result back. Now you have to AND this value with certain other values in order to get the appropriate permission level. For this and the AND values, check the PERMISSIONS function in SQL Books On-Line.
Regards,
Husein
|
|
|
|
|
AND which value with what.
my question is...
how do i get the integer value from it...
when i run the folllowing in query analyzer i get the proper result..i have to do the same in..c++ ,,how do i do it?
USE pubs
IF PERMISSIONS()&2=0x2
PRINT 'The current user can create a table.'
ELSE
PRINT 'The current user cannot create a table.'
thanxxxx
ranjani
|
|
|
|
|
PERMISSIONS function takes two optional parameters.
PERMISSIONS ( [ objectid [ , 'column' ] ] )
If you specify PERMISSIONS, as in the sample you provided in the previous message, you want to know if the user has certain statement permission. These include CREATE DATABASE (master db only), CREATE TABLE, etc. Their permissions are like this:
Bit Statement
0x1 CREATE DATABASE<br />
0x2 CREATE TABLE etc etc
If you specify PERMISSIONS(objectid), then you want to know if the user can perform certain operation on the object, like SELECT, INSERT, DELETE, UPDATE, etc. Again the bits are the same as above 0x1 for SELECT ALL, 0x2 UPDTE ALL, etc.
If you also specify column name with objectid, you are checking if the user can SELECT, UPDATE or reference a certain column.
You should first figure out what is it that you want to check. That is, first determine what kind of permission you want to check for your user. After that build the query in the SQL Query Analyser, and if everything works fine, create a stored procedure that does the calculation and returns a scalar value.
I hope this solves your problem. For more information, check Books On-Line to see the list of the bits used with PERMISSIONS function.
Have fun,
Husein
|
|
|
|
|
how do i create a stored procedure in C++?
my program should for any of the sql servers..
so..manually creating a stored procedure won't work..
so..
i have to create it programatically...how do i go abt doing it?
ranjani
|
|
|
|
|
I have a PL/SQL procedure that returns a index-by table of records.
In the PL/SQL part everything is working fine, but I have no idea how to implement it in my C# code (using OleDB).
I'd like to put the data returning from the procedure into a DataSet.
Does anybody have any idea how to do this?
Many thanks for help in advance.
<br />
<br />
TYPE RECORD_ROUTE IS RECORD<br />
(<br />
ROUTE_VAR VARCHAR2(10),<br />
LP VARCHAR2(2),<br />
NUM NUMBER(3)<br />
);<br />
<br />
<br />
TYPE ROUTES_TYPE IS TABLE OF RECORD_ROUTE INDEX BY BINARY_INTEGER;<br />
<br />
<br />
PROCEDURE ROUTE_VISUAL(RECORDS OUT ROUTES_TYPE);<br />
misiek
|
|
|
|
|
In my c++ code ..i use ADO commands..and to get the permissions..for userss for each and every databse..
i use the following commands..
sprintf(str1,"SELECT PERMISSIONS(OBJECT_ID('TABLE1'))");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);
the abopve function returns an integer value..
how do i read the value??
ranjani
|
|
|
|
|
Use Recordset's Field property and use integer based index (0 in this case) to retrieve the value.
|
|
|
|
|
do u eman to say..use
pRecordset->Fields()-> ..
and what is integer basd index as u have mentioned???
plz reply as soon as poss
thank u in advance
ranjani
|
|
|
|
|
as far as I remember with ADO you would say:
_variant_t vtMyVal;<br />
<br />
vtMyVal = pRecordset->Fields->Item[long(0)]->Value
and this would retrieve the value.
Item[] can take either integer based index (eg. Item[long(0)]->Value) or string based (eg. Item["SomeColumn"]->Value)
I hope this helps.
Best regards,
Husein
|
|
|
|
|
The full code is enclosed herewith!!
When I include the aboce said and run it..the control goes to the catch function..as there's some problem in the above mentioned stt!
void main(void)
{
ADODB::_ConnectionPtr m_pConnection = NULL;
ADODB::_CommandPtr Cmd1;
ADODB::_RecordsetPtr pRecordset = NULL;
VARIANT *vRecordsAffected = NULL;
char ConStr[500];
char str1[50];
_bstr_t bstrProc =( L"sp_StartByteImport" );
BOOL m_bIsConnectionOpen;
// Create an instance of _Connection
HRESULT hr ;
hr = m_pConnection.CreateInstance(__uuidof(ADODB::Connection));
cout << "Connection object created." ;
_variant_t vRecsAffected(0L);
try
{
if (SUCCEEDED(hr))
{
//Open a connection where database is access database : "c:\\emailparsing\\email_details.mdb"
ConStr[0] = '\0';
strcat(ConStr, "Provider=sqloledb;Server=ie10DT2KCO1473;Initial Catalog=Shree;");
strcat(ConStr,"User Id=sa;Password= ;");
/* Provider=Microsoft.Jet.OLEDB.3.51; Data Source="**/
// strcat(ConStr, "C:\\MSSQL7\\Data\\Shree_Data" );
m_pConnection->Open(ConStr, "", "", 0);
//If database opened successfully then set IsConnectionOpen to TRUE
if (SUCCEEDED(hr))
{
m_bIsConnectionOpen = TRUE;
}
}
sprintf(str1,"USE Shree SELECT PERMISSIONS ( OBJECT_ID('TABLE1') )");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);
_variant_t vtMyVal;
vtMyVal = pRecordset->Fields->Item[long(0)]->Value;
printf("\n%s\n\n",vtMyVal);
ConStr[0] = '\0';
strcat(ConStr, "SELECT * FROM ");
strcat(ConStr,"TABLE1");
pRecordset = m_pConnection->Execute(ConStr, vRecordsAffected, 1);
if (!pRecordset->GetEOFile())
{
int i = 0;
while (!pRecordset->GetEOFile())
{
printf("row %d is '%s %s'\n",i,
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("id")->GetValue()),
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("product_name")->GetValue()));
i++;
pRecordset->MoveNext();
}
}
pRecordset->Close();
}
catch (_com_error &e)
{
printf("Description = '%s'\n", (char*) e.Description());
printf("%s\n%s\n%s\n",(char *) e.ErrorMessage(), (char *)e.Source(), (char *)e.Description());
}
::CoUninitialize();
}
ranjani
|
|
|
|
|
The full code is enclosed herewith!!
When I include the aboce said and run it..the control goes to the catch function..as there's some problem in the above mentioned stt!
void main(void)
{
ADODB::_ConnectionPtr m_pConnection = NULL;
ADODB::_CommandPtr Cmd1;
ADODB::_RecordsetPtr pRecordset = NULL;
VARIANT *vRecordsAffected = NULL;
char ConStr[500];
char str1[50];
_bstr_t bstrProc =( L"sp_StartByteImport" );
BOOL m_bIsConnectionOpen;
// Create an instance of _Connection
HRESULT hr ;
hr = m_pConnection.CreateInstance(__uuidof(ADODB::Connection));
cout << "Connection object created." ;
_variant_t vRecsAffected(0L);
try
{
if (SUCCEEDED(hr))
{
//Open a connection where database is access database : "c:\\emailparsing\\email_details.mdb"
ConStr[0] = '\0';
strcat(ConStr, "Provider=sqloledb;Server=ie10DT2KCO1473;Initial Catalog=Shree;");
strcat(ConStr,"User Id=sa;Password= ;");
/* Provider=Microsoft.Jet.OLEDB.3.51; Data Source="**/
// strcat(ConStr, "C:\\MSSQL7\\Data\\Shree_Data" );
m_pConnection->Open(ConStr, "", "", 0);
//If database opened successfully then set IsConnectionOpen to TRUE
if (SUCCEEDED(hr))
{
m_bIsConnectionOpen = TRUE;
}
}
sprintf(str1,"USE Shree SELECT PERMISSIONS ( OBJECT_ID('TABLE1') )");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);
_variant_t vtMyVal;
vtMyVal = pRecordset->Fields->Item[long(0)]->Value;
printf("\n%s\n\n",vtMyVal);
ConStr[0] = '\0';
strcat(ConStr, "SELECT * FROM ");
strcat(ConStr,"TABLE1");
pRecordset = m_pConnection->Execute(ConStr, vRecordsAffected, 1);
if (!pRecordset->GetEOFile())
{
int i = 0;
while (!pRecordset->GetEOFile())
{
printf("row %d is '%s %s'\n",i,
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("id")->GetValue()),
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("product_name")->GetValue()));
i++;
pRecordset->MoveNext();
}
}
pRecordset->Close();
}
catch (_com_error &e)
{
printf("Description = '%s'\n", (char*) e.Description());
printf("%s\n%s\n%s\n",(char *) e.ErrorMessage(), (char *)e.Source(), (char *)e.Description());
}
::CoUninitialize();
}
ranjani
|
|
|
|
|
The first call in the main(void) should be ::CoInitialize(NULL)
This should do the trick.
|
|
|
|
|
yes
i got the answer
by ur answer...
now..can u help me out with sql databse stuffs??
coz''
the return value..that is the integer value i get indicates some permissions assigned to teh user..
how do i check it?
ranjani
|
|
|
|
|
how do i create a stored procedure in C++?
my program should for any of the sql servers..
so..manually creating a stored procedure won't work..
so..
i have to create it programatically...how do i go abt doing it?
ranjani
|
|
|
|
|
Add a new _bstr_t variable and set it's value to "CREATE PROCEDURE ... " and then pass this variable as a parameter to connection's execute method. That could be one way of doing things.
Best regards,
Husein
|
|
|
|
|
AND AFTER DOING THAT..
GET THE RETURN VALUE..AS SAID ABOVE FOR PERMISSIOSN STT IS IT??
ranjani
|
|
|
|
|
iam able to create the stored procedure..
but how do i go about...
rading the return value..
that is my code is now like..
_bstr_t bstrPr = "DROP PROCEDURE bali" ;
pRecordset = m_pConnection->Execute(bstrPr, vRecordsAffected, 1);
_bstr_t bstrp = "CREATE PROCEDURE bali @ioparm int OUTPUT AS \nIF PERMISSIONS()&2=0x2 RETURN 1 ELSE RETURN 0";
pRecordset = m_pConnection->Execute(bstrp, vRecordsAffected, 1);
_bstr_t exect = "EXECUTE bali 2";
pRecordset = m_pConnection->Execute(exect,vRecordsAffected, 1);
_variant_t vtMyVal;
vtMyVal = pRecordset->GetFields()->Item[long(0)]->Value;
printf("\n%d\n",(char *)(_bstr_t)vtMyVal);
Iam not able tor ead the return value from this but..
what mite be the reason for this?
what else shud i add to get it done??
also..
initially..
how do i cehk if a procedure name with the name specified already exists??
ranjani
|
|
|
|
|
ranjjj, how well do you know ADO programming with VC++? In order to get the RETURN_VALUE from the stored procedure, you need to execute it from the recordset and not from the connection. In you recordset object you need to set certain properties, like CommandType and CommandText. If you programmed ADO with VB, it is the same thing with VC++.
regards,
Husein
|
|
|
|
|
Hi,
I need to commit the changes to the database of a dataset containing many (MANY) datatables. A few of them have an auto-increment column, but I need to insert a specific value in the database (which is MS-SQL, by the way).
My code is fine and I send a "SET IDENTITY_INSERT ON" to the database when needed.
Since all the operations I need to perform are very basic and that I have many different tables, I am using the CommandBuilder object. The problem is that the Insert command does NOT include the auto-increment field.
Is there a way to make the extra parameter is added to my InsertCommand? MSDN and the books I have were not of any help.
Thank you very much!
Carl
PS: Here's the code...
<br />
Public Sub CommitTable(ByVal table As DataTable, ByVal transaction As SqlTransaction, Optional ByVal allowIdentityInsert As Boolean = False)<br />
<br />
Dim da As New SqlDataAdapter("SELECT * FROM " + table.TableName, transaction.Connection)<br />
da.SelectCommand.Transaction = transaction<br />
<br />
Dim cmdBuilder As New SqlCommandBuilder(da)<br />
Dim cmd As SqlCommand<br />
<br />
cmdBuilder.QuotePrefix = "["<br />
cmdBuilder.QuoteSuffix = "]"<br />
<br />
da.InsertCommand = cmdBuilder.GetInsertCommand<br />
da.UpdateCommand = cmdBuilder.GetUpdateCommand<br />
da.DeleteCommand = cmdBuilder.GetDeleteCommand<br />
<br />
cmd = New SqlCommand("DELETE FROM " + table.TableName, transaction.Connection, transaction)<br />
cmd.ExecuteNonQuery()<br />
<br />
If allowIdentityInsert Then<br />
cmd.CommandText = "SET IDENTITY_INSERT " + table.TableName + " ON"<br />
cmd.ExecuteNonQuery()<br />
End If<br />
<br />
da.Update(table)<br />
table.AcceptChanges()<br />
<br />
If allowIdentityInsert Then<br />
cmd.CommandText = "SET IDENTITY_INSERT " + table.TableName + " OFF"<br />
cmd.ExecuteNonQuery()<br />
End If<br />
<br />
<br />
End Sub <br />
|
|
|
|
|
Of course the Identity column is not included in your SQL statement. Identity column is an auto-increment column. This is handled by the SQL Server which keeps track of the next number to be added to the identity column. But if you need the value of the Identity column after you added the row, use SELECT @@IDENTITY. Note that you should do this within the Stored Procedure or a batch query. If you send the data from a select statement and then send another statement, in this case SELECT @@IDENTITY, you might not get the right value as another user could have added a new row and the @@IDENTITY would return this new value or you might just get a NULL value.
Best regards,
Husein
|
|
|
|
|
How can I set/change the text in a row header in a data grid.
|
|
|
|
|
ListUserPermissions
in the above mentioned sqldmo function..What value shud passed as the arguments for listing the user access permissions of a sql database???
ranjani
|
|
|
|
|
I believe that this post has the answer to your question. Happy programming! 
|
|
|
|
|
Hi,
I have a DataTable that has a primary key. If I want to find a row by primary key I can use DataTable.Rows.Find (new object [] {id}), or I can perform a row filter like Mytable.DefaultView.RowFilter = "ID = " + id.
It looks like RowFilter is MUCH slower than Find. Any idea why? Does RowFilter perform a linear search and Find does some binary search?
Thanks
Best regards,
Alexandru Savescu
P.S. Interested in art? Visit this!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
