Search Results

Search found 3379 results on 136 pages for 'datetime'.

Page 127/136 | < Previous Page | 123 124 125 126 127 128 129 130 131 132 133 134  | Next Page >

• Reorganizing MySQL table to multiple rows by timestamp.

  - by Ben Burleson

  OK MySQL Wizards: I have a table of position data from multiple probes defined as follows: +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | time | datetime | NO | | NULL | | | probe_id | char(3) | NO | | NULL | | | position | float | NO | | NULL | | +----------+----------+------+-----+---------+-------+ A simple select outputs something like this: +---------------------+----------+----------+ | time | probe_id | position | +---------------------+----------+----------+ | 2010-05-05 14:16:42 | 00A | 0.0045 | | 2010-05-05 14:16:42 | 00B | 0.0005 | | 2010-05-05 14:16:42 | 00C | 0.002 | | 2010-05-05 14:16:42 | 01A | 0 | | 2010-05-05 14:16:42 | 01B | 0.001 | | 2010-05-05 14:16:42 | 01C | 0.0025 | | 2010-05-05 14:16:43 | 00A | 0.0045 | | 2010-05-05 14:16:43 | 00B | 0.0005 | | 2010-05-05 14:16:43 | 00C | 0.002 | | 2010-05-05 14:16:43 | 01A | 0 | | . | . | . | | . | . | . | | . | . | . | +---------------------+----------+----------+ However, I'd like to output something like this: +---------------------+--------+--------+-------+-----+-------+--------+ | time | 00A | 00B | 00C | 01A | 01B | 01C | +---------------------+--------+--------+-------+-----+-------+--------+ | 2010-05-05 14:16:42 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 | | 2010-05-05 14:16:43 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 | | 2010-05-05 14:16:44 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 | | 2010-05-05 14:16:45 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 | | 2010-05-05 14:16:46 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 | | 2010-05-05 14:16:47 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 | | . | . | . | . | . | . | . | | . | . | . | . | . | . | . | | . | . | . | . | . | . | . | +---------------------+--------+--------+-------+-----+-------+--------+ Ideally, the different probe position columns are dynamically generated based on data in the table. Is this possible, or am I pulling my hair out for nothing? I've tried GROUP BY time with GROUP_CONCAT that roughly gets the data out, but I can't separate that output into probe_id columns. mysql SELECT time, GROUP_CONCAT(probe_id), GROUP_CONCAT(position) FROM MG41 GROUP BY time LIMIT 10; +---------------------+-------------------------+------------------------------------+ | time | GROUP_CONCAT(probe_id) | GROUP_CONCAT(position) | +---------------------+-------------------------+------------------------------------+ | 2010-05-05 14:16:42 | 00A,00B,00C,01A,01B,01C | 0.0045,0.0005,0.002,0,0.001,0.0025 | | 2010-05-05 14:16:43 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 | | 2010-05-05 14:16:44 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 | | 2010-05-05 14:16:45 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 | | 2010-05-05 14:16:46 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 | | 2010-05-05 14:16:47 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 | | 2010-05-05 14:16:48 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 | | 2010-05-05 14:16:49 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 | | 2010-05-05 14:16:50 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 | | 2010-05-05 14:16:51 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 | +---------------------+-------------------------+------------------------------------+

  Read the article

