Search Results

Search found 955 results on 39 pages for 'inserts'.

Page 33/39 | < Previous Page | 29 30 31 32 33 34 35 36 37 38 39  | Next Page >

• NHibernate mapping with optimistic-lock="version" and dynamic-update="true" is generating invalid up

  - by SteveBering

  I have an entity "Group" with an assigned ID which is added to an aggregate in order to persist it. This causes an issue because NHibernate can't tell if it is new or existing. To remedy this issue, I changed the mapping to make the Group entity use optimistic locking on a sql timestamp version column. This caused a new issue. Group has a bag of sub objects. So when NHibernate flushes a new group to the database, it first creates the Group record in the Groups table, then inserts each of the sub objects, then does an update of the Group records to update the timestamp value. However, the sql that is generated to complete the update is invalid when the mapping is both dynamic-update="true" and optimistic-lock="version". Here is the mapping: <class xmlns="urn:nhibernate-mapping-2.2" dynamic-update="true" mutable="true" optimistic-lock="version" name="Group" table="Groups"> <id name="GroupNumber" type="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> <column name="GroupNumber" length="5" /> <generator class="assigned" /> </id> <version generated="always" name="Timestamp" type="BinaryBlob" unsaved-value="null"> <column name="TS" not-null="false" sql-type="timestamp" /> </version> <property name="UID" update="false" type="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> <column name="GroupUID" unique="true" /> </property> <property name="Description" type="AnsiString"> <column name="GroupDescription" length="25" not-null="true" /> </property> <bag access="field.camelcase-underscore" cascade="all" inverse="true" lazy="true" name="Assignments" mutable="true" order-by="GroupAssignAssignment"> <key foreign-key="fk_Group_Assignments"> <column name="GroupNumber" /> </key> <one-to-many class="Assignment" /> </bag> <many-to-one class="Aggregate" name="Aggregate"> <column name="GroupParentID" not-null="true" /> </many-to-one> </class> </hibernate-mapping> When the mapping includes both the dynamic update and the optimistic lock, the sql generated is: UPDATE groups SET WHERE GroupNumber = 11111 AND TS=0x00000007877 This is obviously invalid as there are no SET statements. If I remove the dynamic update part, everything gets updated during this update statement instead. This makes the statement valid, but rather unnecessary. Has anyone seen this issue before? Am I missing something? Thanks, Steve

  Read the article

• SQLiteDataAdapter Fill exception

  - by Lirik

  I'm trying to use the OleDb CSV parser to load some data from a CSV file and insert it into a SQLite database, but I get an exception with the OleDbAdapter.Fill method and it's frustrating: An unhandled exception of type 'System.Data.ConstraintException' occurred in System.Data.dll Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. Here is the source code: public void InsertData(String csvFileName, String tableName) { String dir = Path.GetDirectoryName(csvFileName); String name = Path.GetFileName(csvFileName); using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + @";Extended Properties=""Text;HDR=No;FMT=Delimited""")) { conn.Open(); using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + name, conn)) { QuoteDataSet ds = new QuoteDataSet(); adapter.Fill(ds, tableName); // <-- Exception here InsertData(ds, tableName); // <-- Inserts the data into the my SQLite db } } } class Program { static void Main(string[] args) { SQLiteDatabase target = new SQLiteDatabase(); string csvFileName = "D:\\Innovations\\Finch\\dev\\DataFeed\\YahooTagsInfo.csv"; string tableName = "Tags"; target.InsertData(csvFileName, tableName); Console.ReadKey(); } } The "YahooTagsInfo.csv" file looks like this: tagId,tagName,description,colName,dataType,realTime 1,s,Symbol,symbol,VARCHAR,FALSE 2,c8,After Hours Change,afterhours,DOUBLE,TRUE 3,g3,Annualized Gain,annualizedGain,DOUBLE,FALSE 4,a,Ask,ask,DOUBLE,FALSE 5,a5,Ask Size,askSize,DOUBLE,FALSE 6,a2,Average Daily Volume,avgDailyVolume,DOUBLE,FALSE 7,b,Bid,bid,DOUBLE,FALSE 8,b6,Bid Size,bidSize,DOUBLE,FALSE 9,b4,Book Value,bookValue,DOUBLE,FALSE I've tried the following: Removing the first line in the CSV file so it doesn't confuse it for real data. Changing the TRUE/FALSE realTime flag to 1/0. I've tried 1 and 2 together (i.e. removed the first line and changed the flag). None of these things helped... One constraint is that the tagId is supposed to be unique. Here is what the table look like in design view: Can anybody help me figure out what is the problem here? Update: I changed the HDR property from HDR=No to HDR=Yes and now it doesn't give me an exception: OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + @";Extended Properties=""Text;HDR=Yes;FMT=Delimited"""); I assumed that if HDR=No and I removed the header (i.e. first line), then it should work... strangely it didn't work. In any case, now I'm no longer getting the exception. The new problem is here: public void InsertData(QuoteDataSet data, String tableName) { using (SQLiteConnection conn = new SQLiteConnection(_connectionString)) { conn.Open(); using (SQLiteDataAdapter sqliteAdapter = new SQLiteDataAdapter("SELECT * FROM " + tableName, conn)) { Console.WriteLine("Num rows updated is " + sqliteAdapter.Update(data, tableName)); } } } Now the Num rows updated is 0... any hints?

  Read the article

• jQueryUI autocomplete - when no results are returned

  - by Brian M. Hunt

  I'm wondering how one can catch and add a custom handler when empty results are returned from the server when using jQueryUI autocomplete. There seem to be a few questions on this point related to the various jQuery plugins (e.g. jQuery autocomplete display “No data” error message when results empty), but I am wondering if there's a better/simpler way to achieve the same with the jQueryUI autocomplete. It seems to me this is a common use case, and I thought perhaps that jQueryUI had improved on the jQuery autocomplete by adding the ability to cleanly handle this situation. However I've not been able to find documentation of such functionality, and before I hack away at it I'd like to throw out some feelers in case others have seen this before. While probably not particularly influential, I can have the server return anything - e.g. HTTP 204: No Content to a 200/JSON empty list - whatever makes it easiest to catch the result in jQueryUI's autocomplete. My first thought is to pass a callback with two arguments, namely a request object and a response callback to handle the code, per the documentation: The third variation, the callback, provides the most flexibility, and can be used to connect any data source to Autocomplete. The callback gets two arguments: A request object, with a single property called "term", which refers to the value currently in the text input. For example, when the user entered "new yo" in a city field, the Autocomplete term will equal "new yo". A response callback, which expects a single argument to contain the data to suggest to the user. This data should be filtered based on the provided term, and can be in any of the formats described above for simple local data (String-Array or Object-Array with label/value/both properties). When the response callback receives no data, it inserts returns a special one-line object-array that has a label and an indicator that there's no data (so the select/focus recognize it as the indicator that no-data was returned). This seems overcomplicated. I'd prefer to be able to use a source: "http://...", and just have a callback somewhere indicating that no data was returned. Thank you for reading. Brian

  Read the article

• How to best design a date/geographic proximity query on GAE?

  - by Dane

  Hi all, I'm building a directory for finding athletic tournaments on GAE with web2py and a Flex front end. The user selects a location, a radius, and a maximum date from a set of choices. I have a basic version of this query implemented, but it's inefficient and slow. One way I know I can improve it is by condensing the many individual queries I'm using to assemble the objects into bulk queries. I just learned that was possible. But I'm also thinking about a more extensive redesign that utilizes memcache. The main problem is that I can't query the datastore by location because GAE won't allow multiple numerical comparison statements (<,<=,=,) in one query. I'm already using one for date, and I'd need TWO to check both latitude and longitude, so it's a no go. Currently, my algorithm looks like this: 1.) Query by date and select 2.) Use destination function from geopy's distance module to find the max and min latitude and longitudes for supplied distance 3.) Loop through results and remove all with lat/lng outside max/min 4.) Loop through again and use distance function to check exact distance, because step 2 will include some areas outside the radius. Remove results outside supplied distance (is this 2/3/4 combination inefficent?) 5.) Assemble many-to-many lists and attach to objects (this is where I need to switch to bulk operations) 6.) Return to client Here's my plan for using memcache.. let me know if I'm way out in left field on this as I have no prior experience with memcache or server caching in general. -Keep a list in the cache filled with "geo objects" that represent all my data. These have five properties: latitude, longitude, event_id, event_type (in anticipation of expanding beyond tournaments), and start_date. This list will be sorted by date. -Also keep a dict of pointers in the cache which represent the start and end indices in the cache for all the date ranges my app uses (next week, 2 weeks, month, 3 months, 6 months, year, 2 years). -Have a scheduled task that updates the pointers daily at 12am. -Add new inserts to the cache as well as the datastore; update pointers. Using this design, the algorithm would now look like: 1.) Use pointers to slice off appropriate chunk of list based on supplied date. 2-4.) Same as above algorithm, except with geo objects 5.) Use bulk operation to select full tournaments using remaining geo objects' event_ids 6.) Assemble many-to-manys 7.) Return to client Thoughts on this approach? Many thanks for reading and any advice you can give. -Dane

  Read the article

