Search Results

Search found 62701 results on 2509 pages for 'sql function'.

Page 726/2509 | < Previous Page | 722 723 724 725 726 727 728 729 730 731 732 733  | Next Page >

• MSBuild: automate collecting of db migration scripts?

  - by P Dub

  Summary of environment. Asp.net web application (source stored in svn) sqlserver database. (Database schema (tables/sprocs) stored in svn) db version is synced with web application assembly version. (stored in table 'CurrentVersion') CI hudson server that checks out web app from repo and runs custom msbuild file to publish/package app. My msbuild script updates the assembly version of the web app (Major.Minor.Revision.Build) on each build. The 'Revision' is set to the currently checked out svn revision and the 'Build' to the hudson build number (incremented on each automated build). This way i can match the app to a specific trunk revision also get other build stats from the hudson build number. I'd like to automate the collecting of migration scripts (updated sprocs etc) to add to the zip package. I guess by comparing the svn revision of the db that has yet to be deployed to, to the revision being deployed, i can find what db files have changed in the trunk since the last deployment to that database/environment. This could easily be achieved by manually calling the svn diff -r REVNO:REVNO command to list changed .sql files. These files could then manually have to be added to the package. It would be great if this could be automated. Firstly i'd imagine I'll have to write a custom task to check the version of the db that has yet to be deployed to. After that I'm quite unsure. Does anyone have any suggestion on how this would be achieved through an msbuild task either existing or custom? Finally I'll have to autogen a script to add to the package that updates the database version table so as to be in sync with the application.

  Read the article

• mysql replace matching but not changing

  - by alex

  I've used mysql's update replace function before, but even though I think I'm following the same syntax, I can't get this to work-it matches the rows, but doesn't replace. Here's what I'm trying to do: mysql> update contained_widgets set preference_values = replace(preference_values, '<li><a_href="/enewsletter"><span class="not-tc">eNewsletter</span></a></li>', '<li><a_href="/enewsletter"><span class="not-tc">eNewsletter</span></a></li> <li> <a_href="/projects"><span class="not-tc">Projects</span></a></li>'); Query OK, 0 rows affected (0.00 sec) Rows matched: 77 Changed: 0 Warnings: 0 I don't see what I'm missing. Any help is appreciated. I edited "a " to "a_" because the site thinks I'm posting spam links otherwise.

  Read the article

• How to load a type in parent-child website

  - by salvationishere

  I am developing a C# VS 2008 / SQL Server 2008 website. Although this same code compiles in the Project version, the website version does not compile. Many of their files are the same so I do not understand even where to look. The only compile error I get is: Could not load type 'DataMatch' in my DataMatch.aspx file. Is the problem in my web.config file? How do websites differ from projects in VS? All four of these files reside in same directory. Default.aspx file: <%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="AddFileToSQL" Title="Untitled Page" % ... Default.aspx.cs file: ... using System.Data.SqlClient; using System.Security.Principal; namespace AddFileToSQL { public partial class AddFileToSQL : System.Web.UI.Page { ... DataMatch.aspx file: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataMatch.aspx.cs" Inherits="DataMatch" % ... DataMatch.aspx.cs file: ... using System.Web.UI.WebControls; using AddFileToSQL; public partial class DataMatch : AddFileToSQL { ...

  Read the article

• Conditional insert as a single database transaction in HSQLDB 1.8?

  - by Kevin Pauli

  I'm using a particular database table like a "Set" data structure, i.e., you can attempt to insert the same row several times, but it will only contain one instance. The primary key is a natural key. For example, I want the following series of operations to work fine, and result in only one row for Oklahoma: insert into states_visited (state_name) values ('Oklahoma'); insert into states_visited (state_name) values ('Texas'); insert into states_visited (state_name) values ('Oklahoma'); I am of course getting an error due to the duplicate primary key on subsequent inserts of the same value. Is there a way to make the insert conditional, so that these errors are not thrown? I.e. only do the the insert if the natural key does not already exist? I know I could do a where clause and a subquery to test for the row's existence first, but it seems that would be expensive. That's 2 physical operations for one logical "conditional insert" operation. Anything like this in SQL? FYI I am using HSQLDB 1.8

  Read the article

