Search Results

Search found 23955 results on 959 pages for 'insert query'.

Page 246/959 | < Previous Page | 242 243 244 245 246 247 248 249 250 251 252 253  | Next Page >

• Run time insert using bulk update ,giving an internal error?

  - by Vineet

  Hi , I am trying to make a run time table named dynamic and inserting data into it from index by table using bulk update,but when i am trying to execute it this error is coming: ERROR at line 1: ORA-06550: line 0, column 0: PLS-00801: internal error [74301 ] declare type index_tbl_type IS table of number index by binary_integer; num_tbl index_tbl_type; TYPE ref_cur IS REF CURSOR; cur_emp ref_cur; begin execute immediate 'create table dynamic (v_num number)';--Creating a run time tabl FOR i in 1..10000 LOOP execute immediate 'insert into dynamic values('||i||')';--run time insert END LOOP; OPEN cur_emp FOR 'select * from dynamic';--opening ref cursor FETCH cur_emp bulk collect into num_tbl;--bulk inserting in index by table close cur_emp; FORALL i in num_tbl.FIRST..num_tbl.LAST --Bulk update execute immediate 'insert into dynamic values('||num_tbl(i)||')'; end;

  Read the article

• select mysql data using MAX

  - by JPro

  I have a testdata like this: DROP TABLE SELECT_PASS; CREATE TABLE SELECT_PASS(ID INT(20),TESTCASE VARCHAR(20),RESULT VARCHAR(20)); INSERT INTO SELECT_PASS VALUES(1,"TC1","PASS"); INSERT INTO SELECT_PASS VALUES(2,"TC2","PASS"); INSERT INTO SELECT_PASS VALUES(3,"TC3","INCONC"); INSERT INTO SELECT_PASS VALUES(4,"TC1","FAIL"); INSERT INTO SELECT_PASS VALUES(5,"TC21","FAIL"); INSERT INTO SELECT_PASS VALUES(6,"TC4","PASS"); INSERT INTO SELECT_PASS VALUES(7,"TC3","PASS"); INSERT INTO SELECT_PASS VALUES(8,"TC2","PASS"); INSERT INTO SELECT_PASS VALUES(9,"TC1","TIMEOUT"); SELECT TESTCASE, MAX(RESULT) FROM SELECT_PASS GROUP BY TESTCASE; The resultset I get is : TC1 TIMEOUT TC2 PASS TC21 FAIL TC3 PASS TC4 PASS Basically I want to see those testcases which never passed. Any way to do it? Thanks.

  Read the article

• SQLITE - INSERT does not return error but no data is inserted.

  - by Nick

  I am attempting to insert data into a local SQLITE database file from a C# application. The transaction does not throw any errors but the data is not inserted. The same insert statement works from within a query analyzer. Do I need to perform a commit? Is there a Commit method? Command's transaction property is null.. var command = new SQLiteCommand(insert.BuildInsert(tableName,keyValuePairs),Connection); command.ExecuteNonQuery();

  Read the article

• How to insert a value based on lookup from another table [SQL]?

  - by Shaitan00

  I need to find a way to do an INSERT INTO table A but one of the values is something that comes from a lookup on table B, allow me to illustrate. I have the 2 following tables: Table A: A1: String A2: Integer value coming from table B A3: More Data Table B: B1: String B2: Integer Value Example row of A: {"Value", 101, MoreData} Example row of B: {"English", 101} Now, I know I need to INSERT the following into A {"Value2", "English", MoreData} but obviously that won't work because it is expecting an Integer in the second column not the word "English", so I need to do a lookup in Table B first. Something like this: INSERT INTO tableA (A1, A2, A3) VALUES ("Value2", SELECT B2 FROM tableB where B1="English", MoreData); Obviously this doesn't work as-is ... Any suggestions?

  Read the article

• Insert a default row into a combobox that is bound to a datatable?

  - by John M

  On a winform there is a combobox that derives its information from a datatable. The datatable draws from a database list. this.cboList.DataSource = pullData(); this.cboList.DisplayMember = "fieldA"; Once the DataSource is set I am not able to insert a default row (ie *) as the first item in the combobox. I tried this: this.cboList.Items.Insert(0,"*"); Is there a way to insert in the combobox after the datasource is set or should this be done in the datatable?

  Read the article

