The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
objSLXDB.ExecuteSQL
Posted: 11 May 10 2:20 PM
|
I wrote a sub that I want to delete rows based on a field value. Here are the two lines I am concerned with:
strSQL = "Delete From ACTIVITY Where Right(USERDEF1, 12) = '" & txtActivityID.Text & "'"
objSLXDB.ExecuteSQL strSQL
When I execute this code as administrator, all rows that match the criteria are deleted. If I log in as me, then only the rows that match the criteria AND are assigned to my userid are deleted. I need to be able to delete all rows that match my criteria.
My question is: Does ExecuteSQL dynamically modify my delete statement at run time to include that current logged in userid? If so, is there a way for me to execute this statement to delete all rows that match the single criterion?
Thanks for any insight and guidance you may offer. |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 12 May 10 2:33 AM
|
No. It is the SLX OLEDB Provider that sometimes adjusts SQL before it is executed. It does this for security reasons (primarily so that users cannot update information to which they do not have access).
You can use SLX Profiler to see what SQL is actually executed.
One way of avoiding this is to code a direct SQL connection to the database and then execute your SQL through that, rather than using the SLX OLEDB provider. This will, however, break synchronisation if you have, or plan to have, any remote users/offices.
|
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 13 May 10 7:42 AM
|
Phil,
Thanks for the reply. Can you tell me where I can get info on how to use the SLX Profiler since that is something I'm not familiar with? I do have remote users so breaking sync issue is a big issue for us. |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 13 May 10 8:08 AM
|
Check for an exe in program files/SalesLogix called (from memory) SLXProfiler.
Just run it and you should be able to work out what's going on. |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 13 May 10 8:28 AM
|
It's actually pretty easy to use. Here is what I get from the profiler when my code executes:
---------- Client SQL ---------
Delete From ACTIVITY Where Right(USERDEF1, 12) = 'Q6UJ9A01QOPV'
---------- Executed SQL ----------
Delete From ACTIVITY Where Right(USERDEF1, 12) = 'Q6UJ9A01QOPV' AND (EXISTS (select 1 from secrights s WHERE ACTIVITY.userid = s.seccodeid and s.accessid = 'U6UJ9A000099') OR ACTIVITY.USERID IN (SELECT ucx.CALUSERID FROM USERCALENDAR ucx WHERE ucx.USERID = 'U6UJ9A000099' and ucx.ALLOWDELETE = 'T') OR (ACTIVITY.USERID IN (SELECT ucx.CALUSERID FROM USERCALENDAR ucx WHERE ucx.USERID = 'U6UJ9A000099' and ucx.ALLOWEDIT = 'T') AND ACTIVITY.USERDEF1 = 'Complete'))
This causes issues since I need to delete all records in the ACTIVITY table, not just my own...
Can you give a quick explanation on how to use the direct connection to execute the sql? Is there any other way to execute my sql as it is written (without SLX OLEDB modifying it)? |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 13 May 10 8:35 AM
|
| Before I do that, is this something that you want your users to do too, or just something for you? If just you, why not log in as ADMIN when you need to do it? Alternatively, just make sure that you have access to everything via Administrator and it should work without you having to change anything. |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 13 May 10 9:11 AM
|
| This is something that the users would need to do. Essentially, I created a customization that will allow the user to create activities for a group of users at one time, and the creator of the group should be able to delete or edit all the activities for the group. ADMIN can do this as you pointed out, but I need for the users to do it. |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 13 May 10 9:30 AM
|
OK, never done that.
Could you modify your customisation so that, when it creates the activities, it populates Activity.UserID with the group creator's ID, perhaps? That way, you are remaining within the confines of the provider and you won't break sync. I'm just not sure what implications changing that field might have ... |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 13 May 10 9:43 AM
|
The Activity.UserID is the user who is assigned the activity. It shows up on the activity as the Leader, and I don't think I can change that. The only other fields I have are the Activity.CreateUser and the Activity.ModifyUser. Activity.LeadID does not appear to be used for anything even when using the activity functionality as delivered, and I am using the delivered functionality within the scope of my customization.
Is there any way around the SLX OLEDB issue? |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 13 May 10 10:09 AM
|
I'm thinking that you'll have to create another connection to the db, logging that connection is as ADMIN (so you'll have to put the ADMIN password somewhere ...) and then executing the SQL via that connection, so as not to break sync.
Haven't done anything like that for a while, so it would take me a while to dig out.
Others here will have their own ideas, I'm sure. |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 13 May 10 12:22 PM
|
I was reading about the SLX OLEDB Provider and came across two functions; DBOpenSQLFromDatabase and DBOpenSQLFromDatabasefor. It looks like I should be able to use one of these to make the connection with the ADMIN userid to execute my delete statement successfully. The only thing is that I can't seem to figure out how to write the code to use these functions. I searched this site and the architect for any examples, but I came up empty.
Can I use these functions, and does anyone have any examples using either of these functions? |
|
|
| |
|
Re: objSLXDB.ExecuteSQL
Posted: 14 May 10 12:49 PM
|
I got this to work using a new connection, but I hard-coded the credentials.
Conn.Open "OLE DB Services = -1 rovider=SLXOLEDB.1ersist Security Info=Trueassword=<'pwd value'>;User ID=admin;Initial Catalog=;Data Source=;Extended Properties='PORT=1706;LOG=ON';"
Is there a way to read the password value into a variable that I can use in the connection string (replace Password=<'pwd value'>)? |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 20 May 10 1:16 AM
|
create a table to store your connection details (server, db, user, password) Then create a manage form available from a admin released menu that you can add/edit the connection details whenever you need to in the client.
You can use the SLXRWEL.SLXRWEOBJ if you want to encrypt the password or I believe SQL2008 you can set encryption at the DB level. You can retrieve the details before creating your connection string. |
|
|
|
Re: objSLXDB.ExecuteSQL
Posted: 24 May 10 1:09 PM
|
Leon and Phil,
Thanks for the insight and direction. You were able to help me resolve my issues, and I appreciate your timely responses to my questions. This is a great community. |
|
|
|