• Converting delimited string to multiple values in mysql

  - by epo

  I have a mysql legacy table which contains an client identifier and a list of items, the latter as a comma-delimited string. E.g. "xyz001", "foo,bar,baz". This is legacy stuff and the user insists on being able to edit a comma delimited string. They now have a requirement for a report table with the above broken into separate rows, e.g. "xyz001", "foo" "xyz001", "bar" "xyz001", "baz" Breaking the string into substrings is easily doable and I have written a procedure to do this by creating a separate table, but that requires triggers to deal with deletes, updates and inserts. This query is required rarely (say once a month) but has to be absolutely up to date when it is run, so e.g. the overhead of triggers is not warranted and scheduled tasks to create the table might not be timely enough. Is there any way to write a function to return a table or a set so that I can join the identifier with the individual items on demand?

  Read the article

• Using a variable derived from a drop-down list as the column name in a select statement ... Access DB

  - by user1459698

  I'm working with the world's worst DB that was already here so don't blame me for that. So, here's what I have so far ... module = txtModule.value presstype = txtPressType.value SQL_query = "SELECT * FROM tbl_spareparts WHERE '"& module &"' <> '""' AND '"& module &"' = '"& presstype &"' AND Manufacturer = '"& txtsrch.value &"' ORDER BY SAP_Part_No" Set rsData = conn.Execute(SQL_query) This brings up the following SQL statement: SELECT * FROM tbl_spareparts WHERE 'Banyan_Module' <> '"' AND 'Banyan_Module' = 'PB' AND Manufacturer = 'Tester' ORDER BY SAP_Part_No Is there any way I can use the module variable as a column name - obviously the ''s around the column name are causing an error. This is really bothering me. BTW, I'm writing this in VBScript inside a .HTA application page as it has to run locally on tech PCs. Thanks. R.

  Read the article

• Advanced search engine or server for relational database [closed]

  - by Pawel

  In my current project we are storing big volume of data in relational database. One of the recent key requirements is to enrich application by adding some advanced search capabilities. In the Project, performance is one of the important factors due to very large tables (10+ milions of records) with parent-children relations (for example: multi-level parent-child relationship, where I am looking for all parents with specific children). The search engine should also be able to check these references for hits. I have found some potential engines on stack overflow, however it looks like that all of them are dedicated rather for text search than relational db and hosted on linux os: lucene Solr Sphinx As I understand some of them use documents as a source of searching, but is it possible or efficient to create programmaticaly documents based on my relational data? As I am not familiar with all of their features/capabilities can anyone please make some recommendations or propose some different solution? To summarize my requirements: framework/engine to search relational database including decendants. support for Microsoft SQL Server can be used in .NET applications preferably hosted on Windows systems Does any of mentioned above are able to solve my problem? do you know any better solution?

  Read the article

• T-SQL: how to sort table rows based on 2 columns

  - by Criss Nautilus

  I'm quite stuck with this problem for sometime now.. How do I sort column A depending on the contents of Column B? I have this sample: ID count columnA ColumnB 12 1 A B 13 2 C D 14 3 B C I want to sort it like this: ID count ColumnA ColumnB 12 1 A B 14 3 B C 13 2 C D so I need to sort the rows if the previous row of ColumnB = the next row of ColumnA I'm thinking a loop? but can't quite imagine how it will work... I was thinking it will go like this (maybe) SELECT a.ID, a.ColumnA, a.ColumnB FROM TableA WITH a (NOLOCK) LEFT JOIN TableA b WITH (NOLOCK) ON a.ID = b.ID and a.counts = b.counts Where a.columnB = b.ColumnA the above code isn't working though and I was thinking more on the lines of... DECLARE @counts int = 1 DECLARE @done int = 0 --WHILE @done = 0 BEGIN SELECT a.ID, a.ColumnA, a.ColumnB FROM TableA WITH a (NOLOCK) LEFT JOIN TableA b WITH (NOLOCK) ON a.ID = b.ID and a.counts = @counts Where a.columnB = b.ColumnA set @count = @count +1 END If this was a C code, would be easier for me but t-sql's syntax is making it a bit harder for a noobie like me.

  Read the article