• Overload and hide methods in Java

  - by Marco

  Hi, i have an abstract class BaseClass with a public insert() method: public abstract class BaseClass { public void insert(Object object) { // Do something } } which is extended by many other classes. For some of those classes, however, the insert() method must have additional parameters, so that they instead of overriding it I overload the method of the base class with the parameters required, for example: public class SampleClass extends BaseClass { public void insert(Object object, Long param){ // Do Something } } Now, if i instantiate the SampleClass class, i have two insert() methods: SampleClass sampleClass = new SampleClass(); sampleClass.insert(Object object); sampleClass.insert(Object object, Long param); what i'd like to do is to hide the insert() method defined in the base class, so that just the overload would be visible: SampleClass sampleClass = new SampleClass(); sampleClass.insert(Object object, Long param); Could this be done in OOP?

  Read the article

• INSERT INTO ... SELECT ... vs dumping/loading a file in MySQL

  - by Daniel Huckstep

  What are the implications of using a INSERT INTO foo ... SELECT FROM bar JOIN baz ... style insert statement versus using the same SELECT statement to dump (bar, baz) to a file, and then insert into foo by loading the file? In my messing around, I haven't seen a huge difference. I would assume the former would use more memory, but the machine that this runs on has 8GB of RAM, and I never even see it go past half used. Are there any huge (or long term) performance implications that I'm not seeing? Advantages/disadvantages of either?

  Read the article

• How to insert large files in mysql database using php? [closed]

  - by anjan

  Hi! I want to upload a large file of size 10M max to my mysql database. Using .htaccess i changed the PHP's own file upload limit to "10485760" = 10M, i am able to upload files upto 10M size without any problem. But i can not insert the file in database if it is more that 1M in size. i am using file_get_contents to read all file data and pass it to the insert query as a string to be inserted into a LONGBLOB field. But files with more than 1M size is not being added to database, though i can use print_r($_FILES) to examine that the file uploaded correctly. Any help will be appreciated and i will need it within next 6 hours. So, please help! best regards, Anjan * This is a duplicate of http://stackoverflow.com/questions/492549/how-can-i-insert-large-files-in-mysql-db-using-php *

  Read the article

• The SSIS tuning tip that everyone misses

  - by Rob Farley

  I know that everyone misses this, because I’m yet to find someone who doesn’t have a bit of an epiphany when I describe this. When tuning Data Flows in SQL Server Integration Services, people see the Data Flow as moving from the Source to the Destination, passing through a number of transformations. What people don’t consider is the Source, getting the data out of a database. Remember, the source of data for your Data Flow is not your Source Component. It’s wherever the data is, within your database, probably on a disk somewhere. You need to tune your query to optimise it for SSIS, and this is what most people fail to do. I’m not suggesting that people don’t tune their queries – there’s plenty of information out there about making sure that your queries run as fast as possible. But for SSIS, it’s not about how fast your query runs. Let me say that again, but in bolder text: The speed of an SSIS Source is not about how fast your query runs. If your query is used in a Source component for SSIS, the thing that matters is how fast it starts returning data. In particular, those first 10,000 rows to populate that first buffer, ready to pass down the rest of the transformations on its way to the Destination. Let’s look at a very simple query as an example, using the AdventureWorks database: We’re picking the different Weight values out of the Product table, and it’s doing this by scanning the table and doing a Sort. It’s a Distinct Sort, which means that the duplicates are discarded. It'll be no surprise to see that the data produced is sorted. Obvious, I know, but I'm making a comparison to what I'll do later. Before I explain the problem here, let me jump back into the SSIS world... If you’ve investigated how to tune an SSIS flow, then you’ll know that some SSIS Data Flow Transformations are known to be Blocking, some are Partially Blocking, and some are simply Row transformations. Take the SSIS Sort transformation, for example. I’m using a larger data set for this, because my small list of Weights won’t demonstrate it well enough. Seven buffers of data came out of the source, but none of them could be pushed past the Sort operator, just in case the last buffer contained the data that would be sorted into the first buffer. This is a blocking operation. Back in the land of T-SQL, we consider our Distinct Sort operator. It’s also blocking. It won’t let data through until it’s seen all of it. If you weren’t okay with blocking operations in SSIS, why would you be happy with them in an execution plan? The source of your data is not your OLE DB Source. Remember this. The source of your data is the NCIX/CIX/Heap from which it’s being pulled. Picture it like this... the data flowing from the Clustered Index, through the Distinct Sort operator, into the SELECT operator, where a series of SSIS Buffers are populated, flowing (as they get full) down through the SSIS transformations. Alright, I know that I’m taking some liberties here, because the two queries aren’t the same, but consider the visual. The data is flowing from your disk and through your execution plan before it reaches SSIS, so you could easily find that a blocking operation in your plan is just as painful as a blocking operation in your SSIS Data Flow. Luckily, T-SQL gives us a brilliant query hint to help avoid this. OPTION (FAST 10000) This hint means that it will choose a query which will optimise for the first 10,000 rows – the default SSIS buffer size. And the effect can be quite significant. First let’s consider a simple example, then we’ll look at a larger one. Consider our weights. We don’t have 10,000, so I’m going to use OPTION (FAST 1) instead. You’ll notice that the query is more expensive, using a Flow Distinct operator instead of the Distinct Sort. This operator is consuming 84% of the query, instead of the 59% we saw from the Distinct Sort. But the first row could be returned quicker – a Flow Distinct operator is non-blocking. The data here isn’t sorted, of course. It’s in the same order that it came out of the index, just with duplicates removed. As soon as a Flow Distinct sees a value that it hasn’t come across before, it pushes it out to the operator on its left. It still has to maintain the list of what it’s seen so far, but by handling it one row at a time, it can push rows through quicker. Overall, it’s a lot more work than the Distinct Sort, but if the priority is the first few rows, then perhaps that’s exactly what we want. The Query Optimizer seems to do this by optimising the query as if there were only one row coming through: This 1 row estimation is caused by the Query Optimizer imagining the SELECT operation saying “Give me one row” first, and this message being passed all the way along. The request might not make it all the way back to the source, but in my simple example, it does. I hope this simple example has helped you understand the significance of the blocking operator. Now I’m going to show you an example on a much larger data set. This data was fetching about 780,000 rows, and these are the Estimated Plans. The data needed to be Sorted, to support further SSIS operations that needed that. First, without the hint. ...and now with OPTION (FAST 10000): A very different plan, I’m sure you’ll agree. In case you’re curious, those arrows in the top one are 780,000 rows in size. In the second, they’re estimated to be 10,000, although the Actual figures end up being 780,000. The top one definitely runs faster. It finished several times faster than the second one. With the amount of data being considered, these numbers were in minutes. Look at the second one – it’s doing Nested Loops, across 780,000 rows! That’s not generally recommended at all. That’s “Go and make yourself a coffee” time. In this case, it was about six or seven minutes. The faster one finished in about a minute. But in SSIS-land, things are different. The particular data flow that was consuming this data was significant. It was being pumped into a Script Component to process each row based on previous rows, creating about a dozen different flows. The data flow would take roughly ten minutes to run – ten minutes from when the data first appeared. The query that completes faster – chosen by the Query Optimizer with no hints, based on accurate statistics (rather than pretending the numbers are smaller) – would take a minute to start getting the data into SSIS, at which point the ten-minute flow would start, taking eleven minutes to complete. The query that took longer – chosen by the Query Optimizer pretending it only wanted the first 10,000 rows – would take only ten seconds to fill the first buffer. Despite the fact that it might have taken the database another six or seven minutes to get the data out, SSIS didn’t care. Every time it wanted the next buffer of data, it was already available, and the whole process finished in about ten minutes and ten seconds. When debugging SSIS, you run the package, and sit there waiting to see the Debug information start appearing. You look for the numbers on the data flow, and seeing operators going Yellow and Green. Without the hint, I’d sit there for a minute. With the hint, just ten seconds. You can imagine which one I preferred. By adding this hint, it felt like a magic wand had been waved across the query, to make it run several times faster. It wasn’t the case at all – but it felt like it to SSIS.

  Read the article

• Chaining IQueryables together

  - by Matt Greer

  I have a RIA Services based app that is using Entity Framework on the server side (possibly not relevant). In my real app, I can do something like this. EntityQuery<Status> query = statusContext.GetStatusesQuery().Where(s => s.Description.Contains("Foo")); Where statusContext is the client side subclass of DomainContext that RIA Services was kind enough to generate for me. The end result is an EntityQuery<Status> object who's Query property is an object that implements IQueryable and represents my where clause. The WebDomainClient is able to take this EntityQuery and not just give me back all of my Statuses but also filtered with my where clause. I am trying to implement this in a mock DomainClient. This MockDomainClient accepts an IQueryably<Entity> which it returns when asked for. But what if the user makes the query and includes the ad hoc additional query? How can I merge the two together? My MockDomainClient is (this is modeled after this blog post) ... public class MockDomainClient : LocalDomainClient { private IQueryable<Entity> _entities; public MockDomainClient(IQueryable<Entity> entities) { _entities = entities; } public override IQueryable<Entity> DoQuery(EntityQuery query) { if (query.Query == null) { return _entities; } // otherwise want the union of _entities and query.Query, query.Query is IQueryable // the below does not work and was a total shot in the dark: //return _entities.Union(query.Query.Cast<Entity>()); } } public abstract class LocalDomainClient : System.ServiceModel.DomainServices.Client.DomainClient { private SynchronizationContext _syncContext; protected LocalDomainClient() { _syncContext = SynchronizationContext.Current; } ... public abstract IQueryable<Entity> DoQuery(EntityQuery query); protected override IAsyncResult BeginQueryCore(EntityQuery query, AsyncCallback callback, object userState) { IQueryable<Entity> localQuery = DoQuery(query); LocalAsyncResult asyncResult = new LocalAsyncResult(callback, userState, localQuery); _syncContext.Post(o => (o as LocalAsyncResult).Complete(), asyncResult); return asyncResult; } ... }

  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

• How to do a timestamp comparison with JPA query?

  - by Robert

  We need to make sure only results within the last 30 days are returned for a JPQL query. An example follows: Date now = new Date(); Timestamp thirtyDaysAgo = new Timestamp(now.getTime() - 86400000*30); Query query = em.createQuery( "SELECT msg FROM Message msg "+ "WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > {ts, '"+thirtyDaysAgo+"'}"); List result = query.getResultList(); Here is the error we receive: <openjpa-1.2.3-SNAPSHOT-r422266:907835 nonfatal user error org.apache.openjpa.persistence.ArgumentException: An error occurred while parsing the query filter 'SELECT msg FROM BroadcastMessage msg WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime {ts, '2010-04-18 04:15:37.827'}'. Error message: org.apache.openjpa.kernel.jpql.TokenMgrError: Lexical error at line 1, column 217. Encountered: "{" (123), after : "" Help!

  Read the article

• Using normalize-string XPath function from SQL XML query ?

  - by Ross Watson

  Hi, is it possible to run an SQL query, with an XPath "where" clause, and to trim trailing spaces before the comparison ? I have an SQL XML column, in which I have XML nodes with attributes which contain trailing spaces. I would like to find a given record, which has a specified attribute value - without the trailing spaces. When I try, I get... "There is no function '{http://www.w3.org/2004/07/xpath-functions}:normalize-space()'" I have tried the following (query 1 works, query 2 doesn't). This is on SQL 2005. declare @test table (data xml) insert into @test values ('<thing xmlns="http://my.org.uk/Things" x="hello " />') -- query 1 ;with xmlnamespaces ('http://my.org.uk/Things' as ns0) select * from @test where data.exist('ns0:thing[@x="hello "]') != 0 -- query 2 ;with xmlnamespaces ('http://my.org.uk/Things' as ns0) select * from @test where data.exist('ns0:thing[normalize-space(@x)="hello "]') != 0 Thanks for any help, Ross

  Read the article

• Is it Possible to Query Multiple Databases with WCF Data Services?

  - by Mas

  I have data being inserted into multiple databases with the same schema. The multiple databases exist for performance reasons. I need to create a WCF service that a client can use to query the databases. However from the client's point of view, there is only 1 database. By this I mean when a client performs a query, it should query all databases and return the combined results. I also need to provide the flexibility for the client to define its own queries. Therefore I am looking into WCF Data Services, which provides the very nice functionality for client specified queries. So far, it seems that a DataService can only make a query to a single database. I found no override that would allow me to dispatch queries to multiple databases. Does anyone know if it is possible for a WCF Data Service to query against multiple databases with the same schema?

  Read the article

• How do I get a linq to sql group by query into the asp.net mvc view?

  - by Brad Wetli

  Sorry for the newbie question, but I have the following query that groups parking spaces by their garage, but I can't figure out how to iterate the data in the view. I guess I should strongly type the view but am a newbie and having lots of problems figuring this out. Any help would be appreciated. Public Function FindAllSpaces() Implements ISpaceRepository.FindAllSpaces Dim query = _ From s In db.spaces _ Order By s.name Ascending _ Group By s.garageid Into spaces = Group _ Order By garageid Ascending Return query End Function The controller is taking the query object as is and putting it into the viewdata.model and as stated the view is not currently strongly typed as I haven't been able to figure out how to do this. I have run the query successfully in linqpad.

  Read the article

• SQL Server Query Slow from PHP, but FAST from SQL Mgt Studio - WHY???

  - by Ray

  I have a fast running query (sub 1 sec) when I execute the query in SQL Server Mgt Studio, but when I run the exact same query in PHP (on the same db instace) using FreeTDS v8, mssql_query(), it takes much longer (70+ seconds). The tables I'm hitting have an index on a date field that I'm using in the Where clause. Could it be that PHP's mssql functions aren't utilizing the index? I have also tried putting the query inside a stored procedure, then executing the SP from PHP - the same results in time difference occurs. I have also tried adding a WITH ( INDEX( .. ) ) clause on the table where that has the date index, but no luck either. Here's the query: SELECT 1 History, h.CUSTNMBR CustNmbr, CONVERT(VARCHAR(10), h.ORDRDATE, 120 ) OrdDate, h.SOPNUMBE OrdNmbr, h.SUBTOTAL OrdTotal, h.CSTPONBR PONmbr, h.SHIPMTHD Shipper, h.VOIDSTTS VoidStatus, h.BACHNUMB BatchNmbr, h.MODIFDT ModifDt FROM SOP30200 h WITH (INDEX (AK2SOP30200)) WHERE h.SOPTYPE = 2 AND h.DOCDATE >= DATEADD(dd, -61, GETDATE()) AND h.VOIDSTTS = 0 AND h.MODIFDT = CONVERT(VARCHAR(10), DATEADD(dd, -1*@daysAgo, GETDATE()) , 120 ) ;

  Read the article

• How to implement dynamic binary search for search and insert operations of n element (C or C++)

  - by iecut

  The idea is to use multiple arrays, each of length 2^k, to store n elements, according to binary representation of n.Each array is sorted and different arrays are not ordered in any way. In the above mentioned data structure, SEARCH is carried out by a sequence of binary search on each array. INSERT is carried out by a sequence of merge of arrays of the same length until an empty array is reached. More Detail: Lets suppose we have a vertical array of length 2^k and to each node of that array there attached horizontal array of length 2^k. That is, to the first node of vertical array, a horizontal array of length 2^0=1 is connected,to the second node of vertical array, a horizontal array of length 2^1= 2 is connected and so on. So the insert is first carried out in the first horizontal array, for the second insert the first array becomes empty and second horizontal array is full with 2 elements, for the third insert 1st and 2nd array horiz. array are filled and so on. I implemented the normal binary search for search and insert as follows: int main() { int a[20]= {0}; int n, i, j, temp; int *beg, *end, *mid, target; printf(" enter the total integers you want to enter (make it less then 20):\n"); scanf("%d", &n); if (n = 20) return 0; printf(" enter the integer array elements:\n" ); for(i = 0; i < n; i++) { scanf("%d", &a[i]); } // sort the loaded array, binary search! for(i = 0; i < n-1; i++) { for(j = 0; j < n-i-1; j++) { if (a[j+1] < a[j]) { temp = a[j]; a[j] = a[j+1]; a[j+1] = temp; } } } printf(" the sorted numbers are:"); for(i = 0; i < n; i++) { printf("%d ", a[i]); } // point to beginning and end of the array beg = &a[0]; end = &a[n]; // use n = one element past the loaded array! // mid should point somewhere in the middle of these addresses mid = beg += n/2; printf("\n enter the number to be searched:"); scanf("%d",&target); // binary search, there is an AND in the middle of while()!!! while((beg <= end) && (*mid != target)) { // is the target in lower or upper half? if (target < *mid) { end = mid - 1; // new end n = n/2; mid = beg += n/2; // new middle } else { beg = mid + 1; // new beginning n = n/2; mid = beg += n/2; // new middle } } // find the target? if (*mid == target) { printf("\n %d found!", target); } else { printf("\n %d not found!", target); } getchar(); // trap enter getchar(); // wait return 0; } Could anyone please suggest how to modify this program or a new program to implement dynamic binary search that works as explained above!!

  Read the article

• Combining DROP USER and DROP DATABASE with SELECT .. WHERE query?

  - by zsero

  I'd like to make a very simple thing, replicate the functionality of mysql's interactive mysql_secure_installation script. My question is that is there a simple, built-in way in MySQL to combine the output of a SELECT query with the input of a DROP user or DROP database script? For example, if I'd like to drop all users with empty passwords. How could I do that with DROP USER statement? I know an obvious solution would be to run everything for example from a Python script, run a query with mysql -Bse "select..." parse the output with some program construct the drop query run it. Is there an easy way to do it in a simple SQL query? I've seen some example here, but I wouldn't call it simple: http://stackoverflow.com/a/12097567/518169 Would you recommend making a combined query, or just to parse the output using for example Python or bash scripts/sed?

  Read the article

• How do I query through a many-to-many relationship using NHibernate Criteria and Lambda Extensions?

  - by Brian Kendig

  In my database I have a Person table and an Event table (parties, meetings, &c.). This many-to-many relationship is represented through an Invitation table. Each Person can have many Invitations. Each Event can also have many Invitations. If I want a list of Events to which a Person is invited, I can use this HQL query: IQuery query = Session.CreateQuery("SELECT i.Event from Invitation i where i.Person = :p"); query.SetParameter("p", person); return query.List<Person>(); How would I write this query with NHibernate criteria and Lambda Extensions?

  Read the article

• rails 3, active record: any way to tell how many unique values match a "x LIKE ?" query

  - by jpwynn

  I have a query to find all the phone numbers that match a partial expression such as "ends with 234" @matchingphones = Calls.find :all, :conditions => [ "(thephonenumber LIKE ?)", "%234"] The same phone number might be in the database several times, and so might be returned multiple times by this query if it matches. What I need is to know is UNIQUE phone numbers the query returns. For example if the database contains 000-111-1234 * 000-111-3333 000-111-2234 * 000-111-1234 * 000-111-4444 the existing query will return the 3 records marked with * (eg returns one phone number -1234 twice since it's in the database twice) what I need is a query that returns just once instance of each match, in this case 000-111-1234 * 000-111-2234 *

  Read the article

• Plan Caching and Query Memory Part II (Hash Match) – When not to use stored procedure - Most common performance mistake SQL Server developers make.

  - by sqlworkshops

  SQL Server estimates Memory requirement at compile time, when stored procedure or other plan caching mechanisms like sp_executesql or prepared statement are used, the memory requirement is estimated based on first set of execution parameters. This is a common reason for spill over tempdb and hence poor performance. Common memory allocating queries are that perform Sort and do Hash Match operations like Hash Join or Hash Aggregation or Hash Union. This article covers Hash Match operations with examples. It is recommended to read Plan Caching and Query Memory Part I before this article which covers an introduction and Query memory for Sort. In most cases it is cheaper to pay for the compilation cost of dynamic queries than huge cost for spill over tempdb, unless memory requirement for a query does not change significantly based on predicates.   This article covers underestimation / overestimation of memory for Hash Match operation. Plan Caching and Query Memory Part I covers underestimation / overestimation for Sort. It is important to note that underestimation of memory for Sort and Hash Match operations lead to spill over tempdb and hence negatively impact performance. Overestimation of memory affects the memory needs of other concurrently executing queries. In addition, it is important to note, with Hash Match operations, overestimation of memory can actually lead to poor performance.   To read additional articles I wrote click here.   The best way to learn is to practice. To create the below tables and reproduce the behavior, join the mailing list by using this link: www.sqlworkshops.com/ml and I will send you the table creation script. Most of these concepts are also covered in our webcasts: www.sqlworkshops.com/webcasts  Let’s create a Customer’s State table that has 99% of customers in NY and the rest 1% in WA.Customers table used in Part I of this article is also used here.To observe Hash Warning, enable 'Hash Warning' in SQL Profiler under Events 'Errors and Warnings'. --Example provided by www.sqlworkshops.com drop table CustomersState go create table CustomersState (CustomerID int primary key, Address char(200), State char(2)) go insert into CustomersState (CustomerID, Address) select CustomerID, 'Address' from Customers update CustomersState set State = 'NY' where CustomerID % 100 != 1 update CustomersState set State = 'WA' where CustomerID % 100 = 1 go update statistics CustomersState with fullscan go   Let’s create a stored procedure that joins customers with CustomersState table with a predicate on State. --Example provided by www.sqlworkshops.com create proc CustomersByState @State char(2) as begin declare @CustomerID int select @CustomerID = e.CustomerID from Customers e inner join CustomersState es on (e.CustomerID = es.CustomerID) where es.State = @State option (maxdop 1) end go  Let’s execute the stored procedure first with parameter value ‘WA’ – which will select 1% of data. set statistics time on go --Example provided by www.sqlworkshops.com exec CustomersByState 'WA' goThe stored procedure took 294 ms to complete.  The stored procedure was granted 6704 KB based on 8000 rows being estimated.  The estimated number of rows, 8000 is similar to actual number of rows 8000 and hence the memory estimation should be ok.  There was no Hash Warning in SQL Profiler. To observe Hash Warning, enable 'Hash Warning' in SQL Profiler under Events 'Errors and Warnings'.   Now let’s execute the stored procedure with parameter value ‘NY’ – which will select 99% of data. -Example provided by www.sqlworkshops.com exec CustomersByState 'NY' go  The stored procedure took 2922 ms to complete.   The stored procedure was granted 6704 KB based on 8000 rows being estimated.    The estimated number of rows, 8000 is way different from the actual number of rows 792000 because the estimation is based on the first set of parameter value supplied to the stored procedure which is ‘WA’ in our case. This underestimation will lead to spill over tempdb, resulting in poor performance.   There was Hash Warning (Recursion) in SQL Profiler. To observe Hash Warning, enable 'Hash Warning' in SQL Profiler under Events 'Errors and Warnings'.   Let’s recompile the stored procedure and then let’s first execute the stored procedure with parameter value ‘NY’.  In a production instance it is not advisable to use sp_recompile instead one should use DBCC FREEPROCCACHE (plan_handle). This is due to locking issues involved with sp_recompile, refer to our webcasts, www.sqlworkshops.com/webcasts for further details.   exec sp_recompile CustomersByState go --Example provided by www.sqlworkshops.com exec CustomersByState 'NY' go  Now the stored procedure took only 1046 ms instead of 2922 ms.   The stored procedure was granted 146752 KB of memory. The estimated number of rows, 792000 is similar to actual number of rows of 792000. Better performance of this stored procedure execution is due to better estimation of memory and avoiding spill over tempdb.   There was no Hash Warning in SQL Profiler.   Now let’s execute the stored procedure with parameter value ‘WA’. --Example provided by www.sqlworkshops.com exec CustomersByState 'WA' go  The stored procedure took 351 ms to complete, higher than the previous execution time of 294 ms.    This stored procedure was granted more memory (146752 KB) than necessary (6704 KB) based on parameter value ‘NY’ for estimation (792000 rows) instead of parameter value ‘WA’ for estimation (8000 rows). This is because the estimation is based on the first set of parameter value supplied to the stored procedure which is ‘NY’ in this case. This overestimation leads to poor performance of this Hash Match operation, it might also affect the performance of other concurrently executing queries requiring memory and hence overestimation is not recommended.     The estimated number of rows, 792000 is much more than the actual number of rows of 8000.  Intermediate Summary: This issue can be avoided by not caching the plan for memory allocating queries. Other possibility is to use recompile hint or optimize for hint to allocate memory for predefined data range.Let’s recreate the stored procedure with recompile hint. --Example provided by www.sqlworkshops.com drop proc CustomersByState go create proc CustomersByState @State char(2) as begin declare @CustomerID int select @CustomerID = e.CustomerID from Customers e inner join CustomersState es on (e.CustomerID = es.CustomerID) where es.State = @State option (maxdop 1, recompile) end go  Let’s execute the stored procedure initially with parameter value ‘WA’ and then with parameter value ‘NY’. --Example provided by www.sqlworkshops.com exec CustomersByState 'WA' go exec CustomersByState 'NY' go  The stored procedure took 297 ms and 1102 ms in line with previous optimal execution times.   The stored procedure with parameter value ‘WA’ has good estimation like before.   Estimated number of rows of 8000 is similar to actual number of rows of 8000.   The stored procedure with parameter value ‘NY’ also has good estimation and memory grant like before because the stored procedure was recompiled with current set of parameter values.  Estimated number of rows of 792000 is similar to actual number of rows of 792000.    The compilation time and compilation CPU of 1 ms is not expensive in this case compared to the performance benefit.   There was no Hash Warning in SQL Profiler.   Let’s recreate the stored procedure with optimize for hint of ‘NY’. --Example provided by www.sqlworkshops.com drop proc CustomersByState go create proc CustomersByState @State char(2) as begin declare @CustomerID int select @CustomerID = e.CustomerID from Customers e inner join CustomersState es on (e.CustomerID = es.CustomerID) where es.State = @State option (maxdop 1, optimize for (@State = 'NY')) end go  Let’s execute the stored procedure initially with parameter value ‘WA’ and then with parameter value ‘NY’. --Example provided by www.sqlworkshops.com exec CustomersByState 'WA' go exec CustomersByState 'NY' go  The stored procedure took 353 ms with parameter value ‘WA’, this is much slower than the optimal execution time of 294 ms we observed previously. This is because of overestimation of memory. The stored procedure with parameter value ‘NY’ has optimal execution time like before.   The stored procedure with parameter value ‘WA’ has overestimation of rows because of optimize for hint value of ‘NY’.   Unlike before, more memory was estimated to this stored procedure based on optimize for hint value ‘NY’.    The stored procedure with parameter value ‘NY’ has good estimation because of optimize for hint value of ‘NY’. Estimated number of rows of 792000 is similar to actual number of rows of 792000.   Optimal amount memory was estimated to this stored procedure based on optimize for hint value ‘NY’.   There was no Hash Warning in SQL Profiler.   This article covers underestimation / overestimation of memory for Hash Match operation. Plan Caching and Query Memory Part I covers underestimation / overestimation for Sort. It is important to note that underestimation of memory for Sort and Hash Match operations lead to spill over tempdb and hence negatively impact performance. Overestimation of memory affects the memory needs of other concurrently executing queries. In addition, it is important to note, with Hash Match operations, overestimation of memory can actually lead to poor performance.   Summary: Cached plan might lead to underestimation or overestimation of memory because the memory is estimated based on first set of execution parameters. It is recommended not to cache the plan if the amount of memory required to execute the stored procedure has a wide range of possibilities. One can mitigate this by using recompile hint, but that will lead to compilation overhead. However, in most cases it might be ok to pay for compilation rather than spilling sort over tempdb which could be very expensive compared to compilation cost. The other possibility is to use optimize for hint, but in case one sorts more data than hinted by optimize for hint, this will still lead to spill. On the other side there is also the possibility of overestimation leading to unnecessary memory issues for other concurrently executing queries. In case of Hash Match operations, this overestimation of memory might lead to poor performance. When the values used in optimize for hint are archived from the database, the estimation will be wrong leading to worst performance, so one has to exercise caution before using optimize for hint, recompile hint is better in this case.   I explain these concepts with detailed examples in my webcasts (www.sqlworkshops.com/webcasts), I recommend you to watch them. The best way to learn is to practice. To create the above tables and reproduce the behavior, join the mailing list at www.sqlworkshops.com/ml and I will send you the relevant SQL Scripts.  Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2008 and SQL Server 2005 Performance Monitoring & Tuning Hands-on Workshop in London, United Kingdom during March 15-17, 2011, click here to register / Microsoft UK TechNet.These are hands-on workshops with a maximum of 12 participants and not lectures. For consulting engagements click here.   Disclaimer and copyright information:This article refers to organizations and products that may be the trademarks or registered trademarks of their various owners. Copyright of this article belongs to R Meyyappan / www.sqlworkshops.com. You may freely use the ideas and concepts discussed in this article with acknowledgement (www.sqlworkshops.com), but you may not claim any of it as your own work. This article is for informational purposes only; you use any of the suggestions given here entirely at your own risk.   R Meyyappan [email protected] LinkedIn: http://at.linkedin.com/in/rmeyyappan

  Read the article

• Import MySQL file in PHP

  - by Cudos

  I have a MySQL file which I want to import via PHP 5. In the name of user friendliness the user should not use tools like PHPmyadmin etc. Just hit a button and the file will get imported. I have already created code to upload the file to a location on the server. The file looks like this: INSERT INTO products VALUES ('', '0', '10', '', '1', 'be34112', '4536.jpg', '','','','0'); SET @master_id = LAST_INSERT_ID(); INSERT INTO products_description VALUES ('', '1', @master_id, '1', 'Kjole', '', 'beskrivelse', '2000', '25', 'kjole.xml', '', '', ''); INSERT INTO products_to_categories VALUES ('',@master_id,'5'); INSERT INTO products VALUES ('', @master_id, '10', '12', '1', 'be34112', '4536.jpg', '200','','','0'); SET @variant_id = LAST_INSERT_ID(); INSERT INTO products_description VALUES ('', '1', @variant_id, '1', 'Kjole', '', 'beskrivelse', '2000', '25', 'kjole.xml', '', '', ''); INSERT INTO options_to_products VALUES ('', @variant_id, '1', '1'); INSERT INTO options_to_products VALUES ('', @variant_id, '', '2'); INSERT INTO products VALUES ('', @master_id, '20', '17', '1', 'be34113', '4537.jpg', '200','','','0'); SET @variant_id = LAST_INSERT_ID(); INSERT INTO products_description VALUES ('', '1', @variant_id, '1', 'Kjole', '', 'beskrivelse æøå ÆØÅ & íjj´¨¨¨¨fdfd""', '3000', '25', 'kjole.xml', '', '', ''); INSERT INTO options_to_products VALUES ('', @variant_id, '1', ''); INSERT INTO options_to_products VALUES ('', @variant_id, '', '4');

  Read the article

• mysql report sql help

  - by sfgroups

  I have mysql table with data like this. record will have server with total cpu and virtual server with cpu assinged type, cpu srv1, 10 vsrv11, 2 vsrv12, 3 srv2, 15 vsrv21, 6 vsrv22, 7 vsrv23, 1 from the above data, I want to create output like this. server, total cpu, assigned cpu, free cpu srv1, 10, 5, 5 srv2, 15, 14, 1 Can you help me on creating sql query for this report? I have changed my table and data like this. CREATE TABLE `cpuallocation` ( `servertype` varchar(10) DEFAULT NULL, `servername` varchar(20) DEFAULT NULL, `hostname` varchar(20) DEFAULT NULL, `cpu_count` float DEFAULT NULL, UNIQUE KEY `server_uniq_idx` (`servertype`,`servername`,`hostname`) insert into cpuallocation values('srv', 'server1', '',16); insert into cpuallocation values('vir', 'server1', 'host1',5); insert into cpuallocation values('vir', 'server1', 'host2',2.5); insert into cpuallocation values('vir', 'server1', 'host3',4.5); insert into cpuallocation values('srv', 'server2', '',8); insert into cpuallocation values('vir', 'server2', 'host1',5); insert into cpuallocation values('vir', 'server2', 'host2',2.5); insert into cpuallocation values('srv', 'server3', '',24); insert into cpuallocation values('vir', 'server3', 'host1',12); insert into cpuallocation values('vir', 'server3', 'host2',2); insert into cpuallocation values('srv', 'server4', '',12); Update: I created two view, now I getting the result I want. create view v1 as select servername, sum(cpu_count) as cpu_allocated from cpuallocation where servertype='vir' group by servername; create view v2 as select servername, cpu_count as total_cpu from cpuallocation where servertype='srv'; select a.servername, a.total_cpu, b.cpu_allocated from v2 as a left join v1 as b on a.servername=b.servername; +------------+-----------+---------------+ | servername | total_cpu | cpu_allocated | +------------+-----------+---------------+ | server1 | 16 | 12 | | server2 | 8 | 7.5 | | server3 | 24 | 14 | | server4 | 12 | NULL | +------------+-----------+---------------+ 4 rows in set (0.00 sec) Is it possible to create a query with-out creating views?

  Read the article

• Bulk inserting best way to about it? + Helping me understand fully what I found so far

  - by chobo2

  Hi So I saw this post here and read it and it seems like bulk copy might be the way to go. http://stackoverflow.com/questions/682015/whats-the-best-way-to-bulk-database-inserts-from-c I still have some questions and want to know how things actually work. So I found 2 tutorials. http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx First way uses 2 ado.net 2.0 features. BulkInsert and BulkCopy. the second one uses linq to sql and OpenXML. This sort of appeals to me as I am using linq to sql already and prefer it over ado.net. However as one person pointed out in the posts what he just going around the issue at the cost of performance( nothing wrong with that in my opinion) First I will talk about the 2 ways in the first tutorial I am using VS2010 Express, .net 4.0, MVC 2.0, SQl Server 2005 Is ado.net 2.0 the most current version? Based on the technology I am using, is there some updates to what I am going to show that would improve it somehow? Is there any thing that these tutorial left out that I should know about? BulkInsert I am using this table for all the examples. CREATE TABLE [dbo].[TBL_TEST_TEST] ( ID INT IDENTITY(1,1) PRIMARY KEY, [NAME] [varchar](50) ) SP Code USE [Test] GO /****** Object: StoredProcedure [dbo].[sp_BatchInsert] Script Date: 05/19/2010 15:12:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_BatchInsert] (@Name VARCHAR(50) ) AS BEGIN INSERT INTO TBL_TEST_TEST VALUES (@Name); END C# Code /// <summary> /// Another ado.net 2.0 way that uses a stored procedure to do a bulk insert. /// Seems slower then "BatchBulkCopy" way and it crashes when you try to insert 500,000 records in one go. /// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 /// </summary> private static void BatchInsert() { // Get the DataTable with Rows State as RowState.Added DataTable dtInsertRows = GetDataTable(); SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand("sp_BatchInsert", connection); command.CommandType = CommandType.StoredProcedure; command.UpdatedRowSource = UpdateRowSource.None; // Set the Parameter with appropriate Source Column Name command.Parameters.Add("@Name", SqlDbType.VarChar, 50, dtInsertRows.Columns[0].ColumnName); SqlDataAdapter adpt = new SqlDataAdapter(); adpt.InsertCommand = command; // Specify the number of records to be Inserted/Updated in one go. Default is 1. adpt.UpdateBatchSize = 1000; connection.Open(); int recordsInserted = adpt.Update(dtInsertRows); connection.Close(); } So first thing is the batch size. Why would you set a batch size to anything but the number of records you are sending? Like I am sending 500,000 records so I did a Batch size of 500,000. Next why does it crash when I do this? If I set it to 1000 for batch size it works just fine. System.Data.SqlClient.SqlException was unhandled Message="A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)" Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=20 LineNumber=0 Number=233 Server="" State=0 StackTrace: at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataTable dataTable) at TestIQueryable.Program.BatchInsert() in C:\Users\a\Downloads\TestIQueryable\TestIQueryable\TestIQueryable\Program.cs:line 124 at TestIQueryable.Program.Main(String[] args) in C:\Users\a\Downloads\TestIQueryable\TestIQueryable\TestIQueryable\Program.cs:line 16 InnerException: Time it took to insert 500,000 records with insert batch size of 1000 took "2 mins and 54 seconds" Of course this is no official time I sat there with a stop watch( I am sure there are better ways but was too lazy to look what they where) So I find that kinda slow compared to all my other ones(expect the linq to sql insert one) and I am not really sure why. Next I looked at bulkcopy /// <summary> /// An ado.net 2.0 way to mass insert records. This seems to be the fastest. /// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 /// </summary> private static void BatchBulkCopy() { // Get the DataTable DataTable dtInsertRows = GetDataTable(); using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = "TBL_TEST_TEST"; // Number of records to be processed in one go sbc.BatchSize = 500000; // Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table // sbc.ColumnMappings.Add("ID", "ID"); sbc.ColumnMappings.Add("NAME", "NAME"); // Number of records after which client has to be notified about its status sbc.NotifyAfter = dtInsertRows.Rows.Count; // Event that gets fired when NotifyAfter number of records are processed. sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied); // Finally write to server sbc.WriteToServer(dtInsertRows); sbc.Close(); } } This one seemed to go really fast and did not even need a SP( can you use SP with bulk copy? If you can would it be better?) BatchCopy had no problem with a 500,000 batch size.So again why make it smaller then the number of records you want to send? I found that with BatchCopy and 500,000 batch size it took only 5 seconds to complete. I then tried with a batch size of 1,000 and it only took 8 seconds. So much faster then the bulkinsert one above. Now I tried the other tutorial. USE [Test] GO /****** Object: StoredProcedure [dbo].[spTEST_InsertXMLTEST_TEST] Script Date: 05/19/2010 15:39:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData nText) AS DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData INSERT INTO TBL_TEST_TEST(NAME) SELECT XMLProdTable.NAME FROM OPENXML(@hDoc, 'ArrayOfTBL_TEST_TEST/TBL_TEST_TEST', 2) WITH ( ID Int, NAME varchar(100) ) XMLProdTable EXEC sp_xml_removedocument @hDoc C# code. /// <summary> /// This is using linq to sql to make the table objects. /// It is then serailzed to to an xml document and sent to a stored proedure /// that then does a bulk insert(I think with OpenXML) /// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx /// </summary> private static void LinqInsertXMLBatch() { using (TestDataContext db = new TestDataContext()) { TBL_TEST_TEST[] testRecords = new TBL_TEST_TEST[500000]; for (int count = 0; count < 500000; count++) { TBL_TEST_TEST testRecord = new TBL_TEST_TEST(); testRecord.NAME = "Name : " + count; testRecords[count] = testRecord; } StringBuilder sBuilder = new StringBuilder(); System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder); XmlSerializer serializer = new XmlSerializer(typeof(TBL_TEST_TEST[])); serializer.Serialize(sWriter, testRecords); db.insertTestData(sBuilder.ToString()); } } So I like this because I get to use objects even though it is kinda redundant. I don't get how the SP works. Like I don't get the whole thing. I don't know if OPENXML has some batch insert under the hood but I do not even know how to take this example SP and change it to fit my tables since like I said I don't know what is going on. I also don't know what would happen if the object you have more tables in it. Like say I have a ProductName table what has a relationship to a Product table or something like that. In linq to sql you could get the product name object and make changes to the Product table in that same object. So I am not sure how to take that into account. I am not sure if I would have to do separate inserts or what. The time was pretty good for 500,000 records it took 52 seconds The last way of course was just using linq to do it all and it was pretty bad. /// <summary> /// This is using linq to sql to to insert lots of records. /// This way is slow as it uses no mass insert. /// Only tried to insert 50,000 records as I did not want to sit around till it did 500,000 records. /// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx /// </summary> private static void LinqInsertAll() { using (TestDataContext db = new TestDataContext()) { db.CommandTimeout = 600; for (int count = 0; count < 50000; count++) { TBL_TEST_TEST testRecord = new TBL_TEST_TEST(); testRecord.NAME = "Name : " + count; db.TBL_TEST_TESTs.InsertOnSubmit(testRecord); } db.SubmitChanges(); } } I did only 50,000 records and that took over a minute to do. So I really narrowed it done to the linq to sql bulk insert way or bulk copy. I am just not sure how to do it when you have relationship for either way. I am not sure how they both stand up when doing updates instead of inserts as I have not gotten around to try it yet. I don't think I will ever need to insert/update more than 50,000 records at one type but at the same time I know I will have to do validation on records before inserting so that will slow it down and that sort of makes linq to sql nicer as your got objects especially if your first parsing data from a xml file before you insert into the database. Full C# code using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Serialization; using System.Data; using System.Data.SqlClient; namespace TestIQueryable { class Program { private static string connectionString = ""; static void Main(string[] args) { BatchInsert(); Console.WriteLine("done"); } /// <summary> /// This is using linq to sql to to insert lots of records. /// This way is slow as it uses no mass insert. /// Only tried to insert 50,000 records as I did not want to sit around till it did 500,000 records. /// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx /// </summary> private static void LinqInsertAll() { using (TestDataContext db = new TestDataContext()) { db.CommandTimeout = 600; for (int count = 0; count < 50000; count++) { TBL_TEST_TEST testRecord = new TBL_TEST_TEST(); testRecord.NAME = "Name : " + count; db.TBL_TEST_TESTs.InsertOnSubmit(testRecord); } db.SubmitChanges(); } } /// <summary> /// This is using linq to sql to make the table objects. /// It is then serailzed to to an xml document and sent to a stored proedure /// that then does a bulk insert(I think with OpenXML) /// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx /// </summary> private static void LinqInsertXMLBatch() { using (TestDataContext db = new TestDataContext()) { TBL_TEST_TEST[] testRecords = new TBL_TEST_TEST[500000]; for (int count = 0; count < 500000; count++) { TBL_TEST_TEST testRecord = new TBL_TEST_TEST(); testRecord.NAME = "Name : " + count; testRecords[count] = testRecord; } StringBuilder sBuilder = new StringBuilder(); System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder); XmlSerializer serializer = new XmlSerializer(typeof(TBL_TEST_TEST[])); serializer.Serialize(sWriter, testRecords); db.insertTestData(sBuilder.ToString()); } } /// <summary> /// An ado.net 2.0 way to mass insert records. This seems to be the fastest. /// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 /// </summary> private static void BatchBulkCopy() { // Get the DataTable DataTable dtInsertRows = GetDataTable(); using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = "TBL_TEST_TEST"; // Number of records to be processed in one go sbc.BatchSize = 500000; // Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table // sbc.ColumnMappings.Add("ID", "ID"); sbc.ColumnMappings.Add("NAME", "NAME"); // Number of records after which client has to be notified about its status sbc.NotifyAfter = dtInsertRows.Rows.Count; // Event that gets fired when NotifyAfter number of records are processed. sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied); // Finally write to server sbc.WriteToServer(dtInsertRows); sbc.Close(); } } /// <summary> /// Another ado.net 2.0 way that uses a stored procedure to do a bulk insert. /// Seems slower then "BatchBulkCopy" way and it crashes when you try to insert 500,000 records in one go. /// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 /// </summary> private static void BatchInsert() { // Get the DataTable with Rows State as RowState.Added DataTable dtInsertRows = GetDataTable(); SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand("sp_BatchInsert", connection); command.CommandType = CommandType.StoredProcedure; command.UpdatedRowSource = UpdateRowSource.None; // Set the Parameter with appropriate Source Column Name command.Parameters.Add("@Name", SqlDbType.VarChar, 50, dtInsertRows.Columns[0].ColumnName); SqlDataAdapter adpt = new SqlDataAdapter(); adpt.InsertCommand = command; // Specify the number of records to be Inserted/Updated in one go. Default is 1. adpt.UpdateBatchSize = 500000; connection.Open(); int recordsInserted = adpt.Update(dtInsertRows); connection.Close(); } private static DataTable GetDataTable() { // You First need a DataTable and have all the insert values in it DataTable dtInsertRows = new DataTable(); dtInsertRows.Columns.Add("NAME"); for (int i = 0; i < 500000; i++) { DataRow drInsertRow = dtInsertRows.NewRow(); string name = "Name : " + i; drInsertRow["NAME"] = name; dtInsertRows.Rows.Add(drInsertRow); } return dtInsertRows; } static void sbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) { Console.WriteLine("Number of records affected : " + e.RowsCopied.ToString()); } } }

  Read the article

• JPA 2 and Hibernate 3.5.1 MEMBER OF query doesnt work.

  - by Ed_Zero

  I'm trying the following JPQL and it fails misserably: Query query = em.createQuery("SELECT u FROM User u WHERE 'admin' MEMBER OF u.roles"); List users = query.query.getResultList(); I get the following exception: ERROR [main] PARSER.error(454) | <AST>:0:0: unexpected end of subtree java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected end of subtree [SELECT u FROM com.online.data.User u WHERE 'admin' MEMBER OF u.roles] ERROR [main] PARSER.error(454) | <AST>:0:0: expecting "from", found '<ASTNULL>' ... ... Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected end of subtree [SELECT u FROM com.online.data.User u WHERE 'admin' MEMBER OF u.roles] I have Spring 3.0.1.RELEASE, Hibernate 3.5.1-Final and maven to glue dependencies. User class: @Entity public class User { @Id @Column(name = "USER_ID") @GeneratedValue(strategy = GenerationType.IDENTITY) private long id; @Column(unique = true, nullable = false) private String username; private boolean enabled; @ElementCollection private Set<String> roles = new HashSet<String>(); ... } Spring configuration: <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/tx/spring-context-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"> <!-- Reading annotation driven configuration --> <tx:annotation-driven transaction-manager="transactionManager" /> <bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" /> <bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" /> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.driverClassName}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <property name="maxActive" value="100" /> <property name="maxWait" value="1000" /> <property name="poolPreparedStatements" value="true" /> <property name="defaultAutoCommit" value="true" /> </bean> <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"> <property name="entityManagerFactory" ref="entityManagerFactory" /> <property name="dataSource" ref="dataSource" /> </bean> <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="jpaVendorAdapter"> <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> <property name="showSql" value="true" /> <property name="databasePlatform" value="${hibernate.dialect}" /> </bean> </property> <property name="loadTimeWeaver"> <bean class="org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver" /> </property> <property name="jpaProperties"> <props> <prop key="hibernate.hbm2ddl.auto">update</prop> <prop key="hibernate.current_session_context_class">thread</prop> <prop key="hibernate.cache.provider_class">org.hibernate.cache.NoCacheProvider</prop> <prop key="hibernate.show_sql">true</prop> <prop key="hibernate.format_sql">false</prop> <prop key="hibernate.show_comments">true</prop> </props> </property> <property name="persistenceUnitName" value="punit" /> </bean> <bean id="JpaTemplate" class="org.springframework.orm.jpa.JpaTemplate"> <property name="entityManagerFactory" ref="entityManagerFactory" /> </bean> </beans> Persistence.xml <?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence"> <persistence-unit name="punit" transaction-type="RESOURCE_LOCAL" /> </persistence> pom.xml maven dependencies. <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate</artifactId> <version>${hibernate.version}</version> <type>pom</type> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>${hibernate.version}</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-annotations</artifactId> <version>${hibernate.version}</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>${hibernate.version}</version> </dependency> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.2.2</version> <type>jar</type> </dependency> <dependency> <groupId>org.springframework.security</groupId> <artifactId>spring-security-web</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework.security</groupId> <artifactId>spring-security-config</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework.security</groupId> <artifactId>spring-security-taglibs</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework.security</groupId> <artifactId>spring-security-acl</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>javax.annotation</groupId> <artifactId>jsr250-api</artifactId> <version>1.0</version> </dependency> <properties> <!-- Application settings --> <spring.version>3.0.1.RELEASE</spring.version> <hibernate.version>3.5.1-Final</hibernate.version> Im running a unit test to check the configuration and I am able to run other JPQL queries the only ones that I am unable to run are the IS EMPTY, MEMBER OF conditions. The complete unit test is as follows: TestIntegration @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = { "/spring/dataLayer.xml"}) @Transactional @TransactionConfiguration public class TestUserDaoImplIntegration { @PersistenceContext private EntityManager em; @Test public void shouldTest() throws Exception { try { //WORKS Query query = em.createQuery("SELECT u FROM User u WHERE 'admin' in elements(u.roles)"); List users = query.query.getResultList(); //DOES NOT WORK } catch (Exception e) { e.printStackTrace(); throw e; } try { Query query = em.createQuery("SELECT u FROM User u WHERE 'admin' MEMBER OF u.roles"); List users = query.query.getResultList(); } catch (Exception e) { e.printStackTrace(); throw e; } } }

  Read the article

< Previous Page | 242 243 244 245 246 247 248 249 250 251 252 253  | Next Page >