• closing MQ connection

  - by OakvilleWork

  Good afternoon, I wrote a project to Get Park Queue Info from the IBM MQ, it has producing an error when attempting to close the connection though. It is written in java. Under application in Event Viewer on the MQ machine it displays two errors. They are: “Channel program ended abnormally. Channel program ‘system.def.surconn’ ended abnormally. Look at previous error messages for channel program ‘system.def.surconn’ in the error files to determine the cause of the failure. The other message states: “Error on receive from host rnanaj (10.10.12.34) An error occurred receiving data from rnanaj (10.10.12.34) over tcp/ip. This may be due to a communications failure. The return code from tcp/ip recv() call was 10054 (X’2746’). Record these values.” This must be something how I try to connect or close the connection, below I have my code to connect and close, any ideas?? Connect: _logger.info("Start"); File outputFile = new File(System.getProperty("PROJECT_HOME"), "run/" + this.getClass().getSimpleName() + "." + System.getProperty("qmgr") + ".txt"); FileUtils.mkdirs(outputFile.getParentFile()); Connection jmsConn = null; Session jmsSession = null; QueueBrowser queueBrowser = null; BufferedWriter commandsBw = null; try { // get queue connection MQConnectionFactory MQConn = new MQConnectionFactory(); MQConn.setHostName(System.getProperty("host")); MQConn.setPort(Integer.valueOf(System.getProperty("port"))); MQConn.setQueueManager(System.getProperty("qmgr")); MQConn.setChannel("SYSTEM.DEF.SVRCONN"); MQConn.setTransportType(JMSC.MQJMS_TP_CLIENT_MQ_TCPIP); jmsConn = (Connection) MQConn.createConnection(); jmsSession = jmsConn.createSession(false, Session.AUTO_ACKNOWLEDGE); Queue jmsQueue = jmsSession.createQueue("PARK"); // browse thru messages queueBrowser = jmsSession.createBrowser(jmsQueue); Enumeration msgEnum = queueBrowser.getEnumeration(); commandsBw = new BufferedWriter(new FileWriter(outputFile)); // String line = "DateTime\tMsgID\tOrigMsgID\tCorrelationID\tComputerName\tSubsystem\tDispatcherName\tProcessor\tJobID\tErrorMsg"; commandsBw.write(line); commandsBw.newLine(); while (msgEnum.hasMoreElements()) { Message message = (Message) msgEnum.nextElement(); line = dateFormatter.format(new Date(message.getJMSTimestamp())) + "\t" + message.getJMSMessageID() + "\t" + message.getStringProperty("pkd_orig_jms_msg_id") + "\t" + message.getJMSCorrelationID() + "\t" + message.getStringProperty("pkd_computer_name") + "\t" + message.getStringProperty("pkd_subsystem") + "\t" + message.getStringProperty("pkd_dispatcher_name") + "\t" + message.getStringProperty("pkd_processor") + "\t" + message.getStringProperty("pkd_job_id") + "\t" + message.getStringProperty("pkd_sysex_msg"); _logger.info(line); commandsBw.write(line); commandsBw.newLine(); } } Close: finally { IO.close(commandsBw); if (queueBrowser != null) { try { queueBrowser.close();} catch (Exception ignore) {}} if (jmsSession != null) { try { jmsSession.close();} catch (Exception ignore) {}} if (jmsConn != null) { try { jmsConn.stop();} catch (Exception ignore) {}} }

  Read the article

• LINQ Query returns false when it should be true.

  - by deliriousDev

  I have the following LINQ query written by a former developer and it isn't working when it should. public bool IsAvailable(Appointment appointment) { var appointments = _appointmentRepository.Get; var shifts = _scheduleRepository.Get; var city = _customerRepository.Find(appointment.CustomerId).City ?? appointment.Customer.City; const int durationHour = 1; DateTime scheduledEndDate = appointment.ScheduledTime.Add(new TimeSpan(durationHour, 0, 0)); var inWorkingHours = shifts .Where(x => //Check if any available working hours x.Employee.City == city && x.ShiftStart <= appointment.ScheduledTime && x.ShiftEnd >= scheduledEndDate && //check if not booked yet !appointments .Where(a => (appointment.Id == 0 || a.Id != appointment.Id) && a.Employee.Id == x.Employee.Id && ( (a.ScheduledTime <= appointment.ScheduledTime && appointment.ScheduledTime <= EntityFunctions.AddHours(a.ScheduledTime, durationHour)) || (a.ScheduledTime <= scheduledEndDate && scheduledEndDate <= EntityFunctions.AddHours(a.ScheduledTime, durationHour)) )) .Select(a => a.Employee.Id) .Contains(x.Employee.Id) ); if (inWorkingHours.Any()) { var assignedEmployee = inWorkingHours.FirstOrDefault().Employee; appointment.EmployeeId = assignedEmployee.Id; appointment.Employee = assignedEmployee; return true; } return false; } The query is suppose to handle the following scenarios Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time But Does not match any employees in same city - (Return true, Assign as "Unassigned") Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time AND Employee for that shift is in the same city as the customer (Return True AND Assign to the employee) If the customer is NOT in the same city as an employee we assign the appointment as "Unassigned" as along as the scheduledTime is within an of the employees shift start/end times If the customer is in the same city as an employee we assign the appointment to one of the employees (firstOrdefault) and occupy that timeslot. Appointments CAN NOT overlap (Assigned Ones). Unassigned can't overlap each other. This query use to work (I've been told). But now it doesn't and I have tried refactoring it and various other paths with no luck. I am now on week two and just don't know where the issue in the query is or how to write it. Let me know if I need to post anything further. I have verified appointments, shifts, city all populate with valid data so the issue doesn't appear to be with null or missing data.

  Read the article

• jQuery post request is not sent until first post request is compleated

  - by Champ

  I have a function which have a long execution time. public void updateCampaign() { context.Session[processId] = "0|Fetching Lead360 Campaign"; Lead360 objLead360 = new Lead360(); string campaignXML = objLead360.getCampaigns(); string todayDate = DateTime.Now.ToString("dd-MMMM-yyyy"); context.Session[processId] = "1|Creating File for Lead360 Campaign on " + todayDate; string fileName = HttpContext.Current.Server.MapPath("campaigns") + todayDate + ".xml"; objLead360.createFile(fileName, campaignXML); context.Session[processId] = "2|Reading The latest Lead360 Campaign"; string file = File.ReadAllText(fileName); context.Session[processId] = "3|Updating Lead360 Campaign"; string updateStatus = objLead360.updateCampaign(fileName); string[] statusArr = updateStatus.Split('|'); context.Session[processId] = "99|" + statusArr[0] + " New Inserted , " + statusArr[1] + " Updated , With " + statusArr[2] + " Error , "; } So to track the Progress of the function I wrote a another function public void getProgress() { if (context.Session[processId] == null) { string json = "{\"error\":true}"; Response.Write(json); Response.End(); }else{ string[] status = context.Session[processId].ToString().Split('|'); if (status[0] == "99") context.Session.Remove(processId); string json = "{\"error\":false,\"statuscode\":" + status[0] + ",\"statusmsz\":\"" + status[1] + "\" }"; Response.Write(json); Response.End(); } } To call this by jQuery post request is used reqUrl = "AjaxPages/lead360Campaign.aspx?processid=" + progressID + "&action=updatecampaign"; $.post(reqUrl); setTimeout(getProgress, 500); get getProgress is : function getProgress() { reqUrl = "AjaxPages/lead360Campaign.aspx?processid=" + progressID + "&action=getProgress"; $.post(reqUrl, function (response) { var progress = jQuery.parseJSON(response); console.log(progress) if (progress.error) { $("#fetchedCampaign .waitingMsz").html("Some error occured. Please try again later."); $("#fetchedCampaign .waitingMsz").css({ "background": "url(common/images/ajax_error.jpg) no-repeat center 6px" }); return; } if (progress.statuscode == 99) { $("#fetchedCampaign .waitingMsz").html("Update Status :"+ progress.statusmsz ); $("#fetchedCampaign .waitingMsz").css({ "background": "url(common/images/ajax_loded.jpg) no-repeat center 6px" }); return; } $("#fetchedCampaign .waitingMsz").html("Please Wait... " + progress.statusmsz); setTimeout(getProgress, 500); }); } But the problem is that I can't see the intermediate message. Only the last message is been displayed after a long lime of ajax loading message Also on the browser console I just see that after a long time first requested is completed and after that the second request is completed. but there should be for getProgress ? I have checked jquery.doc and it says that $post is an asynchronous request. Can anyone please explain what is wrong with the code or logic?

  Read the article

• How can I edit an entity in MVC4 with EF5 which has a unique constraint?

  - by Yoeri

  [HttpPost] public ActionResult Edit(Car car) { if (ModelState.IsValid) { db.Entry(car).State = EntityState.Modified; db.SaveChanges(); return RedirectToAction("Index"); } return View(car); } This is a controller method scaffolded by MCV 4 My "car" entity has a unique field: LicensePlate. I have custom validation on my Entity: Validation: public partial class Car { partial void ValidateObject(ref List<ValidationResult> validationResults) { using (var db = new GarageIncEntities()) { if (db.Cars.Any(c => c.LicensePlate.Equals(this.LicensePlate))) { validationResults.Add( new ValidationResult("This licenseplate already exists.", new string[]{"LicensePlate"})); } } } } should it be usefull, my car entity: public partial class Car:IValidatableObject { public int Id { get; set; } public string Color { get; set; } public int Weight { get; set; } public decimal Price { get; set; } public string LicensePlate { get; set; } public System.DateTime DateOfSale { get; set; } public int Type_Id { get; set; } public int Fuel_Id { get; set; } public virtual CarType Type { get; set; } public virtual Fuel Fuel { get; set; } public IEnumerable<ValidationResult> Validate(ValidationContext validationContext) { var result = new List<ValidationResult>(); ValidateObject(ref result); return result; } partial void ValidateObject(ref List<ValidationResult> validationResults); } QUESTION: Everytime I edit a car, it raises an error: Validation failed for one or more entities. See 'EntityValidationErrors' property for more details. The error is the one raised by my validation, saying it can't edit because there is already a car with that license plate. If anyone could point me in the right direction to fix this, that would be great! I searched but couldn't find anything, so even related posts are welcome!

  Read the article

• NHibernate Many to Many delete all my data in the table

  - by Daoming Yang

  I would love to thank @Stefan Steinegger and @David helped me out yesterday with many-to-many mapping. I have 3 tables which are "News", "Tags" and "News_Tags" with Many-To-Many relationship and the "News_Tags" is the link table. If I delete one of the news records, the following mappings will delete all my news records which have the same tags. One thing I need to notice, I only allowed unique tag stored in the "Tag" table. This mapping make sense for me, it will delete the tag and related News records, but how can I implement a tagging system with NHibernate? Can anyone give me some suggestion? Many thanks. Daoming. News Mapping: <class name="New" table="News" lazy="false"> <id name="NewID"> <generator class="identity" /> </id> <property name="Title" type="String"></property> <property name="Description" type="String"></property> <set name="TagsList" table="New_Tags" lazy="false" inverse="true" cascade="all"> <key column="NewID" /> <many-to-many class="Tag" column="TagID" /> </set> </class> Tag Mapping: <class name="Tag" table="Tags" lazy="false"> <id name="TagID"> <generator class="identity" /> </id> <property name="TagName" type="String"></property> <property name="DateCreated" type="DateTime"></property> <!--inverse="true" has been defined in the "News mapping"--> <set name="NewsList" table="New_Tags" lazy="false" cascade="all"> <key column="TagID" /> <many-to-many class="New" column="NewID" /> </set> </class>

  Read the article

• Modify values on-the-fly during SqlAdapter.Fill( )

  - by Timothy

  What would the proper way be to modify values on the fly as they are loaded into a DataTable by SqlAdapter.Fill()? I have globalized my application's log messages. An integer indicating the event type and serialized data relevant to the event is stored in the database as show below. When I display the logged events through a DataGridView control to the user, I interpolate the data to a formatting string. event_type event_timestamp event_details ============================================ 3 2010-05-04 20:49:58 jsmith 1 2010-05-04 20:50:42 jsmith ... I am currently iterating through the DataTable's rows to format the messages. public class LogDataTable : DataTable { public LogDataTable() { Locale = CultureInfo.CurrentCulture; Columns.AddRange(new DataColumn[] { new DataColumn("event_type", typeof(Int32)), new DataColumn("event_timestamp", typeof(DateTime)), new DataColumn("event_details", typeof(String))}); } } ... using (SqlDataAdapter adapter = new SqlDataAdapter(...)) { adapter.SelectCommand.Parameters.AddRange(new Object[] { ... }); adapter.Fill(table); } foreach (DataRow row in table.Rows) { switch ((LogEventType)row["event_type"]) { case LogEventType.Create: row["event_details"] = String.Format(Resources.Strings.LogEventCreateMsg, row["event_details"]; break; case LogEventType.Create: row["event_details"] = String.Format(Resources.Strings.LogEventCreateMsg, row["event_details"]; break; ... The end result as displayed would resemble: Type Date and Time Details ==================================================================== [icon] 2010-05-04 20:49:58 Failed login attempt with username jsmith [icon] 2010-05-04 20:50:42 Successful login with username jsmith ... It seems wasteful to iterate the result set twice-- once as the table is filled by the adapter, and again to perform the replacements. I would really like to do the replacement on-the-fly in my LogDataTable class as it is being populated. I have tried overriding an OnRowChanging method in LogDataTable, which throws an InRowChangingEventException. protected override void OnRowChanging(DataRowChangeEventArgs e) { base.OnRowChanging(e); switch ((LogEventType)row["event_type"]) ... I have tried overriding an OnRowChanged method, which throws a StackOverflowException (I assume changing it re-triggers the method ad infinitum?). I have tried overriding an OnTableNewRow method, which does not throw an exception but appears not to be invoked (I assume only when a user adds a row in the view, which I've prevented). I'd greatly appreciate any assistance anyone can give me.

  Read the article

• ASP.NET MVC: How can I explain an invalid type violation to an end-user with Html.ValidationSummary?

  - by Terminal Frost

  Serious n00b warning here; please take mercy! So I finished the Nerd Dinner MVC Tutorial and I'm now in the process of converting a VB.NET application to ASP.NET MVC using the Nerd Dinner program as a sort of rough template. I am using the "IsValid / GetRuleViolations()" pattern to identify invalid user input or values that violate business rules. I am using LINQ to SQL and am taking advantage of the "OnValidate()" hook that allows me to run the validation and throw an application exception upon trying to save changes to the database via the CustomerRepository class. Anyway, everything works well, except that by the time the form values reach my validation method invalid types have already been converted to a default or existing value. (I have a "StreetNumber" property that is an integer, though I imagine this would be a problem for DateTime or any other non-strings as well.) Now, I am guessing that the UpdateModel() method throws an exception and then alters the value because the Html.ValidationMessage is displayed next to the StreetNumber field but my validation method never sees the original input. There are two problems with this: While the Html.ValidationMessage does signal that something is wrong, there is no corresponding entry in the Html.ValidationSummary. If I could even get the exception message to show up there indicating an invalid cast or something that would be better than nothing. My validation method which resides in my Customer partial class never sees the original user input so I do not know if the problem is a missing entry or an invalid type. I can't figure out how I can keep my validation logic nice and neat in one place and still get access to the form values. I could of course write some logic in the View that processes the user input, however that seems like the exact opposite of what I should be doing with MVC. Do I need a new validation pattern or is there some way to pass the original form values to my model class for processing? CustomerController Code // POST: /Customers/Edit/[id] [AcceptVerbs(HttpVerbs.Post)] public ActionResult Edit(int id, FormCollection formValues) { Customer customer = customerRepository.GetCustomer(id); try { UpdateModel(customer); customerRepository.Save(); return RedirectToAction("Details", new { id = customer.AccountID }); } catch { foreach (var issue in customer.GetRuleViolations()) ModelState.AddModelError(issue.PropertyName, issue.ErrorMessage); } return View(customer); }

  Read the article

• how to design this relation in a DB schema

  - by raticulin

  I have a table Car in my db, one of the columns is purchaseDate. I want to be able to tag every car with a number of Policies (limited to 10 policies). Each policy has a time to life (ttl, a duration of time, like '5 years', '10 months' etc), that is, for how long since the car's purchaseDate the policy can be applied. I need to perform the following actions: when inserting a Car, it will be set with a number of Policies (at least one is set) sometimes a Car will be updated to add/remove a Policy searches must be done taking into account date/policies, for example: 'select all cars that are not covered by any policy as of today' My current design is (pol0..pol9 are the policies): CREATE TABLE Car ( id int NOT NULL IDENTITY(1,1), purchaseDate datetime NOT NULL, //more stuff... pol0 smallint default NULL, pol1 smallint default NULL, pol2 smallint default NULL, pol3 smallint default NULL, pol4 smallint default NULL, pol5 smallint default NULL, pol6 smallint default NULL, pol7 smallint default NULL, pol8 smallint default NULL, pol9 smallint default NULL, PRIMARY KEY (id) ) CREATE TABLE Policy ( id smallint NOT NULL, name varchar(50) collate Latin1_General_BIN NOT NULL, ttl varchar(100) collate Latin1_General_BIN NOT NULL, PRIMARY KEY (id) ) The problem I am facing is that the sql to perform the query above is a nightmare to write. As I don't know in which column each policy can be, so I have to check all columns for every policy etc etc. So I am wondering wether it is worth changing this. My questions are: The smallint as Policy id was chosen instead of an 'int IDENTITY' in order to save some space as there are going to be millions of Car records. It just adds complexity when creating a Policy as we must handle the id etc. Was it worth doing this? I am thinking that maybe there is a much better design? Obviously we could move the policy/car relation to its own table CarPolicy, benefits would be: no limit on 10 policies per car adding/removing etc much easier when only the default policy is applied (when no others are applied one called Default policy is applied), we could signal that by not having any entry in CarPolicy, now this is just done inserting the Default policy id in one of the columns. The cons are that we would need to change the DB, ORM classes etc. What would you recommend? Maybe there is another smart way to implement this that we are not aware without using the CarPolicy table?

  Read the article

• multithreading with database

  - by Darsin

  I am looking out for a strategy to utilize multithreading (probably asynchronous delegates) to do a synchronous operation. I am new to multithreading so i will outline my scenario first. This synchronous operation right now is done for one set of data (portfolio) based on the the parameters provided. The (psudeo-code) implementation is given below: public DataSet DoTests(int fundId, DateTime portfolioDate) { // Get test results for the portfolio // Call the database adapter method, which in turn is a stored procedure, // which in turns runs a series of "rule" stored procs and fills a local temp table and returns it back. DataSet resultsDataSet = GetTestResults(fundId, portfolioDate); try { // Do some local processing on the results DoSomeProcessing(resultsDataSet); // Save the results in Test, TestResults and TestAllocations tables in a transaction. // Sets a global transaction which is provided to all the adapter methods called below // It is defined in the Base class StartTransaction("TestTransaction"); // Save Test and get a testId int testId = UpdateTest(resultsDataSet); // Adapter method, uses the same transaction // Update testId in the other tables in the dataset UpdateTestId(resultsDataSet, testId); // Update TestResults UpdateTestResults(resultsDataSet); // Adapter method, uses the same transaction // Update TestAllocations UpdateTestAllocations(resultsDataSet); // Adapter method, uses the same transaction // It is defined in the base class CommitTransaction("TestTransaction"); } catch { RollbackTransaction("TestTransaction"); } return resultsDataSet; } Now the requirement is to do it for multiple set of data. One way would be to call the above DoTests() method in a loop and get the data. I would prefer doing it in parallel. But there are certain catches: StartTransaction() method creates a connection (and transaction) every time it is called. All the underlying database tables, procedures are the same for each call of DoTests(). (obviously). Thus my question are: Will using multithreading anyway improve performance? What are the chances of deadlock especially when new TestId's are being created and the Tests, TestResults and TestAllocations are being saved? How can these deadlocked be handled? Is there any other more efficient way of doing the above operation apart from looping over the DoTests() method repeatedly?

  Read the article

• Stored procedure optimization

  - by George Zacharia

  Hi, i have a stored procedure which takes lot of time to execure .Can any one suggest a better approch so that the same result set is achived. ALTER PROCEDURE [dbo].[spFavoriteRecipesGET] @USERID INT, @PAGENUMBER INT, @PAGESIZE INT, @SORTDIRECTION VARCHAR(4), @SORTORDER VARCHAR(4),@FILTERBY INT AS BEGIN DECLARE @ROW_START INT DECLARE @ROW_END INT SET @ROW_START = (@PageNumber-1)* @PageSize+1 SET @ROW_END = @PageNumber*@PageSize DECLARE @RecipeCount INT DECLARE @RESULT_SET_TABLE TABLE ( Id INT NOT NULL IDENTITY(1,1), FavoriteRecipeId INT, RecipeId INT, DateAdded DATETIME, Title NVARCHAR(255), UrlFriendlyTitle NVARCHAR(250), [Description] NVARCHAR(MAX), AverageRatingId FLOAT, SubmittedById INT, SubmittedBy VARCHAR(250), RecipeStateId INT, RecipeRatingId INT, ReviewCount INT, TweaksCount INT, PhotoCount INT, ImageName NVARCHAR(50) ) INSERT INTO @RESULT_SET_TABLE SELECT FavoriteRecipes.FavoriteRecipeId, Recipes.RecipeId, FavoriteRecipes.DateAdded, Recipes.Title, Recipes.UrlFriendlyTitle, Recipes.[Description], Recipes.AverageRatingId, Recipes.SubmittedById, COALESCE(users.DisplayName,users.UserName,Recipes.SubmittedBy) As SubmittedBy, Recipes.RecipeStateId, RecipeReviews.RecipeRatingId, COUNT(RecipeReviews.Review), COUNT(RecipeTweaks.Tweak), COUNT(Photos.PhotoId), dbo.udfGetRecipePhoto(Recipes.RecipeId) AS ImageName FROM FavoriteRecipes INNER JOIN Recipes ON FavoriteRecipes.RecipeId=Recipes.RecipeId AND Recipes.RecipeStateId <> 3 LEFT OUTER JOIN RecipeReviews ON RecipeReviews.RecipeId=Recipes.RecipeId AND RecipeReviews.ReviewedById=@UserId AND RecipeReviews.RecipeRatingId= ( SELECT MAX(RecipeReviews.RecipeRatingId) FROM RecipeReviews WHERE RecipeReviews.ReviewedById=@UserId AND RecipeReviews.RecipeId=FavoriteRecipes.RecipeId ) OR RecipeReviews.RecipeRatingId IS NULL LEFT OUTER JOIN RecipeTweaks ON RecipeTweaks.RecipeId = Recipes.RecipeId AND RecipeTweaks.TweakedById= @UserId LEFT OUTER JOIN Photos ON Photos.RecipeId = Recipes.RecipeId AND Photos.UploadedById = @UserId AND Photos.RecipeId = FavoriteRecipes.RecipeId AND Photos.PhotoTypeId = 1 LEFT OUTER JOIN users ON Recipes.SubmittedById = users.UserId WHERE FavoriteRecipes.UserId=@UserId GROUP BY FavoriteRecipes.FavoriteRecipeId, Recipes.RecipeId, FavoriteRecipes.DateAdded, Recipes.Title, Recipes.UrlFriendlyTitle, Recipes.[Description], Recipes.AverageRatingId, Recipes.SubmittedById, Recipes.SubmittedBy, Recipes.RecipeStateId, RecipeReviews.RecipeRatingId, users.DisplayName, users.UserName, Recipes.SubmittedBy; WITH SortResults AS ( SELECT ROW_NUMBER() OVER ( ORDER BY CASE WHEN @SORTDIRECTION = 't' AND @SORTORDER='a' THEN TITLE END ASC, CASE WHEN @SORTDIRECTION = 't' AND @SORTORDER='d' THEN TITLE END DESC, CASE WHEN @SORTDIRECTION = 'r' AND @SORTORDER='a' THEN AverageRatingId END ASC, CASE WHEN @SORTDIRECTION = 'r' AND @SORTORDER='d' THEN AverageRatingId END DESC, CASE WHEN @SORTDIRECTION = 'mr' AND @SORTORDER='a' THEN RecipeRatingId END ASC, CASE WHEN @SORTDIRECTION = 'mr' AND @SORTORDER='d' THEN RecipeRatingId END DESC, CASE WHEN @SORTDIRECTION = 'd' AND @SORTORDER='a' THEN DateAdded END ASC, CASE WHEN @SORTDIRECTION = 'd' AND @SORTORDER='d' THEN DateAdded END DESC ) RowNumber, FavoriteRecipeId, RecipeId, DateAdded, Title, UrlFriendlyTitle, [Description], AverageRatingId, SubmittedById, SubmittedBy, RecipeStateId, RecipeRatingId, ReviewCount, TweaksCount, PhotoCount, ImageName FROM @RESULT_SET_TABLE WHERE ((@FILTERBY = 1 AND SubmittedById= @USERID) OR ( @FILTERBY = 2 AND (SubmittedById <> @USERID OR SubmittedById IS NULL)) OR ( @FILTERBY <> 1 AND @FILTERBY <> 2)) ) SELECT RowNumber, FavoriteRecipeId, RecipeId, DateAdded, Title, UrlFriendlyTitle, [Description], AverageRatingId, SubmittedById, SubmittedBy, RecipeStateId, RecipeRatingId, ReviewCount, TweaksCount, PhotoCount, ImageName FROM SortResults WHERE RowNumber BETWEEN @ROW_START AND @ROW_END print @ROW_START print @ROW_END SELECT @RecipeCount=dbo.udfGetFavRecipesCount(@UserId) SELECT @RecipeCount AS RecipeCount SELECT COUNT(Id) as FilterCount FROM @RESULT_SET_TABLE WHERE ((@FILTERBY = 1 AND SubmittedById= @USERID) OR (@FILTERBY = 2 AND (SubmittedById <> @USERID OR SubmittedById IS NULL)) OR (@FILTERBY <> 1 AND @FILTERBY <> 2)) END

  Read the article

• Problems Enforcing Referential Integrity on SQL Server Tables

  - by SidC

  Hello All, I have a SQL Server 2005 database comprised of Customer, Quote, QuoteDetail tables. I want/need to enforce referential integrity such that when an insert is made on quotedetail, the quote and customer tables are also affected. I have tried my best to set up primary/foreign keys on my tables but need some help. Here's the scripts for my tables as they stand now (please don't laugh): Customers: USE [Diel_inventory] GO /****** Object: Table [dbo].[Customers] Script Date: 05/08/2010 03:39:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customers]( [pkCustID] [int] IDENTITY(1,1) NOT NULL, [CompanyName] [nvarchar](50) NULL, [Address] [nvarchar](50) NULL, [City] [nvarchar](50) NULL, [State] [nvarchar](2) NULL, [ZipCode] [nvarchar](5) NULL, [OfficePhone] [nvarchar](12) NULL, [OfficeFAX] [nvarchar](12) NULL, [Email] [nvarchar](50) NULL, [PrimaryContactName] [nvarchar](50) NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([pkCustID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Quotes: USE [Diel_inventory] GO /****** Object: Table [dbo].[Quotes] Script Date: 05/08/2010 03:30:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Quotes]( [pkQuoteID] [int] IDENTITY(1,1) NOT NULL, [fkCustomerID] [int] NOT NULL, [QuoteDate] [timestamp] NOT NULL, [NeedbyDate] [datetime] NULL, [QuoteAmt] [decimal](6, 2) NOT NULL, [QuoteApproved] [bit] NOT NULL, [fkOrderID] [int] NOT NULL, CONSTRAINT [PK_Bids] PRIMARY KEY CLUSTERED ( [pkQuoteID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Quotes] WITH CHECK ADD CONSTRAINT [fkCustomerID] FOREIGN KEY([fkCustomerID]) REFERENCES [dbo].[Customers] ([pkCustID]) GO ALTER TABLE [dbo].[Quotes] CHECK CONSTRAINT [fkCustomerID] QuoteDetail: USE [Diel_inventory] GO /****** Object: Table [dbo].[QuoteDetail] Script Date: 05/08/2010 03:31:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[QuoteDetail]( [ID] [int] IDENTITY(1,1) NOT NULL, [fkQuoteID] [int] NOT NULL, [fkCustomerID] [int] NOT NULL, [fkPartID] [int] NULL, [PartNumber1] [float] NOT NULL, [Qty1] [int] NOT NULL, [PartNumber2] [float] NULL, [Qty2] [int] NULL, [PartNumber3] [float] NULL, [Qty3] [int] NULL, [PartNumber4] [float] NULL, [Qty4] [int] NULL, [PartNumber5] [float] NULL, [Qty5] [int] NULL, [PartNumber6] [float] NULL, [Qty6] [int] NULL, [PartNumber7] [float] NULL, [Qty7] [int] NULL, [PartNumber8] [float] NULL, [Qty8] [int] NULL, [PartNumber9] [float] NULL, [Qty9] [int] NULL, [PartNumber10] [float] NULL, [Qty10] [int] NULL, [PartNumber11] [float] NULL, [Qty11] [int] NULL, [PartNumber12] [float] NULL, [Qty12] [int] NULL, [PartNumber13] [float] NULL, [Qty13] [int] NULL, [PartNumber14] [float] NULL, [Qty14] [int] NULL, [PartNumber15] [float] NULL, [Qty15] [int] NULL, [PartNumber16] [float] NULL, [Qty16] [int] NULL, [PartNumber17] [float] NULL, [Qty17] [int] NULL, [PartNumber18] [float] NULL, [Qty18] [int] NULL, [PartNumber19] [float] NULL, [Qty19] [int] NULL, [PartNumber20] [float] NULL, [Qty20] [int] NULL, CONSTRAINT [PK_QuoteDetail] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[QuoteDetail] WITH CHECK ADD CONSTRAINT [FK_QuoteDetail_Customers] FOREIGN KEY ([fkCustomerID]) REFERENCES [dbo].[Customers] ([pkCustID]) GO ALTER TABLE [dbo].[QuoteDetail] CHECK CONSTRAINT [FK_QuoteDetail_Customers] GO ALTER TABLE [dbo].[QuoteDetail] WITH CHECK ADD CONSTRAINT [FK_QuoteDetail_PartList] FOREIGN KEY ([fkPartID]) REFERENCES [dbo].[PartList] ([RecID]) GO ALTER TABLE [dbo].[QuoteDetail] CHECK CONSTRAINT [FK_QuoteDetail_PartList] GO ALTER TABLE [dbo].[QuoteDetail] WITH CHECK ADD CONSTRAINT [FK_QuoteDetail_Quotes] FOREIGN KEY([fkQuoteID]) REFERENCES [dbo].[Quotes] ([pkQuoteID]) GO ALTER TABLE [dbo].[QuoteDetail] CHECK CONSTRAINT [FK_QuoteDetail_Quotes] Your advice/guidance on how to set these up so that customer ID in Customers is the same as in Quotes (referential integrity) and that CustomerID is inserted on Quotes and Customers when an insert is made to QuoteDetial would be much appreciated. Thanks, Sid

  Read the article

• check if directory exists c#

  - by Ant

  I am trying to see if a directory exists based on an input field from the user. When the user types in the path, I want to check if the path actually exists. I have some c# code already. It returns 1 for any local path, but always returns 0 when I am checking a network path. static string checkValidPath(string path) { //Insert your code that runs under the security context of the authenticating user here. using (ImpersonateUser user = new ImpersonateUser(user, "", password)) { //DirectoryInfo d = new DirectoryInfo(quotelessPath); bool doesExist = Directory.Exists(path); //if (d.Exists) if(doesExist) { user.Dispose(); return "1"; } else { user.Dispose(); return "0"; } } } public class ImpersonateUser : IDisposable { [DllImport("advapi32.dll", SetLastError = true)] private static extern bool LogonUser(string lpszUsername, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, out IntPtr phToken); [DllImport("kernel32", SetLastError = true)] private static extern bool CloseHandle(IntPtr hObject); private IntPtr userHandle = IntPtr.Zero; private WindowsImpersonationContext impersonationContext; public ImpersonateUser(string user, string domain, string password) { if (!string.IsNullOrEmpty(user)) { // Call LogonUser to get a token for the user bool loggedOn = LogonUser(user, domain, password, 9 /*(int)LogonType.LOGON32_LOGON_NEW_CREDENTIALS*/, 3 /*(int)LogonProvider.LOGON32_PROVIDER_WINNT50*/, out userHandle); if (!loggedOn) throw new Win32Exception(Marshal.GetLastWin32Error()); // Begin impersonating the user impersonationContext = WindowsIdentity.Impersonate(userHandle); } } public void Dispose() { if (userHandle != IntPtr.Zero) CloseHandle(userHandle); if (impersonationContext != null) impersonationContext.Undo(); } } Any help is appreciated. Thanks! EDIT 3: updated code to use BrokenGlass's impersonation functions. However, I need to initialize "password" to something... EDIT 2: I updated the code to try and use impersonation as suggested below. It still fails everytime. I assume I am using impersonation improperly... EDIT: As requested by ChrisF, here is the function that calls the checkValidPath function. Frontend aspx file... $.get('processor.ashx', { a: '7', path: x }, function(o) { alert(o); if (o=="0") { $("#outputPathDivValid").dialog({ title: 'Output Path is not valid! Please enter a path that exists!', width: 500, modal: true, resizable: false, buttons: { 'Close': function() { $(this).dialog('close'); } } }); } }); Backend ashx file... public void ProcessRequest (HttpContext context) { context.Response.Cache.SetExpires(DateTime.Now); string sSid = context.Request["sid"]; switch (context.Request["a"]) {//a bunch of case statements here... case "7": context.Response.Write(checkValidPath(context.Request["path"].ToString())); break;

  Read the article

• Fixed strptime exception with thread lock, but slows down the program

  - by eWizardII

  I have the following code, which when is running inside of a thread (the full code is here - https://github.com/eWizardII/homobabel/blob/master/lovebird.py) for null in range(0,1): while True: try: with open('C:/Twitter/tweets/user_0_' + str(self.id) + '.json', mode='w') as f: f.write('[') threadLock.acquire() for i, seed in enumerate(Cursor(api.user_timeline,screen_name=self.ip).items(200)): if i>0: f.write(", ") f.write("%s" % (json.dumps(dict(sc=seed.author.statuses_count)))) j = j + 1 threadLock.release() f.write("]") except tweepy.TweepError, e: with open('C:/Twitter/tweets/user_0_' + str(self.id) + '.json', mode='a') as f: f.write("]") print "ERROR on " + str(self.ip) + " Reason: ", e with open('C:/Twitter/errors_0.txt', mode='a') as a_file: new_ii = "ERROR on " + str(self.ip) + " Reason: " + str(e) + "\n" a_file.write(new_ii) break Now without the thread lock I generate the following error: Exception in thread Thread-117: Traceback (most recent call last): File "C:\Python27\lib\threading.py", line 530, in __bootstrap_inner self.run() File "C:/Twitter/homobabel/lovebird.py", line 62, in run for i, seed in enumerate(Cursor(api.user_timeline,screen_name=self.ip).items(200)): File "build\bdist.win-amd64\egg\tweepy\cursor.py", line 110, in next self.current_page = self.page_iterator.next() File "build\bdist.win-amd64\egg\tweepy\cursor.py", line 85, in next items = self.method(page=self.current_page, *self.args, **self.kargs) File "build\bdist.win-amd64\egg\tweepy\binder.py", line 196, in _call return method.execute() File "build\bdist.win-amd64\egg\tweepy\binder.py", line 182, in execute result = self.api.parser.parse(self, resp.read()) File "build\bdist.win-amd64\egg\tweepy\parsers.py", line 75, in parse result = model.parse_list(method.api, json) File "build\bdist.win-amd64\egg\tweepy\models.py", line 38, in parse_list results.append(cls.parse(api, obj)) File "build\bdist.win-amd64\egg\tweepy\models.py", line 49, in parse user = User.parse(api, v) File "build\bdist.win-amd64\egg\tweepy\models.py", line 86, in parse setattr(user, k, parse_datetime(v)) File "build\bdist.win-amd64\egg\tweepy\utils.py", line 17, in parse_datetime date = datetime(*(time.strptime(string, '%a %b %d %H:%M:%S +0000 %Y')[0:6])) File "C:\Python27\lib\_strptime.py", line 454, in _strptime_time return _strptime(data_string, format)[0] File "C:\Python27\lib\_strptime.py", line 300, in _strptime _TimeRE_cache = TimeRE() File "C:\Python27\lib\_strptime.py", line 188, in __init__ self.locale_time = LocaleTime() File "C:\Python27\lib\_strptime.py", line 77, in __init__ raise ValueError("locale changed during initialization") ValueError: locale changed during initialization The problem is with thread lock on, each thread runs itself serially basically, and it takes way to long for each loop to run for there to be any advantage to having a thread anymore. So if there isn't a way to get rid of the thread lock, is there a way to have it run the for loop inside of the try statement faster?

  Read the article

• SQLException: incorrect syntax near '2'.

  - by Tobechukwu Ezenachukwu

  whenever I call the "ExecuteNonQuery" command on the following CommandText, I get the above SQLException myCommand.CommandText = "INSERT INTO fixtures (round_id, matchcode, date_utc, time_utc, date_london, time_london, team_A_id, team_A, team_A_country, team_B_id, team_B, team_B_country, status, gameweek, winner, fs_A, fs_B, hts_A, hts_B, ets_A, ets_B, ps_A, ps_B, last_updated) VALUES (" _ & round_id & "," & match_id & "," & date_utc & ",'" & time_utc & "'," & date_london & ",'" & time_london & "'," & team_A_id & ",'" & team_A_name & "','" & team_A_country & "'," & team_B_id & ",'" & team_B_name & "','" & _ team_B_country & "','" & status & "'," & gameweek & ",'" & winner & "'," & fs_A & "," & fs_B & "," & hts_A & "," & hts_B & "," & ets_A & "," & ets_B & "," & ps_A & "," & ps_B & "," & last_updated & ")" But whenever, i remove the last table item - "last_updated", the error disappears. Please help me resolve this issue. Is there any special treatment to be given to datetime fields??? Thanks for your help

  Read the article

• SQL query - choosing 'last updated' record in a group, better db design?

  - by Jimmy

  Hi, Let's say I have a MySQL database with 3 tables: table 1: Persons, with 1 column ID (int) table 2: Newsletters, with 1 column ID (int) table 3: Subscriptions, with columns Person_ID (int), Newsletter_ID (int), Subscribed (bool), Updated (Datetime) Subscriptions.Person_ID points to a Person, and Subscription.Newsletter_ID points to a Newsletter. Thus, each person may have 0 or more subscriptions to 0 or more magazines at once. The table Subscriptions will also store the entire history of each person's subscriptions to each newsletter. If a particular Person_ID-Newsletter_ID pair doesn't have a row in the Subscriptions table, then it's equivalent to that pair having a subscription status of 'false'. Here is a sample dataset Persons ID 1 2 3 Newsletters ID 4 5 6 Subscriptions Person_ID Newsletter_ID Subscribed Updated 2 4 true 2010-05-01 3 4 true 2010-05-01 3 5 true 2010-05-10 3 4 false 2010-05-15 Thus, as of 2010-05-16, Person 1 has no subscription, Person 2 has a subscription to Newsletter 4, and Person 3 has a subscription to Newsletter 5. Person 3 had a subscription to Newsletter 4 for a while, but not anymore. I'm trying to do 2 kinds of query. A query that shows everyone's active subscriptions as of query time (we can assume that updated will never be in the future -- thus, this means returning the record with the latest 'updated' value for each Person_ID-Newsletter_ID pair, as long as Subscribed is true (if the latest record for a Person_ID-Newsletter_ID pair has a Subscribed status of false, then I don't want that record returned)). A query that returns all active subscriptions for a specific newsletter - same qualification as in 1. regarding records with 'false' in the Subscribed column. I don't use SQL/databases often enough to tell if this design is good, or if the SQL queries needed would be slow on a database with, say, 1M records in the Subscriptions table. I was using the Visual query builder tool in Visual Studio 2010 but I can't even get the query to return the latest updated record for each Person_ID-Newsletter_ID pair. Is it possible to come up with SQL queries that don't involve using subqueries (presumably because they would become too slow with a larger data set)? If not, would it be a better design to have a separate Subscriptions_History table, and every time a subscription status for a Person_ID-Newsletter-ID pair is added to Subscriptions, any existing record for that pair is moved to Subscriptions_History (that way the Subscriptions table only ever contains the latest status update for any Person_ID-Newsletter_ID pair)? I'm using .net on Windows, so would it be easier (or the same, or harder) to do this kind of queries using Linq? Entity Framework? Thanks!

  Read the article

• How to return the value from MySql Stored Proc ??

  - by karthik

  I am using the below storedproc to generate the Insert statements of a specified table It is build-ed without any errors. Now i want to return the result set in "V_string" as output of the SP DELIMITER $$ DROP PROCEDURE IF EXISTS `demo`.`InsertGenerator` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertGenerator`() SWL_return: BEGIN -- SQLWAYS_EVAL# to retrieve column specific information -- SQLWAYS_EVAL# table DECLARE v_string NATIONAL VARCHAR(3000); -- SQLWAYS_EVAL# first half -- SQLWAYS_EVAL# tement DECLARE v_stringData NATIONAL VARCHAR(3000); -- SQLWAYS_EVAL# data -- SQLWAYS_EVAL# statement DECLARE v_dataType NATIONAL VARCHAR(1000); -- SQLWAYS_EVAL# -- SQLWAYS_EVAL# columns DECLARE v_colName NATIONAL VARCHAR(50); DECLARE NO_DATA INT DEFAULT 0; DECLARE cursCol CURSOR FOR SELECT column_name,data_type FROM `columns` -- WHERE table_name = v_tableName; WHERE table_name = 'tbl_users'; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET NO_DATA = -2; END; DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_DATA = -1; OPEN cursCol; SET v_string = CONCAT('INSERT ',v_tableName,'('); SET v_stringData = ''; SET NO_DATA = 0; FETCH cursCol INTO v_colName,v_dataType; IF NO_DATA <> 0 then -- NOT SUPPORTED print CONCAT('Table ',@tableName, ' not found, processing skipped.') close cursCol; LEAVE SWL_return; end if; WHILE NO_DATA = 0 DO IF v_dataType in('varchar','char','nchar','nvarchar') then SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# ll(',v_colName,'SQLWAYS_EVAL# ''+'); ELSE if v_dataType in('text','ntext') then -- SQLWAYS_EVAL# -- SQLWAYS_EVAL# else SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# ll(cast(',v_colName,'SQLWAYS_EVAL# 00)),'''')+'''''',''+'); ELSE IF v_dataType = 'money' then -- SQLWAYS_EVAL# doesn't get converted -- SQLWAYS_EVAL# implicitly SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# y,''''''+ isnull(cast(',v_colName,'SQLWAYS_EVAL# 0)),''0.0000'')+''''''),''+'); ELSE IF v_dataType = 'datetime' then SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# time,''''''+ isnull(cast(',v_colName, 'SQLWAYS_EVAL# 0)),''0'')+''''''),''+'); ELSE IF v_dataType = 'image' then SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# ll(cast(convert(varbinary,',v_colName, 'SQLWAYS_EVAL# 6)),''0'')+'''''',''+'); ELSE SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# ll(cast(',v_colName,'SQLWAYS_EVAL# 0)),''0'')+'''''',''+'); end if; end if; end if; end if; end if; SET v_string = CONCAT(v_string,v_colName,','); SET NO_DATA = 0; FETCH cursCol INTO v_colName,v_dataType; END WHILE; END $$ DELIMITER ;

  Read the article

• Can't get jQuery AutoComplete to work with External JSON

  - by rockinthesixstring

  I'm working on an ASP.NET app where I'm in need of jQuery AutoComplete. Currently there is nothing happening when I type data into the txt63 input box (and before you flame me for using a name like txt63, I know, I know... but it's not my call :D ). Here's my javascript code <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" type="text/javascript"></script> <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js" type="text/javascript"></script> <script src="http://jquery-ui.googlecode.com/svn/tags/latest/external/jquery.bgiframe-2.1.1.js" type="text/javascript"></script> <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/i18n/jquery-ui-i18n.min.js" type="text/javascript"></script> <script language="javascript" type="text/javascript"> var theSource = '../RegionsAutoComplete.axd?PID=<%= hidden62.value %>' $(function () { $('#<%= txt63.ClientID %>').autocomplete({ source: theSource, minLength: 2, select: function (event, ui) { $('#<%= hidden63.ClientID %>').val(ui.item.id); } }); }); and here is my HTTP Handler Namespace BT.Handlers Public Class RegionsAutoComplete : Implements IHttpHandler Public ReadOnly Property IsReusable() As Boolean Implements System.Web.IHttpHandler.IsReusable Get Return False End Get End Property Public Sub ProcessRequest(ByVal context As System.Web.HttpContext) Implements System.Web.IHttpHandler.ProcessRequest 'the page contenttype is plain text context.Response.ContentType = "application/json" context.Response.ContentEncoding = Encoding.UTF8 'set page caching context.Response.Cache.SetExpires(DateTime.Now.AddHours(24)) context.Response.Cache.SetCacheability(HttpCacheability.Public) context.Response.Cache.SetSlidingExpiration(True) context.Response.Cache.VaryByParams("PID") = True Try ' use the RegionsDataContext Using RegionDC As New DAL.RegionsDataContext ' query the database based on the querysting PID Dim q = (From r In RegionDC.bt_Regions _ Where r.PID = context.Request.QueryString("PID") _ Select r.Region, r.ID) ' now we loop through the array ' and write out the ressults Dim sb As New StringBuilder sb.Append("{") For Each item In q sb.Append("""" & item.Region & """: """ & item.ID & """,") Next sb.Append("}") context.Response.Write(sb.ToString) End Using Catch ex As Exception HealthMonitor.Log(ex, False, "This error occurred while populating the autocomplete handler") End Try End Sub End Class End Namespace The rest of my ASPX page has the appropriate controls as I had this working with the old version of the jQuery library. I'm trying to get it working with the new one because I heard that the "dev" CDN was going to be obsolete. Any help or direction will be greatly appreciated.

  Read the article

• PHP & MySQL Undefined variable problem

  - by comma

  I keep getting the following error Undefined variable: id on line 91 can some one help me correct this problem? The error is on this line. $query2 = "INSERT INTO users_skills (skill_id, user_id, date_created) VALUES ('$id', '$user_id', NOW())"; MySQL database tables. CREATE TABLE tags ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, skill VARCHAR(255) NOT NULL, experience VARCHAR(255) NOT NULL, years VARCHAR(255) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE users_skills ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, skill_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, date_created DATETIME UNSIGNED NOT NULL, PRIMARY KEY (id) ); Here is the PHP & MySQL code. if (isset($_POST['info_submitted'])) { $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $dbc = mysqli_query($mysqli,"SELECT learned_skills.*, users_skills.* FROM learned_skills INNER JOIN users_skills ON learned_skills.id = users_skills.skill_id WHERE user_id='$user_id'"); if (!$dbc) { print mysqli_error($mysqli); return; } $user_id = '5'; $skill = $_POST['skill']; $experience = $_POST['experience']; $years = $_POST['years']; $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $dbc = mysqli_query($mysqli,"SELECT learned_skills.*, users_skills.* FROM learned_skills INNER JOIN users_skills ON users_skills.skill_id = learned_skills.id WHERE users_skills.user_id='$user_id'"); if (mysqli_num_rows($dbc) == 0) { if (isset($_POST['skill']) && trim($_POST['skill'])!=='') { $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $query1 = mysqli_query($mysqli,"INSERT INTO learned_skills (skill, experience, years) VALUES ('" . $skill . "', '" . $experience . "', '" . $years . "')"); if (mysqli_query($mysqli, $query1)) { print mysqli_error($mysqli); return; } $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $dbc = mysqli_query($mysqli,"SELECT id FROM learned_skills WHERE id='" . $skill . "' AND experience='" . $experience . "' AND years='" . $years . "'"); if (!$dbc) { print mysqli_error($mysqli); } else { while($row = mysqli_fetch_array($dbc)){ $id = $row["id"]; } } $query2 = "INSERT INTO users_skills (skill_id, user_id, date_created) VALUES ('$id', '$user_id', NOW())"; } }

  Read the article

• NHibernate Many-to-Many Mapping not working

  - by ClutchDude

  I have a Nhibernate mapping file for a simple user/role mapping. Here are the mapping files: Users.hbm.xml <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Sample.Persistence" namespace="Sample.Persistence.Model"> <class name="User" table="Users"> <id name="UserKey"> <generator class="identity"/> </id> <property name="UserName" column="UserName" type="String" /> <property name="Password" column="Password" type="Byte[]" /> <property name="FirstName" column="FirstName" type="String" /> <property name="LastName" column="LastName" type="String" /> <property name="Email" column="Email" type="String" /> <property name="Active" column="Active" type="Boolean" /> <property name="Locked" column="Locked" type="Boolean" /> <property name="LoginFailures" column="LoginFailures" type="int" /> <property name="LockoutDate" column="LockoutDate" type="DateTime" generated="insert" /> <property name="Expired" column="Expired" type="Boolean" generated="insert"/> <set name="Roles" table="UsersRolesBridge" lazy="false"> <key column="UserKey" /> <many-to-many class="Role" not-found="exception" column="RoleKey" /> </set> </class> </hibernate-mapping> Role.hbm.xml <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Sample.Persistence" namespace="Sample.Persistence.Model"> <class name="Role" table="Roles"> <id name="RoleKey"> <generator class="identity"/> </id> <property name="Name" column="Name" type="String" /> <set name="Users" inverse="true" atable="UsersRolesBridge" lazy="false" > <key column="RoleKey" /> <many-to-many class="User" column="UserKey" /> </set> </class> </hibernate-mapping> I am able to retrieve roles for each user via NHibernate but when I go to save a new object, the roles are not saved in the Bridge table. The user is created and insert with no issues. I've checked that the Role collection, a field on the user, is being populated with the proper rolekey before the Session.Save() is called. There is no exception thrown as well.

  Read the article

• Select latest group by in nhibernate

  - by Kendrick

  I have Canine and CanineHandler objects in my application. The CanineHandler object has a PersonID (which references a completely different database), an EffectiveDate (which specifies when a handler started with the canine), and a FK reference to the Canine (CanineID). Given a specific PersonID, I want to find all canines they're currently responsible for. The (simplified) query I'd use in SQL would be: Select Canine.* from Canine inner join CanineHandler on(CanineHandler.CanineID=Canine.CanineID) inner join (select CanineID,Max(EffectiveDate) MaxEffectiveDate from caninehandler group by CanineID) as CurrentHandler on(CurrentHandler.CanineID=CanineHandler.CanineID and CurrentHandler.MaxEffectiveDate=CanineHandler.EffectiveDate) where CanineHandler.HandlerPersonID=@PersonID Edit: Added mapping files below: <class name="CanineHandler" table="CanineHandler" schema="dbo"> <id name="CanineHandlerID" type="Int32"> <generator class="identity" /> </id> <property name="EffectiveDate" type="DateTime" precision="16" not-null="true" /> <property name="HandlerPersonID" type="Int64" precision="19" not-null="true" /> <many-to-one name="Canine" class="Canine" column="CanineID" not-null="true" access="field.camelcase-underscore" /> </class> <class name="Canine" table="Canine"> <id name="CanineID" type="Int32"> <generator class="identity" /> </id> <property name="Name" type="String" length="64" not-null="true" /> ... <set name="CanineHandlers" table="CanineHandler" inverse="true" order-by="EffectiveDate desc" cascade="save-update" access="field.camelcase-underscore"> <key column="CanineID" /> <one-to-many class="CanineHandler" /> </set> <property name="IsDeleted" type="Boolean" not-null="true" /> </class> I haven't tried yet, but I'm guessing I could do this in HQL. I haven't had to write anything in HQL yet, so I'll have to tackle that eventually anyway, but my question is whether/how I can do this sub-query with the criterion/subqueries objects. I got as far as creating the following detached criteria: DetachedCriteria effectiveHandlers = DetachedCriteria.For<Canine>() .SetProjection(Projections.ProjectionList() .Add(Projections.Max("EffectiveDate"),"MaxEffectiveDate") .Add(Projections.GroupProperty("CanineID"),"handledCanineID") ); but I can't figure out how to do the inner join. If I do this: Session.CreateCriteria<Canine>() .CreateCriteria("CanineHandler", "handler", NHibernate.SqlCommand.JoinType.InnerJoin) .List<Canine>(); I get an error "could not resolve property: CanineHandler of: OPS.CanineApp.Model.Canine". Obviously I'm missing something(s) but from the documentation I got the impression that should return a list of Canines that have handlers (possibly with duplicates). Until I can make this work, adding the subquery isn't going to work... I've found similar questions, such as http://stackoverflow.com/questions/747382/only-get-latest-results-using-nhibernate but none of the answers really seem to apply with the kind of direct result I'm looking for. Any help or suggestion is greatly appreciated.

  Read the article

• MVC | Linq Update Query | Help!

  - by 109221793

  Hi guys, I'm making modifications to a C# MVC application that I've inherited. I have a database, and for simplicity I'll just focus on the two tables I'm working with for this linq query. Item ItemID Int PK ItemName RepairSelection (Yes or No) RepairID Int FK Repair RepairID Int PK RepairCategory SubmissionDate DateSentForRepair Ok, so ItemID is pretty much the identifier, and the View to display the Repair details goes like this (snippet): <%= Html.LabelFor(x => x.ItemID)%> <%= Html.DisplayFor(x => x.ItemID)%><br /> <%= Html.LabelFor(x => x.Repair.RepairCategory)%> <%= Html.DisplayFor(x => x.Repair.RepairCategory, "FormTextShort")%><br /> <%= Html.LabelFor(x => x.Repair.SubmissionDate)%> <%= Html.DisplayFor(x => x.Repair.SubmissionDate)%><br /> <%= Html.LabelFor(x => x.Repair.DateSentForRepair)%> <%= Html.DisplayFor(x => x.Repair.DateSentForRepair)%><br /> <%= Html.ActionLink("Edit Repair Details", "Edit", new { ItemID= Model.ItemID})%> Here is the GET Edit action: public ActionResult Edit(Int64? itemId) { ModelContainer ctn = new ModelContainer(); var item = from i in ctn.Items where i.ItemID == itemId select i; return View(item.First()); } This is also fine, the GET Edit view displays the right details. Where I'm stuck is the linq query to update the Repair table. I have tried it so many ways today that my head is just fried (new to Linq as you may have guessed). My latest try is here (which I know is way off so go easy ;-) ): [HttpPost] public ActionResult Edit(Int64 itemId, Repair repair, Item item, FormCollection formValues) { if (formValues["cancelButton"] != null) { return RedirectToAction("View", new { ItemID = itemId }); } ModelContainer ctn = new ModelContainer(); Repair existingData = ctn.Repair.First(a => a.RepairId == item.RepairID && item.ItemID == itemId); existingData.SentForConversion = DateTime.Parse(formValues["SentForConversion"]); ctn.SaveChanges(); return RedirectToAction("View", new { ItemID = itemId }); } For the above attempt I get a Sequence Contains No Elements error. Any help or pointers would be appreciated. Thanks guys.

  Read the article

• MySQL Split Time Ranges into Smaller Chunks

  - by Neren

  Hello all, I've recently been tasked with finishing a PHP/MySQL web app when the developer quit last week. I'm no MySQL expert, so I apologize if this is an intensely simple question. I've searched SO for the better part of two days trying to find a relatively easy solution to my problem, which is as follows. Problem in a Nutshell: I have a MySQL table full of start and end datetime (GMT -5) & UNIX Timestamp values covering durations of irregular length and need to break/split/divide them into more-regular time chunks (5 minutes). I'm not after a count of row entries per time chunk/bucket/period, if that makes any sense. Data Example: started, ended, started_UNIX, ended_UNIX 2010-10-25 15:12:33, 2010-10-25 15:47:09, 1288033953, 1288036029 What I'm hoping to get: 2010-10-25 15:12:33, 2010-10-25 15:15:00, 1288033953, 1288037700 2010-10-25 15:15:00, 2010-10-25 15:20:00, 1288037700, 1288038000 2010-10-25 15:20:00, 2010-10-25 15:25:00, 1288038000, 1288038300 2010-10-25 15:25:00, 2010-10-25 15:30:00, 1288038300, 1288038600 2010-10-25 15:30:00, 2010-10-25 15:35:00, 1288038600, 1288038900 2010-10-25 15:35:00, 2010-10-25 15:40:00, 1288038900, 1288039200 2010-10-25 15:40:00, 2010-10-25 15:45:00, 1288039200, 1288039500 2010-10-25 15:45:00, 2010-10-25 15:47:09, 1288039500, 1288039629 If you're interested, here's the quick & dirty on the app and why I need the data: App overview: The application receives very simple POST requests generated by a basic sensor device when its input pins go to ground, which submits an INSERT query to the database where MySQL records a timestamp (as started). When the input pins return from a grounded state, the device submits a different POST request, which causes the PHP app to submit an UPDATE query, where a modification time timestamp is inserted (as ended). My employer recently changed the periodic reporting unit of measure from Seconds "On" Per Day to Seconds "On" Per 5 Minute Interval. I had formulated what I thought would be a workable solution, but when I looked at it on paper, it looked like Rube Goldberg's nightmare constructed in MySQL, so that was out. Any suggestions as to how to break these spans into 5 minute blocks? Keeping it all in MySQL would be my preference, though I'll take any suggestions. Thank you for any suggestions you may have. Again, I apologize if this is a no-brainer. If I ask any additional questions of the SO collective consciousness in the future, I'll try to word them a bit better. Any help will be happily welcomed. Thanks, Neren

  Read the article

• How to query across many-to-many association in NHibernate?

  - by Splash

  I have two entities, Post and Tag. The Post entity has a collection of Tags which represents a many-to-many join between the two (that is, each post can have any number of tags and each tag can be associated with any number of posts). I am trying to retrieve all Posts which have a given tag. However, I seem to be unable to get this query right. I essentially want something which means the same as the following pseudo-HQL: from Posts p where p.Tags contains (from Tags t where t.Name = :tagName) order by p.DateTime The only thing I've found which even approaches this is a post by Ayende. However, his approach requires the entity on the other side (in my case, Tag) to have a collection showing the other end of the many-to-many. I don't have this and don't really wish to have it. I find it hard to believe this can't be done. What am I missing? My entities & mappings look like this (simplified): public class Post { public virtual int Id { get; set; } public virtual string Title { get; set; } private IList<Tag> tags = new List<Tag>(); public virtual IEnumerable<Tag> Tags { get { return tags; } } public virtual void AddTag(Tag tag) { this.tags.Add(tag); } } public class PostMap : ClassMap<Post> { public PostMap() { Id(x => x.Id).GeneratedBy.HiLo("99"); Map(x => x.Title); HasManyToMany(x => x.Tags); } } // ---- public class Tag { public virtual int Id { get; set; } public virtual string Name { get; set; } } public class TagMap : ClassMap<Tag> { public TagMap () { Id(x => x.Id).GeneratedBy.HiLo("99"); Map(x => x.Name).Unique(); } }

  Read the article

• symfony2.4 ajax call update or set session issues

  - by user3797283

  i have an issue with symfony2 when i use ajax to set session, hope u pro guys can help me. here is my controller code: //show month event list public function indexAction() { if ($this->getRequest()->isXmlHttpRequest()) { $paging = $this->getRequest()->get("nom"); $session = $this->getRequest()->getSession(); if ($paging) { //if $paging is set, then that's a click pager ajax event //(not 1st time load) $year = $paging; $session->set('year', $year); } else { //$paging is null, it's the first time page load $year = (new \DateTime())->format("Y"); $session->set('year', $year); } $repository = $this ->getDoctrine() ->getManager() ->getRepository('HycAccountBundle:MonthEvent'); $annuallist = $repository->monthListByYear($year); $jsonlist = json_encode($annuallist); return new Response($jsonlist); } //this part is to return entity to twig for using after $em = $this->getDoctrine()->getManager(); $allimages = $em->getRepository('HycAccountBundle:TypeImage') ->findAll(); return $this->render('HycAccountBundle:Account:index.html.twig', array('allimages' => $allimages)); } here is my twig code: <script type="text/javascript"> $(function() { $(document).ready(function (){ jQuery.ajax({ type: 'GET', cache: false, url: "{{ path('hyc_account_homepage') }}", success: function(data, textStatus, jqXHR) { alert({{app.session.get('year')}}); //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! //!!!!!!!!! here i can get year 2014 !!!!!!!!!!!!!! //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! }, error:function (){ alert('error!'); } }); return false; }); }); </script> but when i click pager part, for example, i click year 2013, then ajax call will return a number (data: 'nom=' + num) to controller, but there i reset session value to num normally, but i cant get alert correct session (it's always 2014 as the 1st time) here is the code ajax, almost same as above: <script type="text/javascript"> $(document).ready(function(){ //after paging, reload month $('#page-selection').bootpag({ total: 3000, page: 2014, maxVisible: 5 }).on('page', function(event, num){ jQuery.ajax({ type: 'GET', url: "{{ path('hyc_account_homepage') }}", data: 'nom=' + num, success: function(data, textStatus, jqXHR) { alert({{app.session.get('year')}}); //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! //here is the problem, it's always 2014, not set again !!!!!!!! //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! }, error:function (){ alert('error!'); } }); return false; }); }); </script> hope u guys help me, thanks in advance, i've tried for almost 1 day and looked for almost all in google but find nothing = =

  Read the article

< Previous Page | 123 124 125 126 127 128 129 130 131 132 133 134  | Next Page >