• Stuck on the logic of creating tags for posts (like SO tags)? (PHP)

  - by ggfan

  I am stuck on how to create tags for each post on my site. I am not sure how to add the tags into database. Currently... I have 3 tables: +---------------------+ +--------------------+ +---------------------+ | Tags | | Posting | | PostingTags | +---------------------+ +--------------------+ +---------------------+ | + TagID | | + posting_id | | + posting_id | +---------------------+ +--------------------+ +---------------------+ | + TagName | | + title | | + tagid | +---------------------+ +--------------------+ +---------------------+ The Tags table is just the name of the tags(ex: 1 PHP, 2 MySQL,3 HTML) The posting (ex: 1 What is PHP?, 2 What is CSS?, 3 What is HTML?) The postingtags shows the relation between posting and tags. When users type a posting, I insert the data into the "posting" table. It automatically inserts the posting_id for each post(posting_id is a primary key). $title = mysqli_real_escape_string($dbc, trim($_POST['title'])); $query4 = "INSERT INTO posting (title) VALUES ('$title')"; mysqli_query($dbc, $query4); HOWEVER, how do I insert the tags for each post? When users are filling out the form, there is a checkbox area for all the tags available and they check off whatever tags they want. (I am not doing where users type in the tags they want just yet) This shows each tag with a checkbox. When users check off each tag, it gets stored in an array called "postingtag[]". <label class="styled">Select Tags:</label> <?php $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); $query5 = "SELECT * FROM tags ORDER BY tagname"; $data5 = mysqli_query($dbc, $query5); while ($row5 = mysqli_fetch_array($data5)) { echo '<li><input type="checkbox" name="postingtag[]" value="'.$row5['tagname'].'" ">'.$row5['tagname'].'</li>'; } ?> My question is how do I insert the tags in the array ("postingtag") into my "postingtags" table? Should I... $postingtag = $_POST["postingtag"]; foreach($postingtag as $value){ $query5 = "INSERT INTO postingtags (posting_id, tagID) VALUES (____, $value)"; mysqli_query($dbc, $query5); } 1.In this query, how do I get the posting_id value of the post? I am stuck on the logic here, so if someone can help me explain the next step, I would appreciate it! Is there an easier way to insert tags?

  Read the article

• In MySQL, what is the most effective query design for joining large tables with many to many relatio

  - by lighthouse65

  In our application, we collect data on automotive engine performance -- basically source data on engine performance based on the engine type, the vehicle running it and the engine design. Currently, the basis for new row inserts is an engine on-off period; we monitor performance variables based on a change in engine state from active to inactive and vice versa. The related engineState table looks like this: +---------+-----------+---------------+---------------------+---------------------+-----------------+ | vehicle | engine | engine_state | state_start_time | state_end_time | engine_variable | +---------+-----------+---------------+---------------------+---------------------+-----------------+ | 080025 | E01 | active | 2008-01-24 16:19:15 | 2008-01-24 16:24:45 | 720 | | 080028 | E02 | inactive | 2008-01-24 16:19:25 | 2008-01-24 16:22:17 | 304 | +---------+-----------+---------------+---------------------+---------------------+-----------------+ For a specific analysis, we would like to analyze table content based on a row granularity of minutes, rather than the current basis of active / inactive engine state. For this, we are thinking of creating a simple productionMinute table with a row for each minute in the period we are analyzing and joining the productionMinute and engineEvent tables on the date-time columns in each table. So if our period of analysis is from 2009-12-01 to 2010-02-28, we would create a new table with 129,600 rows, one for each minute of each day for that three-month period. The first few rows of the productionMinute table: +---------------------+ | production_minute | +---------------------+ | 2009-12-01 00:00 | | 2009-12-01 00:01 | | 2009-12-01 00:02 | | 2009-12-01 00:03 | +---------------------+ The join between the tables would be engineState AS es LEFT JOIN productionMinute AS pm ON es.state_start_time <= pm.production_minute AND pm.production_minute <= es.event_end_time. This join, however, brings up multiple environmental issues: The engineState table has 5 million rows and the productionMinute table has 130,000 rows When an engineState row spans more than one minute (i.e. the difference between es.state_start_time and es.state_end_time is greater than one minute), as is the case in the example above, there are multiple productionMinute table rows that join to a single engineState table row When there is more than one engine in operation during any given minute, also as per the example above, multiple engineState table rows join to a single productionMinute row In testing our logic and using only a small table extract (one day rather than 3 months, for the productionMinute table) the query takes over an hour to generate. In researching this item in order to improve performance so that it would be feasible to query three months of data, our thoughts were to create a temporary table from the engineEvent one, eliminating any table data that is not critical for the analysis, and joining the temporary table to the productionMinute table. We are also planning on experimenting with different joins -- specifically an inner join -- to see if that would improve performance. What is the best query design for joining tables with the many:many relationship between the join predicates as outlined above? What is the best join type (left / right, inner)?

  Read the article