• Showing an image after loading it from sql database

  - by user330075

  I have a problem showing the image form database in a view Details and a ImageController. Inside the view I have: img src=Url.Action("GetFile","Image", new {id= Model.id}) and in controller: public FileContentResult GetFile(int idl) { //int idl = 32; SqlDataReader rdr; byte[] fileContent = null; ........... return File(,,); } When the view is called, function GetFile it just won't work. But if I cut out the parameter int id1 and I instantiate it as a variable it does work. public FileContentResult GetFile() { int idl = 32; SqlDataReader rdr; byte[] fileContent = null; ........... return File(,,); } Why?

  Read the article

• How can i mock or test my deferred execution functionality?

  - by cottsak

  I have what could be seen as a bizarre hybrid of IQueryable<T> and IList<T> collections of domain objects passed up my application stack. I'm trying to maintain as much of the 'late querying' or 'lazy loading' as possible. I do this in two ways: By using a LinqToSql data layer and passing IQueryable<T>s through by repositories and to my app layer. Then after my app layer passing IList<T>s but where certain elements in the object/aggregate graph are 'chained' with delegates so as to defer their loading. Sometimes even the delegate contents rely on IQueryable<T> sources and the DataContext are injected. This works for me so far. What is blindingly difficult is proving that this design actually works. Ie. If i defeat the 'lazy' part somewhere and my execution happens early then the whole thing is a waste of time. I'd like to be able to TDD this somehow. I don't know a lot about delegates or thread safety as it applies to delegates acting on the same source. I'd like to be able to mock the DataContext and somehow trace both methods of deferring (IQueryable<T>'s SQL and the delegates) the loading so that i can have tests that prove that both functions are working at different levels/layers of the app/stack. As it's crucial that the deferring works for the design to be of any value, i'd like to see tests fail when i break the design at a given level (separate from the live implementation). Is this possible?

  Read the article

• sqlite3 date operations when joining two tables in a view?

  - by duncan

  In short, how to add minutes to a datetime from an integer located in another table, in one select statement, by joining them? I have a table P(int id, ..., int minutes) and a table S(int id, int p_id, datetime start) I want to generate a view that gives me PS(S.id, P.id, S.start + P.minutes) by joining S.p_id=P.id The problem is, if I was generating the query from the application, I can do stuff like: select datetime('2010-04-21 14:00', '+20 minutes'); 2010-04-21 14:20:00 By creating the string '+20 minutes' in the application and then passing it to sqlite. However I can't find a way to create this string in the select itself: select p.*,datetime(s.start_at, formatstring('+%s minutes', p.minutes)) from p,s where s.p_id=p.id; Because sqlite as far the documentation tells, does not provide any string format function, nor can I see any alternative way of expressing the date modifiers.

  Read the article

• Find Date From Non Contiguous Ranges

  - by AGoodDisplayName

  I am looking to find an best way to find a date from date ranges that may or may not be contiguous (I am trying to avoid a cursor, or a heavy function if possible). Lets say I have hotel guests that come and go (check in, check out). I want to find the date that a certain guest stayed their 45th night with us. The database we use records the data as so: Create Table #GuestLog( ClientId int, StartDate DateTime, EndDate DateTime) Here is some data Insert Into #GuestLog Values(1, '01/01/2010', '01/10/2010') Insert Into #GuestLog Values(1, '01/16/2010', '01/29/2010') Insert Into #GuestLog Values(1, '02/13/2010', '02/26/2010') Insert Into #GuestLog Values(1, '04/05/2010', '06/01/2010') Insert Into #GuestLog Values(1, '07/01/2010', '07/21/2010') So far I can only think of solutions that involve functions with temp tables and crazy stuff like that, I feel like I'm over thinking it. Thanks ahead of time.

  Read the article

• I want a insert query for a temp table

  - by John Stephen

  Hi..I am using C#.Net and Sql Server ( Windows Application ). I had created a temporary table. When a button is clicked, temporary table (#tmp_emp_answer) is created. I am having another button called "insert Values" and also 5 textboxes. The values that are entered in the textbox are used and whenever com.ExecuteNonQuery(); line comes, it throws an error message Invalid object name '#tbl_emp_answer'.. Below is the set of code.. Please give me a solution. Code for insert (in insert value button): private void btninsertvalues_Click(object sender, EventArgs e) { username = txtusername.Text; examloginid = txtexamloginid.Text; question = txtquestion.Text; answer = txtanswer.Text; useranswer = txtanswer.Text; SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=True;"); SqlCommand com = new SqlCommand("Insert into #tbl_emp_answer values('"+username+"','"+examloginid+"','"+question+"','"+answer+"','"+useranswer+"')", con); con.Open(); com.ExecuteNonQuery(); con.Close(); }

  Read the article

• Large scale Merge Replication strategy - what can go wrong?

  - by niidto

  Hi, I'm developing a piece of software that uses Merge Replication and SQL Compact on Windows Mobile 6. At the moment it is running on 5 devices reasonably well. The issues I've come up against are as follows: The schema has had to change a lot, and will continue to have to change as the application evolves. There have been various errors replicating these schema changes down to the device, uploads failing due to schema inconsistencies. Subscriptions expiring (after 14 days) and unable to reinitialize with upload - AKA, potential data los of unsynced data up to that point. Basically, the worst case scenario is data loss, and when merge replication fails, there seems to be no way back to get the data off. My method until now has been to drop and create the subscription on the device. I don't hear many people doing this, though it seems to solve everything. The long term plan is to role this out to 500+ devices. Any advice on people who have undertaken similar projects, and how to minimise data loss and make it so that there's appropriate error handling code to recover from sync failures would be much appreciated. James

  Read the article

• Conversion failed when converting datetime from character string

  - by salvationishere

  I am developing a C# VS 2008 / SQL Server 2005 Express website application. I have tried some of the fixes for this problem but my call stack differs from others. And these fixes did not fix my problem. What steps can I take to troubleshoot this? Here is my error: System.Data.SqlClient.SqlException was caught Message="Conversion failed when converting datetime from character string." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 LineNumber=10 Number=241 Procedure="AppendDataCT" Server="\\\\.\\pipe\\772EF469-84F1-43\\tsql\\query" State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ADONET_namespace.ADONET_methods.AppendDataCT(DataTable dt, Dictionary`2 dic) in c:\Documents and Settings\Admin\My Documents\Visual Studio 2008\WebSites\Jerry\App_Code\ADONET methods.cs:line 102 And here is the related code. When I debugged this code, "dic" only looped through the 3 column names, but did not look into row values which are stored in "dt", the Data Table. public static string AppendDataCT(DataTable dt, Dictionary<string, string> dic) { if (dic.Count != 3) throw new ArgumentOutOfRangeException("dic can only have 3 parameters"); string connString = ConfigurationManager.ConnectionStrings["AW3_string"].ConnectionString; string errorMsg; try { using (SqlConnection conn2 = new SqlConnection(connString)) { using (SqlCommand cmd = conn2.CreateCommand()) { cmd.CommandText = "dbo.AppendDataCT"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn2; foreach (string s in dic.Keys) { SqlParameter p = cmd.Parameters.AddWithValue(s, dic[s]); p.SqlDbType = SqlDbType.VarChar; } conn2.Open(); cmd.ExecuteNonQuery(); conn2.Close(); errorMsg = "The Person.ContactType table was successfully updated!"; } } }

  Read the article

• NHibernate will not insert a record

  - by Brian Beckham

  I have an application that is now 4+ years old that is exhibiting some odd behavior on our latest deployment. The application uses nHibernate for all inserts / updates / selects, etc. We are currently using .NET 2.0, and nHibernate 1.2 (I know, we need to upgrade) This deployment is on Windows 2008 Server x64, IIS 7.5 - what I have seen so far is that the application runs, but is unable to insert or update records in the DB - reads seem fine so far, but writes are a problem. SOME writes actually work, inserts into some small tables, but most never even make it to the DB. Using SQL Profiler, the insert / updates never make it to the server, and turning log4net up to DEBUG, and show_sql true - the select statements appear, but the insert / update statements never make it into the log at all, and never show up at the server. What's even more odd is that the application seems to be oblivious to this - the commandandclose runs without exception (open session in view with an httpmodule), the domain objects come back with uuid's generated, etc. but never get persisted. Certainly an upgrade is due, but I would hate to try it during a deployment, and without time to accurately test the app. Any ideas?

  Read the article

• Aggregate Functions in Index with IBMDB2

  - by Erkan

  Is there any way to pre aggregate results of aggregate functionts (f.i. count()) and store it in an index? The background is: i want to speed up count() queries. So that: Select count(users) from TE123 where region = 'A'; would be supported by an index like Region Count(Users) A 548 E 458 I know that MQTs would also help for this problem. However, in this case it is not possible to use MQT, as we use kind of an ORM and we don't want to define Entities on MQTs. I just slightly remember - one DBA told me - that there is such a function planned for DB2 V10.

  Read the article

• transaction handling in dataset based insert/update in c#

  - by user3703611

  I am trying to insert bulk records in a sql server database table using dataset. But i am unable to do transaction handling. Please help me to apply transaction handling in below code. I am using adapter.UpdateCommand.Transaction = trans; but this line give me an error of Object reference not set to an instance of an object. Code: string ConnectionString = "server=localhost\\sqlexpress;database=WindowsApp;Integrated Security=SSPI;"; SqlConnection conn = new SqlConnection(ConnectionString); conn.Open(); SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable); SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Test ORDER BY Id", conn); SqlCommandBuilder builder = new SqlCommandBuilder(adapter); adapter.UpdateCommand.Transaction = trans; // Create a dataset object DataSet ds = new DataSet("TestSet"); adapter.Fill(ds, "Test"); // Create a data table object and add a new row DataTable TestTable = ds.Tables["Test"]; for (int i=1;i<=50;i++) { DataRow row = TestTable.NewRow(); row["Id"] = i; TestTable .Rows.Add(row); } // Update data adapter adapter.Update(ds, "Test"); trans.Commit(); conn.Close();

  Read the article

• What does "Select Distinct Null As xxxxx" mean?

  - by Soylent Green

  Background: I am mapping Sybase stored procedure return values to java objects using Spring. For example I map a Sybase datatype of varchar as a String type in Java, and a Sybase datatype of int as an int type in Java, etc. I have come across the following code in one of the stored procedures: SELECT DISTINCT A.Col1 AS val1, A.Col2 AS val2, NULL AS someVal, A.col3 AS val3, ... A.col9 AS val9 FROM #SomeTable A ORDER BY Col2, Col3 I have 2 related questions: What does Null mean in this scenario? I am confused as to what is happening here. I am able to determine the data type of Col1, Col2, etc. of course by looking at the table definition of Table A defined earlier in the stored procedure. Thus I know what datatype I can define in my Java object for val1, val2, etc.. But what about "someVal"? What datatype mapping am I supposed to perform for this Null value? I am fairly inexperienced in SQL. Perhaps the answer is much simpler than I realize.

  Read the article

• I need to convert the result of a stored procedure in a dbml file to IQueryable to view a list in an

  - by RJ

  I have a MVC project that has a Linq to SQL dbml class. It is a table called Clients that houses client information. I can easily get the information to display in a View using the code I followed in Nerd Dinner but I have added a stored procedure to the dbml and it's result set is of IQueryable, not IQueryable. I need to convert IQueryable to IQueryable so I can display it in the same View. The reason for the sproc is so I can pass a search string tothe sproc and return the same information as a full list but filtered on the search. I know I can use Linq to filter the whole list but I don't want the whole list so I am using the sproc. Here is the code in my ClientRepository with a comment where I need to convert. What code goes in the commented spot. public IQueryable<Client> SelectClientsBySearch(String search) { IQueryable<SelectClientsBySearchResult> spClientList = (from p in db.SelectClientsBySearch(search) select p).AsQueryable(); //what is the code to convert IQueryable<SelectClientsBySearchResult> to IQueryable<Client> return clientList; }

  Read the article

• Access: Expression too complex to be evaluated

  - by user2502964

  I'm trying to sort out values from a database by the weekending date. The script I'm using functions on 6 of my 7 databases (they are all constructed identically). The 7th database doesn't function. I get the expression too complex error. any help figuring out why?? Here is my code: SELECT UPC_Test.Type, UPC_Test.[Model No], UPC_Test.[Model Desc], UPC_Test.[Serial No], Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[Test Date],0)),"m/d/yyyy") AS [Test Date], UPC_Test.Parameter, UPC_Test.[Failure Symptom], UPC_Test.[Repair Action], UPC_Test.[Factory Select], UPC_Test.[Test Station] FROM UPC_Test GROUP BY UPC_Test.Type, UPC_Test.[Model No], UPC_Test.[Model Desc], UPC_Test.[Serial No], Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[Test Date],0)),"m/d/yyyy"), UPC_Test.Parameter, UPC_Test.[Failure Symptom], UPC_Test.[Repair Action], UPC_Test.[Factory Select], UPC_Test.[Test Station] HAVING (((UPC_Test.Type)="Production") AND ((Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[Test Date],0)),"m/d/yyyy"))=[Enter]) AND ((UPC_Test.[Failure Symptom])<>"") AND ((UPC_Test.[Repair Action])<>"") AND ((UPC_Test.[Test Station])="UPC RF Test")) ORDER BY Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[Test Date],0)),"m/d/yyyy");

  Read the article

• reporting tool/viewer for large datasets

  - by FrustratedWithFormsDesigner

  I have a data processing system that generates very large reports on the data it processes. By "large" I mean that a "small" execution of this system produces about 30 MB of reporting data when dumped into a CSV file and a large dataset is about 130-150 MB (I'm sure someone out there has a bigger idea of "large" but that's not the point... ;) Excel has the ideal interface for the report consumers in the form of its Data Lists: users can filter and segment the data on-the-fly to see the specific details that they are interested in - they can also add notes and markup to the reports, create charts, graphs, etc... They know how to do all this and it's much easier to let them do it if we just give them the data. Excel was great for the small test datasets, but it cannot handle these large ones. Does anyone know of a tool that can provide a similar interface as Excel data lists, but that can handle much larger files? The next tool I tried was MS Access, and found that the Access file bloats hugely (30 MB input file leads to about 70 MB Access file, and when I open the file, run a report and close it the file's at 120-150 MB!), the import process is slow and very manual (currently, the CSV files are created by the same plsql script that runs the main process so there's next to no intervention on my part). I also tried an Access database with linked tables to the database tables that store the report data and that was many times slower (for some reason, sqlplus could query and generate the report file in a minute or soe while Access would take anywhere from 2-5 minutes for the same data) (If it helps, the data processing system is written in PL/SQL and runs on Oracle 10g.)

  Read the article

• Faster Insertion of Records into a Table with SQLAlchemy

  - by Kyle Brandt

  I am parsing a log and inserting it into either MySQL or SQLite using SQLAlchemy and Python. Right now I open a connection to the DB, and as I loop over each line, I insert it after it is parsed (This is just one big table right now, not very experienced with SQL). I then close the connection when the loop is done. The summarized code is: log_table = schema.Table('log_table', metadata, schema.Column('id', types.Integer, primary_key=True), schema.Column('time', types.DateTime), schema.Column('ip', types.String(length=15)) .... engine = create_engine(...) metadata.bind = engine connection = engine.connect() .... for line in file_to_parse: m = line_regex.match(line) if m: fields = m.groupdict() pythonified = pythoninfy_log(fields) #Turn them into ints, datatimes, etc if use_sql: ins = log_table.insert(values=pythonified) connection.execute(ins) parsed += 1 My two questions are: Is there a way to speed up the inserts within this basic framework? Maybe have a Queue of inserts and some insertion threads, some sort of bulk inserts, etc? When I used MySQL, for about ~1.2 million records the insert time was 15 minutes. With SQLite, the insert time was a little over an hour. Does that time difference between the db engines seem about right, or does it mean I am doing something very wrong?

  Read the article

• DBD::CSV: Problem with userdefined functions

  - by sid_com

  From the SQL::Statement::Functions documentation: Creating User-Defined Functions ... More complex functions can make use of a number of arguments always passed to functions automatically. Functions always receive these values in @_: sub FOO { my( $self, $sth, $rowhash, @params ); } #!/usr/bin/env perl use 5.012; use warnings; use strict; use DBI; my $dbh = DBI->connect( "DBI:CSV:", undef, undef, { RaiseError => 1, } ); my $table = 'wages'; my $array_ref = [ [ 'id', 'number' ], [ 0, 6900 ], [ 1, 3200 ], [ 2, 1800 ], ]; $dbh->do( "CREATE TEMP TABLE $table AS import( ? )", {}, $array_ref ); sub routine { my $self = shift; my $sth = shift; my $rowhash = shift; # return $_[0] / 30; }; $dbh->do( "CREATE FUNCTION routine" ); my $sth = $dbh->prepare( "SELECT id, routine( number ) AS result FROM $table" ); $sth->execute(); $sth->dump_results(); When I try this I get an error-message: DBD::CSV::st execute failed: Use of uninitialized value $_[0] in division (/) at ./so.pl line 27. [for Statement "SELECT id, routine( number ) AS result FROM "wages""] at ./so.pl line 34. When I comment out the third argument I works as expected ( because it looks as if the third argument is missing ): #!/usr/bin/env perl ... sub routine { my $self = shift; my $sth = shift; #my $rowhash = shift; return $_[0] / 30; }; ... 0, 230 1, 106.667 2, 60 3 rows Is this a bug?

  Read the article

• How do I make software that preserves database integrity and correctness? Please help, confused.

  - by user287745

  i have made an application project in vs 08 c#, sql server from vs 08. the database has like 20 tables and many fields in each have made an interface for adding deleting editting and retrieving data according to predefined needs of the users. now i have to 1) make to project in to a software which i can deliver to professor. that is he can just double click the icon and the software simply starts. no vs 08 needed to start the debugging 2) the database will be on one powerful computer (dual core latest everything win xp) and the user will access it from another computer connected using LAN i am able to change the connection string to the shared database using vs 08/ debugger whenever the server changes but how am i supposed to do that when its a software? 3)there will by many clients am i supposed to give the same software to every one, so they all can connect to the database, how will the integrity and correctness of the database be maintained? i mean the db.mdf file will be in a folder which will be shared with read and write access. so its not necessary that only one user will write at a time. so is there any coding for this or? please help me out here i am stuck do not know what to do i have no practical experience, would appreciate all the help thank you

  Read the article

< Previous Page | 722 723 724 725 726 727 728 729 730 731 732 733  | Next Page >