Search Results

Search found 20224 results on 809 pages for 'query optimization'.

Page 440/809 | < Previous Page | 436 437 438 439 440 441 442 443 444 445 446 447  | Next Page >

  • Codeigniter: Combining activeRecord with manual queries?

    - by Industrial
    Hi everybody, I've though a bit about the activerecord vs. manual queries in Codeigniter. ActiveRecord is awesome when it's all about standard queries and holds development time really low. However, when there's a need to add some complexity to the queries, the ActiveRecord gets quite complicated to work with. Sub queries or complex joins gives atleast me a lot of headache. Since the current "$this-db-query" -call immediately executes the set query, it can't be combined with normal activeRecord calls. So, what can I do to combine the two methods? Thanks!

    Read the article

  • Overly accessible and incredibly resource hungry relationships between business objects. How can I f

    - by Mike
    Hi, Firstly, This might seem like a long question. I don't think it is... The code is just an overview of what im currently doing. It doesn't feel right, so I am looking for constructive criticism and warnings for pitfalls and suggestions of what I can do. I have a database with business objects. I need to access properties of parent objects. I need to maintain some sort of state through business objects. If you look at the classes, I don't think that the access modifiers are right. I don't think its structured very well. Most of the relationships are modelled with public properties. SubAccount.Account.User.ID <-- all of those are public.. Is there a better way to model a relationship between classes than this so its not so "public"? The other part of this question is about resources: If I was to make a User.GetUserList() function that returns a List, and I had 9000 users, when I call the GetUsers method, it will make 9000 User objects and inside that it will make 9000 new AccountCollection objects. What can I do to make this project not so resource hungry? Please find the code below and rip it to shreds. public class User { public string ID {get;set;} public string FirstName {get; set;} public string LastName {get; set;} public string PhoneNo {get; set;} public AccountCollection accounts {get; set;} public User { accounts = new AccountCollection(this); } public static List<Users> GetUsers() { return Data.GetUsers(); } } public AccountCollection : IEnumerable<Account> { private User user; public AccountCollection(User user) { this.user = user; } public IEnumerable<Account> GetEnumerator() { return Data.GetAccounts(user); } } public class Account { public User User {get; set;} //This is public so that the subaccount can access its Account's User's ID public int ID; public string Name; public Account(User user) { this.user = user; } } public SubAccountCollection : IEnumerable<SubAccount> { public Account account {get; set;} public SubAccountCollection(Account account) { this.account = account; } public IEnumerable<SubAccount> GetEnumerator() { return Data.GetSubAccounts(account); } } public class SubAccount { public Account account {get; set;} //this is public so that my Data class can access the account, to get the account's user's ID. public SubAccount(Account account) { this.account = account; } public Report GenerateReport() { Data.GetReport(this); } } public static class Data { public static List<Account> GetSubAccounts(Account account) { using (var dc = new databaseDataContext()) { List<SubAccount> query = (from a in dc.Accounts where a.UserID == account.User.ID //this is getting the account's user's ID select new SubAccount(account) { ID = a.ID, Name = a.Name, }).ToList(); } } public static List<Account> GetAccounts(User user) { using (var dc = new databaseDataContext()) { List<Account> query = (from a in dc.Accounts where a.UserID == User.ID //this is getting the user's ID select new Account(user) { ID = a.ID, Name = a.Name, }).ToList(); } } public static Report GetReport(SubAccount subAccount) { Report report = new Report(); //database access code here //need to get the user id of the subaccount's account for data querying. //i've got the subaccount, but how should i get the user id. //i would imagine something like this: int accountID = subAccount.Account.User.ID; //but this would require the subaccount's Account property to be public. //i do not want this to be accessible from my other project (UI). //reading up on internal seems to do the trick, but within my code it still feels //public. I could restrict the property to read, and only private set. return report; } public static List<User> GetUsers() { using (var dc = new databaseDataContext()) { var query = (from u in dc.Users select new User { ID = u.ID, FirstName = u.FirstName, LastName = u.LastName, PhoneNo = u.PhoneNo }).ToList(); return query; } } }

    Read the article

  • Persist url parameter throughout grails app

    - by avelis
    Essentially I am looking to have a url query parameter persist throughout the life of the grails application (POST or GET). ex. http://localhost:8080/demo/controller/action/?myParam=foobar I have tried a couple routes. Dynamic method overriding redirect and customizing application tags for createLink. However, since I also use grails webflows it doesn't quite get every single URL. I also tried using a groovy servlet (groovlet) to capture every URL and append the query parameter. The last attempt hasn't been very successful. Am I missing an obvious component to grails? Am I on the right track? Is there another avenue I haven't explored yet? Thanks in advance

    Read the article

  • XPath and special characters

    - by Bryan
    I am having an issue with an XPath query I'm performing for a Sitecore CMS system. This query works fine: /root/content/Meta-Data/Tips/* But when I try this: /root/content/Meta-Data/Tips/*[@SomeAttribute='somekey'] I get an error which says "End of string expected at position 22" which is where the dash character is found. I was under the impression that the dash was not a special character in XML... am I doing something wrong here? Do I need to encode this in some way? Or is this a bug in the XPath parser? Any suggested workarounds?

    Read the article

  • ROW_NUMBER() VS. DISTINCT

    - by ramadan2050
    Dear All, I have a problem with ROW_NUMBER() , if i used it with DISTINCT in the following Query I have 2 scenarios: 1- run this query direct : give me for example 400 record as a result 2- uncomment a line which start with [--Uncomment1--] : give me 700 record as a result it duplicated some records not all the records what I want is to solve this problem or to find any way to show a row counter beside each row, to make a [where rownumber between 1 and 30] --Uncomment2-- if I put the whole query in a table, and then filter it , it is work but it still so slow waiting for any feedback and I will appreciate that Thanks in advance SELECT * FROM (SELECT Distinct CRSTask.ID AS TaskID, CRSTask.WFLTaskID, --Uncomment1-- ROW_NUMBER() OVER (ORDER By CRSTask.CreateDate asc ) AS RowNum , CRSTask.WFLStatus AS Task_WFLStatus, CRSTask.Name AS StepName, CRSTask.ModifiedDate AS Task_ModifyDate, CRSTask.SendingDate AS Task_SendingDate, CRSTask.ReceiveDate AS Task_ReceiveDate, CRSTask.CreateDate AS Task_CreateDate, CRS_Task_Recipient_Vw.Task_CurrentSenderName, CRS_Task_Recipient_Vw.Task_SenderName, CRS_INFO.ID AS CRS_ID, CRS_INFO.ReferenceNumber, CRS_INFO.CRSBeneficiaries, CRS_INFO.BarCodeNumber, ISNULL(dbo.CRS_FNC_GetTaskReceiver(CRSTask.ID), '') + ' ' + ISNULL (CRS_Organization.ArName, '') AS OrgName, CRS_Info.IncidentID, COALESCE(CRS_Subject.ArSubject, '??? ????') AS ArSubject, COALESCE(CRS_INFO.Subject, 'Blank Subject') AS CRS_Subject, CRS_INFO.Mode, CRS_Task_Recipient_Vw.ReceiverID, CRS_Task_Recipient_Vw.ReceiverType, CRS_Task_Recipient_Vw.CC, Temp_Portal_Users_View.ID AS CRS_LockedByID, Temp_Portal_Users_View.ArabicName AS CRS_LockedByName, CRSDraft.ID AS DraftID, CRSDraft.Type AS DraftType, CASE WHEN CRS_Folder = 1 THEN Task_SenderName WHEN CRS_Folder = 2 THEN Task_SenderName WHEN CRS_Folder = 3 THEN Task_CurrentSenderName END AS SenderName, CRS_Task_Folder_Vw.CRS_Folder, CRS_INFO.Status, CRS_INFO.CRS_Type, CRS_Type.arName AS CRS_Type_Name FROM CRS_Task_Folder_Vw LEFT OUTER JOIN CRSTask ON CRSTask.ID = CRS_Task_Folder_Vw.TaskID LEFT OUTER JOIN CRS_INFO ON CRS_INFO.ID = CRSTask.CRSID LEFT OUTER JOIN CRS_Subject ON COALESCE( SUBSTRING( CRS_INFO.Subject, CHARINDEX('_', CRS_INFO.Subject) + 1, LEN(CRS_INFO.Subject) ), 'Blank Subject' ) = CRS_Subject.ID LEFT OUTER JOIN CRSInfoAttribute ON CRS_INFO.ID = CRSInfoAttribute.ID LEFT OUTER JOIN CRS_Organization ON CRS_Organization.ID = CRSInfoAttribute.SourceID LEFT OUTER JOIN CRS_Type ON CRS_INFO.CRS_Type = CRS_Type.ID LEFT OUTER JOIN CRS_Way ON CRS_INFO.CRS_Send_Way = CRS_Way.ID LEFT OUTER JOIN CRS_Priority ON CRS_INFO.CRS_Priority_ID = CRS_Priority.ID LEFT OUTER JOIN CRS_SecurityLevel ON CRS_INFO.SecurityLevelID = CRS_SecurityLevel.ID LEFT OUTER JOIN Portal_Users_View ON Portal_Users_View.ID = CRS_INFO.CRS_Initiator LEFT OUTER JOIN AD_DOC_TBL ON CRS_INFO.DocumentID = AD_DOC_TBL.ID LEFT OUTER JOIN CRSTask AS Temp_CRSTask ON CRSTask.ParentTask = Temp_CRSTask.ID LEFT OUTER JOIN Portal_Users_View AS Temp_Portal_Users_View ON Temp_Portal_Users_View.ID = AD_DOC_TBL.Lock_User_ID LEFT OUTER JOIN Portal_Users_View AS Temp1_Portal_Users_View ON Temp1_Portal_Users_View.ID = CRS_INFO.ClosedBy LEFT OUTER JOIN CRSDraft ON CRSTask.ID = CRSDraft.TaskID LEFT OUTER JOIN CRS_Task_Recipient_Vw ON CRSTask.ID = CRS_Task_Recipient_Vw.TaskID --LEFT OUTER JOIN CRSTaskReceiverUsers ON CRSTask.ID = CRSTaskReceiverUsers.CRSTaskID AND CRS_Task_Recipient_Vw.ReceiverID = CRSTaskReceiverUsers.ReceiverID LEFT OUTER JOIN CRSTaskReceiverUserProfile ON CRSTask.ID = CRSTaskReceiverUserProfile.TaskID WHERE Crs_Info.SUBJECT <> 'Blank Subject' AND (CRS_INFO.Subject NOT LIKE '%null%') AND CRS_Info.IsDeleted <> 1 /* AND CRSTask.WFLStatus <> 6 AND CRSTask.WFLStatus <> 8 */ AND ( ( CRS_Task_Recipient_Vw.ReceiverID IN (1, 29) AND CRS_Task_Recipient_Vw.ReceiverType IN (1, 3, 4) ) ) AND 1 = 1 )Codes --Uncomment2-- WHERE Codes.RowNum BETWEEN 1 AND 30 ORDER BY Codes.Task_CreateDate ASC

    Read the article

  • Django QuerySet filter + order_by + limit

    - by handsofaten
    So I have a Django app that processes test results, and I'm trying to find the median score for a certain assessment. I would think that this would work: e = Exam.objects.all() total = e.count() median = int(round(total / 2)) median_exam = Exam.objects.filter(assessment=assessment.id).order_by('score')[median:1] median_score = median_exam.score But it always returns an empty list. I can get the result I want with this: e = Exam.objects.all() total = e.count() median = int(round(total / 2)) exams = Exam.objects.filter(assessment=assessment.id).order_by('score') median_score = median_exam[median].score I would just prefer not to have to query the entire set of exams. I thought about just writing a raw MySQL query that looks something like: SELECT score FROM assess_exam WHERE assessment_id = 5 ORDER BY score LIMIT 690,1 But if possible, I'd like to stay within Django's ORM. Mostly, it's just bothering me that I can't seem to use order_by with a filter and a limit. Any ideas?

    Read the article

  • find rows that fall between a day and time of the week in sql server

    - by Corgalore
    I have a table of rows in MS SQL that contain a start and end day of the week, hour, and time. I need a T-SQL query that can pull rows from that table where GETDATE matches the day of week and time of those rows. Specifically, I need the query to work if a row has a day/time that starts on one day of the week and ends on the next day. Here's the structure I'm working with: _start_day_of_week (int) = 5_start_hour (int) = 15_start_minute (int) = 30 _end_day_of_week (int) = 6_end_hour (int) = 2 _end_minute (int) = 30 _title (string) = 'My Sample Row' _id (int) = 1 How would I retrieve this row given the current DATETIME?

    Read the article

  • How to create a custom ADO Multi Dimensional Catalog with no database

    - by Alan Clark
    Does anyone know of an example of how to dynamically define and build ADO MD (ActiveX Data Objects Multidimensional) catalogs and cube definitions with a set of data other than a database? Background: we have a huge amount of data in our application that we export to a database and then query using the usual SQL joins, groups, sums etc to produce reports. The data in the application is originally in objects and arrays. The problem is the amount of data is so large the export can take 2 hours. So I am trying to figure out a good way of querying the objects in memory, either by a custom OLAP algorithm or library, or ADO MD. But I haven't been able to find an example of using ADO MD without a database behind it. We are using Delphi 2010 so would use ADO ActiveX but I imagine the ADO.NET MD is similar. I realize that if the application data was already stored in a database the problem would solve itself. Also if Delphi had LINQ capability I could query the objects and arrays that way.

    Read the article

  • MySql: Select all entries with count...

    - by Scarface
    Hey guys quick question, I have a query that I want to count all entries it finds, and select all so when I use while($row=mysql_fetch_assoc($query)){ it will list all entries. The problem I am encountering is that while, all the entries are successfully counted and the right number is listed, only the latest entry is select and listed when I echo $row['title']. If I delete , COUNT(*) as total then it selects all but I was wondering if it was possible to use count and select *. I was wondering if anyone knew what I am doing wrong? SELECT *, COUNT(*) as total FROM new_messages WHERE username='$session->username

    Read the article

  • Creating a customized video using Flash and XML

    - by Aaron Ladage
    The problem: I have to create a Flash video (in CS3) that will query a MySQL database and display that data at certain points in the video. The bigger problem: I'm not a Flash/ActionScript developer, so this is all very foreign to me! I've divided this project into two parts: a.) dynamically generate an XML feed from the data using PHP (using an ID number passed in the URL's query string), and b.) be able to work with it in Flash. I've got the first part working, but am pretty lost in Flash. I can parse the XML, but I'm not sure how to set the data up as variables and attach it to a video's cue points. Can anyone point me in the direction of a good tutorial or offer some advice?

    Read the article

  • Getting really weird long Contact Group names

    - by Pentium10
    When looking at the Contact Groups on Google Contacts or in the People application of my HTC Legend phone, I get the groups names ok eg: Friends, Family, VIP, Favorite etc... But in my application I get really wrong names such as "Family" became "System Group: Family" "Friends" became "System Group: Friends" "Favorite" became "Favorite_5656100000000_3245664334564" I use the below code to read these values: public Cursor getFromSystem() { // Get the base URI for the People table in the Contacts content // provider. Uri contacts = ContactsContract.Groups.CONTENT_URI; // Make the query. ContentResolver cr = ctx.getContentResolver(); // Form an array specifying which columns to return. String[] projection = new String[] { ContactsContract.Groups._ID, ContactsContract.Groups.TITLE, ContactsContract.Groups.NOTES }; Cursor managedCursor = cr.query(contacts, projection, ContactsContract.Groups.DELETED + "=0", null, ContactsContract.Groups.TITLE + " COLLATE LOCALIZED ASC"); return managedCursor; } What I am missing?

    Read the article

  • PHP/MySQL allowing current user to edit there account information

    - by user1837896
    i have created 2 pages update.php edit.php we start on edit.php so here is edit.php's script <?php session_start(); $id = $_SESSION["id"]; $username = $_POST["username"]; $fname = $_POST["fname"]; $password = $_POST["password"]; $email = $_POST["email"]; mysql_connect('mysql13.000webhost.com', 'a2670376_Users', 'Password') or die(mysql_error()); echo "MySQL Connection Established! <br>"; mysql_select_db("a2670376_Pass") or die(mysql_error()); echo "Database Found! <br>"; $query = "UPDATE members SET username = '$username', fname = '$fname', password = '$password' WHERE id = '$id'"; $res = mysql_query($query); if ($res) echo "<p>Record Updated<p>"; else echo "Problem updating record. MySQL Error: " . mysql_error(); ?> <form action="update.php" method="post"> <input type="hidden" name="id" value="<?=$id;?>"> ScreenName:<br> <input type='text' name='username' id='username' maxlength='25' style='width:247px' name="username" value="<?=$username;?>"/><br> FullName:<br> <input type='text' name='fname' id='fname' maxlength='20' style='width:248px' name="ud_img" value="<?=$fname;?>"/><br> Email:<br> <input type='text' name='email' id='email' maxlength='50' style='width:250px' name="ud_img" value="<?=$email;?>"/><br> Password:<br> <input type='text' name='password' id='password' maxlength='25' style='width:251px' value="<?=$password;?>"/><br> <input type="Submit"> </form> now here is the update.php page where i am having the MAJOR problem <?php session_start(); mysql_connect('mysql13.000webhost.com', 'a2670376_Users', 'Password') or die(mysql_error()); mysql_select_db("a2670376_Pass") or die(mysql_error()); $id = (int)$_SESSION["id"]; $username = mysql_real_escape_string($_POST["username"]); $fname = mysql_real_escape_string($_POST["fname"]); $email = mysql_real_escape_string($_POST["email"]); $password = mysql_real_escape_string($_POST["password"]); $query="UPDATE members SET username = '$username', fname = '$fname', email = '$email', password = '$password' WHERE id='$id'"; mysql_query($query)or die(mysql_error()); if(mysql_affected_rows()>=1){ echo "<p>($id) Record Updated<p>"; }else{ echo "<p>($id) Not Updated<p>"; } ?> now on edit.php i fill out the form to edit the account "test" while i am logged into it now once the form if filled out i click on |Submit!| button and it takes me to update.php and it returns this (0) Not Updated (0) <= id of user logged in Not Updated <= MySql Error from mysql_query($query)or die(mysql_error()); if(mysql_affected_rows()>=1){ i want it to update the user logged in and if i am not mistaken in this script it says $id = (int)$_SESSION["id"]; witch updates the user with the id of the person who is logged in but it isnt updating its saying that no tables were effected if it helps heres my MySql Database picture just click here http://i50.tinypic.com/21juqfq.png if this could possibly be any help to find the solution i have 2 more files delete.php and delete_ac.php they have can remove users from my sql database and they show the user id and it works there are no bugs in this script at all PLEASE DO NOT MAKE SUGGESTIONS FOR THE SCRIPTS BELOW delete.php first <?php $host="mysql13.000webhost.com"; // Host name $username="a2670376_Users"; // Mysql username $password="PASSWORD"; // Mysql password $db_name="a2670376_Pass"; // Database name $tbl_name="members"; // Table name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // select record from mysql $sql="SELECT * FROM $tbl_name"; $result=mysql_query($sql); ?> <table border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td colspan="8" style="bgcolor: #FFFFFF"><strong><img src="http://i47.tinypic.com/u6ihk.png" height="30" widht="30">Delete data in mysql</strong> </td> </tr> <tr> <td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>UserName</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>FullName</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Password</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Date</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Ip</strong></td> <td align="center" bgcolor="#FFFFFF">&nbsp;</td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['username']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['fname']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['password']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['date']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['ip']; ?></td> <td bgcolor="#FFFFFF"><a href="delete_ac.php?id=<? echo $rows['id']; ?>">delete</a></td> </tr> <?php // close while loop } ?> </table> <?php // close connection; sql_close(); ?> and now delete_ac.php <table width="500" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td colspan="8" bgcolor="#FFFFFF"><strong><img src="http://t2.gstatic.com/images? q=tbn:ANd9GcS_kwpNSSt3UuBHxq5zhkJQAlPnaXyePaw07R652f4StmvIQAAf6g" height="30" widht="30">Removal Of Account</strong> </td> </tr> <tr> <td align="center" bgcolor="#FFFFFF"> <?php $host="mysql13.000webhost.com"; // Host name $username="a2670376_Users"; // Mysql username $password="javascript00"; // Mysql password $db_name="a2670376_Pass"; // Database name $tbl_name="members"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // get value of id that sent from address bar $id=$_GET['id']; // Delete data in mysql from row that has this id $sql="DELETE FROM $tbl_name WHERE id='$id'"; $result=mysql_query($sql); // if successfully deleted if($result){ echo "Deleted Successfully"; echo "<BR>"; echo "<a href='delete.php'>Back to main page</a>"; } else { echo "ERROR"; } ?> <?php // close connection mysql_close(); ?> </td> </tr> </table>

    Read the article

  • SQL GROUP BY - also SELECT not-constant columns

    - by Michal Kosek
    can someone please help me with this query? I have 2 tables in my database: log_visitors: -------------------- id | host_id log_access: -------------------- visitor | document | timestamp "log_access.visitor" links to "log_visitors.id" Currently, I'm using this query: SELECT log_visitors.host_id , MIM(log_access.timestamp) AS min_timestamp FROM log_access INNER JOIN log_visitors ON (log_access.visitor = log_visitors.id) GROUP BY log_visitors.host_id; to get "MIN(timestamp)" for each "host_id" in the database. HERE'S MY QUESTION: I also need to get "document" for that access with that timestamp... I can't simply add "log_access.document" into SELECT list, since it's not constant and I am not grouping by document... Any ideas?

    Read the article

  • ASP.NET Dynamically filtering data

    - by Jasper
    For a project I'm working on, we're looking for a way to dynamically add filters to a page which then control the dataoutput in, for instance, a grid. We want to add the filters dynamically because we want the customer to be able to change which properties can be filtered and what filtertype (textbox, dropdown, colourpicker, etc.) should be used. The filter should work as follows: - The customer links a filter to a certain property and specifies the filtertype (for this example: dropdown). - A user control which contains all the filter loads all filters specified - The filters load all values of the specified property as options. The first time the page loads; this would be the values of all items. - Now the user selects a value from one of the filters; the page reloads - Only items which have the specified filter value are retrieved, the user may specify one or more filters at the same time. - Once a user drills down by filtering, only filtervalues of the retrieved items should be used in the other filters. I have the following problems: - When I create the filters runtime, events are lost because the controls get recreated each postback. - I could place the filters in PreInit which should solve this, but then determining which controls should be loaded becomes a problem since loading all environment vars isn't finished yet - I don't know a good way of returning all the filter values to a central point from which I can make a good query. - The query has to be dynamic. I'm using linq which I want to make dynamic so I don't have to select everything everytime. How to make a dynamic select query based on a string stored in the database? - I have to select items based on the filtervalues and then adjust the rest of the filters to the already made selection. That kind of messes up the whole regular databinding sequence. Any help in one of the above would be great! PS: One thing I thought about was passing along filter values in the postback which would have to be recognizable. That way the server could use them for selection and then create the filters and autoselect the previously selected filtervalues. I'm not quite sure how to acheive this though...

    Read the article

  • LINQ aggregate left join on SQL CE

    - by P Daddy
    What I need is such a simple, easy query, it blows me away how much work I've done just trying to do it in LINQ. In T-SQL, it would be: SELECT I.InvoiceID, I.CustomerID, I.Amount AS AmountInvoiced, I.Date AS InvoiceDate, ISNULL(SUM(P.Amount), 0) AS AmountPaid, I.Amount - ISNULL(SUM(P.Amount), 0) AS AmountDue FROM Invoices I LEFT JOIN Payments P ON I.InvoiceID = P.InvoiceID WHERE I.Date between @start and @end GROUP BY I.InvoiceID, I.CustomerID, I.Amount, I.Date ORDER BY AmountDue DESC The best equivalent LINQ expression I've come up with, took me much longer to do: var invoices = ( from I in Invoices where I.Date >= start && I.Date <= end join P in Payments on I.InvoiceID equals P.InvoiceID into payments select new{ I.InvoiceID, I.CustomerID, AmountInvoiced = I.Amount, InvoiceDate = I.Date, AmountPaid = ((decimal?)payments.Select(P=>P.Amount).Sum()).GetValueOrDefault(), AmountDue = I.Amount - ((decimal?)payments.Select(P=>P.Amount).Sum()).GetValueOrDefault() } ).OrderByDescending(row=>row.AmountDue); This gets an equivalent result set when run against SQL Server. Using a SQL CE database, however, changes things. The T-SQL stays almost the same. I only have to change ISNULL to COALESCE. Using the same LINQ expression, however, results in an error: There was an error parsing the query. [ Token line number = 4, Token line offset = 9,Token in error = SELECT ] So we look at the generated SQL code: SELECT [t3].[InvoiceID], [t3].[CustomerID], [t3].[Amount] AS [AmountInvoiced], [t3].[Date] AS [InvoiceDate], [t3].[value] AS [AmountPaid], [t3].[value2] AS [AmountDue] FROM ( SELECT [t0].[InvoiceID], [t0].[CustomerID], [t0].[Amount], [t0].[Date], COALESCE(( SELECT SUM([t1].[Amount]) FROM [Payments] AS [t1] WHERE [t0].[InvoiceID] = [t1].[InvoiceID] ),0) AS [value], [t0].[Amount] - (COALESCE(( SELECT SUM([t2].[Amount]) FROM [Payments] AS [t2] WHERE [t0].[InvoiceID] = [t2].[InvoiceID] ),0)) AS [value2] FROM [Invoices] AS [t0] ) AS [t3] WHERE ([t3].[Date] >= @p0) AND ([t3].[Date] <= @p1) ORDER BY [t3].[value2] DESC Ugh! Okay, so it's ugly and inefficient when run against SQL Server, but we're not supposed to care, since it's supposed to be quicker to write, and the performance difference shouldn't be that large. But it just doesn't work against SQL CE, which apparently doesn't support subqueries within the SELECT list. In fact, I've tried several different left join queries in LINQ, and they all seem to have the same problem. Even: from I in Invoices join P in Payments on I.InvoiceID equals P.InvoiceID into payments select new{I, payments} generates: SELECT [t0].[InvoiceID], [t0].[CustomerID], [t0].[Amount], [t0].[Date], [t1].[InvoiceID] AS [InvoiceID2], [t1].[Amount] AS [Amount2], [t1].[Date] AS [Date2], ( SELECT COUNT(*) FROM [Payments] AS [t2] WHERE [t0].[InvoiceID] = [t2].[InvoiceID] ) AS [value] FROM [Invoices] AS [t0] LEFT OUTER JOIN [Payments] AS [t1] ON [t0].[InvoiceID] = [t1].[InvoiceID] ORDER BY [t0].[InvoiceID] which also results in the error: There was an error parsing the query. [ Token line number = 2, Token line offset = 5,Token in error = SELECT ] So how can I do a simple left join on a SQL CE database using LINQ? Am I wasting my time?

    Read the article

  • Configure Hibernate validation for bean

    - by sergionni
    Hi. I need to perform validation based on SQL query result. Query is defined as annotation - as @NamedQuery in my entity bean. According to Hibernate documentation(doc), there is possibility to validate bean on following operations: pre-update pre-insert pre-delete looks like: <hibernate-configuration> <session-factory> ... <event type="pre-update"> <listener class="org.hibernate.cfg.beanvalidation.BeanValidationEventListener"/> </event> <event type="pre-insert"> <listener class="org.hibernate.cfg.beanvalidation.BeanValidationEventListener"/> </event> <event type="pre-delete"> <listener class="org.hibernate.cfg.beanvalidation.BeanValidationEventListener"/> </event> </hibernate-configuration> The question is how to connect my bean with the validation configuration, described above.

    Read the article

  • Using one sqlconnection/sqlcommand through 2 DB-bound methods

    - by dotnetdev
    I have a class with a method which gets data from a database through a SELECT stored proc. This method uses a SqlDbReader by returning ExecuteReader() on a SqlCommand. The connection and everything is made in this method, with fields (such as connection string) set as class level fields. I now need to populate an array based on the results of this query (so the columns of each row will go into the array with its own index). However, this query will not select all of the data from one table which is involved. I can write the queries to get what I need, but how can I use one connection throughout the class? If I instantiate the connection object and call Open() in the constructor, I get an exception @ runtime. I'm hoping for something like this: // At class level: sqlconn.Open(); // sqlcommand set up Method() { // Fire stored proc // Insert results in a collection } Method2() { // Pass same collection in (use same one), // Add new row columns into same collection } How can I do this with strict performance in mind? Thanks

    Read the article

  • MySQL Connection error in spring MVC

    - by nidhi
    i am working on spring MVC facing the following exception while connecting to my sql. Blocorg.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:572) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:728) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:744) at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:775) at com.trackmeetings.dao.InboxDAOImpl.getNotificationsCount(InboxDAOImpl.java:51) at com.trackmeetings.service.InboxService.getNotificationsCount(InboxService.java:56) at com.trackmeetings.controller.HomePageController.getLogInUserDetails(HomePageController.java:80) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:175) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:421) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:409) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:774) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560) at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.trackmeetings.util.NoCacheFilter.doFilter(NoCacheFilter.java:56) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Thread.java:680) Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225) at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77) ... 38 more Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failurekquote how can i resolve this. please help,

    Read the article

  • ActiveRecord Mapping

    - by Diego Dias
    Good morning, guys. I'm with a problem with the query that NHibernate generates. My mapping is like bellow: public class Matricula { [BelongsTo("IdTurma", NotNull=True)] public Turma {get;set;} } public class Turma { [BelongsTo("IdCurso", NotNull=True)] public Curso {get;set;} } public class Curso { [PrimaryKey("IdCurso", Generator=PrimaryKeyType.Identity)] public int IdCurso{get;set;} } The problem is: The Nh generates the query with left where should be inner. Between Matricula and Turma they makes inner, but between Turma and Curso they makes left outer. What's problem? What's wrong?

    Read the article

  • Why Is Vertical Resolution Monitor Resolution so Often a Multiple of 360?

    - by Jason Fitzpatrick
    Stare at a list of monitor resolutions long enough and you might notice a pattern: many of the vertical resolutions, especially those of gaming or multimedia displays, are multiples of 360 (720, 1080, 1440, etc.) But why exactly is this the case? Is it arbitrary or is there something more at work? Today’s Question & Answer session comes to us courtesy of SuperUser—a subdivision of Stack Exchange, a community-driven grouping of Q&A web sites. The Question SuperUser reader Trojandestroy recently noticed something about his display interface and needs answers: YouTube recently added 1440p functionality, and for the first time I realized that all (most?) vertical resolutions are multiples of 360. Is this just because the smallest common resolution is 480×360, and it’s convenient to use multiples? (Not doubting that multiples are convenient.) And/or was that the first viewable/conveniently sized resolution, so hardware (TVs, monitors, etc) grew with 360 in mind? Taking it further, why not have a square resolution? Or something else unusual? (Assuming it’s usual enough that it’s viewable). Is it merely a pleasing-the-eye situation? So why have the display be a multiple of 360? The Answer SuperUser contributor User26129 offers us not just an answer as to why the numerical pattern exists but a history of screen design in the process: Alright, there are a couple of questions and a lot of factors here. Resolutions are a really interesting field of psychooptics meeting marketing. First of all, why are the vertical resolutions on youtube multiples of 360. This is of course just arbitrary, there is no real reason this is the case. The reason is that resolution here is not the limiting factor for Youtube videos – bandwidth is. Youtube has to re-encode every video that is uploaded a couple of times, and tries to use as little re-encoding formats/bitrates/resolutions as possible to cover all the different use cases. For low-res mobile devices they have 360×240, for higher res mobile there’s 480p, and for the computer crowd there is 360p for 2xISDN/multiuser landlines, 720p for DSL and 1080p for higher speed internet. For a while there were some other codecs than h.264, but these are slowly being phased out with h.264 having essentially ‘won’ the format war and all computers being outfitted with hardware codecs for this. Now, there is some interesting psychooptics going on as well. As I said: resolution isn’t everything. 720p with really strong compression can and will look worse than 240p at a very high bitrate. But on the other side of the spectrum: throwing more bits at a certain resolution doesn’t magically make it better beyond some point. There is an optimum here, which of course depends on both resolution and codec. In general: the optimal bitrate is actually proportional to the resolution. So the next question is: what kind of resolution steps make sense? Apparently, people need about a 2x increase in resolution to really see (and prefer) a marked difference. Anything less than that and many people will simply not bother with the higher bitrates, they’d rather use their bandwidth for other stuff. This has been researched quite a long time ago and is the big reason why we went from 720×576 (415kpix) to 1280×720 (922kpix), and then again from 1280×720 to 1920×1080 (2MP). Stuff in between is not a viable optimization target. And again, 1440P is about 3.7MP, another ~2x increase over HD. You will see a difference there. 4K is the next step after that. Next up is that magical number of 360 vertical pixels. Actually, the magic number is 120 or 128. All resolutions are some kind of multiple of 120 pixels nowadays, back in the day they used to be multiples of 128. This is something that just grew out of LCD panel industry. LCD panels use what are called line drivers, little chips that sit on the sides of your LCD screen that control how bright each subpixel is. Because historically, for reasons I don’t really know for sure, probably memory constraints, these multiple-of-128 or multiple-of-120 resolutions already existed, the industry standard line drivers became drivers with 360 line outputs (1 per subpixel). If you would tear down your 1920×1080 screen, I would be putting money on there being 16 line drivers on the top/bottom and 9 on one of the sides. Oh hey, that’s 16:9. Guess how obvious that resolution choice was back when 16:9 was ‘invented’. Then there’s the issue of aspect ratio. This is really a completely different field of psychology, but it boils down to: historically, people have believed and measured that we have a sort of wide-screen view of the world. Naturally, people believed that the most natural representation of data on a screen would be in a wide-screen view, and this is where the great anamorphic revolution of the ’60s came from when films were shot in ever wider aspect ratios. Since then, this kind of knowledge has been refined and mostly debunked. Yes, we do have a wide-angle view, but the area where we can actually see sharply – the center of our vision – is fairly round. Slightly elliptical and squashed, but not really more than about 4:3 or 3:2. So for detailed viewing, for instance for reading text on a screen, you can utilize most of your detail vision by employing an almost-square screen, a bit like the screens up to the mid-2000s. However, again this is not how marketing took it. Computers in ye olden days were used mostly for productivity and detailed work, but as they commoditized and as the computer as media consumption device evolved, people didn’t necessarily use their computer for work most of the time. They used it to watch media content: movies, television series and photos. And for that kind of viewing, you get the most ‘immersion factor’ if the screen fills as much of your vision (including your peripheral vision) as possible. Which means widescreen. But there’s more marketing still. When detail work was still an important factor, people cared about resolution. As many pixels as possible on the screen. SGI was selling almost-4K CRTs! The most optimal way to get the maximum amount of pixels out of a glass substrate is to cut it as square as possible. 1:1 or 4:3 screens have the most pixels per diagonal inch. But with displays becoming more consumery, inch-size became more important, not amount of pixels. And this is a completely different optimization target. To get the most diagonal inches out of a substrate, you want to make the screen as wide as possible. First we got 16:10, then 16:9 and there have been moderately successful panel manufacturers making 22:9 and 2:1 screens (like Philips). Even though pixel density and absolute resolution went down for a couple of years, inch-sizes went up and that’s what sold. Why buy a 19″ 1280×1024 when you can buy a 21″ 1366×768? Eh… I think that about covers all the major aspects here. There’s more of course; bandwidth limits of HDMI, DVI, DP and of course VGA played a role, and if you go back to the pre-2000s, graphics memory, in-computer bandwdith and simply the limits of commercially available RAMDACs played an important role. But for today’s considerations, this is about all you need to know. Have something to add to the explanation? Sound off in the the comments. Want to read more answers from other tech-savvy Stack Exchange users? Check out the full discussion thread here.     

    Read the article

  • Raise the time-out for a single webservice

    - by Carra
    My web configuration looks as follows: <system.web> <compilation debug="false"/> <httpRuntime executionTimeout="90"/> </system.web> This is fine for most webservices but one webservice has a query that is running a very long (5 minutes) time and will be stopped before it has finished. Is it possible to set the runtime to 5 minutes for this webservice alone? I've thought about running the database query async (fire and forget) but it doesn't seem possible with sybase/oracle through ODBC.

    Read the article

  • Automating WebTrends analysis

    - by tridium
    Every week I access server logs processed by WebTrends (for about 7 profiles) and copy ad clickthrough and visitor information into Excel spreadsheets. A lot of it is just accessing certain sections and finding the right title and then copying the unique visitor information. I tried using WebTrends' built-in query tool but that is really poorly done (only uses a drag-and-drop system instead of text-based) and it has a maximum number of parameters and maximum length of queries to query with. As far as I know, the tools in WebTrends are not suitable to my purpose of automating the entire web metrics gathering process. I've gotten access to the raw server logs, but it seems redundant to parse that given that they are already being processed by WebTrends. To me it seems very scriptable, but how would I go about doing that? Is screen-scraping an option?

    Read the article

  • Perl TDS character sets

    - by skiphoppy
    I'm using the FreeTDS driver with DBD::Sybase, connecting to an MS SQL Server. When I query certain values of certain records, I get this error: DBD::Sybase::st fetchrow_arrayref failed: OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (9) NUMBER = (99) Server , database Message String: WARNING! Some character(s) could not be converted into client's character set. Unconverted bytes were changed to question marks ('?'). This seems to happen for records that contain special Windows character-set characters, such as curly quotes, copied and pasted from people's Outlook and Word messages. Unfortunately, I do not have any control of this database; sanitizing the input on the way in is obviously the way to go, but is not available to me. What FreeTDS settings do I need to change to be able to successfully query these records?

    Read the article

  • Pear MDB2 class and raiserror exceptions in MSSQL

    - by drholzmichl
    Hi, in MSSQL it's possible to raise an error with raiserror(). I want to use a severity, which doesn't interrupt the connection. This error is raised in a stored procedure. In SQL Management Studio all is fine and I get my error code when executing this SP. But when trying to execute this SP via MDB2 in PHP5 this doesn't work. All I get is an empty array. MDB2 object is created via (including needed options): $db =& MDB2::connect($dsn); $db->setFetchMode(MDB2_FETCHMODE_ASSOC); $db->setOption('portability',MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL); The following works (I get a PEAR error): $db->query("RAISERROR('test',11,0);"); But when calling a stored procedure which raises this error via $db->query("EXEC sp_raise_error"); there is not output. What's wrong?

    Read the article

< Previous Page | 436 437 438 439 440 441 442 443 444 445 446 447  | Next Page >