• LINQ InsertOnSubmit Required Fields needed for debugging

  - by Derek Hunziker

  Hi All, I've been using the ADO.NET Strogly-Typed DataSet model for about 2 years now for handling CRUD and stored procedure executions. This past year I built my first MVC app and I really enjoyed the ease and flexibility of LINQ. Perhaps the biggest selling point for me was that with LINQ I didn't have to create "Insert" stored procedures that would return the SCOPE_IDENTITY anymore (The auto-generated insert statements in the DataSet model were not capable of this without modification). Currently, I'm using LINQ with ASP.NET 3.5 WebForms. My inserts are looking like this: ProductsDataContext dc = new ProductsDataContext(); product p = new product { Title = "New Product", Price = 59.99, Archived = false }; dc.products.InsertOnSubmit(p); dc.SubmitChanges(); int productId = p.Id; So, this product example is pretty basic, right, and in the future, I'll probably be adding more fields to the database such as "InStock", "Quantity", etc... The way I understand it, I will need to add those fields to the database table and then delete and re-add the tables to the LINQ to SQL Class design view in order to refresh the DataContext. Does that sound right? The problem is that any new fields that are non-null are NOT caught by the ASP.NET build processes. For example, if I added a non-null field of "Quantity" to the database, the code above would still build. In the DataSet model, the stored procedure method would accept a certain amount of parameters and would warn me that my Insert would fail if I didn't include a quantity value. The same goes for LINQ stored procedure methods, however, to my knowledge, LINQ doesn't offer a way to auto generate the insert statements and that means I'm back to where I started. The bottom line is if I used insert statements like the one above and I add a non-null field to my database, it would break my app in about 10-20 places and there would be no way for me to detect it. Is my only option to do a solution-side search for the keyword "products.InsertOnSubmit" and make sure the new field is getting assigned? Is there a better way? Thanks!

  Read the article

• Can LINQ-to-SQL omit unspecified columns on insert so a database default value is used?

  - by Todd Ropog

  I have a non-nullable database column which has a default value set. When inserting a row, sometimes a value is specified for the column, sometimes one is not. This works fine in TSQL when the column is omitted. For example, given the following table: CREATE TABLE [dbo].[Table1]( [id] [int] IDENTITY(1,1) NOT NULL, [col1] [nvarchar](50) NOT NULL, [col2] [nvarchar](50) NULL, CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([id] ASC) ) GO ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [DF_Table1_col1] DEFAULT ('DB default') FOR [col1] The following two statements will work: INSERT INTO Table1 (col1, col2) VALUES ('test value', '') INSERT INTO Table1 (col2) VALUES ('') In the second statement, the default value is used for col1. The problem I have is when using LINQ-to-SQL (L2S) with a table like this. I want to produce the same behavior, but I can't figure out how to make L2S do that. I want to be able to run the following code and have the first row get the value I specify and the second row get the default value from the database: var context = new DataClasses1DataContext(); var row1 = new Table1 { col1 = "test value", col2 = "" }; context.Table1s.InsertOnSubmit(row1); context.SubmitChanges(); var row2 = new Table1 { col2 = "" }; context.Table1s.InsertOnSubmit(row2); context.SubmitChanges(); If the Auto Generated Value property of col1 is False, the first row is created as desired, but the second row fails with a null error on col1. If Auto Generated Value is True, both rows are created with the default value from the database. I've tried various combinations of Auto Generated Value, Auto-Sync and Nullable, but nothing I've tried gives the behavior I want. L2S does not omit the column from the insert statement when no value is specified. Instead it does something like this: INSERT INTO Table1 (col1, col2) VALUES (null, '') ...which of course causes a null error on col1. Is there some way to get L2S to omit a column from the insert statement if no value is given? Or is there some other way to get the behavior I want? I need the default value at the database level because not all row inserts are done via L2S, and in some cases the default value is a little more complex than a hard coded value (e.g. creating the default based on another field) so I'd rather avoid duplicating that logic.

  Read the article

• Why does PostgresQL query performance drop over time, but restored when rebuilding index

  - by Jim Rush

  According to this page in the manual, indexes don't need to be maintained. However, we are running with a PostgresQL table that has a continuous rate of updates, deletes and inserts that over time (a few days) sees a significant query degradation. If we delete and recreate the index, query performance is restored. We are using out of the box settings. The table in our test is currently starting out empty and grows to half a million rows. It has a fairly large row (lots of text fields). We are search is based of an index, not the primary key (I've confirmed the index is being used, at least under normal conditions) The table is being used as a persistent store for a single process. Using PostgresQL on Windows with a Java client I'm willing to give up insert and update performance to keep up the query performance. We are considering rearchitecting the application so that data is spread across various dynamic tables in a manner that allows us to drop and rebuild indexes periodically without impacting the application. However, as always, there is a time crunch to get this to work and I suspect we are missing something basic in our configuration or usage. We have considered forcing vacuuming and rebuild to run at certain times, but I suspect the locking period for such an action would cause our query to block. This may be an option, but there are some real-time (windows of 3-5 seconds) implications that require other changes in our code. Additional information: Table and index CREATE TABLE icl_contacts ( id bigint NOT NULL, campaignfqname character varying(255) NOT NULL, currentstate character(16) NOT NULL, xmlscheduledtime character(23) NOT NULL, ... 25 or so other fields. Most of them fixed or varying character fiel ... CONSTRAINT icl_contacts_pkey PRIMARY KEY (id) ) WITH (OIDS=FALSE); ALTER TABLE icl_contacts OWNER TO postgres; CREATE INDEX icl_contacts_idx ON icl_contacts USING btree (xmlscheduledtime, currentstate, campaignfqname); Analyze: Limit (cost=0.00..3792.10 rows=750 width=32) (actual time=48.922..59.601 rows=750 loops=1) - Index Scan using icl_contacts_idx on icl_contacts (cost=0.00..934580.47 rows=184841 width=32) (actual time=48.909..55.961 rows=750 loops=1) Index Cond: ((xmlscheduledtime < '2010-05-20T13:00:00.000'::bpchar) AND (currentstate = 'SCHEDULED'::bpchar) AND ((campaignfqname)::text = '.main.ee45692a-6113-43cb-9257-7b6bf65f0c3e'::text)) And, yes, I am aware there there are a variety of things we could do to normalize and improve the design of this table. Some of these options may be available to us. My focus in this question is about understanding how PostgresQL is managing the index and query over time (understand why, not just fix). If it were to be done over or significantly refactored, there would be a lot of changes.

  Read the article

