Search Results

Search found 14354 results on 575 pages for 'existing records'.

Page 355/575 | < Previous Page | 351 352 353 354 355 356 357 358 359 360 361 362  | Next Page >

  • SQL Select syntax error

    - by Odette
    hi guys thanks for your help yesterday. I am now trying to incorporate the query from yesterday into an existing query so I can show the highest itemcode's reporting group in the existing query..but I have a syntax error somewhere at my Select statement. ERROR: Keyword SELECT not expected. I have tried putting brackets at every possible place but still no go..can you please help? (ps-this whole query has been giving me nightmares!) WITH CALC1 AS (SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST FROM @[email protected] WHERE OTIT01 < '' UNION ALL SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST FROM @[email protected] WHERE OTIT02 < '' UNION ALL SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQCQ03 * OVRC03,2) AS COST FROM @[email protected] WHERE OTIT03 < '' UNION ALL SELECT OTQUOT, OTIT04 AS ITEMS, ROUND(OQCQ04 * OVRC04,2) AS COST FROM @[email protected] WHERE OTIT04 < '' UNION ALL SELECT OTQUOT, OTIT05 AS ITEMS, ROUND(OQCQ05 * OVRC05,2) AS COST FROM @[email protected] WHERE OTIT05 < '' UNION ALL SELECT OTQUOT, OTIT06 AS ITEMS, ROUND(OQCQ06 * OVRC06,2) AS COST FROM @[email protected] WHERE OTIT06 < '' UNION ALL SELECT OTQUOT, OTIT07 AS ITEMS, ROUND(OQCQ07 * OVRC07,2) AS COST FROM @[email protected] WHERE OTIT07 < '' UNION ALL SELECT OTQUOT, OTIT08 AS ITEMS, ROUND(OQCQ08 * OVRC08,2) AS COST FROM @[email protected] WHERE OTIT08 < '' UNION ALL SELECT OTQUOT, OTIT09 AS ITEMS, ROUND(OQCQ09 * OVRC09,2) AS COST FROM @[email protected] WHERE OTIT09 < '' UNION ALL SELECT OTQUOT, OTIT10 AS ITEMS, ROUND(OQCQ10 * OVRC10,2) AS COST FROM @[email protected] WHERE OTIT10 < '' ) (SELECT OTQUOT, DESC FROM ( SELECT OTQUOT, ITEMS, B.IXRPGP AS GROUP, C.OTRDSC AS DESC, COST, ROW_NUMBER() OVER (ORDER BY COST DESC) AS RN FROM CALC1 AS A INNER JOIN @[email protected] AS B ON (A.ITEMS = B.IKITMC) INNER JOIN DATAGRP.GDSGRP AS C ON (B.IXRPGP = C.OKRPGP) ) T WHERE T.RN = 1) SELECT A.OKPBRN, A.OCAREA, A.OTCCDE, A.OTCNAM, A.OTSMAN, A.OKPBRN||A.OAPNUM AS OTQUOT, A.OTONUM, A.OTCAD1, A.OTCAD2, A.OTCAD3, A.OTPCDE, A.OTDEL1, A.OTDEL2, A.OTDEL3, CHAR(DATE(CASE WHEN SUBSTR(A.ODOQDT,5,4) = '0000' THEN '0001' ELSE SUBSTR(A.ODOQDT,5,4) END ||'-'|| CASE WHEN SUBSTR(A.ODOQDT,4,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,3,2) END ||'-'|| CASE WHEN SUBSTR(A.ODOQDT,1,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,1,2) END), ISO) AS ODOQDT_CCYYMMDD, CHAR(DATE(CASE WHEN SUBSTR(A.ODDELD,7,2) = '' THEN '0001' ELSE '20'||SUBSTR(A.ODDELD,7,2) END ||'-'|| CASE WHEN SUBSTR(A.ODDELD,4,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,4,2) END ||'-'|| CASE WHEN SUBSTR(A.ODDELD,1,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,1,2) END), ISO) AS ODDELD_CCYYMMDD, B.DESC, A.OVQTVL FROM @[email protected] AS A INNER JOIN CALC1 AS B ON (A.OKPBRN||A.OAPNUM = B.OTQUOT) WHERE A.OKPBRN = '@OKPBRN@' AND A.OTCCDE NOT LIKE '*DEP%' AND CHAR(DATE(CASE WHEN SUBSTR(A.ODOQDT,5,4) = '0000' THEN '0001' ELSE SUBSTR (A.ODOQDT,5,4) END ||'-'|| CASE WHEN SUBSTR(A.ODOQDT,4,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,3,2) END ||'-'|| CASE WHEN SUBSTR(A.ODOQDT,1,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,1,2) END), ISO) = CHAR(CURDATE() - 3 MONTH, ISO) AND A.OCQF01 = '0' AND A.OCQF02 = '0' AND A.OCQF04 = '0' AND A.OCQF05 = '0' AND A.OCQF06 = '0' AND A.OCQF07 = '0' AND A.OCQF08 = '0' AND A.OCQF09 = '0' AND A.OCQF10 = '1' AND A.OTCGRP LIKE 'S/%' ORDER BY A.OTSMAN ASC, A.OVQTVL DESC, CHAR(DATE(CASE WHEN SUBSTR(A.ODDELD,7,2) = '' THEN '0001' ELSE '20'||SUBSTR(A.ODDELD,7,2) END ||'-'|| CASE WHEN SUBSTR(A.ODDELD,4,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,4,2) END ||'-'|| CASE WHEN SUBSTR(A.ODDELD,1,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,1,2) END),ISO) ASC

    Read the article

  • change password.......

    - by shimaTun
    I've created a code to change a password. Now it seem contain an error. When I fill in the form to change password, and click save the error message: You forgot enter your userid! Please try again. I really don’t know what the error message means. Please guys. Help me fix it. Here's is the code: <?php # change password.php //set the page title and include the html header. $page_title = 'Change Your Password'; //include('templates/header.inc'); if(isset($_POST['submit'])){//handle the form require_once('connectioncomplaint.php');//connect to the db. //include "connectioncomplaint.php"; //create a function for escaping the data. function escape_data($data){ global $dbc;//need the connection. if(ini_get('magic_quotes_gpc')){ $data=stripslashes($data); } return mysql_real_escape_string($data); }//end function $message=NULL;//create the empty new variable. //check for a username if(empty($_POST['userid'])){ $u=FALSE; $message .='<p> You forgot enter your userid!</p>'; }else{ $u=escape_data($_POST['userid']); } //check for existing password if(empty($_POST['password'])){ $p=FALSE; $message .='<p>You forgot to enter your existing password!</p>'; }else{ $p=escape_data($_POST['password']); } //check for a password and match againts the comfirmed password. if(empty($_POST['password1'])) { $np=FALSE; $message .='<p> you forgot to enter your new password!</p>'; }else{ if($_POST['password1'] == $_POST['password2']){ $np=escape_data($_POST['password1']); }else{ $np=FALSE; $message .='<p> your new password did not match the confirmed new password!</p>'; } } if($u && $p && $np){//if everything's ok. $query="SELECT userid FROM access WHERE (userid='$u' AND password=PASSWORD('$p'))"; $result=@mysql_query($query); $num=mysql_num_rows($result); if($num == 1){ $row=mysql_fetch_array($result, MYSQL_NUM); //make the query $query="UPDATE access SET password=PASSWORD('$np') WHERE userid=$row[0]"; $result=@mysql_query($query);//run the query. if(mysql_affected_rows() == 1) {//if it run ok. //send an email,if desired. echo '<p><b>your password has been changed.</b></p>'; //include('templates/footer.inc');//include the HTML footer. exit();//quit the script. }else{//if it did not run OK. $message= '<p>Your password could not be change due to a system error.We apolpgize for any inconvenience.</p><p>' .mysql_error() .'</p>'; } }else{ $message= '<p> Your username and password do not match our records.</p>'; } mysql_close();//close the database connection. }else{ $message .='<p>Please try again.</p>'; } }//end of the submit conditional. //print the error message if there is one. if(isset($message)){ echo'<font color="red">' , $message, '</font>'; } ?> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

    Read the article

  • An "elegant" way of identifying a field?

    - by Alix
    Hi. I'm writing a system that underlies programmer applications and that needs to detect their access to certain data. I can mostly do so with properties, like this: public class NiceClass { public int x { get; set; } } Then I go in and tweak the get and set accessors so that they handle the accesses appropriately. However this requires that the users (application programmers) define all of their data as properties. If the users want to use pre-existing classes that have "normal" fields (as opposed to properties), I cannot detect those accesses. Example: public class NotSoNiceClass { public int y; } I cannot detect accesses to y. However, I want to allow the use of pre-existing classes. As a compromise the users are responsible for notifying me whenever an access to that kind of data occurs. For example: NotSoNiceClass notSoNice; ... Write(notSoNice.y, 0); // (as opposed to notSoNice.y = 0;) Something like that. Believe me, I've researched this very thoroughly and even directly analysing the bytecode to detect accesses isn't reliable due to possible indirections, etc. I really do need the users to notify me. And now my question: could you recommend an "elegant" way to perform these notifications? (Yes, I know this whole situation isn't "elegant" to begin with; I'm trying not to make it worse ;) ). How would you do it? This is a problem for me because actually the situation is like this: I have the following class: public class SemiNiceClass { public NotSoNiceClass notSoNice { get; set; } public int z { get; set; } } If the user wants to do this: SemiNiceClass semiNice; ... semiNice.notSoNice.y = 0; They must instead do something like this: semiNice.Write("notSoNice").y = 0; Where Write will return a clone of notSoNice, which is what I wanted the set accessor to do anyway. However, using a string is pretty ugly: if later they refactor the field they'll have to go over their Write("notSoNice") accesses and change the string. How can we identify the field? I can only think of strings, ints and enums (i.e., ints again). But: We've already discussed the problem with strings. Ints are a pain. They're even worse because the user needs to remember which int corresponds to which field. Refactoring is equally difficult. Enums (such as NOT_SO_NICE and Z, i.e., the fields of SemiNiceClass) ease refactoring, but they require the user to write an enum per class (SemiNiceClass, etc), with a value per field of the class. It's annoying. I don't want them to hate me ;) So why, I hear you ask, can we not do this (below)? semiNice.Write(semiNice.notSoNice).y = 0; Because I need to know what field is being accessed, and semiNice.notSoNice doesn't identify a field. It's the value of the field, not the field itself. Sigh. I know this is ugly. Believe me ;) I'll greatly appreciate suggestions. Thanks in advance! (Also, I couldn't come up with good tags for this question. Please let me know if you have better ideas, and I'll edit them)

    Read the article

  • .NET Declarative Security: Why is SecurityAction.Deny impossible to work with?

    - by rally25rs
    I've been messing with this for about a day and a half now sifting through .NET reflector and MSDN docs, and can't figure anything out... As it stands in the .NET framework, you can demand that the current Principal belong to a role to be able to execute a method by marking a method like this: [PrincipalPermission(SecurityAction.Demand, Role = "CanEdit")] public void Save() { ... } I am working with an existing security model that already has a "ReadOnly" role defined, so I need to do exactly the opposite of above... block the Save() method if a user is in the "ReadOnly" role. No problem, right? just flip the SecurityAction to .Deny: [PrincipalPermission(SecurityAction.Deny, Role = "ReadOnly")] public void Save() { ... } Well, it turns out that this does nothing at all. The method still runs fine. It seems that the PrincipalPermissionAttribute defines: public override IPermission CreatePermission() But when the attribute is set to SecurityAction.Deny, this method is never called, so no IPermission object is ever created. Does anyone know of a way to get .Deny to work? I've been trying to make a custom secutiry attribute, but even that doesn't work. I tried to get tricky and do: public class MyPermissionAttribute : CodeAccessSecurityAttribute { private SecurityAction securityAction; public MyPermissionAttribute(SecurityAction action) : base(SecurityAction.Demand) { if (action != SecurityAction.Demand && action != SecurityAction.Deny) throw new ArgumentException("Unsupported SecurityAction. Only Demand and Deny are supported."); this.securityAction = action; } public override IPermission CreatePermission() { // do something based on the SecurityAction... } } Notice my attribute constructor always passes SecurityAction.Demand, which is the one action that would work previously. However, even in this case, the CreatePermission() method is still only called when the attribute is set to .Demand, and not .Deny! Maybe the runtime is actually checking the attribute instead of the SecurityAction passed to the CodeAccessSecurityAttribute constructor? I'm not sure what else to try here... anyone have any ideas? You wouldn't think it would be that hard to deny method access based on a role, instead of only demanding it. It really disturbed me that the default PrincipalPermission appears from within an IDE like it would be just fine doing a .Deny, and there is like a 1-liner in the MSDN docs that hint that it won't work. You would think the PrincipalPermissionAttribute constructor would throw an exception immediately if anything other that .Demand is specified, since that could create a big security hole! I never would have realized that .Deny does nothing at all if I hadn't been unit testing! Again, all this stems from having to deal with an existing security model that has a "ReadOnly" role that needs to be denied access, instead of doing it the other way around, where I cna just grant access to a role. Thanks for any help! Quick followup: I can actually make my custom attribute work by doing this: public class MyPermissionAttribute : CodeAccessSecurityAttribute { public SecurityAction SecurityAction { get; set; } public MyPermissionAttribute(SecurityAction action) : base(action) { } public override IPermission CreatePermission() { switch(this.SecurityAction) { ... } // check Demand or Deny } } And decorating the method: [MyPermission(SecurityAction.Demand, SecurityAction = SecurityAction.Deny, Role = "ReadOnly")] public void Save() { ... } But that is terribly ugly, since I'm specifying both Demand and Deny in the same attribute. But it does work... Another interesting note: My custom class extends CodeAccessSecurityAttribute, which in turn only extends SecurityAttribute. If I cnage my custom class to directly extend SecurityAttribute, then nothing at all works. So it seems the runtime is definately looking for only CodeAccessSecurityAttribute instances in the metadata, and does something funny with the SecurityAction specified, even if a custom constructor overrides it.

    Read the article

  • Cannot Create New Team Project TFS2010 TF249063 TF218017

    - by Kodicus
    Server: Windows 2008 R2 Standard Team Foundation Server 2010 WSS 3.0 TFS Configuration: Single Server instalation (including SharePoint) The following error occurs when trying to create a new team project from my local machine. The ://sourcecontrol site and ://sourcecontrol/sites/DefaultCollection/ site appears to be functioning fine and my user is a Site collection administrator on both. I can navigate both sites through a browser on my local machine. Thanks for your help! 2010-04-23T10:01:42 | Module: Internal | Team Foundation Server proxy retrieved | Completion time: 0 seconds 2010-04-23T10:01:42 | Module: Wizard | Retrieved IAuthorizationService proxy | Completion time: 0 seconds 2010-04-23T10:01:42 | Module: Wizard | TF30227: Project creation permissions retrieved | Completion time: 0.109382 seconds 2010-04-23T10:01:42 | Module: Internal | The template information for Team Foundation Server "sourcecontrol\DefaultCollection" was retrieved from the Team Foundation Server. | Completion time: 0.15626 seconds ---begin Exception entry--- Time: 2010-04-23T10:03:24 Module: Wizard Exception Message: TF218017: A SharePoint site could not be created for use as the team project portal. The following error occurred: TF249063: The following Web service is not available: ://sourcecontrol/_vti_bin/TeamFoundationIntegrationService.asmx. This Web service is used for the Team Foundation Server Extensions for SharePoint Products. The underlying error is: The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.. Verify that the following URL points to a valid SharePoint Web application and that the application is available: ://sourcecontrol. If the URL is correct and the Web application is operating normally, verify that a firewall is not blocking access to the Web application. (type TeamFoundationServerException) Exception Stack Trace: at Microsoft.VisualStudio.TeamFoundation.WssSiteCreator.CheckCreateSite(TfsTeamProjectCollection tfsServer, Uri adminUri, Uri siteUri) at Microsoft.VisualStudio.TeamFoundation.WssSiteCreator.ValidateSettings(ProjectCreationContext context) at Microsoft.VisualStudio.TeamFoundation.PortfolioProjectForm.OnFinish() Inner Exception Details: Exception Message: TF249063: The following Web service is not available: ://sourcecontrol/_vti_bin/TeamFoundationIntegrationService.asmx. This Web service is used for the Team Foundation Server Extensions for SharePoint Products. The underlying error is: The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.. Verify that the following URL points to a valid SharePoint Web application and that the application is available: ://sourcecontrol. If the URL is correct and the Web application is operating normally, verify that a firewall is not blocking access to the Web application. (type TeamFoundationServiceUnavailableException) Exception Stack Trace: at Microsoft.TeamFoundation.Client.SharePoint.SharePointTeamFoundationIntegrationService.HandleException(Exception e) at Microsoft.TeamFoundation.Client.SharePoint.SharePointTeamFoundationIntegrationService.CheckUrl(String absolutePath, CheckUrlOptions options, Guid configurationServerId, Guid projectCollectionId) at Microsoft.TeamFoundation.Client.SharePoint.WssUtilities.CheckUrl(ICredentials credentials, Uri adminUrl, Uri siteUrl, CheckUrlOptions options, Guid configurationServerId, Guid projectCollectionId) at Microsoft.TeamFoundation.Client.SharePoint.WssUtilities.CheckCreateSite(TfsConnection tfs, Uri adminUrl, Uri siteUrl) at Microsoft.VisualStudio.TeamFoundation.WssSiteCreator.CheckCreateSite(TfsTeamProjectCollection tfsServer, Uri adminUri, Uri siteUri) Inner Exception Details: Exception Message: The underlying connection was closed: A connection that was expected to be kept alive was closed by the server. (type WebException) Exception Stack Trace: at System.Net.WebRequest.GetResponse() at Microsoft.TeamFoundation.Client.TeamFoundationClientProxyBase.AsyncWebRequest.ExecRequest(Object obj) Inner Exception Details: Exception Message: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (type IOException) Exception Stack Trace: at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.Connection.SyncRead(WebRequest request, Boolean userRetrievedStream, Boolean probeRead) Inner Exception Details: Exception Message: An existing connection was forcibly closed by the remote host (type SocketException) Exception Stack Trace: at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) --- end Exception entry ---

    Read the article

  • JPA IndirectSet changes not reflected in Spring frontend

    - by Jon
    I'm having an issue with Spring JPA and IndirectSets. I have two entities, Parent and Child, defined below. I have a Spring form in which I'm trying to create a new Child and link it to an existing Parent, then have everything reflected in the database and in the web interface. What's happening is that it gets put into the database, but the UI doesn't seem to agree. The two entities that are linked to each other in a OneToMany relationship like so: @Entity @Table(name = "parent", catalog = "myschema", uniqueConstraints = @UniqueConstraint(columnNames = "ChildLinkID")) public class Parent { private Integer id; private String childLinkID; private Set<Child> children = new HashSet<Child>(0); @Id @GeneratedValue(strategy = IDENTITY) @Column(name = "id", unique = true, nullable = false) public Integer getId() { return this.id; } public void setId(Integer id) { this.id = id; } @Column(name = "ChildLinkID", unique = true, nullable = false, length = 6) public String getChildLinkID() { return this.childLinkID; } public void setChildLinkID(String childLinkID) { this.childLinkID = childLinkID; } @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "parent") public Set<Child> getChildren() { return this.children; } public void setChildren(Set<Child> children) { this.children = children; } } @Entity @Table(name = "child", catalog = "myschema") public class Child extends private Integer id; private Parent parent; @Id @GeneratedValue(strategy = IDENTITY) @Column(name = "id", unique = true, nullable = false) public Integer getId() { return this.id; } public void setId(Integer id) { this.id = id; } @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "ChildLinkID", referencedColumnName = "ChildLinkID", nullable = false) public Parent getParent() { return this.parent; } public void setParent(Parent parent) { this.parent = parent; } } And of course, assorted simple properties on each of them. Now, the problem is that when I edit those simple properties from my Spring interface, everything works beautifully. I can persist new entities of these types and they'll appear when using the JPATemplate to do a find on, say, all Parents (getJpaTemplate().find("select p from Parent p")) or on individual entities by ID or another property. The problem I'm running into is that now, I'm trying to create a new Child linked to an existing Parent through a link from the Parent's page. Here's the important bits of the Controller (note that I've placed the JPA foo in the controller here to make it clearer; the actual JpaDaoSupport is actually in another class, appropriately tiered): protected Object formBackingObject(HttpServletRequest request) throws Exception { String parentArg = request.getParameter("parent"); int parentId = Integer.parseInt(parentArg); Parent parent = getJpaTemplate().find(Parent.class, parentId); Child child = new Child(); child.setParent(parent); NewChildCommand command = new NewChildCommand(); command.setChild(child); return command; } protected ModelAndView onSubmit(Object cmd) throws Exception { NewChildCommand command = (NewChildCommand)cmd; Child child = command.getChild(); child.getParent().getChildren().add(child); getJpaTemplate().merge(child); return new ModelAndView(new RedirectView(getSuccessView())); } Like I said, I can run through the form and fill in the new values for the Child -- the Parent's details aren't even displayed. When it gets back to the controller, it goes through and saves it to the underlying database, but the interface never reflects it. Once I restart the app, it's all there and populated appropriately. What can I do to clear this up? I've tried to call extra merges, tried refreshes (which gave a transaction exception), everything short of just writing my own database access code. I've made sure that every class has an appropriate equals() and hashCode(), have full JPA debugging on to see that it's making appropriate SQL calls (it doesn't seem to make any new calls to the Child table) and stepped through in the debugger (it's all in IndirectSets, as expected, and between saving and displaying the Parent the object takes on a new memory address). What's my next step?

    Read the article

  • I'm trying to handle the updates on 2 related tables in one DetailsView using Jquery and Linq, and h

    - by Ben Reisner
    Given two related tables (reports, report_fields) where there can be many report_fields entries for each reports entry, I need to allow the user to enter new report_fields, delete existing report_fields, and re-arrange the order. Currently I am using a DetailsView to handle the editing of the reports. I added some logic to handle report_fields, and currently it allows you to succesfully re-arrange the order, but i'm a little stumped as to the best way to add new items, or delete existing items. The basic logic I have is that each report_fields is represented by a . It has a description as the text, and a field for each field in the report_fields table. I use JQuery Sortable to allow the user to re-arrange the LIs. Abbreviated Create Table Statements:(foreign key constraint ignored for brevity) create table report( id integer primary key identity, reportname varchar(250) ) create table report_fields( id integer primary key identity, reportID integer, keyname integer, keyvalue integer, field_order integer ) My abbreviated markup: <asp:DetailsView ...> ... <asp:TemplateField HeaderText="Fields"> <EditItemTemplate> <ul class="MySortable"> <asp:Repeater ID="Repeater1" runat="server" DataSource='<%# Eval("report_fields") %>'> <ItemTemplate> <li> <%# Eval("keyname") %>: <%# Eval("keyvalue") %> <input type="hidden" name="keyname[]" value='<%# Eval("keyname") %>' /> <input type="hidden" name="keyvalue[]" value='<%# Eval("keyvalue") %>' /> </li> </ItemTemplate> </asp:Repeater> </ul> </EditItemTemplate> </asp:TemplateField> </asp:DetailsView> <asp:LinqDataSource ID="LinqDataSource2" onupdating="LinqDataSource2_Updating" table=reports ... /> $(function() { $(".MySortable").sortable({ placeholder: 'MySortable-highlight' }).disableSelection(); }); Code Behind Class: public partial class Administration_AddEditReport protected void LinqDataSource2_Updating(object sender, LinqDataSourceUpdateEventArgs e) { report r = (report)e.NewObject; MyDataContext dc = new MyDataContext(); var fields = from f in dc.report_fields where f.reportID == r.id select f; dc.report_fields.DeleteAllOnSubmit(fields); NameValueCollection nvc = Request.Params; string[] keyname = nvc["keyname[]"].Split(','); string[] keyvalue = nvc["keyvalue[]"].Split(','); for (int i = 0; i < keyname.Length; i++) { report_field rf = new report_field(); rf.reportID = r.id; rf.keyname = keyname[i]; rf.keyvalue = keyvalue[i]; rf.field_order = i; dc.report_fields.InsertOnSubmit(rf); } dc.SubmitChanges(); } }

    Read the article

  • Log a user in to an ASP.net application using Windows Authentication without using Windows Authentic

    - by Rising Star
    I have an ASP.net application I'm developing authentication for. I am using an existing cookie-based log on system to log users in to the system. The application runs as an anonymous account and then checks the cookie when the user wants to do something restricted. This is working fine. However, there is one caveat: I've been told that for each page that connects to our SQL server, I need to make it so that the user connects using an Active Directory account. because the system I'm using is cookie based, the user isn't logged in to Active Directory. Therefore, I use impersonation to connect to the server as a specific account. However, the powers that be here don't like impersonation; they say that it clutters up the code. I agree, but I've found no way around this. It seems that the only way that a user can be logged in to an ASP.net application is by either connecting with Internet Explorer from a machine where the user is logged in with their Active Directory account or by typing an Active Directory username and password. Neither of these two are workable in my application. I think it would be nice if I could make it so that when a user logs in and receives the cookie (which actually comes from a separate log on application, by the way), there could be some code run which tells the application to perform all network operations as the user's Active Directory account, just as if they had typed an Active Directory username and password. It seems like this ought to be possible somehow, but the solution evades me. How can I make this work? Update To those who have responded so far, I apologize for the confusion I have caused. The responses I've received indicate that you've misunderstood the question, so please allow me to clarify. I have no control over the requirement that users must perform network operations (such as SQL queries) using Active Directory accounts. I've been told several times (online and in meat-space) that this is an unusual requirement and possibly bad practice. I also have no control over the requirement that users must log in using the existing cookie-based log on application. I understand that in an ideal MS ecosystem, I would simply dis-allow anonymous access in my IIS settings and users would log in using Windows Authentication. This is not the case. The current system is that as far as IIS is concerned, the user logs in anonymously (even though they supply credentials which result in the issuance of a cookie) and we must programmatically check the cookie to see if the user has access to any restricted resources. In times past, we have simply used a single SQL account to perform all queries. My direct supervisor (who has many years of experience with this sort of thing) wants to change this. He says that if each user has his own AD account to perform SQL queries, it gives us more of a trail to follow if someone tries to do something wrong. The closest thing I've managed to come up with is using WIF to give the user a claim to a specific Active Directory account, but I still have to use impersonation because even still, the ASP.net process presents anonymous credentials to the SQL server. It boils down to this: Can I log users in with Active Directory accounts in my ASP.net application without having the users manually enter their AD credentials? (Windows Authentication)

    Read the article

  • How do I get through proxy server environments for non-standard services?

    - by Ripred
    I'm not real hip on exactly what role(s) today's proxy servers can play and I'm learning so go easy on me :-) I have a client/server system I have written using a homegrown protocol and need to enhance the client side to negotiate its way out of a proxy environment. I have an existing client and server system written in C and C++ for the speed and a small amount of MFC in the client to handle the user interface. I have written both the server and client side of the system on Windows (the people I work for are mainly web developers using Windows everything - not a choice) sticking to Berkeley Sockets as it were via wsock32 for efficiency. The clients connect to the server through a nonstandard port (even though using port 80 is an option to get out of some environments but the protocol that goes over it isn't HTTP). The TCP connection(s) stay open for the duration of the clients participation in real time conferences. Our customer base is expanding to all kinds of networked environments. I have been able to solve a lot of problems by adding the ability to connect securely over port 443 and using secure sockets which allows the protocol to pass through a lot environments since the internal packets can't be sniffed. But more and more of our customers are behind a proxy server environment and my direct connections don't make it through. My old school understanding of proxy servers is that they act as a proxy for external HTML content over HTTP, possibly locally caching popular material for faster local access, and also allowing their IT staff to blacklist certain destination sites. Customer are complaining that my software doesn't recognize and easily navigate its way through their proxy environments but I'm finding it difficult to decide what my "best fit" solution should be. My software doesn't tear down the connection after each client request, and on top of that packets can come from either side at any time, basically your typical custom client/server system for a specific niche. My first reaction is "why can't they just add my servers addresses to their white list" but if there is a programmatic way I can get through without requiring their IT staff to help it is politically better and arguably a better solution anyway. Plus maybe I'm still not understanding the role and purpose of what proxy servers and environments have grown to be these days. My first attempt at a solution was to use WinInet with its various proxy capabilities to establish a connection over port 80 to my non-standard protocol server (which knows enough to recognize and answer a simple HTTP-looking GET request and answer it with a simple HTTP response page to get around some environments that employ initial packet sniffing (DPI)). I retrieved the actual SOCKET handle behind WinInet's HINTERNET request object and had hoped to use that in place of my software's existing SOCKET connection and hopefully not need to change much more on the client side. It initially seemed to be my solution but on further inspection it seems that the OS gets first-chance at the received data on this socket since when I get notified of events via the standard select(...) statement on the socket and query the size of the data available via ioctlsocket the call succeeds but returns 0 bytes available, the reads don't work and it goes downhill from there. Can someone tell me of a client-side library (commercial is fine) will let me get past these proxy server environments with as little user and IT staff help as possible? From what I read it has grown past SOCKS and I figure someone has to have solved this problem before me. Thanks for reading my long-winded question, Ripred

    Read the article

  • Generated LinqtoSql Sql 5x slower than SAME EXACT hand-written sql

    - by JasonM
    I have a sql statement which is hardcoded in an existing VB6 app. I'm upgrading a new version in C# and using Linq To Sql. I was able to get LinqToSql to generate the same sql (before I start refactoring), but for some reason the Sql generated by LinqToSql is 5x slower than the original sql. This is running the generated Sql Directly in LinqPad. The only real difference my meager sql eyes can spot is the WITH (NOLOCK), which if I add into the LinqToSql generated sql, makes no difference. Can someone point out what I'm doing wrong here? Thanks! Existing Hard Coded Sql (5.0 Seconds) SELECT DISTINCT CH.ClaimNum, CH.AcnProvID, CH.AcnPatID, CH.TinNum, CH.Diag1, CH.GroupNum, CH.AllowedTotal FROM Claims.dbo.T_ClaimsHeader AS CH WITH (NOLOCK) WHERE CH.ContractID IN ('123A','123B','123C','123D','123E','123F','123G','123H') AND ( ( (CH.Transmited Is Null or CH.Transmited = '') AND CH.DateTransmit Is Null AND CH.EobDate Is Null AND CH.ProcessFlag IN ('Y','E') AND CH.DataSource NOT IN ('A','EC','EU') AND CH.AllowedTotal > 0 ) ) ORDER BY CH.AcnPatID, CH.ClaimNum Generated Sql from LinqToSql (27.6 Seconds) -- Region Parameters DECLARE @p0 NVarChar(4) SET @p0 = '123A' DECLARE @p1 NVarChar(4) SET @p1 = '123B' DECLARE @p2 NVarChar(4) SET @p2 = '123C' DECLARE @p3 NVarChar(4) SET @p3 = '123D' DECLARE @p4 NVarChar(4) SET @p4 = '123E' DECLARE @p5 NVarChar(4) SET @p5 = '123F' DECLARE @p6 NVarChar(4) SET @p6 = '123G' DECLARE @p7 NVarChar(4) SET @p7 = '123H' DECLARE @p8 VarChar(1) SET @p8 = '' DECLARE @p9 NVarChar(1) SET @p9 = 'Y' DECLARE @p10 NVarChar(1) SET @p10 = 'E' DECLARE @p11 NVarChar(1) SET @p11 = 'A' DECLARE @p12 NVarChar(2) SET @p12 = 'EC' DECLARE @p13 NVarChar(2) SET @p13 = 'EU' DECLARE @p14 Decimal(5,4) SET @p14 = 0 -- EndRegion SELECT DISTINCT [t0].[ClaimNum], [t0].[acnprovid] AS [AcnProvID], [t0].[acnpatid] AS [AcnPatID], [t0].[tinnum] AS [TinNum], [t0].[diag1] AS [Diag1], [t0].[GroupNum], [t0].[allowedtotal] AS [AllowedTotal] FROM [Claims].[dbo].[T_ClaimsHeader] AS [t0] WHERE ([t0].[contractid] IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7)) AND (([t0].[Transmited] IS NULL) OR ([t0].[Transmited] = @p8)) AND ([t0].[DATETRANSMIT] IS NULL) AND ([t0].[EOBDATE] IS NULL) AND ([t0].[PROCESSFLAG] IN (@p9, @p10)) AND (NOT ([t0].[DataSource] IN (@p11, @p12, @p13))) AND ([t0].[allowedtotal] > @p14) ORDER BY [t0].[acnpatid], [t0].[ClaimNum] New LinqToSql Code (30+ seconds... Times out ) var contractIds = T_ContractDatas.Where(x => x.EdiSubmissionGroupID == "123-01").Select(x => x.CONTRACTID).ToList(); var processFlags = new List<string> {"Y","E"}; var dataSource = new List<string> {"A","EC","EU"}; var results = (from claims in T_ClaimsHeaders where contractIds.Contains(claims.contractid) && (claims.Transmited == null || claims.Transmited == string.Empty ) && claims.DATETRANSMIT == null && claims.EOBDATE == null && processFlags.Contains(claims.PROCESSFLAG) && !dataSource.Contains(claims.DataSource) && claims.allowedtotal > 0 select new { ClaimNum = claims.ClaimNum, AcnProvID = claims.acnprovid, AcnPatID = claims.acnpatid, TinNum = claims.tinnum, Diag1 = claims.diag1, GroupNum = claims.GroupNum, AllowedTotal = claims.allowedtotal }).OrderBy(x => x.ClaimNum).OrderBy(x => x.AcnPatID).Distinct(); I'm using the list of constants above to make LinqToSql Generate IN ('xxx','xxx',etc) Otherwise it uses subqueries which are just as slow...

    Read the article

  • Different setter behavior between DbContext and ObjectContext

    - by Paul
    (This is using EntityFramework 4.2 CTP) I haven't found any references to this on the web yet, although it's likely I'm using the wrong terminology while searching. There's also a very likely scenario where this is 100% expected behavior, just looking for confirmation and would rather not dig through the tt template (still new to this). Assuming I have a class with a boolean field called Active and I have one row that already has this value set to true. I have code that executes to set said field to True regardless of it's existing value. If I use DbContext to update the value to True no update is made. If I use ObjectContext to update the value an update is made regardless of the existing value. This is happening in the exact same EDMX, all I did was change the code generation template from DbContext to EntityObject. Update: Ok, found the confirmation I was looking for...consider this a dupe...next time I'll do MOAR SEARCHING! Entity Framework: Cancel a property change if no change in value ** Update 2: ** Problem: the default tt template wraps the "if (this != value)" in the setter with "if (iskey), so only primarykey fields receive this logic. Solution: it's not the most graceful thing, but I removed this check...we'll see how it pans out in real usage. I included the entire tt template, my changes are denoted with "**"... //////// //////// Write SimpleType Properties. //////// private void WriteSimpleTypeProperty(EdmProperty simpleProperty, CodeGenerationTools code) { MetadataTools ef = new MetadataTools(this); #> /// <summary> /// <#=SummaryComment(simpleProperty)#> /// </summary><#=LongDescriptionCommentElement(simpleProperty, 1)#> [EdmScalarPropertyAttribute(EntityKeyProperty= <#=code.CreateLiteral(ef.IsKey(simpleProperty))#>, IsNullable=<#=code.CreateLiteral(ef.IsNullable(simpleProperty))#>)] [DataMemberAttribute()] <#=code.SpaceAfter(NewModifier(simpleProperty))#><#=Accessibility.ForProperty(simpleProperty)#> <#=MultiSchemaEscape(simpleProperty.TypeUsage, code)#> <#=code.Escape(simpleProperty)#> { <#=code.SpaceAfter(Accessibility.ForGetter(simpleProperty))#>get { <#+ if (ef.ClrType(simpleProperty.TypeUsage) == typeof(byte[])) { #> return StructuralObject.GetValidValue(<#=code.FieldName(simpleProperty)#>); <#+ } else { #> return <#=code.FieldName(simpleProperty)#>; <#+ } #> } <#=code.SpaceAfter(Accessibility.ForSetter((simpleProperty)))#>set { <#+ **//if (ef.IsKey(simpleProperty)) **//{ if (ef.ClrType(simpleProperty.TypeUsage) == typeof(byte[])) { #> if (!StructuralObject.BinaryEquals(<#=code.FieldName(simpleProperty)#>, value)) <#+ } else { #> if (<#=code.FieldName(simpleProperty)#> != value) <#+ } #> { <#+ PushIndent(CodeRegion.GetIndent(1)); **//} #> <#=ChangingMethodName(simpleProperty)#>(value); ReportPropertyChanging("<#=simpleProperty.Name#>"); <#=code.FieldName(simpleProperty)#> = <#=CastToEnumType(simpleProperty.TypeUsage, code)#>StructuralObject.SetValidValue(<#=CastToUnderlyingType(simpleProperty.TypeUsage, code)#>value<#=OptionalNullableParameterForSetValidValue(simpleProperty, code)#>, "<#=simpleProperty.Name#>"); ReportPropertyChanged("<#=simpleProperty.Name#>"); <#=ChangedMethodName(simpleProperty)#>(); <#+ //if (ef.IsKey(simpleProperty)) //{ PopIndent(); #> } <#+ //} #> } }

    Read the article

  • Merging .net object graph

    - by Tiju John
    Hi guys, has anyone come across any scenario wherein you needed to merge one object with another object of same type, merging the complete object graph. for e.g. If i have a person object and one person object is having first name and other the last name, some way to merge both the objects into a single object. public class Person { public Int32 Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } } public class MyClass { //both instances refer to the same person, probably coming from different sources Person obj1 = new Person(); obj1.Id=1; obj1.FirstName = "Tiju"; Person obj2 = new Person(); ojb2.Id=1; obj2.LastName = "John"; //some way of merging both the object obj1.MergeObject(obj2); //?? //obj1.Id // = 1 //obj1.FirstName // = "Tiju" //obj1.LastName // = "John" } I had come across such type of requirement and I wrote an extension method to do the same. public static class ExtensionMethods { private const string Key = "Id"; public static IList MergeList(this IList source, IList target) { Dictionary itemData = new Dictionary(); //fill the dictionary for existing list string temp = null; foreach (object item in source) { temp = GetKeyOfRecord(item); if (!String.IsNullOrEmpty(temp)) itemData[temp] = item; } //if the same id exists, merge the object, otherwise add to the existing list. foreach (object item in target) { temp = GetKeyOfRecord(item); if (!String.IsNullOrEmpty(temp) && itemData.ContainsKey(temp)) itemData[temp].MergeObject(item); else source.Add(item); } return source; } private static string GetKeyOfRecord(object o) { string keyValue = null; Type pointType = o.GetType(); if (pointType != null) foreach (PropertyInfo propertyItem in pointType.GetProperties()) { if (propertyItem.Name == Key) { keyValue = (string)propertyItem.GetValue(o, null); } } return keyValue; } public static object MergeObject(this object source, object target) { if (source != null && target != null) { Type typeSource = source.GetType(); Type typeTarget = target.GetType(); //if both types are same, try to merge if (typeSource != null && typeTarget != null && typeSource.FullName == typeTarget.FullName) if (typeSource.IsClass && !typeSource.Namespace.Equals("System", StringComparison.InvariantCulture)) { PropertyInfo[] propertyList = typeSource.GetProperties(); for (int index = 0; index < propertyList.Length; index++) { Type tempPropertySourceValueType = null; object tempPropertySourceValue = null; Type tempPropertyTargetValueType = null; object tempPropertyTargetValue = null; //get rid of indexers if (propertyList[index].GetIndexParameters().Length == 0) { tempPropertySourceValue = propertyList[index].GetValue(source, null); tempPropertyTargetValue = propertyList[index].GetValue(target, null); } if (tempPropertySourceValue != null) tempPropertySourceValueType = tempPropertySourceValue.GetType(); if (tempPropertyTargetValue != null) tempPropertyTargetValueType = tempPropertyTargetValue.GetType(); //if the property is a list IList ilistSource = tempPropertySourceValue as IList; IList ilistTarget = tempPropertyTargetValue as IList; if (ilistSource != null || ilistTarget != null) { if (ilistSource != null) ilistSource.MergeList(ilistTarget); else propertyList[index].SetValue(source, ilistTarget, null); } //if the property is a Dto else if (tempPropertySourceValue != null || tempPropertyTargetValue != null) { if (tempPropertySourceValue != null) tempPropertySourceValue.MergeObject(tempPropertyTargetValue); else propertyList[index].SetValue(source, tempPropertyTargetValue, null); } } } } return source; } } However, this works when the source property is null, if target has it, it will copy that to source. IT can still be improved to merge when inconsistencies are there e.g. if FirstName="Tiju" and FirstName="John" Any commments appreciated. Thanks TJ

    Read the article

  • data not reloading into tableview from core data on minor update

    - by Martin KS
    I've got a basic photo album application, on the first view a list of albums is displayed with a subtitle showing how many images are in each album. I've got everything working to add albums, and add images to albums. The problem is that the image count lines are accurate whenever the app loads, but I can't get them to update during execution. The following viewdidload correctly populates all lines of the tableview when the app loads: - (void)viewDidLoad { [super viewDidLoad]; // Set the title. self.title = @"Photo albums"; // Configure the add and edit buttons. self.navigationItem.leftBarButtonItem = self.editButtonItem; addButton = [[UIBarButtonItem alloc] initWithBarButtonSystemItem:UIBarButtonSystemItemAdd target:self action:@selector(addAlbum)]; addButton.enabled = YES; self.navigationItem.rightBarButtonItem = addButton; /* Fetch existing albums. Create a fetch request; find the Album entity and assign it to the request; add a sort descriptor; then execute the fetch. */ NSFetchRequest *request = [[NSFetchRequest alloc] init]; NSEntityDescription *entity = [NSEntityDescription entityForName:@"Album" inManagedObjectContext:managedObjectContext]; [request setEntity:entity]; // Order the albums by name. NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"albumName" ascending:NO]; NSArray *sortDescriptors = [[NSArray alloc] initWithObjects:sortDescriptor, nil]; [request setSortDescriptors:sortDescriptors]; [sortDescriptor release]; [sortDescriptors release]; // Execute the fetch -- create a mutable copy of the result. NSError *error = nil; NSMutableArray *mutableFetchResults = [[managedObjectContext executeFetchRequest:request error:&error] mutableCopy]; if (mutableFetchResults == nil) { // Handle the error. } LocationsAppDelegate *mainDelegate = (LocationsAppDelegate *)[[UIApplication sharedApplication] delegate]; // Set master albums array to the mutable array, then clean up. [mainDelegate setAlbumsArray:mutableFetchResults]; [mutableFetchResults release]; [request release]; } But when I run similar code inside viewdidappear, nothing happens: { /* Fetch existing albums. Create a fetch request; find the Album entity and assign it to the request; add a sort descriptor; then execute the fetch. */ NSFetchRequest *request = [[NSFetchRequest alloc] init]; NSEntityDescription *entity = [NSEntityDescription entityForName:@"Album" inManagedObjectContext:managedObjectContext]; [request setEntity:entity]; // Order the albums by creation date, most recent first. NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"albumName" ascending:NO]; NSArray *sortDescriptors = [[NSArray alloc] initWithObjects:sortDescriptor, nil]; [request setSortDescriptors:sortDescriptors]; [sortDescriptor release]; [sortDescriptors release]; // Execute the fetch -- create a mutable copy of the result. NSError *error = nil; NSMutableArray *mutableFetchResults = [[managedObjectContext executeFetchRequest:request error:&error] mutableCopy]; if (mutableFetchResults == nil) { // Handle the error. } LocationsAppDelegate *mainDelegate = (LocationsAppDelegate *)[[UIApplication sharedApplication] delegate]; // Set master albums array to the mutable array, then clean up. [mainDelegate setAlbumsArray:mutableFetchResults]; [self.tableView reloadData]; [mutableFetchResults release]; [request release]; } Apologies if I've missed the answer to this question elsewhere, but what am I missing?

    Read the article

  • What's a clean way to have the server return a JavaScript function which would then be invoked?

    - by Khnle
    My application is architected as a host of plug-ins that have not yet been written. There's a long reason for this, but with each new year, the business logic will be different and we don't know what it will be like (Think of TurboTax if that helps). The plug-ins consist of both server and client components. The server components deals with business logic and persisting the data into database tables which will be created at a later time as well. The JavaScript manipulates the DOM for the browsers to render afterward. Each plugin lives in a separate assembly, so that they won't disturb the main application, i.e., we don't want to recompile the main application. Long story short, I am looking for a way to return JavaScript functions to the client from an Ajax get request, and execute these JavaScript functions (which are just returned). Invoking a function in Javascript is easy. The hard part is how to organize or structure so that I won't have to deal with maintenance problem. So concat using StringBuilder to end up with JavaScript code as a result of calling toString() from the string builder object is out of the question. I want to have no difference between writing JavaScript codes normally and writing Javascript codes for this dynamic purpose. An alternative is to manipulate the DOM on the server side, but I doubt that it would be as elegantly as using jQuery on the client side. I am open for a C# library that supports chainable calls like jQuery that also manipulates the DOM too. Do you have any idea or is it too much to ask or are you too confused? Edit1: The point is to avoid recompiling, hence the plug-ins architecture. In some other parts of the program, I already use the concept of dynamically loading Javascript files. That works fine. What I am looking here is somewhere in the middle of the program when an Ajax request is sent to the server. Edit 2: To illustrate my question: Normally, you would see the following code. An Ajax request is sent to the server, a JSON result is returned to the client which then uses jQuery to manipulate the DOM (creating tag and adding to the container in this case). $.ajax({ type: 'get', url: someUrl, data: {'': ''}, success: function(data) { var ul = $('<ul>').appendTo(container); var decoded = $.parseJSON(data); $.each(decoded, function(i, e) { var li = $('<li>').text(e.FIELD1 + ',' + e.FIELD2 + ',' + e.FIELD3); ul.append(li); }); } }); The above is extremely simple. But next year, what the server returns is totally different and how the data to be rendered would also be different. In a way, this is what I want: var container = $('#some-existing-element-on-the-page'); $.ajax({ type: 'get', url: someUrl, data: {'': ''}, success: function(data) { var decoded = $.parseJSON(data); var fx = decoded.fx; var data = decode.data; //fx is the dynamic function that create the DOM from the data and append to the existing container fx(container, data); } }); I don't need to know, at this time what data would be like, but in the future I will, and I can then write fx accordingly.

    Read the article

  • jQuery - animating 'left' position of absolutely positioned div when sliding panel is revealed

    - by trickymatt
    Hello, I have a hidden panel off the left side of the screen which slides into view on the click of a 'tab' positioned on the left side of the screen. I need the panel to slide over the top of the existing page content, and I need the tab to move with it. and so both are absolutely positioned in css. Everything works fine, apart from I need the tab (and thus the tab-container) to move left with the panel when it is revealed, so it appears to be stuck to the right-hand-side of the panel. Its relatively simple when using floats, but of course this affects the layout of the existing content, hence absolute positioning. I have tried animating the left position of the panel-container (see the documented jquery function), but I cant get it to work. My HTML <div><!--sample page content--> <p> Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et </p> </div> <div id="panel" class="height"> <!--the hidden panel --> <div class="content"> <p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore</p> </div> </div> <!--if javascript is disabled use this link--> <div id="tab-container" class="height"> <a href="#" onclick="return()"> <div id="tab"><!-- this will activate the panel. --></div> </a> </div> My jQuery $(document).ready(function(){ $("#panel, .content").hide(); //hides the panel and content from the user $('#tab').toggle(function(){ //adding a toggle function to the #tab $('#panel').stop().animate({width:"400px", opacity:0.8}, 100, //sliding the #panel to 400px // THIS NEXT FUNCTION DOES NOT WORK --> function() { $('#tab-container').animate({left:"400px"} //400px to match the panel width }); function() { $('.content').fadeIn('slow'); //slides the content into view. }); }, function(){ //when the #tab is next cliked $('.content').fadeOut('slow', function() { //fade out the content $('#panel').stop().animate({width:"0", opacity:0.1}, 500); //slide the #panel back to a width of 0 }); }); }); and this is the css #panel { position:absolute; left:0px; top:50px; background-color:#999999; height:500px; display:none;/*hide the panel if Javascript is not running*/ } #panel .content { width:290px; margin-left:30px; } #tab-container{ position:absolute; top:20px; width:50px; height:620px; background:#161616; } #tab { width:50px; height:150px; margin-top:100px; display:block; cursor:pointer; background:#DDD; } Many thanks

    Read the article

  • hosting simple python scripts in a container to handle concurrency, configuration, caching, etc.

    - by Justin Grant
    My first real-world Python project is to write a simple framework (or re-use/adapt an existing one) which can wrap small python scripts (which are used to gather custom data for a monitoring tool) with a "container" to handle boilerplate tasks like: fetching a script's configuration from a file (and keeping that info up to date if the file changes and handle decryption of sensitive config data) running multiple instances of the same script in different threads instead of spinning up a new process for each one expose an API for caching expensive data and storing persistent state from one script invocation to the next Today, script authors must handle the issues above, which usually means that most script authors don't handle them correctly, causing bugs and performance problems. In addition to avoiding bugs, we want a solution which lowers the bar to create and maintain scripts, especially given that many script authors may not be trained programmers. Below are examples of the API I've been thinking of, and which I'm looking to get your feedback about. A scripter would need to build a single method which takes (as input) the configuration that the script needs to do its job, and either returns a python object or calls a method to stream back data in chunks. Optionally, a scripter could supply methods to handle startup and/or shutdown tasks. HTTP-fetching script example (in pseudocode, omitting the actual data-fetching details to focus on the container's API): def run (config, context, cache) : results = http_library_call (config.url, config.http_method, config.username, config.password, ...) return { html : results.html, status_code : results.status, headers : results.response_headers } def init(config, context, cache) : config.max_threads = 20 # up to 20 URLs at one time (per process) config.max_processes = 3 # launch up to 3 concurrent processes config.keepalive = 1200 # keep process alive for 10 mins without another call config.process_recycle.requests = 1000 # restart the process every 1000 requests (to avoid leaks) config.kill_timeout = 600 # kill the process if any call lasts longer than 10 minutes Database-data fetching script example might look like this (in pseudocode): def run (config, context, cache) : expensive = context.cache["something_expensive"] for record in db_library_call (expensive, context.checkpoint, config.connection_string) : context.log (record, "logDate") # log all properties, optionally specify name of timestamp property last_date = record["logDate"] context.checkpoint = last_date # persistent checkpoint, used next time through def init(config, context, cache) : cache["something_expensive"] = get_expensive_thing() def shutdown(config, context, cache) : expensive = cache["something_expensive"] expensive.release_me() Is this API appropriately "pythonic", or are there things I should do to make this more natural to the Python scripter? (I'm more familiar with building C++/C#/Java APIs so I suspect I'm missing useful Python idioms.) Specific questions: is it natural to pass a "config" object into a method and ask the callee to set various configuration options? Or is there another preferred way to do this? when a callee needs to stream data back to its caller, is a method like context.log() (see above) appropriate, or should I be using yield instead? (yeild seems natural, but I worry it'd be over the head of most scripters) My approach requires scripts to define functions with predefined names (e.g. "run", "init", "shutdown"). Is this a good way to do it? If not, what other mechanism would be more natural? I'm passing the same config, context, cache parameters into every method. Would it be better to use a single "context" parameter instead? Would it be better to use global variables instead? Finally, are there existing libraries you'd recommend to make this kind of simple "script-running container" easier to write?

    Read the article

  • Problems with Asynchronous UDP Sockets

    - by ihatenetworkcoding
    Hi, I'm struggling a bit with socket programming (something I'm not at all familiar with) and I can't find anything which helps from google or MSDN (awful). Apologies for the length of this. Basically I have an existing service which recieves and responds to requests over UDP. I can't change this at all. I also have a client within my webapp which dispatches and listens for responses to that service. The existing client I've been given is a singleton which creates a socket and an array of response slots, and then creates a background thread with an infinite looping method that makes "sock.Receive()" calls and pushes the data received into the slot array. All kinds of things about this seem wrong to me and the infinite thread breaks my unit testing so I'm trying to replace this service with one which makes it's it's send/receives asynchronously instead. Point 1: Is this the right approach? I want a non-blocking, scalable, thread-safe service. My first attempt is roughly like this, which sort of worked but the data I got back was always shorter than expected (i.e. the buffer did not have the number of bytes requested) and seemed to throw exceptions when processed. private Socket MyPreConfiguredSocket; public object Query() { //build a request this.MyPreConfiguredSocket.SendTo(MYREQUEST, packet.Length, SocketFlags.Multicast, this._target); IAsyncResult h = this._sock.BeginReceiveFrom(response, 0, BUFFER_SIZE, SocketFlags.None, ref this._target, new AsyncCallback(ARecieve), this._sock); if (!h.AsyncWaitHandle.WaitOne(TIMEOUT)) { throw new Exception("Timed out"); } //process response data (always shortened) } private void ARecieve (IAsyncResult result) { int bytesreceived = (result as Socket).EndReceiveFrom(result, ref this._target); } My second attempt was based on more google trawling and this recursive pattern I frequently saw, but this version always times out! It never gets to ARecieve. public object Query() { //build a request this.MyPreConfiguredSocket.SendTo(MYREQUEST, packet.Length, SocketFlags.Multicast, this._target); State s = new State(this.MyPreConfiguredSocket); this.MyPreConfiguredSocket.BeginReceiveFrom(s.Buffer, 0, BUFFER_SIZE, SocketFlags.None, ref this._target, new AsyncCallback(ARecieve), s); if (!s.Flag.WaitOne(10000)) { throw new Exception("Timed out"); } //always thrown //process response data } private void ARecieve (IAsyncResult result) { //never gets here! State s = (result as State); int bytesreceived = s.Sock.EndReceiveFrom(result, ref this._target); if (bytesreceived > 0) { s.Received += bytesreceived; this._sock.BeginReceiveFrom(s.Buffer, s.Received, BUFFER_SIZE, SocketFlags.None, ref this._target, new AsyncCallback(ARecieve), s); } else { s.Flag.Set(); } } private class State { public State(Socket sock) { this._sock = sock; this._buffer = new byte[BUFFER_SIZE]; this._buffer.Initialize(); } public Socket Sock; public byte[] Buffer; public ManualResetEvent Flag = new ManualResetEvent(false); public int Received = 0; } Point 2: So clearly I'm getting something quite wrong. Point 3: I'm not sure if I'm going about this right. How does the data coming from the remote service even get to the right listening thread? Do I need to create a socket per request? Out of my comfort zone here. Need help.

    Read the article

  • undefined method `new_record?' for nil:NilClass

    - by TopperH
    In rails 3.2 I created a post controller. Each post can have a different number of paperclip attachments. To achieve this I created a assets model where each asset has a paperclip attachment. One post has_many assets and assets belong_to post. Asset model class Asset < ActiveRecord::Base belongs_to :post has_attached_file :photo, :styles => { :thumb => "200x200>" } end Post model class Post < ActiveRecord::Base attr_accessible :content, :title has_many :assets, :dependent => :destroy validates_associated :assets after_update :save_assets def new_asset_attributes=(asset_attributes) asset_attributes.each do |attributes| assets.build(attributes) end end def existing_asset_attributes=(asset_attributes) assets.reject(&:new_record?).each do |asset| attributes = asset_attributes[asset.id.to_s] if attributes asset.attributes = attributes else asset.delete(asset) end end end def save_assets assets.each do |asset| asset.save(false) end end end Posts helper module PostsHelper def add_asset_link(name) link_to_function name do |post| post.insert_html :bottom, :assets, :partial => 'asset', :object => Asset.new end end end Form for post <%= form_for @post, :html => { :multipart => true } do |f| %> <% if @post.errors.any? %> <div id="error_explanation"> <h2><%= pluralize(@post.errors.count, "error") %> prohibited this post from being saved:</h2> <ul> <% @post.errors.full_messages.each do |msg| %> <li><%= msg %></li> <% end %> </ul> </div> <% end %> <div class="field"> <%= f.label :title %><br /> <%= f.text_field :title %> </div> <div class="field"> <%= f.label :content %><br /> <%= f.text_area :content %> </div> <div id="assets"> Attach a file or image<br /> <%= render 'asset', :collection => @post.assets %> </div> <div class="actions"> <%= f.submit %> </div> <% end %> Asset partial <div class="asset"> <% new_or_existing = asset.new_record? ? 'new' : 'existing' %> <% prefix = "post[#{new_or_existing}_asset_attributes][]" %> <% fields_for prefix, asset do |asset_form| -%> <p> Asset: <%= asset_form.file_field :photo %> <%= link_to_function "remove", "$(this).up('.asset').remove()" %> </p> <% end -%> </div> Most of the code is taken from here: https://gist.github.com/33011 and I understand this is a rails2 app, anyway I don't understand what this error means: undefined method `new_record?' for nil:NilClass Extracted source (around line #2): 1: <div class="asset"> 2: <% new_or_existing = asset.new_record? ? 'new' : 'existing' %> 3: <% prefix = "post[#{new_or_existing}_asset_attributes][]" %> 4: 5: <% fields_for prefix, asset do |asset_form| -%>

    Read the article

  • Sensible Way to Pass Web Data in XML to a SQL Server Database

    - by Emtucifor
    After exploring several different ways to pass web data to a database for update purposes, I'm wondering if XML might be a good strategy. The database is currently SQL 2000. In a few months it will move to SQL 2005 and I will be able to change things if needed, but I need a SQL 2000 solution now. First of all, the database in question uses the EAV model. I know that this kind of database is generally highly frowned on, so for the purposes of this question, please just accept that this is not going to change. The current update method has the web server inserting values (that have all been converted first to their correct underlying types, then to sql_variant) to a temp table. A stored procedure is then run which expects the temp table to exist and it takes care of updating, inserting, or deleting things as needed. So far, only a single element has needed to be updated at a time. But now, there is a requirement to be able to edit multiple elements at once, and also to support hierarchical elements, each of which can have its own list of attributes. Here's some example XML I hand-typed to demonstrate what I'm thinking of. Note that in this database the Entity is Element and an ID of 0 signifies "create" aka an insert of a new item. <Elements> <Element ID="1234"> <Attr ID="221">Value</Attr> <Attr ID="225">287</Attr> <Attr ID="234"> <Element ID="99825"> <Attr ID="7">Value1</Attr> <Attr ID="8">Value2</Attr> <Attr ID="9" Action="delete" /> </Element> <Element ID="99826" Action="delete" /> <Element ID="0" Type="24"> <Attr ID="7">Value4</Attr> <Attr ID="8">Value5</Attr> <Attr ID="9">Value6</Attr> </Element> <Element ID="0" Type="24"> <Attr ID="7">Value7</Attr> <Attr ID="8">Value8</Attr> <Attr ID="9">Value9</Attr> </Element> </Attr> <Rel ID="3827" Action="delete" /> <Rel ID="2284" Role="parent"> <Element ID="3827" /> <Element ID="3829" /> <Attr ID="665">1</Attr> </Rel> <Rel ID="0" Type="23" Role="child"> <Element ID="3830" /> <Attr ID="67" </Rel> </Element> <Element ID="0" Type="87"> <Attr ID="221">Value</Attr> <Attr ID="225">569</Attr> <Attr ID="234"> <Element ID="0" Type="24"> <Attr ID="7">Value10</Attr> <Attr ID="8">Value11</Attr> <Attr ID="9">Value12</Attr> </Element> </Attr> </Element> <Element ID="1235" Action="delete" /> </Elements> Some Attributes are straight value types, such as AttrID 221. But AttrID 234 is a special "multi-value" type that can have a list of elements underneath it, and each one can have one or more values. Types only need to be presented when a new item is created, since the ElementID fully implies the type if it already exists. I'll probably support only passing in changed items (as detected by javascript). And there may be an Action="Delete" on Attr elements as well, since NULLs are treated as "unselected"--sometimes it's very important to know if a Yes/No question has intentionally been answered No or if no one's bothered to say Yes yet. There is also a different kind of data, a Relationship. At this time, those are updated through individual AJAX calls as things are edited in the UI, but I'd like to include those so that changes to relationships can be canceled (right now, once you change it, it's done). So those are really elements, too, but they are called Rel instead of Element. Relationships are implemented as ElementID1 and ElementID2, so the RelID 2284 in the XML above is in the database as: ElementID 2284 ElementID1 1234 ElementID2 3827 Having multiple children in one relationship isn't currently supported, but it would be nice later. Does this strategy and the example XML make sense? Is there a more sensible way? I'm just looking for some broad critique to help save me from going down a bad path. Any aspect that you'd like to comment on would be helpful. The web language happens to be Classic ASP, but that could change to ASP.Net at some point. A persistence engine like Linq or nHibernate is probably not acceptable right now--I just want to get this already working application enhanced without a huge amount of development time. I'll choose the answer that shows experience and has a balance of good warnings about what not to do, confirmations of what I'm planning to do, and recommendations about something else to do. I'll make it as objective as possible. P.S. I'd like to handle unicode characters as well as very long strings (10k +). UPDATE I have had this working for some time and I used the ADO Recordset Save-To-Stream trick to make creating the XML really easy. The result seems to be fairly fast, though if speed ever becomes a problem I may revisit this. In the meantime, my code works to handle any number of elements and attributes on the page at once, including updating, deleting, and creating new items all in one go. I settled on a scheme like so for all my elements: Existing data elements Example: input name e12345_a678 (element 12345, attribute 678), the input value is the value of the attribute. New elements Javascript copies a hidden template of the set of HTML elements needed for the type into the correct location on the page, increments a counter to get a new ID for this item, and prepends the number to the names of the form items. var newid = 0; function metadataAdd(reference, nameid, value) { var t = document.createElement('input'); t.setAttribute('name', nameid); t.setAttribute('id', nameid); t.setAttribute('type', 'hidden'); t.setAttribute('value', value); reference.appendChild(t); } function multiAdd(target, parentelementid, attrid, elementtypeid) { var proto = document.getElementById('a' + attrid + '_proto'); var instance = document.createElement('p'); target.parentNode.parentNode.insertBefore(instance, target.parentNode); var thisid = ++newid; instance.innerHTML = proto.innerHTML.replace(/{prefix}/g, 'n' + thisid + '_'); instance.id = 'n' + thisid; instance.className += ' new'; metadataAdd(instance, 'n' + thisid + '_p', parentelementid); metadataAdd(instance, 'n' + thisid + '_c', attrid); metadataAdd(instance, 'n' + thisid + '_t', elementtypeid); return false; } Example: Template input name _a678 becomes n1_a678 (a new element, the first one on the page, attribute 678). all attributes of this new element are tagged with the same prefix of n1. The next new item will be n2, and so on. Some hidden form inputs are created: n1_t, value is the elementtype of the element to be created n1_p, value is the parent id of the element (if it is a relationship) n1_c, value is the child id of the element (if it is a relationship) Deleting elements A hidden input is created in the form e12345_t with value set to 0. The existing controls displaying that attribute's values are disabled so they are not included in the form post. So "set type to 0" is treated as delete. With this scheme, every item on the page has a unique name and can be distinguished properly, and every action can be represented properly. When the form is posted, here's a sample of building one of the two recordsets used (classic ASP code): Set Data = Server.CreateObject("ADODB.Recordset") Data.Fields.Append "ElementID", adInteger, 4, adFldKeyColumn Data.Fields.Append "AttrID", adInteger, 4, adFldKeyColumn Data.Fields.Append "Value", adLongVarWChar, 2147483647, adFldIsNullable Or adFldMayBeNull Data.CursorLocation = adUseClient Data.CursorType = adOpenDynamic Data.Open This is the recordset for values, the other is for the elements themselves. I step through the posted form and for the element recordset use a Scripting.Dictionary populated with instances of a custom Class that has the properties I need, so that I can add the values piecemeal, since they don't always come in order. New elements are added as negative to distinguish them from regular elements (rather than requiring a separate column to indicate if it is new or addresses an existing element). I use regular expression to tear apart the form keys: "^(e|n)([0-9]{1,10})_(a|p|t|c)([0-9]{0,10})$" Then, adding an attribute looks like this. Data.AddNew ElementID.Value = DataID AttrID.Value = Integerize(Matches(0).SubMatches(3)) AttrValue.Value = Request.Form(Key) Data.Update ElementID, AttrID, and AttrValue are references to the fields of the recordset. This method is hugely faster than using Data.Fields("ElementID").Value each time. I loop through the Dictionary of element updates and ignore any that don't have all the proper information, adding the good ones to the recordset. Then I call my data-updating stored procedure like so: Set Cmd = Server.CreateObject("ADODB.Command") With Cmd Set .ActiveConnection = MyDBConn .CommandType = adCmdStoredProc .CommandText = "DataPost" .Prepared = False .Parameters.Append .CreateParameter("@ElementMetadata", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Element)) .Parameters.Append .CreateParameter("@ElementData", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Data)) End With Result.Open Cmd ' previously created recordset object with options set Here's the function that does the xml conversion: Private Function XMLFromRecordset(Recordset) Dim Stream Set Stream = Server.CreateObject("ADODB.Stream") Stream.Open Recordset.Save Stream, adPersistXML Stream.Position = 0 XMLFromRecordset = Stream.ReadText End Function Just in case the web page needs to know, the SP returns a recordset of any new elements, showing their page value and their created value (so I can see that n1 is now e12346 for example). Here are some key snippets from the stored procedure. Note this is SQL 2000 for now, though I'll be able to switch to 2005 soon: CREATE PROCEDURE [dbo].[DataPost] @ElementMetaData ntext, @ElementData ntext AS DECLARE @hdoc int --- snip --- EXEC sp_xml_preparedocument @hdoc OUTPUT, @ElementMetaData, '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" />' INSERT #ElementMetadata (ElementID, ElementTypeID, ElementID1, ElementID2) SELECT * FROM OPENXML(@hdoc, '/xml/rs:data/rs:insert/z:row', 0) WITH ( ElementID int, ElementTypeID int, ElementID1 int, ElementID2 int ) ORDER BY ElementID -- orders negative items (new elements) first so they begin counting at 1 for later ID calculation EXEC sp_xml_removedocument @hdoc --- snip --- UPDATE E SET E.ElementTypeID = M.ElementTypeID FROM Element E INNER JOIN #ElementMetadata M ON E.ElementID = M.ElementID WHERE E.ElementID >= 1 AND M.ElementTypeID >= 1 The following query does the correlation of the negative new element ids to the newly inserted ones: UPDATE #ElementMetadata -- Correlate the new ElementIDs with the input rows SET NewElementID = Scope_Identity() - @@RowCount + DataID WHERE ElementID < 0 Other set-based queries do all the other work of validating that the attributes are allowed, are the correct data type, and inserting, updating, and deleting elements and attributes. I hope this brief run-down is useful to others some day! Converting ADO Recordsets to an XML stream was a huge winner for me as it saved all sorts of time and had a namespace and schema already defined that made the results come out correctly. Using a flatter XML format with 2 inputs was also much easier than sticking to some ideal about having everything in a single XML stream.

    Read the article

  • Sensible Way to Pass Web Data to Sql Server Database

    - by Emtucifor
    After exploring several different ways to pass web data to a database for update purposes, I'm wondering if XML might be a good strategy. The database is currently SQL 2000. In a few months it will move to SQL 2005 and I will be able to change things if needed, but I need a SQL 2000 solution now. First of all, the database in question uses the EAV model. I know that this kind of database is generally highly frowned on, so for the purposes of this question, please just accept that this is not going to change. The current update method has the web server inserting values (that have all been converted first to their correct underlying types, then to sql_variant) to a temp table. A stored procedure is then run which expects the temp table to exist and it takes care of updating, inserting, or deleting things as needed. So far, only a single element has needed to be updated at a time. But now, there is a requirement to be able to edit multiple elements at once, and also to support hierarchical elements, each of which can have its own list of attributes. Here's some example XML I hand-typed to demonstrate what I'm thinking of. Note that in this database the Entity is Element and an ID of 0 signifies "create" aka an insert of a new item. <Elements> <Element ID="1234"> <Attr ID="221">Value</Attr> <Attr ID="225">287</Attr> <Attr ID="234"> <Element ID="99825"> <Attr ID="7">Value1</Attr> <Attr ID="8">Value2</Attr> <Attr ID="9" Action="delete" /> </Element> <Element ID="99826" Action="delete" /> <Element ID="0" Type="24"> <Attr ID="7">Value4</Attr> <Attr ID="8">Value5</Attr> <Attr ID="9">Value6</Attr> </Element> <Element ID="0" Type="24"> <Attr ID="7">Value7</Attr> <Attr ID="8">Value8</Attr> <Attr ID="9">Value9</Attr> </Element> </Attr> <Rel ID="3827" Action="delete" /> <Rel ID="2284" Role="parent"> <Element ID="3827" /> <Element ID="3829" /> <Attr ID="665">1</Attr> </Rel> <Rel ID="0" Type="23" Role="child"> <Element ID="3830" /> <Attr ID="67" </Rel> </Element> <Element ID="0" Type="87"> <Attr ID="221">Value</Attr> <Attr ID="225">569</Attr> <Attr ID="234"> <Element ID="0" Type="24"> <Attr ID="7">Value10</Attr> <Attr ID="8">Value11</Attr> <Attr ID="9">Value12</Attr> </Element> </Attr> </Element> <Element ID="1235" Action="delete" /> </Elements> Some Attributes are straight value types, such as AttrID 221. But AttrID 234 is a special "multi-value" type that can have a list of elements underneath it, and each one can have one or more values. Types only need to be presented when a new item is created, since the ElementID fully implies the type if it already exists. I'll probably support only passing in changed items (as detected by javascript). And there may be an Action="Delete" on Attr elements as well, since NULLs are treated as "unselected"--sometimes it's very important to know if a Yes/No question has intentionally been answered No or if no one's bothered to say Yes yet. There is also a different kind of data, a Relationship. At this time, those are updated through individual AJAX calls as things are edited in the UI, but I'd like to include those so that changes to relationships can be canceled (right now, once you change it, it's done). So those are really elements, too, but they are called Rel instead of Element. Relationships are implemented as ElementID1 and ElementID2, so the RelID 2284 in the XML above is in the database as: ElementID 2284 ElementID1 1234 ElementID2 3827 Having multiple children in one relationship isn't currently supported, but it would be nice later. Does this strategy and the example XML make sense? Is there a more sensible way? I'm just looking for some broad critique to help save me from going down a bad path. Any aspect that you'd like to comment on would be helpful. The web language happens to be Classic ASP, but that could change to ASP.Net at some point. A persistence engine like Linq or nHibernate is probably not acceptable right now--I just want to get this already working application enhanced without a huge amount of development time. I'll choose the answer that shows experience and has a balance of good warnings about what not to do, confirmations of what I'm planning to do, and recommendations about something else to do. I'll make it as objective as possible. P.S. I'd like to handle unicode characters as well as very long strings (10k +). UPDATE I have had this working for some time and I used the ADO Recordset Save-To-Stream trick to make creating the XML really easy. The result seems to be fairly fast, though if speed ever becomes a problem I may revisit this. In the meantime, my code works to handle any number of elements and attributes on the page at once, including updating, deleting, and creating new items all in one go. I settled on a scheme like so for all my elements: Existing data elements Example: input name e12345_a678 (element 12345, attribute 678), the input value is the value of the attribute. New elements Javascript copies a hidden template of the set of HTML elements needed for the type into the correct location on the page, increments a counter to get a new ID for this item, and prepends the number to the names of the form items. var newid = 0; function metadataAdd(reference, nameid, value) { var t = document.createElement('input'); t.setAttribute('name', nameid); t.setAttribute('id', nameid); t.setAttribute('type', 'hidden'); t.setAttribute('value', value); reference.appendChild(t); } function multiAdd(target, parentelementid, attrid, elementtypeid) { var proto = document.getElementById('a' + attrid + '_proto'); var instance = document.createElement('p'); target.parentNode.parentNode.insertBefore(instance, target.parentNode); var thisid = ++newid; instance.innerHTML = proto.innerHTML.replace(/{prefix}/g, 'n' + thisid + '_'); instance.id = 'n' + thisid; instance.className += ' new'; metadataAdd(instance, 'n' + thisid + '_p', parentelementid); metadataAdd(instance, 'n' + thisid + '_c', attrid); metadataAdd(instance, 'n' + thisid + '_t', elementtypeid); return false; } Example: Template input name _a678 becomes n1_a678 (a new element, the first one on the page, attribute 678). all attributes of this new element are tagged with the same prefix of n1. The next new item will be n2, and so on. Some hidden form inputs are created: n1_t, value is the elementtype of the element to be created n1_p, value is the parent id of the element (if it is a relationship) n1_c, value is the child id of the element (if it is a relationship) Deleting elements A hidden input is created in the form e12345_t with value set to 0. The existing controls displaying that attribute's values are disabled so they are not included in the form post. So "set type to 0" is treated as delete. With this scheme, every item on the page has a unique name and can be distinguished properly, and every action can be represented properly. When the form is posted, here's a sample of building one of the two recordsets used (classic ASP code): Set Data = Server.CreateObject("ADODB.Recordset") Data.Fields.Append "ElementID", adInteger, 4, adFldKeyColumn Data.Fields.Append "AttrID", adInteger, 4, adFldKeyColumn Data.Fields.Append "Value", adLongVarWChar, 2147483647, adFldIsNullable Or adFldMayBeNull Data.CursorLocation = adUseClient Data.CursorType = adOpenDynamic Data.Open This is the recordset for values, the other is for the elements themselves. I step through the posted form and for the element recordset use a Scripting.Dictionary populated with instances of a custom Class that has the properties I need, so that I can add the values piecemeal, since they don't always come in order. New elements are added as negative to distinguish them from regular elements (rather than requiring a separate column to indicate if it is new or addresses an existing element). I use regular expression to tear apart the form keys: "^(e|n)([0-9]{1,10})_(a|p|t|c)([0-9]{0,10})$" Then, adding an attribute looks like this. Data.AddNew ElementID.Value = DataID AttrID.Value = Integerize(Matches(0).SubMatches(3)) AttrValue.Value = Request.Form(Key) Data.Update ElementID, AttrID, and AttrValue are references to the fields of the recordset. This method is hugely faster than using Data.Fields("ElementID").Value each time. I loop through the Dictionary of element updates and ignore any that don't have all the proper information, adding the good ones to the recordset. Then I call my data-updating stored procedure like so: Set Cmd = Server.CreateObject("ADODB.Command") With Cmd Set .ActiveConnection = MyDBConn .CommandType = adCmdStoredProc .CommandText = "DataPost" .Prepared = False .Parameters.Append .CreateParameter("@ElementMetadata", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Element)) .Parameters.Append .CreateParameter("@ElementData", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Data)) End With Result.Open Cmd ' previously created recordset object with options set Here's the function that does the xml conversion: Private Function XMLFromRecordset(Recordset) Dim Stream Set Stream = Server.CreateObject("ADODB.Stream") Stream.Open Recordset.Save Stream, adPersistXML Stream.Position = 0 XMLFromRecordset = Stream.ReadText End Function Just in case the web page needs to know, the SP returns a recordset of any new elements, showing their page value and their created value (so I can see that n1 is now e12346 for example). Here are some key snippets from the stored procedure. Note this is SQL 2000 for now, though I'll be able to switch to 2005 soon: CREATE PROCEDURE [dbo].[DataPost] @ElementMetaData ntext, @ElementData ntext AS DECLARE @hdoc int --- snip --- EXEC sp_xml_preparedocument @hdoc OUTPUT, @ElementMetaData, '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" />' INSERT #ElementMetadata (ElementID, ElementTypeID, ElementID1, ElementID2) SELECT * FROM OPENXML(@hdoc, '/xml/rs:data/rs:insert/z:row', 0) WITH ( ElementID int, ElementTypeID int, ElementID1 int, ElementID2 int ) ORDER BY ElementID -- orders negative items (new elements) first so they begin counting at 1 for later ID calculation EXEC sp_xml_removedocument @hdoc --- snip --- UPDATE E SET E.ElementTypeID = M.ElementTypeID FROM Element E INNER JOIN #ElementMetadata M ON E.ElementID = M.ElementID WHERE E.ElementID >= 1 AND M.ElementTypeID >= 1 The following query does the correlation of the negative new element ids to the newly inserted ones: UPDATE #ElementMetadata -- Correlate the new ElementIDs with the input rows SET NewElementID = Scope_Identity() - @@RowCount + DataID WHERE ElementID < 0 Other set-based queries do all the other work of validating that the attributes are allowed, are the correct data type, and inserting, updating, and deleting elements and attributes. I hope this brief run-down is useful to others some day! Converting ADO Recordsets to an XML stream was a huge winner for me as it saved all sorts of time and had a namespace and schema already defined that made the results come out correctly. Using a flatter XML format with 2 inputs was also much easier than sticking to some ideal about having everything in a single XML stream.

    Read the article

  • SSIS code smell – Unused columns in the dataflow

    - by jamiet
    A code smell is defined on Wikipedia as being a “symptom in the source code of a program that possibly indicates a deeper problem”. It’s a term commonly used by our code-writing brethren to describe sub-optimal code but I think the term can be applied equally well to SSIS packages too as I shall now explain One of my pet hates about SSIS development is packages that throw warnings of the form: The output column "ColumnName" (1358) on output "OLE DB Source Output" (1289) and component "OLE_SRC Name" (1279) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.  The warning is fairly self-explanatory – any column that appears in the data flow but doesn’t get used will throw this warning when the data flow is executed. Its not the negligible performance degradation that they cause that bothers me though, it’s the clutter that they cause in your log file/table. Take a look at the following screenshot if you don’t believe me: There are 231409 such warnings in the system that I took this screenshot from, that is 231409 log records that should not be there. The most infuriating thing about this warning is that it is so easily avoidable; eliminating such columns is a very quick and easy thing to do in the SSIS Designer. The only problem I see is that the warnings don’t occur until you execute the package – it would be preferable for the designer to have an unobtrusive way of informing you of them as well. Anyway, I digress… I consider such warnings to be a code smell because, to me, they’re symptomatic of a lack of due care and attention; a lack of developer discipline if you will. What other code smells can you think of when building SSIS packages? If I get a good list in the comments maybe I’ll compile them into a later blog post. @Jamiet Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • Toorcon14

    - by danx
    Toorcon 2012 Information Security Conference San Diego, CA, http://www.toorcon.org/ Dan Anderson, October 2012 It's almost Halloween, and we all know what that means—yes, of course, it's time for another Toorcon Conference! Toorcon is an annual conference for people interested in computer security. This includes the whole range of hackers, computer hobbyists, professionals, security consultants, press, law enforcement, prosecutors, FBI, etc. We're at Toorcon 14—see earlier blogs for some of the previous Toorcon's I've attended (back to 2003). This year's "con" was held at the Westin on Broadway in downtown San Diego, California. The following are not necessarily my views—I'm just the messenger—although I could have misquoted or misparaphrased the speakers. Also, I only reviewed some of the talks, below, which I attended and interested me. MalAndroid—the Crux of Android Infections, Aditya K. Sood Programming Weird Machines with ELF Metadata, Rebecca "bx" Shapiro Privacy at the Handset: New FCC Rules?, Valkyrie Hacking Measured Boot and UEFI, Dan Griffin You Can't Buy Security: Building the Open Source InfoSec Program, Boris Sverdlik What Journalists Want: The Investigative Reporters' Perspective on Hacking, Dave Maas & Jason Leopold Accessibility and Security, Anna Shubina Stop Patching, for Stronger PCI Compliance, Adam Brand McAfee Secure & Trustmarks — a Hacker's Best Friend, Jay James & Shane MacDougall MalAndroid—the Crux of Android Infections Aditya K. Sood, IOActive, Michigan State PhD candidate Aditya talked about Android smartphone malware. There's a lot of old Android software out there—over 50% Gingerbread (2.3.x)—and most have unpatched vulnerabilities. Of 9 Android vulnerabilities, 8 have known exploits (such as the old Gingerbread Global Object Table exploit). Android protection includes sandboxing, security scanner, app permissions, and screened Android app market. The Android permission checker has fine-grain resource control, policy enforcement. Android static analysis also includes a static analysis app checker (bouncer), and a vulnerablity checker. What security problems does Android have? User-centric security, which depends on the user to grant permission and make smart decisions. But users don't care or think about malware (the're not aware, not paranoid). All they want is functionality, extensibility, mobility Android had no "proper" encryption before Android 3.0 No built-in protection against social engineering and web tricks Alternative Android app markets are unsafe. Simply visiting some markets can infect Android Aditya classified Android Malware types as: Type A—Apps. These interact with the Android app framework. For example, a fake Netflix app. Or Android Gold Dream (game), which uploads user files stealthy manner to a remote location. Type K—Kernel. Exploits underlying Linux libraries or kernel Type H—Hybrid. These use multiple layers (app framework, libraries, kernel). These are most commonly used by Android botnets, which are popular with Chinese botnet authors What are the threats from Android malware? These incude leak info (contacts), banking fraud, corporate network attacks, malware advertising, malware "Hackivism" (the promotion of social causes. For example, promiting specific leaders of the Tunisian or Iranian revolutions. Android malware is frequently "masquerated". That is, repackaged inside a legit app with malware. To avoid detection, the hidden malware is not unwrapped until runtime. The malware payload can be hidden in, for example, PNG files. Less common are Android bootkits—there's not many around. What they do is hijack the Android init framework—alteering system programs and daemons, then deletes itself. For example, the DKF Bootkit (China). Android App Problems: no code signing! all self-signed native code execution permission sandbox — all or none alternate market places no robust Android malware detection at network level delayed patch process Programming Weird Machines with ELF Metadata Rebecca "bx" Shapiro, Dartmouth College, NH https://github.com/bx/elf-bf-tools @bxsays on twitter Definitions. "ELF" is an executable file format used in linking and loading executables (on UNIX/Linux-class machines). "Weird machine" uses undocumented computation sources (I think of them as unintended virtual machines). Some examples of "weird machines" are those that: return to weird location, does SQL injection, corrupts the heap. Bx then talked about using ELF metadata as (an uintended) "weird machine". Some ELF background: A compiler takes source code and generates a ELF object file (hello.o). A static linker makes an ELF executable from the object file. A runtime linker and loader takes ELF executable and loads and relocates it in memory. The ELF file has symbols to relocate functions and variables. ELF has two relocation tables—one at link time and another one at loading time: .rela.dyn (link time) and .dynsym (dynamic table). GOT: Global Offset Table of addresses for dynamically-linked functions. PLT: Procedure Linkage Tables—works with GOT. The memory layout of a process (not the ELF file) is, in order: program (+ heap), dynamic libraries, libc, ld.so, stack (which includes the dynamic table loaded into memory) For ELF, the "weird machine" is found and exploited in the loader. ELF can be crafted for executing viruses, by tricking runtime into executing interpreted "code" in the ELF symbol table. One can inject parasitic "code" without modifying the actual ELF code portions. Think of the ELF symbol table as an "assembly language" interpreter. It has these elements: instructions: Add, move, jump if not 0 (jnz) Think of symbol table entries as "registers" symbol table value is "contents" immediate values are constants direct values are addresses (e.g., 0xdeadbeef) move instruction: is a relocation table entry add instruction: relocation table "addend" entry jnz instruction: takes multiple relocation table entries The ELF weird machine exploits the loader by relocating relocation table entries. The loader will go on forever until told to stop. It stores state on stack at "end" and uses IFUNC table entries (containing function pointer address). The ELF weird machine, called "Brainfu*k" (BF) has: 8 instructions: pointer inc, dec, inc indirect, dec indirect, jump forward, jump backward, print. Three registers - 3 registers Bx showed example BF source code that implemented a Turing machine printing "hello, world". More interesting was the next demo, where bx modified ping. Ping runs suid as root, but quickly drops privilege. BF modified the loader to disable the library function call dropping privilege, so it remained as root. Then BF modified the ping -t argument to execute the -t filename as root. It's best to show what this modified ping does with an example: $ whoami bx $ ping localhost -t backdoor.sh # executes backdoor $ whoami root $ The modified code increased from 285948 bytes to 290209 bytes. A BF tool compiles "executable" by modifying the symbol table in an existing ELF executable. The tool modifies .dynsym and .rela.dyn table, but not code or data. Privacy at the Handset: New FCC Rules? "Valkyrie" (Christie Dudley, Santa Clara Law JD candidate) Valkyrie talked about mobile handset privacy. Some background: Senator Franken (also a comedian) became alarmed about CarrierIQ, where the carriers track their customers. Franken asked the FCC to find out what obligations carriers think they have to protect privacy. The carriers' response was that they are doing just fine with self-regulation—no worries! Carriers need to collect data, such as missed calls, to maintain network quality. But carriers also sell data for marketing. Verizon sells customer data and enables this with a narrow privacy policy (only 1 month to opt out, with difficulties). The data sold is not individually identifiable and is aggregated. But Verizon recommends, as an aggregation workaround to "recollate" data to other databases to identify customers indirectly. The FCC has regulated telephone privacy since 1934 and mobile network privacy since 2007. Also, the carriers say mobile phone privacy is a FTC responsibility (not FCC). FTC is trying to improve mobile app privacy, but FTC has no authority over carrier / customer relationships. As a side note, Apple iPhones are unique as carriers have extra control over iPhones they don't have with other smartphones. As a result iPhones may be more regulated. Who are the consumer advocates? Everyone knows EFF, but EPIC (Electrnic Privacy Info Center), although more obsecure, is more relevant. What to do? Carriers must be accountable. Opt-in and opt-out at any time. Carriers need incentive to grant users control for those who want it, by holding them liable and responsible for breeches on their clock. Location information should be added current CPNI privacy protection, and require "Pen/trap" judicial order to obtain (and would still be a lower standard than 4th Amendment). Politics are on a pro-privacy swing now, with many senators and the Whitehouse. There will probably be new regulation soon, and enforcement will be a problem, but consumers will still have some benefit. Hacking Measured Boot and UEFI Dan Griffin, JWSecure, Inc., Seattle, @JWSdan Dan talked about hacking measured UEFI boot. First some terms: UEFI is a boot technology that is replacing BIOS (has whitelisting and blacklisting). UEFI protects devices against rootkits. TPM - hardware security device to store hashs and hardware-protected keys "secure boot" can control at firmware level what boot images can boot "measured boot" OS feature that tracks hashes (from BIOS, boot loader, krnel, early drivers). "remote attestation" allows remote validation and control based on policy on a remote attestation server. Microsoft pushing TPM (Windows 8 required), but Google is not. Intel TianoCore is the only open source for UEFI. Dan has Measured Boot Tool at http://mbt.codeplex.com/ with a demo where you can also view TPM data. TPM support already on enterprise-class machines. UEFI Weaknesses. UEFI toolkits are evolving rapidly, but UEFI has weaknesses: assume user is an ally trust TPM implicitly, and attached to computer hibernate file is unprotected (disk encryption protects against this) protection migrating from hardware to firmware delays in patching and whitelist updates will UEFI really be adopted by the mainstream (smartphone hardware support, bank support, apathetic consumer support) You Can't Buy Security: Building the Open Source InfoSec Program Boris Sverdlik, ISDPodcast.com co-host Boris talked about problems typical with current security audits. "IT Security" is an oxymoron—IT exists to enable buiness, uptime, utilization, reporting, but don't care about security—IT has conflict of interest. There's no Magic Bullet ("blinky box"), no one-size-fits-all solution (e.g., Intrusion Detection Systems (IDSs)). Regulations don't make you secure. The cloud is not secure (because of shared data and admin access). Defense and pen testing is not sexy. Auditors are not solution (security not a checklist)—what's needed is experience and adaptability—need soft skills. Step 1: First thing is to Google and learn the company end-to-end before you start. Get to know the management team (not IT team), meet as many people as you can. Don't use arbitrary values such as CISSP scores. Quantitive risk assessment is a myth (e.g. AV*EF-SLE). Learn different Business Units, legal/regulatory obligations, learn the business and where the money is made, verify company is protected from script kiddies (easy), learn sensitive information (IP, internal use only), and start with low-hanging fruit (customer service reps and social engineering). Step 2: Policies. Keep policies short and relevant. Generic SANS "security" boilerplate policies don't make sense and are not followed. Focus on acceptable use, data usage, communications, physical security. Step 3: Implementation: keep it simple stupid. Open source, although useful, is not free (implementation cost). Access controls with authentication & authorization for local and remote access. MS Windows has it, otherwise use OpenLDAP, OpenIAM, etc. Application security Everyone tries to reinvent the wheel—use existing static analysis tools. Review high-risk apps and major revisions. Don't run different risk level apps on same system. Assume host/client compromised and use app-level security control. Network security VLAN != segregated because there's too many workarounds. Use explicit firwall rules, active and passive network monitoring (snort is free), disallow end user access to production environment, have a proxy instead of direct Internet access. Also, SSL certificates are not good two-factor auth and SSL does not mean "safe." Operational Controls Have change, patch, asset, & vulnerability management (OSSI is free). For change management, always review code before pushing to production For logging, have centralized security logging for business-critical systems, separate security logging from administrative/IT logging, and lock down log (as it has everything). Monitor with OSSIM (open source). Use intrusion detection, but not just to fulfill a checkbox: build rules from a whitelist perspective (snort). OSSEC has 95% of what you need. Vulnerability management is a QA function when done right: OpenVas and Seccubus are free. Security awareness The reality is users will always click everything. Build real awareness, not compliance driven checkbox, and have it integrated into the culture. Pen test by crowd sourcing—test with logging COSSP http://www.cossp.org/ - Comprehensive Open Source Security Project What Journalists Want: The Investigative Reporters' Perspective on Hacking Dave Maas, San Diego CityBeat Jason Leopold, Truthout.org The difference between hackers and investigative journalists: For hackers, the motivation varies, but method is same, technological specialties. For investigative journalists, it's about one thing—The Story, and they need broad info-gathering skills. J-School in 60 Seconds: Generic formula: Person or issue of pubic interest, new info, or angle. Generic criteria: proximity, prominence, timeliness, human interest, oddity, or consequence. Media awareness of hackers and trends: journalists becoming extremely aware of hackers with congressional debates (privacy, data breaches), demand for data-mining Journalists, use of coding and web development for Journalists, and Journalists busted for hacking (Murdock). Info gathering by investigative journalists include Public records laws. Federal Freedom of Information Act (FOIA) is good, but slow. California Public Records Act is a lot stronger. FOIA takes forever because of foot-dragging—it helps to be specific. Often need to sue (especially FBI). CPRA is faster, and requests can be vague. Dumps and leaks (a la Wikileaks) Journalists want: leads, protecting ourselves, our sources, and adapting tools for news gathering (Google hacking). Anonomity is important to whistleblowers. They want no digital footprint left behind (e.g., email, web log). They don't trust encryption, want to feel safe and secure. Whistleblower laws are very weak—there's no upside for whistleblowers—they have to be very passionate to do it. Accessibility and Security or: How I Learned to Stop Worrying and Love the Halting Problem Anna Shubina, Dartmouth College Anna talked about how accessibility and security are related. Accessibility of digital content (not real world accessibility). mostly refers to blind users and screenreaders, for our purpose. Accessibility is about parsing documents, as are many security issues. "Rich" executable content causes accessibility to fail, and often causes security to fail. For example MS Word has executable format—it's not a document exchange format—more dangerous than PDF or HTML. Accessibility is often the first and maybe only sanity check with parsing. They have no choice because someone may want to read what you write. Google, for example, is very particular about web browser you use and are bad at supporting other browsers. Uses JavaScript instead of links, often requiring mouseover to display content. PDF is a security nightmare. Executible format, embedded flash, JavaScript, etc. 15 million lines of code. Google Chrome doesn't handle PDF correctly, causing several security bugs. PDF has an accessibility checker and PDF tagging, to help with accessibility. But no PDF checker checks for incorrect tags, untagged content, or validates lists or tables. None check executable content at all. The "Halting Problem" is: can one decide whether a program will ever stop? The answer, in general, is no (Rice's theorem). The same holds true for accessibility checkers. Language-theoretic Security says complicated data formats are hard to parse and cannot be solved due to the Halting Problem. W3C Web Accessibility Guidelines: "Perceivable, Operable, Understandable, Robust" Not much help though, except for "Robust", but here's some gems: * all information should be parsable (paraphrasing) * if not parsable, cannot be converted to alternate formats * maximize compatibility in new document formats Executible webpages are bad for security and accessibility. They say it's for a better web experience. But is it necessary to stuff web pages with JavaScript for a better experience? A good example is The Drudge Report—it has hand-written HTML with no JavaScript, yet drives a lot of web traffic due to good content. A bad example is Google News—hidden scrollbars, guessing user input. Solutions: Accessibility and security problems come from same source Expose "better user experience" myth Keep your corner of Internet parsable Remember "Halting Problem"—recognize false solutions (checking and verifying tools) Stop Patching, for Stronger PCI Compliance Adam Brand, protiviti @adamrbrand, http://www.picfun.com/ Adam talked about PCI compliance for retail sales. Take an example: for PCI compliance, 50% of Brian's time (a IT guy), 960 hours/year was spent patching POSs in 850 restaurants. Often applying some patches make no sense (like fixing a browser vulnerability on a server). "Scanner worship" is overuse of vulnerability scanners—it gives a warm and fuzzy and it's simple (red or green results—fix reds). Scanners give a false sense of security. In reality, breeches from missing patches are uncommon—more common problems are: default passwords, cleartext authentication, misconfiguration (firewall ports open). Patching Myths: Myth 1: install within 30 days of patch release (but PCI §6.1 allows a "risk-based approach" instead). Myth 2: vendor decides what's critical (also PCI §6.1). But §6.2 requires user ranking of vulnerabilities instead. Myth 3: scan and rescan until it passes. But PCI §11.2.1b says this applies only to high-risk vulnerabilities. Adam says good recommendations come from NIST 800-40. Instead use sane patching and focus on what's really important. From NIST 800-40: Proactive: Use a proactive vulnerability management process: use change control, configuration management, monitor file integrity. Monitor: start with NVD and other vulnerability alerts, not scanner results. Evaluate: public-facing system? workstation? internal server? (risk rank) Decide:on action and timeline Test: pre-test patches (stability, functionality, rollback) for change control Install: notify, change control, tickets McAfee Secure & Trustmarks — a Hacker's Best Friend Jay James, Shane MacDougall, Tactical Intelligence Inc., Canada "McAfee Secure Trustmark" is a website seal marketed by McAfee. A website gets this badge if they pass their remote scanning. The problem is a removal of trustmarks act as flags that you're vulnerable. Easy to view status change by viewing McAfee list on website or on Google. "Secure TrustGuard" is similar to McAfee. Jay and Shane wrote Perl scripts to gather sites from McAfee and search engines. If their certification image changes to a 1x1 pixel image, then they are longer certified. Their scripts take deltas of scans to see what changed daily. The bottom line is change in TrustGuard status is a flag for hackers to attack your site. Entire idea of seals is silly—you're raising a flag saying if you're vulnerable.

    Read the article

  • NHibernate Pitfalls: Fetch and Paging

    - by Ricardo Peres
    This is part of a series of posts about NHibernate Pitfalls. See the entire collection here. NHibernate allows you to force loading additional references (many to one, one to one) or collections (one to many, many to many) in a query. You must know, however, that this is incompatible with paging. It’s easy to see why. Let’s say you want to get 5 products starting on the fifth, you can issue the following LINQ query: 1: session.Query<Product>().Take(5).Skip(5).ToList(); Will product this SQL in SQL Server: 1: SELECT 2: TOP (@p0) product1_4_, 3: name4_, 4: price4_ 5: FROM 6: (select 7: product0_.product_id as product1_4_, 8: product0_.name as name4_, 9: product0_.price as price4_, 10: ROW_NUMBER() OVER( 11: ORDER BY 12: CURRENT_TIMESTAMP) as __hibernate_sort_row 13: from 14: product product0_) as query 15: WHERE 16: query.__hibernate_sort_row > @p1 17: ORDER BY If, however, you wanted to bring as well the associated order details, you might be tempted to try this: 1: session.Query<Product>().Fetch(x => x.OrderDetails).Take(5).Skip(5).ToList(); Which, in turn, will produce this SQL: 1: SELECT 2: TOP (@p0) product1_4_0_, 3: order1_3_1_, 4: name4_0_, 5: price4_0_, 6: order2_3_1_, 7: product3_3_1_, 8: quantity3_1_, 9: product3_0__, 10: order1_0__ 11: FROM 12: (select 13: product0_.product_id as product1_4_0_, 14: orderdetai1_.order_detail_id as order1_3_1_, 15: product0_.name as name4_0_, 16: product0_.price as price4_0_, 17: orderdetai1_.order_id as order2_3_1_, 18: orderdetai1_.product_id as product3_3_1_, 19: orderdetai1_.quantity as quantity3_1_, 20: orderdetai1_.product_id as product3_0__, 21: orderdetai1_.order_detail_id as order1_0__, 22: ROW_NUMBER() OVER( 23: ORDER BY 24: CURRENT_TIMESTAMP) as __hibernate_sort_row 25: from 26: product product0_ 27: left outer join 28: order_detail orderdetai1_ 29: on product0_.product_id=orderdetai1_.product_id 30: ) as query 31: WHERE 32: query.__hibernate_sort_row > @p1 33: ORDER BY 34: query.__hibernate_sort_row; However, because of the JOIN, what happens is that, if your products have more than one order details, you will get several records – one per order detail – per product, which means that pagination will be broken. There is an workaround, which forces you to write your LINQ query in another way: 1: session.Query<OrderDetail>().Where(x => session.Query<Product>().Select(y => y.ProductId).Take(5).Skip(5).Contains(x.Product.ProductId)).Select(x => x.Product).ToList() Or, using HQL: 1: session.CreateQuery("select od.Product from OrderDetail od where od.Product.ProductId in (select p.ProductId from Product p skip 5 take 5)").List<Product>(); The generated SQL will then be: 1: select 2: product1_.product_id as product1_4_, 3: product1_.name as name4_, 4: product1_.price as price4_ 5: from 6: order_detail orderdetai0_ 7: left outer join 8: product product1_ 9: on orderdetai0_.product_id=product1_.product_id 10: where 11: orderdetai0_.product_id in ( 12: SELECT 13: TOP (@p0) product_id 14: FROM 15: (select 16: product2_.product_id, 17: ROW_NUMBER() OVER( 18: ORDER BY 19: CURRENT_TIMESTAMP) as __hibernate_sort_row 20: from 21: product product2_) as query 22: WHERE 23: query.__hibernate_sort_row > @p1 24: ORDER BY 25: query.__hibernate_sort_row); Which will get you what you want: for 5 products, all of their order details.

    Read the article

  • Advanced TSQL Tuning: Why Internals Knowledge Matters

    - by Paul White
    There is much more to query tuning than reducing logical reads and adding covering nonclustered indexes.  Query tuning is not complete as soon as the query returns results quickly in the development or test environments.  In production, your query will compete for memory, CPU, locks, I/O and other resources on the server.  Today’s entry looks at some tuning considerations that are often overlooked, and shows how deep internals knowledge can help you write better TSQL. As always, we’ll need some example data.  In fact, we are going to use three tables today, each of which is structured like this: Each table has 50,000 rows made up of an INTEGER id column and a padding column containing 3,999 characters in every row.  The only difference between the three tables is in the type of the padding column: the first table uses CHAR(3999), the second uses VARCHAR(MAX), and the third uses the deprecated TEXT type.  A script to create a database with the three tables and load the sample data follows: USE master; GO IF DB_ID('SortTest') IS NOT NULL DROP DATABASE SortTest; GO CREATE DATABASE SortTest COLLATE LATIN1_GENERAL_BIN; GO ALTER DATABASE SortTest MODIFY FILE ( NAME = 'SortTest', SIZE = 3GB, MAXSIZE = 3GB ); GO ALTER DATABASE SortTest MODIFY FILE ( NAME = 'SortTest_log', SIZE = 256MB, MAXSIZE = 1GB, FILEGROWTH = 128MB ); GO ALTER DATABASE SortTest SET ALLOW_SNAPSHOT_ISOLATION OFF ; ALTER DATABASE SortTest SET AUTO_CLOSE OFF ; ALTER DATABASE SortTest SET AUTO_CREATE_STATISTICS ON ; ALTER DATABASE SortTest SET AUTO_SHRINK OFF ; ALTER DATABASE SortTest SET AUTO_UPDATE_STATISTICS ON ; ALTER DATABASE SortTest SET AUTO_UPDATE_STATISTICS_ASYNC ON ; ALTER DATABASE SortTest SET PARAMETERIZATION SIMPLE ; ALTER DATABASE SortTest SET READ_COMMITTED_SNAPSHOT OFF ; ALTER DATABASE SortTest SET MULTI_USER ; ALTER DATABASE SortTest SET RECOVERY SIMPLE ; USE SortTest; GO CREATE TABLE dbo.TestCHAR ( id INTEGER IDENTITY (1,1) NOT NULL, padding CHAR(3999) NOT NULL,   CONSTRAINT [PK dbo.TestCHAR (id)] PRIMARY KEY CLUSTERED (id), ) ; CREATE TABLE dbo.TestMAX ( id INTEGER IDENTITY (1,1) NOT NULL, padding VARCHAR(MAX) NOT NULL,   CONSTRAINT [PK dbo.TestMAX (id)] PRIMARY KEY CLUSTERED (id), ) ; CREATE TABLE dbo.TestTEXT ( id INTEGER IDENTITY (1,1) NOT NULL, padding TEXT NOT NULL,   CONSTRAINT [PK dbo.TestTEXT (id)] PRIMARY KEY CLUSTERED (id), ) ; -- ============= -- Load TestCHAR (about 3s) -- ============= INSERT INTO dbo.TestCHAR WITH (TABLOCKX) ( padding ) SELECT padding = REPLICATE(CHAR(65 + (Data.n % 26)), 3999) FROM ( SELECT TOP (50000) n = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 FROM master.sys.columns C1, master.sys.columns C2, master.sys.columns C3 ORDER BY n ASC ) AS Data ORDER BY Data.n ASC ; -- ============ -- Load TestMAX (about 3s) -- ============ INSERT INTO dbo.TestMAX WITH (TABLOCKX) ( padding ) SELECT CONVERT(VARCHAR(MAX), padding) FROM dbo.TestCHAR ORDER BY id ; -- ============= -- Load TestTEXT (about 5s) -- ============= INSERT INTO dbo.TestTEXT WITH (TABLOCKX) ( padding ) SELECT CONVERT(TEXT, padding) FROM dbo.TestCHAR ORDER BY id ; -- ========== -- Space used -- ========== -- EXECUTE sys.sp_spaceused @objname = 'dbo.TestCHAR'; EXECUTE sys.sp_spaceused @objname = 'dbo.TestMAX'; EXECUTE sys.sp_spaceused @objname = 'dbo.TestTEXT'; ; CHECKPOINT ; That takes around 15 seconds to run, and shows the space allocated to each table in its output: To illustrate the points I want to make today, the example task we are going to set ourselves is to return a random set of 150 rows from each table.  The basic shape of the test query is the same for each of the three test tables: SELECT TOP (150) T.id, T.padding FROM dbo.Test AS T ORDER BY NEWID() OPTION (MAXDOP 1) ; Test 1 – CHAR(3999) Running the template query shown above using the TestCHAR table as the target, we find that the query takes around 5 seconds to return its results.  This seems slow, considering that the table only has 50,000 rows.  Working on the assumption that generating a GUID for each row is a CPU-intensive operation, we might try enabling parallelism to see if that speeds up the response time.  Running the query again (but without the MAXDOP 1 hint) on a machine with eight logical processors, the query now takes 10 seconds to execute – twice as long as when run serially. Rather than attempting further guesses at the cause of the slowness, let’s go back to serial execution and add some monitoring.  The script below monitors STATISTICS IO output and the amount of tempdb used by the test query.  We will also run a Profiler trace to capture any warnings generated during query execution. DECLARE @read BIGINT, @write BIGINT ; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; SET STATISTICS IO ON ; SELECT TOP (150) TC.id, TC.padding FROM dbo.TestCHAR AS TC ORDER BY NEWID() OPTION (MAXDOP 1) ; SET STATISTICS IO OFF ; SELECT tempdb_read_MB = (SUM(num_of_bytes_read) - @read) / 1024. / 1024., tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024., internal_use_MB = ( SELECT internal_objects_alloc_page_count / 128.0 FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID ) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; Let’s take a closer look at the statistics and query plan generated from this: Following the flow of the data from right to left, we see the expected 50,000 rows emerging from the Clustered Index Scan, with a total estimated size of around 191MB.  The Compute Scalar adds a column containing a random GUID (generated from the NEWID() function call) for each row.  With this extra column in place, the size of the data arriving at the Sort operator is estimated to be 192MB. Sort is a blocking operator – it has to examine all of the rows on its input before it can produce its first row of output (the last row received might sort first).  This characteristic means that Sort requires a memory grant – memory allocated for the query’s use by SQL Server just before execution starts.  In this case, the Sort is the only memory-consuming operator in the plan, so it has access to the full 243MB (248,696KB) of memory reserved by SQL Server for this query execution. Notice that the memory grant is significantly larger than the expected size of the data to be sorted.  SQL Server uses a number of techniques to speed up sorting, some of which sacrifice size for comparison speed.  Sorts typically require a very large number of comparisons, so this is usually a very effective optimization.  One of the drawbacks is that it is not possible to exactly predict the sort space needed, as it depends on the data itself.  SQL Server takes an educated guess based on data types, sizes, and the number of rows expected, but the algorithm is not perfect. In spite of the large memory grant, the Profiler trace shows a Sort Warning event (indicating that the sort ran out of memory), and the tempdb usage monitor shows that 195MB of tempdb space was used – all of that for system use.  The 195MB represents physical write activity on tempdb, because SQL Server strictly enforces memory grants – a query cannot ‘cheat’ and effectively gain extra memory by spilling to tempdb pages that reside in memory.  Anyway, the key point here is that it takes a while to write 195MB to disk, and this is the main reason that the query takes 5 seconds overall. If you are wondering why using parallelism made the problem worse, consider that eight threads of execution result in eight concurrent partial sorts, each receiving one eighth of the memory grant.  The eight sorts all spilled to tempdb, resulting in inefficiencies as the spilled sorts competed for disk resources.  More importantly, there are specific problems at the point where the eight partial results are combined, but I’ll cover that in a future post. CHAR(3999) Performance Summary: 5 seconds elapsed time 243MB memory grant 195MB tempdb usage 192MB estimated sort set 25,043 logical reads Sort Warning Test 2 – VARCHAR(MAX) We’ll now run exactly the same test (with the additional monitoring) on the table using a VARCHAR(MAX) padding column: DECLARE @read BIGINT, @write BIGINT ; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; SET STATISTICS IO ON ; SELECT TOP (150) TM.id, TM.padding FROM dbo.TestMAX AS TM ORDER BY NEWID() OPTION (MAXDOP 1) ; SET STATISTICS IO OFF ; SELECT tempdb_read_MB = (SUM(num_of_bytes_read) - @read) / 1024. / 1024., tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024., internal_use_MB = ( SELECT internal_objects_alloc_page_count / 128.0 FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID ) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; This time the query takes around 8 seconds to complete (3 seconds longer than Test 1).  Notice that the estimated row and data sizes are very slightly larger, and the overall memory grant has also increased very slightly to 245MB.  The most marked difference is in the amount of tempdb space used – this query wrote almost 391MB of sort run data to the physical tempdb file.  Don’t draw any general conclusions about VARCHAR(MAX) versus CHAR from this – I chose the length of the data specifically to expose this edge case.  In most cases, VARCHAR(MAX) performs very similarly to CHAR – I just wanted to make test 2 a bit more exciting. MAX Performance Summary: 8 seconds elapsed time 245MB memory grant 391MB tempdb usage 193MB estimated sort set 25,043 logical reads Sort warning Test 3 – TEXT The same test again, but using the deprecated TEXT data type for the padding column: DECLARE @read BIGINT, @write BIGINT ; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; SET STATISTICS IO ON ; SELECT TOP (150) TT.id, TT.padding FROM dbo.TestTEXT AS TT ORDER BY NEWID() OPTION (MAXDOP 1, RECOMPILE) ; SET STATISTICS IO OFF ; SELECT tempdb_read_MB = (SUM(num_of_bytes_read) - @read) / 1024. / 1024., tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024., internal_use_MB = ( SELECT internal_objects_alloc_page_count / 128.0 FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID ) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; This time the query runs in 500ms.  If you look at the metrics we have been checking so far, it’s not hard to understand why: TEXT Performance Summary: 0.5 seconds elapsed time 9MB memory grant 5MB tempdb usage 5MB estimated sort set 207 logical reads 596 LOB logical reads Sort warning SQL Server’s memory grant algorithm still underestimates the memory needed to perform the sorting operation, but the size of the data to sort is so much smaller (5MB versus 193MB previously) that the spilled sort doesn’t matter very much.  Why is the data size so much smaller?  The query still produces the correct results – including the large amount of data held in the padding column – so what magic is being performed here? TEXT versus MAX Storage The answer lies in how columns of the TEXT data type are stored.  By default, TEXT data is stored off-row in separate LOB pages – which explains why this is the first query we have seen that records LOB logical reads in its STATISTICS IO output.  You may recall from my last post that LOB data leaves an in-row pointer to the separate storage structure holding the LOB data. SQL Server can see that the full LOB value is not required by the query plan until results are returned, so instead of passing the full LOB value down the plan from the Clustered Index Scan, it passes the small in-row structure instead.  SQL Server estimates that each row coming from the scan will be 79 bytes long – 11 bytes for row overhead, 4 bytes for the integer id column, and 64 bytes for the LOB pointer (in fact the pointer is rather smaller – usually 16 bytes – but the details of that don’t really matter right now). OK, so this query is much more efficient because it is sorting a very much smaller data set – SQL Server delays retrieving the LOB data itself until after the Sort starts producing its 150 rows.  The question that normally arises at this point is: Why doesn’t SQL Server use the same trick when the padding column is defined as VARCHAR(MAX)? The answer is connected with the fact that if the actual size of the VARCHAR(MAX) data is 8000 bytes or less, it is usually stored in-row in exactly the same way as for a VARCHAR(8000) column – MAX data only moves off-row into LOB storage when it exceeds 8000 bytes.  The default behaviour of the TEXT type is to be stored off-row by default, unless the ‘text in row’ table option is set suitably and there is room on the page.  There is an analogous (but opposite) setting to control the storage of MAX data – the ‘large value types out of row’ table option.  By enabling this option for a table, MAX data will be stored off-row (in a LOB structure) instead of in-row.  SQL Server Books Online has good coverage of both options in the topic In Row Data. The MAXOOR Table The essential difference, then, is that MAX defaults to in-row storage, and TEXT defaults to off-row (LOB) storage.  You might be thinking that we could get the same benefits seen for the TEXT data type by storing the VARCHAR(MAX) values off row – so let’s look at that option now.  This script creates a fourth table, with the VARCHAR(MAX) data stored off-row in LOB pages: CREATE TABLE dbo.TestMAXOOR ( id INTEGER IDENTITY (1,1) NOT NULL, padding VARCHAR(MAX) NOT NULL,   CONSTRAINT [PK dbo.TestMAXOOR (id)] PRIMARY KEY CLUSTERED (id), ) ; EXECUTE sys.sp_tableoption @TableNamePattern = N'dbo.TestMAXOOR', @OptionName = 'large value types out of row', @OptionValue = 'true' ; SELECT large_value_types_out_of_row FROM sys.tables WHERE [schema_id] = SCHEMA_ID(N'dbo') AND name = N'TestMAXOOR' ; INSERT INTO dbo.TestMAXOOR WITH (TABLOCKX) ( padding ) SELECT SPACE(0) FROM dbo.TestCHAR ORDER BY id ; UPDATE TM WITH (TABLOCK) SET padding.WRITE (TC.padding, NULL, NULL) FROM dbo.TestMAXOOR AS TM JOIN dbo.TestCHAR AS TC ON TC.id = TM.id ; EXECUTE sys.sp_spaceused @objname = 'dbo.TestMAXOOR' ; CHECKPOINT ; Test 4 – MAXOOR We can now re-run our test on the MAXOOR (MAX out of row) table: DECLARE @read BIGINT, @write BIGINT ; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; SET STATISTICS IO ON ; SELECT TOP (150) MO.id, MO.padding FROM dbo.TestMAXOOR AS MO ORDER BY NEWID() OPTION (MAXDOP 1, RECOMPILE) ; SET STATISTICS IO OFF ; SELECT tempdb_read_MB = (SUM(num_of_bytes_read) - @read) / 1024. / 1024., tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024., internal_use_MB = ( SELECT internal_objects_alloc_page_count / 128.0 FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID ) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS' ; TEXT Performance Summary: 0.3 seconds elapsed time 245MB memory grant 0MB tempdb usage 193MB estimated sort set 207 logical reads 446 LOB logical reads No sort warning The query runs very quickly – slightly faster than Test 3, and without spilling the sort to tempdb (there is no sort warning in the trace, and the monitoring query shows zero tempdb usage by this query).  SQL Server is passing the in-row pointer structure down the plan and only looking up the LOB value on the output side of the sort. The Hidden Problem There is still a huge problem with this query though – it requires a 245MB memory grant.  No wonder the sort doesn’t spill to tempdb now – 245MB is about 20 times more memory than this query actually requires to sort 50,000 records containing LOB data pointers.  Notice that the estimated row and data sizes in the plan are the same as in test 2 (where the MAX data was stored in-row). The optimizer assumes that MAX data is stored in-row, regardless of the sp_tableoption setting ‘large value types out of row’.  Why?  Because this option is dynamic – changing it does not immediately force all MAX data in the table in-row or off-row, only when data is added or actually changed.  SQL Server does not keep statistics to show how much MAX or TEXT data is currently in-row, and how much is stored in LOB pages.  This is an annoying limitation, and one which I hope will be addressed in a future version of the product. So why should we worry about this?  Excessive memory grants reduce concurrency and may result in queries waiting on the RESOURCE_SEMAPHORE wait type while they wait for memory they do not need.  245MB is an awful lot of memory, especially on 32-bit versions where memory grants cannot use AWE-mapped memory.  Even on a 64-bit server with plenty of memory, do you really want a single query to consume 0.25GB of memory unnecessarily?  That’s 32,000 8KB pages that might be put to much better use. The Solution The answer is not to use the TEXT data type for the padding column.  That solution happens to have better performance characteristics for this specific query, but it still results in a spilled sort, and it is hard to recommend the use of a data type which is scheduled for removal.  I hope it is clear to you that the fundamental problem here is that SQL Server sorts the whole set arriving at a Sort operator.  Clearly, it is not efficient to sort the whole table in memory just to return 150 rows in a random order. The TEXT example was more efficient because it dramatically reduced the size of the set that needed to be sorted.  We can do the same thing by selecting 150 unique keys from the table at random (sorting by NEWID() for example) and only then retrieving the large padding column values for just the 150 rows we need.  The following script implements that idea for all four tables: SET STATISTICS IO ON ; WITH TestTable AS ( SELECT * FROM dbo.TestCHAR ), TopKeys AS ( SELECT TOP (150) id FROM TestTable ORDER BY NEWID() ) SELECT T1.id, T1.padding FROM TestTable AS T1 WHERE T1.id = ANY (SELECT id FROM TopKeys) OPTION (MAXDOP 1) ; WITH TestTable AS ( SELECT * FROM dbo.TestMAX ), TopKeys AS ( SELECT TOP (150) id FROM TestTable ORDER BY NEWID() ) SELECT T1.id, T1.padding FROM TestTable AS T1 WHERE T1.id IN (SELECT id FROM TopKeys) OPTION (MAXDOP 1) ; WITH TestTable AS ( SELECT * FROM dbo.TestTEXT ), TopKeys AS ( SELECT TOP (150) id FROM TestTable ORDER BY NEWID() ) SELECT T1.id, T1.padding FROM TestTable AS T1 WHERE T1.id IN (SELECT id FROM TopKeys) OPTION (MAXDOP 1) ; WITH TestTable AS ( SELECT * FROM dbo.TestMAXOOR ), TopKeys AS ( SELECT TOP (150) id FROM TestTable ORDER BY NEWID() ) SELECT T1.id, T1.padding FROM TestTable AS T1 WHERE T1.id IN (SELECT id FROM TopKeys) OPTION (MAXDOP 1) ; SET STATISTICS IO OFF ; All four queries now return results in much less than a second, with memory grants between 6 and 12MB, and without spilling to tempdb.  The small remaining inefficiency is in reading the id column values from the clustered primary key index.  As a clustered index, it contains all the in-row data at its leaf.  The CHAR and VARCHAR(MAX) tables store the padding column in-row, so id values are separated by a 3999-character column, plus row overhead.  The TEXT and MAXOOR tables store the padding values off-row, so id values in the clustered index leaf are separated by the much-smaller off-row pointer structure.  This difference is reflected in the number of logical page reads performed by the four queries: Table 'TestCHAR' logical reads 25511 lob logical reads 000 Table 'TestMAX'. logical reads 25511 lob logical reads 000 Table 'TestTEXT' logical reads 00412 lob logical reads 597 Table 'TestMAXOOR' logical reads 00413 lob logical reads 446 We can increase the density of the id values by creating a separate nonclustered index on the id column only.  This is the same key as the clustered index, of course, but the nonclustered index will not include the rest of the in-row column data. CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.TestCHAR (id); CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.TestMAX (id); CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.TestTEXT (id); CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.TestMAXOOR (id); The four queries can now use the very dense nonclustered index to quickly scan the id values, sort them by NEWID(), select the 150 ids we want, and then look up the padding data.  The logical reads with the new indexes in place are: Table 'TestCHAR' logical reads 835 lob logical reads 0 Table 'TestMAX' logical reads 835 lob logical reads 0 Table 'TestTEXT' logical reads 686 lob logical reads 597 Table 'TestMAXOOR' logical reads 686 lob logical reads 448 With the new index, all four queries use the same query plan (click to enlarge): Performance Summary: 0.3 seconds elapsed time 6MB memory grant 0MB tempdb usage 1MB sort set 835 logical reads (CHAR, MAX) 686 logical reads (TEXT, MAXOOR) 597 LOB logical reads (TEXT) 448 LOB logical reads (MAXOOR) No sort warning I’ll leave it as an exercise for the reader to work out why trying to eliminate the Key Lookup by adding the padding column to the new nonclustered indexes would be a daft idea Conclusion This post is not about tuning queries that access columns containing big strings.  It isn’t about the internal differences between TEXT and MAX data types either.  It isn’t even about the cool use of UPDATE .WRITE used in the MAXOOR table load.  No, this post is about something else: Many developers might not have tuned our starting example query at all – 5 seconds isn’t that bad, and the original query plan looks reasonable at first glance.  Perhaps the NEWID() function would have been blamed for ‘just being slow’ – who knows.  5 seconds isn’t awful – unless your users expect sub-second responses – but using 250MB of memory and writing 200MB to tempdb certainly is!  If ten sessions ran that query at the same time in production that’s 2.5GB of memory usage and 2GB hitting tempdb.  Of course, not all queries can be rewritten to avoid large memory grants and sort spills using the key-lookup technique in this post, but that’s not the point either. The point of this post is that a basic understanding of execution plans is not enough.  Tuning for logical reads and adding covering indexes is not enough.  If you want to produce high-quality, scalable TSQL that won’t get you paged as soon as it hits production, you need a deep understanding of execution plans, and as much accurate, deep knowledge about SQL Server as you can lay your hands on.  The advanced database developer has a wide range of tools to use in writing queries that perform well in a range of circumstances. By the way, the examples in this post were written for SQL Server 2008.  They will run on 2005 and demonstrate the same principles, but you won’t get the same figures I did because 2005 had a rather nasty bug in the Top N Sort operator.  Fair warning: if you do decide to run the scripts on a 2005 instance (particularly the parallel query) do it before you head out for lunch… This post is dedicated to the people of Christchurch, New Zealand. © 2011 Paul White email: @[email protected] twitter: @SQL_Kiwi

    Read the article

  • Announcing Oracle Enterprise Content Management Suite 11g

    - by [email protected]
    Today Oracle announced Oracle Enterprise Content Management Suite 11g. This is a major release for us, and reinforces our three key themes at Oracle: Complete New in this release - Oracle ECM Suite 11g is built on a single, unified repository. Every piece of content - documents, HTML pages, digital assets, scanned images - is stored and accessbile directly from the repository, whether you are working on websites, creating brand logos, processing accounts payable invoices, or running records and retention functions. It makes complete, end-to-end management of content possible, from the point it enters the organization, through its entire lifecycle. Also new in this release, the installation, access, monitoring and administration of Oracle ECM Suite 11g is centralized. As a complete system, organizations can lower the costs of training and usage by having a centralized source of information that is easily administered. As part of this new unified repository release, Oracle has released a benchmarking white paper that shows the extreme performance and scalability of Oracle ECM Suite. When tested on a two node UCM Server running on Sun Oracle DB Machine Half Rack Hardware with an Exadata storage server, Oracle ECM Suite 11g is able to ingest over 178 million documents per day. Open Oracle ECM Suite 11g is built on a service-oriented architecture. All functions are available through standards-based services calls in Web Services or Java. In this release Oracle unveils Open Web Content Management. Open Web Content Management is a revolutionary approach to web content management that decouples the content management process from the process of creating web applications. One piece of this approach is our one-click web content management. With one click, a web application builder can drag content services into their application, enabling their users to also edit content with just one click. Open Web Content Management is also open because it enables Web developers to add Web content management to new and existing JavaServer Pages (JSP), JavaServer Faces (JSF) and Oracle Application Development Framework (ADF) Faces applications Open content distribution - Oracle ECM Suite 11g offers flexible deployment options with a built-in smart cache so organizations can deliver Web sites or Web applications without requiring Oracle ECM Suite as part of the delivery system Integrated Oracle ECM Suite 11g also offers a series of next generation desktop integrations, providing integrations such as: New MS Office integration with menus to access managed content, insert managed links, and compare managed documents using standard MS Office reviewing tools Automatic identity tagging of documents on download - to help users understand which versions they are viewing and prevent duplicate content items in the content repository. New "smart productivity folders" to show a users workflow inbox, saved searches and checked out content directly from Windows Explorer Drag and drop metadata pop-ups Check in and check out for all file formats with any standard WebDAV server As part of Oracle's Enterprise Application Documents initiative, Oracle Content Management 11g also provides certified application integrations with solution templates You can read the press release here. You can see more assets at the launch center here. You can sign up for the announcement webinar and hear more about the new features here. You can read the benchmarking study here.

    Read the article

< Previous Page | 351 352 353 354 355 356 357 358 359 360 361 362  | Next Page >