• How to fine tune a Membership Provider?

  - by Venemo

  After all the answers to my last question about fine-tuning turned out to be more useful than I expected, I thought that I would ask another similar Question about the MembershipProviders as well. Okay, so firstly, to clarify: I know what a Membership, Role, and Profile provider is, how to implement my own, and how to configure them, and most of the things about them. Implementing a role and profile provider is pretty straightforward, because they only require simple CRUD most of the time. (A single line of LINQ is enough for about half of the RoleProvider's methods.) However, the Membership provider is a differend beast. Many of you may realize that it violates the SR (Single Responsibility) principle, because it has to do EVERYTHING related to user management. While this leaves a lot of room for customizations, it has its downsides as well. There is no information on the Internet about what their EXACT expected behaviour is, such as when should they throw exceptions or simply return null, and stuff like that. I use this sample implementation for reference, but it also contains several contradictions. For example, it uses its own ValidateUser method for checking for credentials in the ChangePassword method. But the ValidateUser also updates the user's LastLoginDate to the current date. So, does the framework expect that I set it in my own provider as well, or is it simply a mistake in the sample? The other is: the ChangePassword method throws an exception every time when validating the new password, but CreateUser doesn't ever throw an exception, it simply returns false. And last, but not least: it counts the invalid password attempts of the user and locks them if it passes a threshold. While this is good, but it requires manual action to unlock the users. Is it a problem if my provider automatically unlocks the user after a certain amount of time? (EDIT) I almost forgot: the CreateUser method in the sample inserts the ID from the method parameter. I actually think this is bad practice, because I use inters with auto incement as IDs, so inserting them from some method parameter is not an option. Should I just ignore the parameter, or require that its value is null and throw an exception if it isn't? All in all, does ASP.NET have any assumptions about the behaviour of a MembershipProvider? Is there any documentation which describes when should I throw an exception or just return null? I also tried to find a set of generic unit tests which would provide some guidance about the expected behaviour, but no luck, I found plenty of articles about "Unit testing is good", and "How to unit test a MembershipProvider", but not one where there would be any actual tests. Thanks in advance for everyone!

  Read the article

• INSERT OR IGNORE in a trigger

  - by dan04

  I have a database (for tracking email statistics) that has grown to hundreds of megabytes, and I've been looking for ways to reduce it. It seems that the main reason for the large file size is that the same strings tend to be repeated in thousands of rows. To avoid this problem, I plan to create another table for a string pool, like so: CREATE TABLE AddressLookup ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Address TEXT UNIQUE ); CREATE TABLE EmailInfo ( MessageID INTEGER PRIMARY KEY AUTOINCREMENT, ToAddrRef INTEGER REFERENCES AddressLookup(ID), FromAddrRef INTEGER REFERENCES AddressLookup(ID) /* Additional columns omitted for brevity. */ ); And for convenience, a view to join these tables: CREATE VIEW EmailView AS SELECT MessageID, A1.Address AS ToAddr, A2.Address AS FromAddr FROM EmailInfo LEFT JOIN AddressLookup A1 ON (ToAddrRef = A1.ID) LEFT JOIN AddressLookup A2 ON (FromAddrRef = A2.ID); In order to be able to use this view as if it were a regular table, I've made some triggers: CREATE TRIGGER trg_id_EmailView INSTEAD OF DELETE ON EmailView BEGIN DELETE FROM EmailInfo WHERE MessageID = OLD.MessageID; END; CREATE TRIGGER trg_ii_EmailView INSTEAD OF INSERT ON EmailView BEGIN INSERT OR IGNORE INTO AddressLookup(Address) VALUES (NEW.ToAddr); INSERT OR IGNORE INTO AddressLookup(Address) VALUES (NEW.FromAddr); INSERT INTO EmailInfo SELECT NEW.MessageID, A1.ID, A2.ID FROM AddressLookup A1, AddressLookup A2 WHERE A1.Address = NEW.ToAddr AND A2.Address = NEW.FromAddr; END; CREATE TRIGGER trg_iu_EmailView INSTEAD OF UPDATE ON EmailView BEGIN UPDATE EmailInfo SET MessageID = NEW.MessageID WHERE MessageID = OLD.MessageID; REPLACE INTO EmailView SELECT NEW.MessageID, NEW.ToAddr, NEW.FromAddr; END; The problem After: INSERT OR REPLACE INTO EmailView VALUES (1, '[email protected]', '[email protected]'); INSERT OR REPLACE INTO EmailView VALUES (2, '[email protected]', '[email protected]'); The updated rows contain: MessageID ToAddr FromAddr --------- ------ -------- 1 NULL [email protected] 2 [email protected] [email protected] There's a NULL that shouldn't be there. The corresponding cell in the EmailInfo table contains an orphaned ToAddrRef value. If you do the INSERTs one at a time, you'll see that Alice's ID in the AddressLookup table changes! It appears that this behavior is documented: An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead. So the "REPLACE" in the top-level "INSERT OR REPLACE" statement is overriding the critical "INSERT OR IGNORE" in the trigger program. Is there a way I can make it work the way that I wanted?

  Read the article

• How to salvage SQL server 2008 query from KILLED/ROLLBACK state?

  - by littlegreen

  I have a stored procedure that inserts batches of millions of rows, emerging from a certain query, into an SQL database. It has one parameter selecting the batch; when this parameter is omitted, it will gather a list of batches and recursively call itself, in order to iterate over batches. In (pseudo-)code, it looks something like this: CREATE PROCEDURE spProcedure AS BEGIN IF @code = 0 BEGIN ... WHILE @@Fetch_Status=0 BEGIN EXEC spProcedure @code FETCH NEXT ... INTO @code END END ELSE BEGIN -- Disable indexes ... INSERT INTO table SELECT (...) -- Enable indexes ... Now it can happen that this procedure is slow, for whatever reason: it can't get a lock, one of the indexes it uses is misdefined or disabled. In that case, I want to be able kill the procedure, truncate and recreate the resulting table, and try again. However, when I try and kill the procedure, the process frequently oozes into a KILLED/ROLLBACK state from which there seems to be no return. From Google I have learned to do an sp_lock, find the spid, and then kill it with KILL <spid>. But when I try to kill it, it tells me SPID 75: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 554 seconds. I did find a forum message hinting that another spid should be killed before the other one can start a rollback. But that didn't work for me either, plus I do not understand, why that would be the case... could it be because I am recursively calling my own stored procedure? (But it should be having the same spid, right?) In any case, my process is just sitting there, being dead, not responding to kills, and locking the table. This is very frustrating, as I want to go on developing my queries, not waiting hours on my server sitting dead while pretending to be finishing a supposed rollback. Is there some way in which I can tell the server not to store any rollback information for my query? Or not to allow any other queries to interfere with the rollback, so that it will not take so long? Or how to rewrite my query in a better way, or how kill the process successfully without restarting the server?

  Read the article

• Problem inserting JavaScript into a CKEditor Instance running inside a jQuery-UI Dialog Box

  - by PlasmaFlux

  Hello Overflowers! Here's what's going on: I'm building an app (using PHP, jQuery, etc), part of which lets users edit various bits of a web page (Header, Footer, Main Content, Sidebar) using CKEditor. It's set up so that each editable bit has an "Edit Content" button in the upper right that, on click, launches an instance of CKEditor inside a jQuery-UI Dialog box. After the user is done editing, they can click an 'Update Changes' button that passes the edited content back off to the main page and closes the Dialog/CKeditor. It's all working magnificently, save for one thing. If someone inserts any JavaScript code, wrapped in 'script' tags, using either the Insert HTML Plugin for CKEditor or by going to 'Source' in CKEditor and placing the code in the source, everything seems okay until they click the 'Update Changes' button. The JavaScript appears to be inserting properly, but when 'Update Changes' is clicked, instead of the Dialog closing and passing the script back into the div where it belongs, what I get instead is an all-white screen with just the output of the JavaScript. For example, a simple 'Hello World' script results in a white screen with the string 'Hello World' in the upper left corner; for more intricate scripts, like an API call to, say Aweber, that generates a newsletter signup form, I get an all-white screen with the resulting form from the Aweber API call perfectly rendered in the middle of the screen. One of the most confusing parts is that, on these pages, if I click 'View Source', I get absolutely nothing. Blank emptiness. Here's all my code that handles the launching of the CKEditor instance inside the jQuery-UI Dialog, and the passing of the edited data back into its associated div on click of the 'Update Changes' button: $(function() { $('.foobar_edit_button') .button() .click(function() { var beingEdited = $(this).nextAll('.foobar_editable').attr('id'); var content = $(this).nextAll('.foobar_editable').html(); $('#foobar_editor').html(content); $('#foobar_editor').dialog( { open:function() { $(this).ckeditor(function() { CKFinder.SetupCKEditor( this, '<?php echo '/foobar/lib/editor/ckfinder/'; ?>' ); }); }, close:function() { $(this).ckeditorGet().destroy(); }, autoOpen: false, width: 840, modal: true, buttons: { 'Update Changes': function() { // TODO: submit changes to server via ajax once its completed: for ( instance in CKEDITOR.instances ) CKEDITOR.instances[instance].updateElement(); var edited_content = $('#foobar_editor').html(); $('#' + beingEdited).html(edited_content); $(this).dialog('close'); } } }); $('#foobar_editor').dialog('open'); }); }); I'm all sorts of confused. If anyone can point me in the right direction, it will be greatly appreciated. Thanks!

  Read the article

• server|configuration problem, a php script just die with no error log & no reason

  - by Roberto

  Hi (first of all, thanks for your attention & sorry for my bad english hahaha also this is not a programming error, or thats what I think, I think this is an error in some configuration of the server or something else but I dont know what) I have a php script (is running like a process of linux, its not running on the web browser) that send SMS via SMPP on the port 2055 (using sockets in php) & then inserts like 10,000 rows into a dababase on MySQL, the script gets the data from a XML file; firts it was running in a shared server (Hostgator is our hosting provider) & at the begining it worked fine, with no trouble, but 5 months later an error appear, the process just die with no reason, the script only sent & inserted 700 rows in the table of the database & the process didnt show any warning or error, nothing appears in the error logs, & I didnt make any change in the script Hostgator never helped us hahaha so we decided to move the script from the shared server to a dedicated server; I thought it was a memory problem or something like that, but when we move the script to the dedicated server the problem just get worse, the script die when has just sent & inserted 40 to 50 rows to the database the information about this error: the shared server is on Red Hat 4.1.2-46 & the dedicated server is on CentOS 5.4 I have commented the line that sends the SMS, & the problem remains in the shared server, at the begining the script was fine, but then the script started to die when has just inserted 700 aprox. in the database, & now the script is dying when has inserted 2500 rows, its better but we didnt change anything in the dedicated server, the script dies when has just inserted like 40 row in the table the script, before it dies, change to a zombie process & we dont know why the usage of memory appears to be 0.3%, and of the cpu appears to be 0.7% to 1% I have changed the max memory limit of php to 128Mb, and even to -1 (so php wont have any limit) but the problem remains we have the limit of 50 connections of mysql at the same time, so I think this is not the problem Im using mysqli to connect from php to mysql Hostgator report that they haven't made any change or update in the servers what could be the problem?? what should I do??? what should I search??? is something in the logic Im missing?? what steps do I have to follow when managing & searching problems of process on Linux??? thank you very much, I think this is not a programming problem, but you have more experience than me, you can tell me thanks!!! bye!!! :)

  Read the article

• Advantage of creating a generic repository vs. specific repository for each object?

  - by LuckyLindy

  We are developing an ASP.NET MVC application, and are now building the repository/service classes. I'm wondering if there are any major advantages to creating a generic IRepository interface that all repositories implement, vs. each Repository having its own unique interface and set of methods. For example: a generic IRepository interface might look like (taken from this answer): public interface IRepository : IDisposable { T[] GetAll<T>(); T[] GetAll<T>(Expression<Func<T, bool>> filter); T GetSingle<T>(Expression<Func<T, bool>> filter); T GetSingle<T>(Expression<Func<T, bool>> filter, List<Expression<Func<T, object>>> subSelectors); void Delete<T>(T entity); void Add<T>(T entity); int SaveChanges(); DbTransaction BeginTransaction(); } Each Repository would implement this interface (e.g. CustomerRepository:IRepository, ProductRepository:IRepository, etc). The alternate that we've followed in prior projects would be: public interface IInvoiceRepository : IDisposable { EntityCollection<InvoiceEntity> GetAllInvoices(int accountId); EntityCollection<InvoiceEntity> GetAllInvoices(DateTime theDate); InvoiceEntity GetSingleInvoice(int id, bool doFetchRelated); InvoiceEntity GetSingleInvoice(DateTime invoiceDate, int accountId); //unique InvoiceEntity CreateInvoice(); InvoiceLineEntity CreateInvoiceLine(); void SaveChanges(InvoiceEntity); //handles inserts or updates void DeleteInvoice(InvoiceEntity); void DeleteInvoiceLine(InvoiceLineEntity); } In the second case, the expressions (LINQ or otherwise) would be entirely contained in the Repository implementation, whoever is implementing the service just needs to know which repository function to call. I guess I don't see the advantage of writing all the expression syntax in the service class and passing to the repository. Wouldn't this mean easy-to-messup LINQ code is being duplicated in many cases? For example, in our old invoicing system, we call InvoiceRepository.GetSingleInvoice(DateTime invoiceDate, int accountId) from a few different services (Customer, Invoice, Account, etc). That seems much cleaner than writing the following in multiple places: rep.GetSingle(x => x.AccountId = someId && x.InvoiceDate = someDate.Date); The only disadvantage I see to using the specific approach is that we could end up with many permutations of Get* functions, but this still seems preferable to pushing the expression logic up into the Service classes. What am I missing?

  Read the article

• JPA Entity Manager resource handling

  - by chiragshahkapadia

  Every time I call JPA method its creating entity and binding query. My persistence properties are: <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/> <property name="hibernate.cache.provider_class" value="net.sf.ehcache.hibernate.SingletonEhCacheProvider"/> <property name="hibernate.cache.use_second_level_cache" value="true"/> <property name="hibernate.cache.use_query_cache" value="true"/> And I am creating entity manager the way shown below: emf = Persistence.createEntityManagerFactory("pu"); em = emf.createEntityManager(); em = Persistence.createEntityManagerFactory("pu").createEntityManager(); Is there any nice way to manage entity manager resource instead create new every time or any property can set in persistence. Remember it's JPA. See below binding log every time : 15:35:15,527 INFO [AnnotationBinder] Binding entity from annotated class: * 15:35:15,527 INFO [QueryBinder] Binding Named query: * = * 15:35:15,527 INFO [QueryBinder] Binding Named query: * = * 15:35:15,527 INFO [QueryBinder] Binding Named query: 15:35:15,527 INFO [QueryBinder] Binding Named query: 15:35:15,527 INFO [QueryBinder] Binding Named query: 15:35:15,527 INFO [QueryBinder] Binding Named query: 15:35:15,527 INFO [QueryBinder] Binding Named query: 15:35:15,527 INFO [QueryBinder] Binding Named query: 15:35:15,527 INFO [QueryBinder] Binding Named query: 15:35:15,527 INFO [EntityBinder] Bind entity com.* on table * 15:35:15,542 INFO [HibernateSearchEventListenerRegister] Unable to find org.hibernate.search.event.FullTextIndexEventListener on the classpath. Hibernate Search is not enabled. 15:35:15,542 INFO [NamingHelper] JNDI InitialContext properties:{} 15:35:15,542 INFO [DatasourceConnectionProvider] Using datasource: 15:35:15,542 INFO [SettingsFactory] RDBMS: and Real Application Testing options 15:35:15,542 INFO [SettingsFactory] JDBC driver: Oracle JDBC driver, version: 9.2.0.1.0 15:35:15,542 INFO [Dialect] Using dialect: org.hibernate.dialect.Oracle10gDialect 15:35:15,542 INFO [TransactionFactoryFactory] Transaction strategy: org.hibernate.transaction.JDBCTransactionFactory 15:35:15,542 INFO [TransactionManagerLookupFactory] No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recomm ended) 15:35:15,542 INFO [SettingsFactory] Automatic flush during beforeCompletion(): disabled 15:35:15,542 INFO [SettingsFactory] Automatic session close at end of transaction: disabled 15:35:15,542 INFO [SettingsFactory] JDBC batch size: 15 15:35:15,542 INFO [SettingsFactory] JDBC batch updates for versioned data: disabled 15:35:15,542 INFO [SettingsFactory] Scrollable result sets: enabled 15:35:15,542 INFO [SettingsFactory] JDBC3 getGeneratedKeys(): disabled 15:35:15,542 INFO [SettingsFactory] Connection release mode: auto 15:35:15,542 INFO [SettingsFactory] Default batch fetch size: 1 15:35:15,542 INFO [SettingsFactory] Generate SQL with comments: disabled 15:35:15,542 INFO [SettingsFactory] Order SQL updates by primary key: disabled 15:35:15,542 INFO [SettingsFactory] Order SQL inserts for batching: disabled 15:35:15,542 INFO [SettingsFactory] Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory 15:35:15,542 INFO [ASTQueryTranslatorFactory] Using ASTQueryTranslatorFactory 15:35:15,542 INFO [SettingsFactory] Query language substitutions: {} 15:35:15,542 INFO [SettingsFactory] JPA-QL strict compliance: enabled 15:35:15,542 INFO [SettingsFactory] Second-level cache: enabled 15:35:15,542 INFO [SettingsFactory] Query cache: enabled 15:35:15,542 INFO [SettingsFactory] Cache region factory : org.hibernate.cache.impl.bridge.RegionFactoryCacheProviderBridge 15:35:15,542 INFO [RegionFactoryCacheProviderBridge] Cache provider: net.sf.ehcache.hibernate.SingletonEhCacheProvider 15:35:15,542 INFO [SettingsFactory] Optimize cache for minimal puts: disabled 15:35:15,542 INFO [SettingsFactory] Structured second-level cache entries: disabled 15:35:15,542 INFO [SettingsFactory] Query cache factory: org.hibernate.cache.StandardQueryCacheFactory 15:35:15,542 INFO [SettingsFactory] Statistics: disabled 15:35:15,542 INFO [SettingsFactory] Deleted entity synthetic identifier rollback: disabled 15:35:15,542 INFO [SettingsFactory] Default entity-mode: pojo 15:35:15,542 INFO [SettingsFactory] Named query checking : enabled 15:35:15,542 INFO [SessionFactoryImpl] building session factory 15:35:15,542 INFO [SessionFactoryObjectFactory] Not binding factory to JNDI, no JNDI name configured 15:35:15,542 INFO [UpdateTimestampsCache] starting update timestamps cache at region: org.hibernate.cache.UpdateTimestampsCache 15:35:15,542 INFO [StandardQueryCache] starting query cache at region: org.hibernate.cache.StandardQueryCache

  Read the article

• Flex AS3: ProgressBar doesn't move

  - by jolierouge

  Hey All, I am a little stuck and need some advice/help. I have a progress bar: <mx:ProgressBar id="appProgress" mode="manual" width="300" label="{appProgressMsg}" minimum="0" maximum="100"/> I have two listener functions, one sets the progress, and one sets the appProgressMsg: public function incProgress(e:TEvent):void { var p:uint = Math.floor(e.data.number / e.data.total * 100); trace("Setting Perc." + p); appProgress.setProgress(p, 100); } public function setApplicationProgressStep(e:TEvent):void { trace("Setting step:" + e.data); appProgressMsg = e.data; } I want to reuse this progress bar alot. And not necessarily for ProgressEvents, but when going through steps. For instance, I loop over a bunch of database inserts, and want to undate the progress etc. Here is a sample: public function updateDatabase(result:Object):void { var total:int = 0; var i:int = 0; var r:SQLResult; trace("updateDatabase called."); for each (var table:XML in this.queries.elements("table")) { var key:String = table.attribute("name"); if (result[key]) { send(TEvent.UpdateApplicationProgressStep, "Updating " + key); i = 1; total = result[key].length; for each (var row:Object in result[key]) { //now, we need to see if we already have this record. send(TEvent.UpdateApplicationProgress, { number:i, total: total } ); r = this.query("select * from " + key + " where server_id = '" + row.id + "'"); if (r.data == null) { //there is no entry with this id, make one. this.query(table.insert, row); } else { //it exists, so let's update. this.update(key, row); } i++; } } } } Everything works fine. That is, the listener functions are called and I get trace output like: updateDatabase called. Setting step:Updating project Setting Perc 25 Setting Perc 50 Setting Perc 75 Setting Perc 100 The issue is, only the very last percent and step is shown. that is, when it's all done, the progress bar jumps to 100% and shows the last step label. Does anyone know why this is? Thanks in advance for any help, Jason

  Read the article

• EF4 Import/Lookup thousands of records - my performance stinks!

  - by Dennis Ward

  I'm trying to setup something for a movie store website (using ASP.NET, EF4, SQL Server 2008), and in my scenario, I want to allow a "Member" store to import their catalog of movies stored in a text file containing ActorName, MovieTitle, and CatalogNumber as follows: Actor, Movie, CatalogNumber John Wayne, True Grit, 4577-12 (repeated for each record) This data will be used to lookup an actor and movie, and create a "MemberMovie" record, and my import speed is terrible if I import more than 100 or so records using these tables: Actor Table: Fields = {ID, Name, etc.} Movie Table: Fields = {ID, Title, ActorID, etc.} MemberMovie Table: Fields = {ID, CatalogNumber, MovieID, etc.} My methodology to import data into the MemberMovie table from a text file is as follows (after the file has been uploaded successfully): Create a context. For each line in the file, lookup the artist in the Actor table. For each Movie in the Artist table, lookup the matching title. If a matching Movie is found, add a new MemberMovie record to the context and call ctx.SaveChanges(). The performance of my implementation is terrible. My expectation is that this can be done with thousands of records in a few seconds (after the file has been uploaded), and I've got something that times out the browser. My question is this: What is the best approach for performing bulk lookups/inserts like this? Should I call SaveChanges only once rather than for each newly created MemberMovie? Would it be better to implement this using something like a stored procedure? A snippet of my loop is roughly this (edited for brevity): while ((fline = file.ReadLine()) != null) { string [] token = fline.Split(separator); string Actor = token[0]; string Movie = token[1]; string CatNumber = token[2]; Actor found_actor = ctx.Actors.Where(a => a.Name.Equals(actor)).FirstOrDefault(); if (found_actor == null) continue; Movie found_movie = found_actor.Movies.Where( s => s.Title.Equals(title, StringComparison.CurrentCultureIgnoreCase)).FirstOrDefault(); if (found_movie == null) continue; ctx.MemberMovies.AddObject(new MemberMovie() { MemberProfileID = profile_id, CatalogNumber = CatNumber, Movie = found_movie }); try { ctx.SaveChanges(); } catch { } } Any help is appreciated! Thanks, Dennis

  Read the article

• service broker message process order

  - by Blootac

  Everywhere I read says that messages handled by the service broker are processed in the order that they arrive, and yet if you create a table, message type, contract, service etc , and on activation have a stored proc that waits for 2 seconds and inserts the msg into a table, set the max queue readers to 5 or 10, and send 20 odd messages I can see in the table that they are inserted out of order even though when I insert them into the queue and look at the contents of the queue I can see that the messages are all in the right order. Is it due to the delay waitfor waiting for the nearest second and each thread having different subsecond times and then fighting for a lock or something? The reason i've got a delay in there is to simulate delays with joins etc Thanks demo code: --create the table and service broker CREATE TABLE test ( id int identity(1,1), contents varchar(100) ) CREATE MESSAGE TYPE test CREATE CONTRACT mycontract ( test sent by initiator ) GO CREATE PROCEDURE dostuff AS BEGIN DECLARE @msg varchar(100); RECEIVE TOP (1) @msg = message_body FROM myQueue IF @msg IS NOT NULL BEGIN WAITFOR DELAY '00:00:02' INSERT INTO test(contents)values(@msg) END END GO ALTER QUEUE myQueue WITH STATUS = ON, ACTIVATION ( STATUS = ON, PROCEDURE_NAME = dostuff, MAX_QUEUE_READERS = 10, EXECUTE AS SELF ) create service senderService on queue myQueue ( mycontract ) create service receiverService on queue myQueue ( mycontract ) GO --********************************************************** --now insert lots of messages to the queue DECLARE @dialog_handle uniqueidentifier BEGIN DIALOG @dialog_handle FROM SERVICE senderService TO SERVICE 'receiverService' ON CONTRACT mycontract; SEND ON CONVERSATION @dialog_handle MESSAGE TYPE test ('<test>1</test>'); BEGIN DIALOG @dialog_handle FROM SERVICE senderService TO SERVICE 'receiverService' ON CONTRACT mycontract; SEND ON CONVERSATION @dialog_handle MESSAGE TYPE test ('<test>2</test>') BEGIN DIALOG @dialog_handle FROM SERVICE senderService TO SERVICE 'receiverService' ON CONTRACT mycontract; SEND ON CONVERSATION @dialog_handle MESSAGE TYPE test ('<test>3</test>') BEGIN DIALOG @dialog_handle FROM SERVICE senderService TO SERVICE 'receiverService' ON CONTRACT mycontract; SEND ON CONVERSATION @dialog_handle MESSAGE TYPE test ('<test>4</test>') BEGIN DIALOG @dialog_handle FROM SERVICE senderService TO SERVICE 'receiverService' ON CONTRACT mycontract; SEND ON CONVERSATION @dialog_handle MESSAGE TYPE test ('<test>5</test>') BEGIN DIALOG @dialog_handle FROM SERVICE senderService TO SERVICE 'receiverService' ON CONTRACT mycontract; SEND ON CONVERSATION @dialog_handle MESSAGE TYPE test ('<test>6</test>') BEGIN DIALOG @dialog_handle FROM SERVICE senderService TO SERVICE 'receiverService' ON CONTRACT mycontract; SEND ON CONVERSATION @dialog_handle MESSAGE TYPE test ('<test>7</test>')

  Read the article

• Alternative to Page_Load in ASP.NET (and a good WTF story)

  - by Jason

  Woo, I have a doozy of a problem (might even be one for the Daily WTF) and I'm hoping there's a solution. (My apologies for the long post...) I have been working on a website that I inherited about a month ago. One of the parts I have been working on is fixing one of the controls (essentially a dynamic header bar) so that it displays additional information as requested by my users. As part of doing this project, I created a Site.master file so that I wouldn't have to recode the header bar into every single page. When I first started doing this, it seemingly worked very well. All the pages I had developed looked great and the bar updated as it should displaying the information as it should. Well, when I dropped the Site.master (and this control) into older site pages (ones I did not specifically develop) I noticed that it looked bad on some of them, but not all of them. When I say it looked bad, basically, the control would left-align itself to the page rather than center as it should. I spent a couple hours debugging to no avail - CSS looked correct, the HTML appeared to be okay, I didn't see anything in the Javascript (although, I did miss something as I'll point out in a second), and even the old code looked correct (to the best that it could - it's not very well written). Another coworker took a look at the site and couldn't find anything at first, either. It wasn't until I just thought to look at the rendered source code of the page (I had been working in the developer view up to this point in IE8) that it became clear what was wrong. The original developer performs searches on many of the pages. To accomplish this, he queries the database for ALL the data and then loads them into Javascript arrays within the page so he can get access to them. This in itself is a huge problem because we're talking about thousands of items, and it obviously isn't scalable (and, yes, the site is slow). However, it finally clicked what was screwing up the Site.master - when he loads the data into the Javascript arrays, he writes out the data to the HTML upon Page_Load using numerous Response.Write(string) calls. The WTF (and what was messing me up) is that he inserts the Javascript before the DOCTYPE causing IE to go into quirks mode! So, because I need to at least get this release out (I'll fix the real problem later), I was wondering: is there a way I can force this Javascript to be inserted elsewhere into the HTML—after the DOCTYPE at the very least? Right now, all the Response.Write() calls are being done in the Page_Load method. I just need them to be inserted later.

  Read the article

• transforming binary data using ssis and sql server 2008

  - by Rick

  Hello All - I have a task to import/transform and extract zipped binary files that contain both text data as well as embeded binary data. Within the data is data that is relational in nature and needs to be processed into a defined database structure. Currently I have a C# single threaded app that essentially grabs all the files from the directory (currently there is 13K files of varying sizes) and extracts the data on a single thread line by line inserts to the database. As you could imagine this is a very slow process and unacceptable. There are several different parsing routines used depending on the header record in the file. There are potentially upto a million rows per file when all the data is extracted to the row level of detail. Follow on task is to parse those rows into their appropriate tables based on is content. i.e. the textual content has to be parsed further into "buckets" of like data in the database. That about sums up the big picture. Now for the problem task list. How do i iterate through a packet of data using SSIS? In the app the file is decompressed and then is parsed using streams data type and byte arrays and is routed to the required parsing routine based on the header data of each packet. There is bit swapping involved as well. Should i wrap up the app code into a script task(s) and let it do the custom processing? The data is seperated by year and the sql server tables is partitioned by year as well. I need to be able to "catch" bad file data as well and process by hand most likely. Should i simply load the zipped file to sql as a blob and parse the file with T-SQL? Would that be multi threaded if done that way? Not sure how to do the parsing in tsql that is involved here. Which do you think would be faster? Potentially the data that is currently processed via files could come to us via a socket. Can SSIS collect that data in real time? How would i go about setting that up? Processing these new files from the directorys will become a daily task. I can manage the data once i get it to sql server. Getting it there in a timely fashion seems to be the long pole in the tent for me. I would appreciate any comments or suggestions from the group. Rick

  Read the article

• Problem determining how to order F# types due to circular references

  - by James Black

  I have some types that extend a common type, and these are my models. I then have DAO types for each model type for CRUD operations. I now have a need for a function that will allow me to find an id given any model type, so I created a new type for some miscellaneous functions. The problem is that I don't know how to order these types. Currently I have models before dao, but I somehow need DAOMisc before CityDAO and CityDAO before DAOMisc, which isn't possible. The simple approach would be to put this function in each DAO, referring to just the types that can come before it, so, State comes before City as State has a foreign key relationship with City, so the miscellaneous function would be very short. But, this just strikes me as wrong, so I am not certain how to best approach this. Here is my miscellaneous type, where BaseType is a common type for all my models. type DAOMisc = member internal self.FindIdByType item = match(item:BaseType) with | :? StateType as i -> let a = (StateDAO()).Retrieve i a.Head.Id | :? CityType as i -> let a = (CityDAO()).Retrieve i a.Head.Id | _ -> -1 Here is one dao type. CommonDAO actually has the code for the CRUD operations, but that is not important here. type CityDAO() = inherit CommonDAO<CityType>("city", ["name"; "state_id"], (fun(reader) -> [ while reader.Read() do let s = new CityType() s.Id <- reader.GetInt32 0 s.Name <- reader.GetString 1 s.StateName <- reader.GetString 3 ]), list.Empty ) This is my model type: type CityType() = inherit BaseType() let mutable name = "" let mutable stateName = "" member this.Name with get() = name and set restnameval=name <- restnameval member this.StateName with get() = stateName and set stateidval=stateName <- stateidval override this.ToSqlValuesList = [this.Name;] override this.ToFKValuesList = [StateType(Name=this.StateName);] The purpose for this FindIdByType function is that I want to find the id for a foreign key relationship, so I can set the value in my model and then have the CRUD functions do the operations with all the correct information. So, City needs the id for the state name, so I would get the state name, put it into the state type, then call this function to get the id for that state, so my city insert will also include the id for the foreign key. This seems to be the best approach, in a very generic way to handle inserts, which is the current problem I am trying to solve.

  Read the article

• Large number of UPDATE queries slowing down page

  - by Bryan Lewis

  I am reading and validating large fixed-width text files (range from 10-50K lines) that are submitted via our ASP.net website (coded in VB.Net). I do an initial scan of the file to check for basic issues (line length, etc). Then I import each row into a MS SQL table. Each DB rows basically consists of a record_ID (Primary, auto-incrementing) and about 50 varchar fields. After the insert is done, I run a validation function on the file that checks each field in each row based on a bunch of criteria (trimmed length, isnumeric, range checks, etc). If it finds an error in any field, it inserts a record into the Errors table, which has an error_ID, the record_ID and an error message. In addition, if the field fails in a particular way, I have to do a "reset" on that field. A reset might consist of blanking the entire field, or simply replacing the value with another value (e.g. replacing the string with a new one that has all illegals chars taken out). I have a 5,000 line test file. The upload, initial check, and import takes about 5-6 seconds. The detailed error check and insert into the Errors table takes about 5-8 seconds (this file has about 1200 errors in it). However, the "resets" part takes about 40-45 seconds for 750 fields that need to be reset. When I comment out the resets function (returning immediately without actually calling the UPDATE stored proc), the process is very fast. With the resets turned on, the pages take 50 seconds to return. My UPDATE stored proc is using some recommended code from http://sommarskog.se/dynamic_sql.html, whereby it uses CASE instead of dynamic SQL: UPDATE dbo.Records SET dbo.Records.file_ID = CASE @field_name WHEN 'file_ID' THEN @field_value ELSE file_ID END, . . (all 50 varchar field CASE statements here) . WHERE dbo.Records.record_ID = @record_ID Is there any way I can help my performance here. Can I somehow group all of these UPDATE calls into a single transaction? Should I be reworking the UPDATE query somehow? Or is it just sheer quantity of 750+ UPDATEs and things are just slow (it's a quad proc server with 8GB ram). Any suggestions appreciated.

  Read the article

• Different behaviour using unidirectional or bidirectional relation

  - by sinuhepop

  I want to persist a mail entity which has some resources (inline or attachment). First I related them as a bidirectional relation: @Entity public class Mail extends BaseEntity { @OneToMany(mappedBy = "mail", cascade = CascadeType.ALL, orphanRemoval = true) private List<MailResource> resource; private String receiver; private String subject; private String body; @Temporal(TemporalType.TIMESTAMP) private Date queued; @Temporal(TemporalType.TIMESTAMP) private Date sent; public Mail(String receiver, String subject, String body) { this.receiver = receiver; this.subject = subject; this.body = body; this.queued = new Date(); this.resource = new ArrayList<>(); } public void addResource(String name, MailResourceType type, byte[] content) { resource.add(new MailResource(this, name, type, content)); } } @Entity public class MailResource extends BaseEntity { @ManyToOne(optional = false) private Mail mail; private String name; private MailResourceType type; private byte[] content; } And when I saved them: Mail mail = new Mail("[email protected]", "Hi!", "..."); mail.addResource("image", MailResourceType.INLINE, someBytes); mail.addResource("documentation.pdf", MailResourceType.ATTACHMENT, someOtherBytes); mailRepository.save(mail); Three inserts were executed: INSERT INTO MAIL (ID, BODY, QUEUED, RECEIVER, SENT, SUBJECT) VALUES (?, ?, ?, ?, ?, ?) INSERT INTO MAILRESOURCE (ID, CONTENT, NAME, TYPE, MAIL_ID) VALUES (?, ?, ?, ?, ?) INSERT INTO MAILRESOURCE (ID, CONTENT, NAME, TYPE, MAIL_ID) VALUES (?, ?, ?, ?, ?) Then I thought it would be better using only a OneToMany relation. No need to save which Mail is in every MailResource: @Entity public class Mail extends BaseEntity { @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true) @JoinColumn(name = "mail_id") private List<MailResource> resource; ... public void addResource(String name, MailResourceType type, byte[] content) { resource.add(new MailResource(name, type, content)); } } @Entity public class MailResource extends BaseEntity { private String name; private MailResourceType type; private byte[] content; } Generated tables are exactly the same (MailResource has a FK to Mail). The problem is the executed SQL: INSERT INTO MAIL (ID, BODY, QUEUED, RECEIVER, SENT, SUBJECT) VALUES (?, ?, ?, ?, ?, ?) INSERT INTO MAILRESOURCE (ID, CONTENT, NAME, TYPE) VALUES (?, ?, ?, ?) INSERT INTO MAILRESOURCE (ID, CONTENT, NAME, TYPE) VALUES (?, ?, ?, ?) UPDATE MAILRESOURCE SET mail_id = ? WHERE (ID = ?) UPDATE MAILRESOURCE SET mail_id = ? WHERE (ID = ?) Why this two updates? I'm using EclipseLink, will this behaviour be the same using another JPA provider as Hibernate? Which solution is better?

  Read the article

• linq to sql with nservicebus table lock issue

  - by IGoor

  I am building a system using NServiceBus and my DataLayer is using Linq 2 SQL. The system is made up of 2 services. Service1 receives messages from NSB. It will query Table1 in my database and inserts a record into Table1 If a certain condition is met a new NSB message is sent to the 2nd service Service2 will update records also in Table1 when it receives messages from Service1 and it does some other non database related work. Service2 is a long running process. The problem I am having is the moment Service2 updates a record in Table1, the table is locked. The lock seems to be in place until Service2 has completed all it is processing. i.e. The lock is not released after my datacontext is disposed. This causes the query in Service1 to timeout. Once Service2 completes processing, Service1 resumes processing again without problem. So for example Service1 code may look like: int x =0; using (DataContext db = new DataContext()) { x = (from dp in db.Table1 select dp).Count(); // this line will timeout while service2 is processing Table1 t = new Table1(); t.Data = "test"; db.Table1.InsertOnSubmit(t); db.SubmitChanges(); } if(x % 50 == 0) CallService2(); The code in service2 may look like: using (DataContext db = new DataContext()) { Table1 t = db.Table1.Where(t => t.id == myId); t.Data = "updated"; db.SubmitChanges(); } // I would have expected the lock to have been released at this point, but this is not the case. DoSomeLongRunningTasks(); // lock will be released once service2 exits I don't understand why the lock is not released when the datacontext is disposed in Service2. To get around the problem I have been calling: db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"); and this works, but I am not happy using it. I want to solve this problem properly. Has any one experienced this sort of problem before and does any one know how to solve it? Why is the lock not released after the datacontext has been disposed? Thanks in advance. p.s. sorry for the extremely long post.

  Read the article

< Previous Page | 29 30 31 32 33 34 35 36 37 38 39  | Next Page >