Search Results

Search found 31328 results on 1254 pages for 'sql join'.

Page 520/1254 | < Previous Page | 516 517 518 519 520 521 522 523 524 525 526 527  | Next Page >

  • How do I force a restore over an existing database?

    - by Ian Boyd
    I have a database, and i want to force a restore over top of it. I check the option: Overwrite the existing database (WITH REPLACE) But, as expected, SSMS is unable to overwrite the existing database. Of course i don't want different filenames; i want to overwrite the existing database. How do i force a restore over an existing database? And for Google search crawler: File '%s' is claimed by '%s'(4) and '%s"(3). The WITH MOVE clause can be used to relocate one or more files. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3176) Update The script (before i deleted the database, because i needed to get it done) was: RESTORE DATABASE [HealthCareGovManager] FILE = N'HealthCareGovManager_Data', FILE = N'HealthCareGovManager_Archive', FILE = N'HealthCareGovManager_AuditLog' FROM DISK = N'D:\STAGING\HealthCareGovManager10232013.bak' WITH FILE = 1, MOVE N'HealthCareGovManager_Data' TO N'D:\CGI Data\HealthCareGovManager.MDF', MOVE N'HealthCareGovManager_Archive' TO N'D:\CGI Data\HealthCareGovManager.ndf', MOVE N'HealthCareGovManager_AuditLog' TO N'D:\CGI Data\HealthCareGovManager.ndf', MOVE N'HealthCareGovManager_Log' TO N'D:\CGI Data\HealthCareGovManager.LDF', NOUNLOAD, REPLACE, STATS = 10 I used the UI to delete the existing database, so that i could use the UI to force an overwrite of the (non)existing database. Hopefully there can be an answer so that the next guy can have an answer. No, nobody was in the context of the database (The error message from other connections is quite different from this error, and i only got to see this error after i killed the other connections).

    Read the article

  • Copy Database Wizard fails on creation of view into another not-yet-copied database

    - by user22037
    Update - I found that doing a manual detach/reattach using MSDN article "How to: Move a Database Using Detach and Attach (Transact-SQL)" got around this issue. I'll just be creating a script to dettach and reattach but do the file copies manually. Any info on how to overcome the problems with the wizard would be helpful in the future. I am in the process of moving around 20 databases from our current server to a new one. When performing the copies however I have found that some databases can not copy if they have views into other databases that have not yet been copied to the target system. The log file generated says "failed with the following error: "Invalid object name" in reference to the database in the view. If I first copy just the database referenced in the view and then in a separate step copy the database over containing the view it is successful. However some other database have views into each other so can't just adjust the order in which the copy occurs. Is there any way to ignore this error and just allow everything to copy?

    Read the article

  • Problems creating a functioning table

    - by Hoser
    This is a pretty simple SQL query I would assume, but I'm having problems getting it to work. if (object_id('#InfoTable')is not null) Begin Drop Table #InfoTable End create table #InfoTable (NameOfObject varchar(50), NameOfCounter varchar(50), SampledValue float(30), DayStamp datetime) insert into #InfoTable(NameOfObject, NameOfCounter, SampledValue, DayStamp) select vPerformanceRule.ObjectName AS NameOfObject, vPerformanceRule.CounterName AS NameOfCounter, Perf.vPerfRaw.SampleValue AS SampledValue, Perf.vPerfHourly.DateTime AS DayStamp from vPerformanceRule, vPerformanceRuleInstance, Perf.vPerfHourly, Perf.vPerfRaw where (ObjectName like 'Logical Disk' and CounterName like '% Free Space' AND SampleValue > 95 AND SampleValue < 100) order by DayStamp desc select NameOfObject, NameOfCounter, SampledValue, DayStamp from #InfoTable Drop Table #InfoTable I've tried various other forms of syntax, but no matter what I do, I get these error messages. Msg 207, Level 16, State 1, Line 10 Invalid column name 'NameOfObject'. Msg 207, Level 16, State 1, Line 10 Invalid column name 'NameOfCounter'. Msg 207, Level 16, State 1, Line 10 Invalid column name 'SampledValue'. Msg 207, Level 16, State 1, Line 10 Invalid column name 'DayStamp'. Msg 207, Level 16, State 1, Line 22 Invalid column name 'NameOfObject'. Msg 207, Level 16, State 1, Line 22 Invalid column name 'NameOfCounter'. Msg 207, Level 16, State 1, Line 22 Invalid column name 'SampledValue'. Msg 207, Level 16, State 1, Line 22 Invalid column name 'DayStamp'. Line 10 is the first 'insert into' line, and line 22 is the second select line. Any ideas?

    Read the article

  • Issues with "There is already an object named 'xxx' in the database'

    - by Hoser
    I'm fairly new to SQL so this may be an easy mistake, but I haven't been able to find a solid solution anywhere else. Problem is whenever I try to use my temp table, it tells me it cannot be used because there is already an object with that name. I frequently try switching up the names, and sometimes it'll let me work with the table for a little while, but it never lasts for long. Am I dropping the table incorrectly? Also, I've had people suggest to just use a permanent table, but this database does not allow me to do that. create table #RandomTableName(NameOfObject varchar(50), NameOfCounter varchar(50), SampledValue decimal) select vPerformanceRule.ObjectName, vPerformanceRule.CounterName, Perf.vPerfRaw.SampleValue into #RandomTableName from vPerformanceRule, vPerformanceRuleInstance, Perf.vPerfRaw where (ObjectName like 'Processor' AND CounterName like '% Processor Time') OR(ObjectName like 'System' AND CounterName like 'Processor Queue Length') OR(ObjectName like 'Memory' AND CounterName like 'Pages/Sec') OR(ObjectName like 'Physical Disk' AND CounterName like 'Avg. Disk Queue Length') OR(ObjectName like 'Physical Disk' AND CounterName like 'Avg. Disk sec/Read') OR(ObjectName like 'Physical Disk' and CounterName like '% Disk Time') OR(ObjectName like 'Logical Disk' and CounterName like '% Free Space' AND SampleValue > 70 AND SampleValue < 100) order by ObjectName, SampleValue drop table #RandomTableName

    Read the article

  • The instruction at “0x7c910a19” referenced memory at “oxffffffff”. The memory could not be “read”

    - by ClareBear
    Hello guys/girls I have a small issue, I receive the following error before the .vbs terminates. I don't know why this error is thrown. Below is the process of the .vbs file: Call ImportTransactions() Call UpdateTransactions() Function ImportTransactions() Dim objConnection, objCommand, objRecordset, strOracle Dim strSQL, objRecordsetInsert Set objConnection = CreateObject("ADODB.Connection") objConnection.Open "DSN=*****;UID=*****;PWD==*****;" Set objCommand = CreateObject("ADODB.Command") Set objRecordset = CreateObject("ADODB.Recordset") strOracle = "SELECT query here from Oracle database" objCommand.CommandText = strOracle objCommand.CommandType = 1 objCommand.CommandTimeout = 0 Set objCommand.ActiveConnection = objConnection objRecordset.cursorType = 0 objRecordset.cursorlocation = 3 objRecordset.Open objCommand, , 1, 3 If objRecordset.EOF = False Then Do Until objRecordset.EOF = True strSQL = "INSERT query here into SQL database" strSQL = Query(strSQL) Call RunSQL(strSQL, objRecordsetInsert, False, conTimeOut, conServer, conDatabase, conUsername, conPassword) objRecordset.MoveNext Loop End If objRecordset.Close() Set objRecordset = Nothing Set objRecordsetInsert = Nothing End Function Function UpdateTransactions() Dim strSQLUpdateVAT, strSQLUpdateCodes Dim objRecordsetVAT, objRecordsetUpdateCodes strSQLUpdateVAT = "UPDATE query here SET [value:costing output] = ([value:costing output] * -1)" Call RunSQL(strSQLUpdateVAT, objRecordsetVAT, False, conTimeOut, conServer, conDatabase, conUsername, conPassword) strSQLUpdateCodes = "UPDATE query here SET [value:costing output] = ([value:costing output] * -1) different WHERE clause" Call RunSQL(strSQLUpdateCodes, objRecordsetUpdateCodes, False, conTimeOut, conServer, conDatabase, conUsername, conPassword) Set objRecordsetVAT = Nothing Set objRecordsetUpdateCodes = Nothing End Function It does both the import and update and seems to throw this error after. If I comment out the ImportTransactions it doesnt throw a error, however I have produced similar code for another vbs file and this does not throw any errors Thanks in advance for any help, Clare

    Read the article

  • Discover intended Foreign Keys from JOINS in scripts

    - by Jason
    I'm inheriting a database that has 400 tables and only 150 foreign key constraints registered. Knowing what I do about the application and looking at the table columns, it's easy to say that there ought to be a lot more. I'm afraid that the current application software will break if I started adding the missing FKs because the developers have probably come to rely on this "freedom", but step one in fixing the problem is to come up with the list of missing FKs so we can evaluate them as a team. To make matters worse, the referencing columns don't share a naming convention. The relationships ARE coded informally into the hundreds of ad-hoc queries and stored procedures, so my hope is to parse these files programmatically looking for JOINS between actual tables (but not table variables, etc). Challenges I foresee in this approach are: newlines, optional aliases and table hints, alias resolution. Any better ideas? (Besides quitting) Are there any pre-built tools that can solve this? I don't think regex can handle this. Do you disagree? SQL Parsers? I tried using Microsoft.SqlServer.Management.SqlParser.Parser but all that is exposed is the lexer - can't get an AST out of it - all that stuff is internal.

    Read the article

  • Node.js Adventure - When Node Flying in Wind

    - by Shaun
    In the first post of this series I mentioned some popular modules in the community, such as underscore, async, etc.. I also listed a module named “Wind (zh-CN)”, which is created by one of my friend, Jeff Zhao (zh-CN). Now I would like to use a separated post to introduce this module since I feel it brings a new async programming style in not only Node.js but JavaScript world. If you know or heard about the new feature in C# 5.0 called “async and await”, or you learnt F#, you will find the “Wind” brings the similar async programming experience in JavaScript. By using “Wind”, we can write async code that looks like the sync code. The callbacks, async stats and exceptions will be handled by “Wind” automatically and transparently.   What’s the Problem: Dense “Callback” Phobia Let’s firstly back to my second post in this series. As I mentioned in that post, when we wanted to read some records from SQL Server we need to open the database connection, and then execute the query. In Node.js all IO operation are designed as async callback pattern which means when the operation was done, it will invoke a function which was taken from the last parameter. For example the database connection opening code would be like this. 1: sql.open(connectionString, function(error, conn) { 2: if(error) { 3: // some error handling code 4: } 5: else { 6: // connection opened successfully 7: } 8: }); And then if we need to query the database the code would be like this. It nested in the previous function. 1: sql.open(connectionString, function(error, conn) { 2: if(error) { 3: // some error handling code 4: } 5: else { 6: // connection opened successfully 7: conn.queryRaw(command, function(error, results) { 8: if(error) { 9: // failed to execute this command 10: } 11: else { 12: // records retrieved successfully 13: } 14: }; 15: } 16: }); Assuming if we need to copy some data from this database to another then we need to open another connection and execute the command within the function under the query function. 1: sql.open(connectionString, function(error, conn) { 2: if(error) { 3: // some error handling code 4: } 5: else { 6: // connection opened successfully 7: conn.queryRaw(command, function(error, results) { 8: if(error) { 9: // failed to execute this command 10: } 11: else { 12: // records retrieved successfully 13: target.open(targetConnectionString, function(error, t_conn) { 14: if(error) { 15: // connect failed 16: } 17: else { 18: t_conn.queryRaw(copy_command, function(error, results) { 19: if(error) { 20: // copy failed 21: } 22: else { 23: // and then, what do you want to do now... 24: } 25: }; 26: } 27: }; 28: } 29: }; 30: } 31: }); This is just an example. In the real project the logic would be more complicated. This means our application might be messed up and the business process will be fragged by many callback functions. I would like call this “Dense Callback Phobia”. This might be a challenge how to make code straightforward and easy to read, something like below. 1: try 2: { 3: // open source connection 4: var s_conn = sqlConnect(s_connectionString); 5: // retrieve data 6: var results = sqlExecuteCommand(s_conn, s_command); 7: 8: // open target connection 9: var t_conn = sqlConnect(t_connectionString); 10: // prepare the copy command 11: var t_command = getCopyCommand(results); 12: // execute the copy command 13: sqlExecuteCommand(s_conn, t_command); 14: } 15: catch (ex) 16: { 17: // error handling 18: }   What’s the Problem: Sync-styled Async Programming Similar as the previous problem, the callback-styled async programming model makes the upcoming operation as a part of the current operation, and mixed with the error handling code. So it’s very hard to understand what on earth this code will do. And since Node.js utilizes non-blocking IO mode, we cannot invoke those operations one by one, as they will be executed concurrently. For example, in this post when I tried to copy the records from Windows Azure SQL Database (a.k.a. WASD) to Windows Azure Table Storage, if I just insert the data into table storage one by one and then print the “Finished” message, I will see the message shown before the data had been copied. This is because all operations were executed at the same time. In order to make the copy operation and print operation executed synchronously I introduced a module named “async” and the code was changed as below. 1: async.forEach(results.rows, 2: function (row, callback) { 3: var resource = { 4: "PartitionKey": row[1], 5: "RowKey": row[0], 6: "Value": row[2] 7: }; 8: client.insertEntity(tableName, resource, function (error) { 9: if (error) { 10: callback(error); 11: } 12: else { 13: console.log("entity inserted."); 14: callback(null); 15: } 16: }); 17: }, 18: function (error) { 19: if (error) { 20: error["target"] = "insertEntity"; 21: res.send(500, error); 22: } 23: else { 24: console.log("all done."); 25: res.send(200, "Done!"); 26: } 27: }); It ensured that the “Finished” message will be printed when all table entities had been inserted. But it cannot promise that the records will be inserted in sequence. It might be another challenge to make the code looks like in sync-style? 1: try 2: { 3: forEach(row in rows) { 4: var entity = { /* ... */ }; 5: tableClient.insert(tableName, entity); 6: } 7:  8: console.log("Finished"); 9: } 10: catch (ex) { 11: console.log(ex); 12: }   How “Wind” Helps “Wind” is a JavaScript library which provides the control flow with plain JavaScript for asynchronous programming (and more) without additional pre-compiling steps. It’s available in NPM so that we can install it through “npm install wind”. Now let’s create a very simple Node.js application as the example. This application will take some website URLs from the command arguments and tried to retrieve the body length and print them in console. Then at the end print “Finish”. I’m going to use “request” module to make the HTTP call simple so I also need to install by the command “npm install request”. The code would be like this. 1: var request = require("request"); 2:  3: // get the urls from arguments, the first two arguments are `node.exe` and `fetch.js` 4: var args = process.argv.splice(2); 5:  6: // main function 7: var main = function() { 8: for(var i = 0; i < args.length; i++) { 9: // get the url 10: var url = args[i]; 11: // send the http request and try to get the response and body 12: request(url, function(error, response, body) { 13: if(!error && response.statusCode == 200) { 14: // log the url and the body length 15: console.log( 16: "%s: %d.", 17: response.request.uri.href, 18: body.length); 19: } 20: else { 21: // log error 22: console.log(error); 23: } 24: }); 25: } 26: 27: // finished 28: console.log("Finished"); 29: }; 30:  31: // execute the main function 32: main(); Let’s execute this application. (I made them in multi-lines for better reading.) 1: node fetch.js 2: "http://www.igt.com/us-en.aspx" 3: "http://www.igt.com/us-en/games.aspx" 4: "http://www.igt.com/us-en/cabinets.aspx" 5: "http://www.igt.com/us-en/systems.aspx" 6: "http://www.igt.com/us-en/interactive.aspx" 7: "http://www.igt.com/us-en/social-gaming.aspx" 8: "http://www.igt.com/support.aspx" Below is the output. As you can see the finish message was printed at the beginning, and the pages’ length retrieved in a different order than we specified. This is because in this code the request command, console logging command are executed asynchronously and concurrently. Now let’s introduce “Wind” to make them executed in order, which means it will request the websites one by one, and print the message at the end.   First of all we need to import the “Wind” package and make sure the there’s only one global variant named “Wind”, and ensure it’s “Wind” instead of “wind”. 1: var Wind = require("wind");   Next, we need to tell “Wind” which code will be executed asynchronously so that “Wind” can control the execution process. In this case the “request” operation executed asynchronously so we will create a “Task” by using a build-in helps function in “Wind” named Wind.Async.Task.create. 1: var requestBodyLengthAsync = function(url) { 2: return Wind.Async.Task.create(function(t) { 3: request(url, function(error, response, body) { 4: if(error || response.statusCode != 200) { 5: t.complete("failure", error); 6: } 7: else { 8: var data = 9: { 10: uri: response.request.uri.href, 11: length: body.length 12: }; 13: t.complete("success", data); 14: } 15: }); 16: }); 17: }; The code above created a “Task” from the original request calling code. In “Wind” a “Task” means an operation will be finished in some time in the future. A “Task” can be started by invoke its start() method, but no one knows when it actually will be finished. The Wind.Async.Task.create helped us to create a task. The only parameter is a function where we can put the actual operation in, and then notify the task object it’s finished successfully or failed by using the complete() method. In the code above I invoked the request method. If it retrieved the response successfully I set the status of this task as “success” with the URL and body length. If it failed I set this task as “failure” and pass the error out.   Next, we will change the main() function. In “Wind” if we want a function can be controlled by Wind we need to mark it as “async”. This should be done by using the code below. 1: var main = eval(Wind.compile("async", function() { 2: })); When the application is running, Wind will detect “eval(Wind.compile(“async”, function” and generate an anonymous code from the body of this original function. Then the application will run the anonymous code instead of the original one. In our example the main function will be like this. 1: var main = eval(Wind.compile("async", function() { 2: for(var i = 0; i < args.length; i++) { 3: try 4: { 5: var result = $await(requestBodyLengthAsync(args[i])); 6: console.log( 7: "%s: %d.", 8: result.uri, 9: result.length); 10: } 11: catch (ex) { 12: console.log(ex); 13: } 14: } 15: 16: console.log("Finished"); 17: })); As you can see, when I tried to request the URL I use a new command named “$await”. It tells Wind, the operation next to $await will be executed asynchronously, and the main thread should be paused until it finished (or failed). So in this case, my application will be pause when the first response was received, and then print its body length, then try the next one. At the end, print the finish message.   Finally, execute the main function. The full code would be like this. 1: var request = require("request"); 2: var Wind = require("wind"); 3:  4: var args = process.argv.splice(2); 5:  6: var requestBodyLengthAsync = function(url) { 7: return Wind.Async.Task.create(function(t) { 8: request(url, function(error, response, body) { 9: if(error || response.statusCode != 200) { 10: t.complete("failure", error); 11: } 12: else { 13: var data = 14: { 15: uri: response.request.uri.href, 16: length: body.length 17: }; 18: t.complete("success", data); 19: } 20: }); 21: }); 22: }; 23:  24: var main = eval(Wind.compile("async", function() { 25: for(var i = 0; i < args.length; i++) { 26: try 27: { 28: var result = $await(requestBodyLengthAsync(args[i])); 29: console.log( 30: "%s: %d.", 31: result.uri, 32: result.length); 33: } 34: catch (ex) { 35: console.log(ex); 36: } 37: } 38: 39: console.log("Finished"); 40: })); 41:  42: main().start();   Run our new application. At the beginning we will see the compiled and generated code by Wind. Then we can see the pages were requested one by one, and at the end the finish message was printed. Below is the code Wind generated for us. As you can see the original code, the output code were shown. 1: // Original: 2: function () { 3: for(var i = 0; i < args.length; i++) { 4: try 5: { 6: var result = $await(requestBodyLengthAsync(args[i])); 7: console.log( 8: "%s: %d.", 9: result.uri, 10: result.length); 11: } 12: catch (ex) { 13: console.log(ex); 14: } 15: } 16: 17: console.log("Finished"); 18: } 19:  20: // Compiled: 21: /* async << function () { */ (function () { 22: var _builder_$0 = Wind.builders["async"]; 23: return _builder_$0.Start(this, 24: _builder_$0.Combine( 25: _builder_$0.Delay(function () { 26: /* var i = 0; */ var i = 0; 27: /* for ( */ return _builder_$0.For(function () { 28: /* ; i < args.length */ return i < args.length; 29: }, function () { 30: /* ; i ++) { */ i ++; 31: }, 32: /* try { */ _builder_$0.Try( 33: _builder_$0.Delay(function () { 34: /* var result = $await(requestBodyLengthAsync(args[i])); */ return _builder_$0.Bind(requestBodyLengthAsync(args[i]), function (result) { 35: /* console.log("%s: %d.", result.uri, result.length); */ console.log("%s: %d.", result.uri, result.length); 36: return _builder_$0.Normal(); 37: }); 38: }), 39: /* } catch (ex) { */ function (ex) { 40: /* console.log(ex); */ console.log(ex); 41: return _builder_$0.Normal(); 42: /* } */ }, 43: null 44: ) 45: /* } */ ); 46: }), 47: _builder_$0.Delay(function () { 48: /* console.log("Finished"); */ console.log("Finished"); 49: return _builder_$0.Normal(); 50: }) 51: ) 52: ); 53: /* } */ })   How Wind Works Someone may raise a big concern when you find I utilized “eval” in my code. Someone may assume that Wind utilizes “eval” to execute some code dynamically while “eval” is very low performance. But I would say, Wind does NOT use “eval” to run the code. It only use “eval” as a flag to know which code should be compiled at runtime. When the code was firstly been executed, Wind will check and find “eval(Wind.compile(“async”, function”. So that it knows this function should be compiled. Then it utilized parse-js to analyze the inner JavaScript and generated the anonymous code in memory. Then it rewrite the original code so that when the application was running it will use the anonymous one instead of the original one. Since the code generation was done at the beginning of the application was started, in the future no matter how long our application runs and how many times the async function was invoked, it will use the generated code, no need to generate again. So there’s no significant performance hurt when using Wind.   Wind in My Previous Demo Let’s adopt Wind into one of my previous demonstration and to see how it helps us to make our code simple, straightforward and easy to read and understand. In this post when I implemented the functionality that copied the records from my WASD to table storage, the logic would be like this. 1, Open database connection. 2, Execute a query to select all records from the table. 3, Recreate the table in Windows Azure table storage. 4, Create entities from each of the records retrieved previously, and then insert them into table storage. 5, Finally, show message as the HTTP response. But as the image below, since there are so many callbacks and async operations, it’s very hard to understand my logic from the code. Now let’s use Wind to rewrite our code. First of all, of course, we need the Wind package. Then we need to include the package files into project and mark them as “Copy always”. Add the Wind package into the source code. Pay attention to the variant name, you must use “Wind” instead of “wind”. 1: var express = require("express"); 2: var async = require("async"); 3: var sql = require("node-sqlserver"); 4: var azure = require("azure"); 5: var Wind = require("wind"); Now we need to create some async functions by using Wind. All async functions should be wrapped so that it can be controlled by Wind which are open database, retrieve records, recreate table (delete and create) and insert entity in table. Below are these new functions. All of them are created by using Wind.Async.Task.create. 1: sql.openAsync = function (connectionString) { 2: return Wind.Async.Task.create(function (t) { 3: sql.open(connectionString, function (error, conn) { 4: if (error) { 5: t.complete("failure", error); 6: } 7: else { 8: t.complete("success", conn); 9: } 10: }); 11: }); 12: }; 13:  14: sql.queryAsync = function (conn, query) { 15: return Wind.Async.Task.create(function (t) { 16: conn.queryRaw(query, function (error, results) { 17: if (error) { 18: t.complete("failure", error); 19: } 20: else { 21: t.complete("success", results); 22: } 23: }); 24: }); 25: }; 26:  27: azure.recreateTableAsync = function (tableName) { 28: return Wind.Async.Task.create(function (t) { 29: client.deleteTable(tableName, function (error, successful, response) { 30: console.log("delete table finished"); 31: client.createTableIfNotExists(tableName, function (error, successful, response) { 32: console.log("create table finished"); 33: if (error) { 34: t.complete("failure", error); 35: } 36: else { 37: t.complete("success", null); 38: } 39: }); 40: }); 41: }); 42: }; 43:  44: azure.insertEntityAsync = function (tableName, entity) { 45: return Wind.Async.Task.create(function (t) { 46: client.insertEntity(tableName, entity, function (error, entity, response) { 47: if (error) { 48: t.complete("failure", error); 49: } 50: else { 51: t.complete("success", null); 52: } 53: }); 54: }); 55: }; Then in order to use these functions we will create a new function which contains all steps for data copying. 1: var copyRecords = eval(Wind.compile("async", function (req, res) { 2: try { 3: } 4: catch (ex) { 5: console.log(ex); 6: res.send(500, "Internal error."); 7: } 8: })); Let’s execute steps one by one with the “$await” keyword introduced by Wind so that it will be invoked in sequence. First is to open the database connection. 1: var copyRecords = eval(Wind.compile("async", function (req, res) { 2: try { 3: // connect to the windows azure sql database 4: var conn = $await(sql.openAsync(connectionString)); 5: console.log("connection opened"); 6: } 7: catch (ex) { 8: console.log(ex); 9: res.send(500, "Internal error."); 10: } 11: })); Then retrieve all records from the database connection. 1: var copyRecords = eval(Wind.compile("async", function (req, res) { 2: try { 3: // connect to the windows azure sql database 4: var conn = $await(sql.openAsync(connectionString)); 5: console.log("connection opened"); 6: // retrieve all records from database 7: var results = $await(sql.queryAsync(conn, "SELECT * FROM [Resource]")); 8: console.log("records selected. count = %d", results.rows.length); 9: } 10: catch (ex) { 11: console.log(ex); 12: res.send(500, "Internal error."); 13: } 14: })); After recreated the table, we need to create the entities and insert them into table storage. 1: var copyRecords = eval(Wind.compile("async", function (req, res) { 2: try { 3: // connect to the windows azure sql database 4: var conn = $await(sql.openAsync(connectionString)); 5: console.log("connection opened"); 6: // retrieve all records from database 7: var results = $await(sql.queryAsync(conn, "SELECT * FROM [Resource]")); 8: console.log("records selected. count = %d", results.rows.length); 9: if (results.rows.length > 0) { 10: // recreate the table 11: $await(azure.recreateTableAsync(tableName)); 12: console.log("table created"); 13: // insert records in table storage one by one 14: for (var i = 0; i < results.rows.length; i++) { 15: var entity = { 16: "PartitionKey": results.rows[i][1], 17: "RowKey": results.rows[i][0], 18: "Value": results.rows[i][2] 19: }; 20: $await(azure.insertEntityAsync(tableName, entity)); 21: console.log("entity inserted"); 22: } 23: } 24: } 25: catch (ex) { 26: console.log(ex); 27: res.send(500, "Internal error."); 28: } 29: })); Finally, send response back to the browser. 1: var copyRecords = eval(Wind.compile("async", function (req, res) { 2: try { 3: // connect to the windows azure sql database 4: var conn = $await(sql.openAsync(connectionString)); 5: console.log("connection opened"); 6: // retrieve all records from database 7: var results = $await(sql.queryAsync(conn, "SELECT * FROM [Resource]")); 8: console.log("records selected. count = %d", results.rows.length); 9: if (results.rows.length > 0) { 10: // recreate the table 11: $await(azure.recreateTableAsync(tableName)); 12: console.log("table created"); 13: // insert records in table storage one by one 14: for (var i = 0; i < results.rows.length; i++) { 15: var entity = { 16: "PartitionKey": results.rows[i][1], 17: "RowKey": results.rows[i][0], 18: "Value": results.rows[i][2] 19: }; 20: $await(azure.insertEntityAsync(tableName, entity)); 21: console.log("entity inserted"); 22: } 23: // send response 24: console.log("all done"); 25: res.send(200, "All done!"); 26: } 27: } 28: catch (ex) { 29: console.log(ex); 30: res.send(500, "Internal error."); 31: } 32: })); If we compared with the previous code we will find now it became more readable and much easy to understand. It’s very easy to know what this function does even though without any comments. When user go to URL “/was/copyRecords” we will execute the function above. The code would be like this. 1: app.get("/was/copyRecords", function (req, res) { 2: copyRecords(req, res).start(); 3: }); And below is the logs printed in local compute emulator console. As we can see the functions executed one by one and then finally the response back to me browser.   Scaffold Functions in Wind Wind provides not only the async flow control and compile functions, but many scaffold methods as well. We can build our async code more easily by using them. I’m going to introduce some basic scaffold functions here. In the code above I created some functions which wrapped from the original async function such as open database, create table, etc.. All of them are very similar, created a task by using Wind.Async.Task.create, return error or result object through Task.complete function. In fact, Wind provides some functions for us to create task object from the original async functions. If the original async function only has a callback parameter, we can use Wind.Async.Binding.fromCallback method to get the task object directly. For example the code below returned the task object which wrapped the file exist check function. 1: var Wind = require("wind"); 2: var fs = require("fs"); 3:  4: fs.existsAsync = Wind.Async.Binding.fromCallback(fs.exists); In Node.js a very popular async function pattern is that, the first parameter in the callback function represent the error object, and the other parameters is the return values. In this case we can use another build-in function in Wind named Wind.Async.Binding.fromStandard. For example, the open database function can be created from the code below. 1: sql.openAsync = Wind.Async.Binding.fromStandard(sql.open); 2:  3: /* 4: sql.openAsync = function (connectionString) { 5: return Wind.Async.Task.create(function (t) { 6: sql.open(connectionString, function (error, conn) { 7: if (error) { 8: t.complete("failure", error); 9: } 10: else { 11: t.complete("success", conn); 12: } 13: }); 14: }); 15: }; 16: */ When I was testing the scaffold functions under Wind.Async.Binding I found for some functions, such as the Azure SDK insert entity function, cannot be processed correctly. So I personally suggest writing the wrapped method manually.   Another scaffold method in Wind is the parallel tasks coordination. In this example, the steps of open database, retrieve records and recreated table should be invoked one by one, but it can be executed in parallel when copying data from database to table storage. In Wind there’s a scaffold function named Task.whenAll which can be used here. Task.whenAll accepts a list of tasks and creates a new task. It will be returned only when all tasks had been completed, or any errors occurred. For example in the code below I used the Task.whenAll to make all copy operation executed at the same time. 1: var copyRecordsInParallel = eval(Wind.compile("async", function (req, res) { 2: try { 3: // connect to the windows azure sql database 4: var conn = $await(sql.openAsync(connectionString)); 5: console.log("connection opened"); 6: // retrieve all records from database 7: var results = $await(sql.queryAsync(conn, "SELECT * FROM [Resource]")); 8: console.log("records selected. count = %d", results.rows.length); 9: if (results.rows.length > 0) { 10: // recreate the table 11: $await(azure.recreateTableAsync(tableName)); 12: console.log("table created"); 13: // insert records in table storage in parallal 14: var tasks = new Array(results.rows.length); 15: for (var i = 0; i < results.rows.length; i++) { 16: var entity = { 17: "PartitionKey": results.rows[i][1], 18: "RowKey": results.rows[i][0], 19: "Value": results.rows[i][2] 20: }; 21: tasks[i] = azure.insertEntityAsync(tableName, entity); 22: } 23: $await(Wind.Async.Task.whenAll(tasks)); 24: // send response 25: console.log("all done"); 26: res.send(200, "All done!"); 27: } 28: } 29: catch (ex) { 30: console.log(ex); 31: res.send(500, "Internal error."); 32: } 33: })); 34:  35: app.get("/was/copyRecordsInParallel", function (req, res) { 36: copyRecordsInParallel(req, res).start(); 37: });   Besides the task creation and coordination, Wind supports the cancellation solution so that we can send the cancellation signal to the tasks. It also includes exception solution which means any exceptions will be reported to the caller function.   Summary In this post I introduced a Node.js module named Wind, which created by my friend Jeff Zhao. As you can see, different from other async library and framework, adopted the idea from F# and C#, Wind utilizes runtime code generation technology to make it more easily to write async, callback-based functions in a sync-style way. By using Wind there will be almost no callback, and the code will be very easy to understand. Currently Wind is still under developed and improved. There might be some problems but the author, Jeff, should be very happy and enthusiastic to learn your problems, feedback, suggestion and comments. You can contact Jeff by - Email: [email protected] - Group: https://groups.google.com/d/forum/windjs - GitHub: https://github.com/JeffreyZhao/wind/issues   Source code can be download here.   Hope this helps, Shaun All documents and related graphics, codes are provided "AS IS" without warranty of any kind. Copyright © Shaun Ziyan Xu. This work is licensed under the Creative Commons License.

    Read the article

  • How to join a Windows Domain and Map Network Drives?

    - by AgainstClint
    I just installed the current build for Ubuntu on a partition for my work computer. I am a novice when it comes to Linux/Ubuntu, which is why I installed it along side windows. I want to learn how to operate and use Ubuntu much more than I do now, so I figured installing it and trying to do day to day functions here would be a "Thrown into the pool with sharks" way to do it, and I like that way. I did however have a few questions: We are on a Domain in Windows, is there any way to join that domain using the Ubuntu partition? We Also have 16 mapped network drives. I don't actually need ALL of them mapped for Ubuntu, but is there a way to Map at least one of them to see/use here in Ubuntu. Outlook Corporate email, how can I sign in/use it while...well, you get the idea. As I said earlier, I am VERY new to Ubuntu, i've only played around with it a bit at home and never at the office. If you could simplify it down for me a bit, that would be great.

    Read the article

  • I am trying to link my php form and my sql but having difficulties

    - by user1912599
    I am not sure what I am doing wrong as far as my php goes but I can't get my form to link with my sql. Here are the codes for my form and php code for my link to sql <?php echo displayform(); function displayForm() { $r = ''; //build it $r .='<form action="database.php" method="post">'; //table $r .=displayNiceFormBegin(); $r .=displayRow('FirstName:', '<input type="text" name="fname" id="fname"/>'); $r .=displayRow('LastName:', '<input type="text" name="lname" id="lname"/>'); $r .=displayRow('Address:', '<input type="text" name="address" id ="address"/>'); $r .=displayRow('Phone:', '<input type="text" name="phone" id ="phone"/>'); $r .=displayRow('Deparment:', '<input type="text" name="department"id="department"/>'); $r .=displayRow('', '<input type="submit" value="Submit Registration" />'); $r .=displayNiceFormEnd(); $r .='</form>'; return $r; } function displayRow($left, $right) { $r .= ''; //build it $r .='<tr>'; $r .= '<td>' . $left . '</td>'; $r .= '<td>' . $right . '</td>'; $r .='</tr>'; return $r; } function displayNiceFormBegin(){ $r .=''; //build it $r .= '<table style="background-color: beige; border: 1px dashed #999"><tr><td>'; $r .='<table style="margin:10px">'; return $r; } function displayNiceFormENd() { $r .=''; //build it $r .='</table>'; $r .='</td></tr><table>'; return $r; } ?> <?php $host="localhost"; // Host name $username="695788_ogems"; // Mysql username $password="opd69715"; // Mysql password $db_name="ottawaglandorfems_zzl_ogems"; // Database name $tbl_name=".*"; // Table name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // Get values from form $fname=$_POST['fname']; $lname=$_POST['lname']; $address=$_POST['address']; $phone=$_POST['phone']; $department=$_POST['deparment']; // Insert data into mysql $sql="INSERT INTO $tbl_name(FirstName,LastName,Address,Phone,Department)VALUES('$fname', '$lname', '$address','$phone','$deparment')"; $result=mysql_query($sql); // if successfully insert data into database, displays message "Successful". if($result){ echo "Successful"; echo "<BR>"; echo "<a href='ottawa-glandorfems.org/form3.php'>Back to main page</a>"; } else { echo "ERROR"; } ?> <?php // close connection mysql_close(); ?> I keep getting an error. Thank you!!!!

    Read the article

  • Problem running oracle script from command line using sqlplus

    - by Charlie
    I'm having a problem trying to run my sql script into oracle using sqlplus. The script just populates some dummy data: DECLARE role1Id NUMBER; user1Id NUMBER; role2Id NUMBER; user2Id NUMBER; role3Id NUMBER; user3Id NUMBER; perm1Id NUMBER; perm2Id NUMBER; perm3Id NUMBER; perm4Id NUMBER; perm5Id NUMBER; BEGIN INSERT INTO PB_USER(USER_ID,USER_NAME, USER_EMAIL, USER_ACTIVEYN) VALUES(PB_USER_ID_SEQ.nextval, 'RoleDataManagerTests_Username', '[email protected]',' '); INSERT INTO ROLES(ROLE_ID, ROLE_NAME) VALUES(PB_ROLE_ID_SEQ.nextval, 'Test role 1'); INSERT INTO ROLES(ROLE_ID, ROLE_NAME) VALUES(PB_ROLE_ID_SEQ.nextval, 'Test role 2'); INSERT INTO ROLES(ROLE_ID, ROLE_NAME) VALUES(PB_ROLE_ID_SEQ.nextval, 'Test role 3'); SELECT ROLE_ID INTO role1Id FROM ROLES WHERE ROLE_NAME = 'Test role 1'; SELECT USER_ID INTO user1Id FROM PB_USER WHERE USER_NAME = 'RoleDataManagerTests_Username'; INSERT INTO USERS_ROLES(USER_ID, ROLE_ID) VALUES(user1Id, role1Id); SELECT ROLE_ID INTO role2Id FROM ROLES WHERE ROLE_NAME = 'Test role 2'; SELECT USER_ID INTO user2Id FROM PB_USER WHERE USER_NAME = 'RoleDataManagerTests_Username'; INSERT INTO USERS_ROLES(USER_ID, ROLE_ID) VALUES(user2Id, role2Id); SELECT ROLE_ID INTO role3Id FROM ROLES WHERE ROLE_NAME = 'Test role 3'; SELECT USER_ID INTO user3Id FROM PB_USER WHERE USER_NAME = 'RoleDataManagerTests_Username'; INSERT INTO USERS_ROLES(USER_ID, ROLE_ID) VALUES(user3Id, role3Id); INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION) VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm1', 'permission 1'); INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION) VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm2', 'permission 2'); INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION) VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm3', 'permission 3'); INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION) VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm4', 'permission 4'); INSERT INTO PERMISSIONS(PERMISSION_ID, KEY, DESCRIPTION) VALUES (PB_PERMISSION_ID_SEQ.nextval, 'perm5', 'permission 5'); SELECT PERMISSION_ID INTO perm1Id FROM PERMISSIONS WHERE KEY = 'perm1'; SELECT PERMISSION_ID INTO perm2Id FROM PERMISSIONS WHERE KEY = 'perm2'; SELECT PERMISSION_ID INTO perm3Id FROM PERMISSIONS WHERE KEY = 'perm3'; SELECT PERMISSION_ID INTO perm4Id FROM PERMISSIONS WHERE KEY = 'perm4'; SELECT PERMISSION_ID INTO perm5Id FROM PERMISSIONS WHERE KEY = 'perm5'; INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID) VALUES(role1Id, perm1Id); INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID) VALUES(role1Id, perm2Id); INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID) VALUES(role1Id, perm3Id); INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID) VALUES(role2Id, perm3Id); INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID) VALUES(role3Id, perm4Id); INSERT INTO ROLES_PERMISSIONS(ROLE_ID, PERMISSION_ID) VALUES(role3Id, perm5Id); END; / My script works fine when I run it using Oracle SQL Developer but when I use the sqlplus command line tool this is what's outputted and then it just hangs: SQL*Plus: Release 11.1.0.7.0 - Production on Tue May 11 09:49:34 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine and Real Application Testing options I'm running the tool using this command line, which works fine for other scripts: sqlplus username/password@server/dbname @Setup.sql Any ideas? Thanks.

    Read the article

  • About global.asax and the events there

    - by eski
    So what i'm trying to understand is the whole global.asax events. I doing a simple counter that records website visits. I am using MSSQL. Basicly i have two ints. totalNumberOfUsers - The total visist from begining. currentNumberOfUsers - Total of users viewing the site at the moment. So the way i understand global.asax events is that every time someone comes to the site "Session_Start" is fired once. So once per user. "Application_Start" is fired only once the first time someone comes to the site. Going with this i have my global.asax file here. <script runat="server"> string connectionstring = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString; void Application_Start(object sender, EventArgs e) { // Code that runs on application startup Application.Lock(); Application["currentNumberOfUsers"] = 0; Application.UnLock(); string sql = "Select c_hit from v_counter where (id=1)"; SqlConnection connect = new SqlConnection(connectionstring); SqlCommand cmd = new SqlCommand(sql, connect); cmd.Connection.Open(); cmd.ExecuteNonQuery(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Application.Lock(); Application["totalNumberOfUsers"] = reader.GetInt32(0); Application.UnLock(); } reader.Close(); cmd.Connection.Close(); } void Application_End(object sender, EventArgs e) { // Code that runs on application shutdown } void Application_Error(object sender, EventArgs e) { // Code that runs when an unhandled error occurs } void Session_Start(object sender, EventArgs e) { // Code that runs when a new session is started Application.Lock(); Application["totalNumberOfUsers"] = (int)Application["totalNumberOfUsers"] + 1; Application["currentNumberOfUsers"] = (int)Application["currentNumberOfUsers"] + 1; Application.UnLock(); string sql = "UPDATE v_counter SET c_hit = @hit WHERE c_type = 'totalNumberOfUsers'"; SqlConnection connect = new SqlConnection(connectionstring); SqlCommand cmd = new SqlCommand(sql, connect); SqlParameter hit = new SqlParameter("@hit", SqlDbType.Int); hit.Value = Application["totalNumberOfUsers"]; cmd.Parameters.Add(hit); cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } void Session_End(object sender, EventArgs e) { // Code that runs when a session ends. // Note: The Session_End event is raised only when the sessionstate mode // is set to InProc in the Web.config file. If session mode is set to StateServer // or SQLServer, the event is not raised. Application.Lock(); Application["currentNumberOfUsers"] = (int)Application["currentNumberOfUsers"] - 1; Application.UnLock(); } </script> In the page_load i have this protected void Page_Load(object sender, EventArgs e) { l_current.Text = Application["currentNumberOfUsers"].ToString(); l_total.Text = Application["totalNumberOfUsers"].ToString(); } So if i understand this right, every time someone comes to the site both the currentNumberOfUsers and totalNumberOfUsers are incremented with 1. But when the session is over the currentNumberOfUsers is decremented with 1. If i go to the site with 3 types of browsers with the same computer i should have 3 in hits on both counters. Doing this again after hours i should have 3 in current and 6 in total, right ? The way its working right now is the current goes up to 2 and the total is incremented on every postback on IE and Chrome but not on firefox. And one last thing, is this the same thing ? Application["value"] = 0; value = Application["value"] //OR Application.Set("Value", 0); Value = Application.Get("Value");

    Read the article

  • Unable to edit a database row from JSF

    - by user1924104
    Hi guys i have a data table in JSF which displays all of the contents of my database table, it displays it fine, i also have a delete function that can successfully delete from the database fine and updates the data table fine however when i try to update the database i get the error java.lang.IllegalArgumentException: Cannot convert richard.test.User@129d62a7 of type class richard.test.User to long below is the code that i have been using to delete the rows in the database that is working fine : public void delete(long userID) { PreparedStatement ps = null; Connection con = null; if (userID != 0) { try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); String sql = "DELETE FROM user1 WHERE userId=" + userID; ps = con.prepareStatement(sql); int i = ps.executeUpdate(); if (i > 0) { System.out.println("Row deleted successfully"); } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); ps.close(); } catch (Exception e) { e.printStackTrace(); } } } } i simply wanted to edit the above code so it would update the records instead of deleting them so i edited it to look like : public void editData(long userID) { PreparedStatement ps = null; Connection con = null; if (userID != 0) { try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); String sql = "UPDATE user1 set name = '"+name+"', email = '"+ email +"', address = '"+address+"' WHERE userId=" + userID; ps = con.prepareStatement(sql); int i = ps.executeUpdate(); if (i > 0) { System.out.println("Row updated successfully"); } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); ps.close(); } catch (Exception e) { e.printStackTrace(); } } } } and the xhmtl is : <p:dataTable id="dataTable" var="u" value="#{userBean.getUserList()}" paginator="true" rows="10" editable="true" paginatorTemplate="{CurrentPageReport} {FirstPageLink} {PreviousPageLink} {PageLinks} {NextPageLink} {LastPageLink} {RowsPerPageDropdown}" rowsPerPageTemplate="5,10,15"> <p:column> <f:facet name="header"> User ID </f:facet> #{u.userID} </p:column> <p:column> <f:facet name="header"> Name </f:facet> #{u.name} </p:column> <p:column> <f:facet name="header"> Email </f:facet> #{u.email} </p:column> <p:column> <f:facet name="header"> Address </f:facet> #{u.address} </p:column> <p:column> <f:facet name="header"> Created Date </f:facet> #{u.created_date} </p:column> <p:column> <f:facet name="header"> Delete </f:facet> <h:commandButton value="Delete" action="#{user.delete(u.userID)}" /> </p:column> <p:column> <f:facet name="header"> Delete </f:facet> <h:commandButton value="Edit" action="#{user.editData(u)}" /> </p:column> currently when you press the edit button it will only update it with the same values as i haven't yet managed to get the datatable to be editable with the database, i have seen a few examples with an array list where the data table gets its values from but never a database so if you have any advice on this too it would be great thanks

    Read the article

  • Hibernate limitations on using variables in queries

    - by sammichy
    I had asked the following question I have the following table structure for a table Player Table Player { Long playerID; Long points; Long rank; } Assuming that the playerID and the points have valid values, can I update the rank for all the players based on the number of points in a single query? If two people have the same number of points, they should tie for the rank. And received the answer from Daniel Vassalo (thank you). UPDATE player JOIN (SELECT p.playerID, IF(@lastPoint <> p.points, @curRank := @curRank + 1, @curRank) AS rank, IF(@lastPoint = p.points, @curRank := @curRank + 1, @curRank), @lastPoint := p.points FROM player p JOIN (SELECT @curRank := 0, @lastPoint := 0) r ORDER BY p.points DESC ) ranks ON (ranks.playerID = player.playerID) SET player.rank = ranks.rank; When I try to execute this as a native query in Hibernate, the following exception is thrown. java.lang.IllegalArgumentException: org.hibernate.QueryException: Space is not allowed after parameter prefix ':' Apparently this has been an open issue for the last couple of years, I want to know if the ranking query can be made to work either Without using any variables in the SQL query OR Using any workaround for Hibernate.

    Read the article

  • Complex query in mysql

    - by Satish
    I have two tables reports and holidays. reports: (username varchar(30),activity varchar(30),hours int(3),report_date date) holidays: (holiday_name varchar(30), holiday_date date) select * from reports gives +----------+-----------+---------+------------+ | username | activity | hours | date | +----------+-----------+---------+------------+ | prasoon | testing | 3 | 2009-01-01 | | prasoon | coding | 4 | 2009-01-03 | | gautam | coding | 1 | 2009-01-05 | | prasoon | coding | 4 | 2009-01-06 | | prasoon | coding | 4 | 2009-01-10 | | gautam | coding | 4 | 2009-01-10 | +----------+-----------+---------+------------+ select * from holidays gives +--------------+---------------+ | holiday_name | holiday_date | +--------------+---------------+ | Diwali | 2009-01-02 | | Holi | 2009-01-05 | +--------------+---------------+ When I used the following query SELECT dates.date AS date, CASE WHEN holiday_name IS NULL THEN COALESCE(reports.activity, 'Absent') WHEN holiday_name IS NOT NULL and reports.activity IS NOT NULL THEN reports.activity ELSE '' END AS activity, CASE WHEN holiday_name IS NULL THEN COALESCE(reports.hours, 'Absent') WHEN holiday_name IS NOT NULL and reports.hours IS NOT NULL THEN reports.hours ELSE '' END AS hours, CASE WHEN holiday_name IS NULL THEN COALESCE(holidays.holiday_name, '') ELSE holidays.holiday_name END AS holiday_name FROM dates LEFT OUTER JOIN reports ON dates.date = reports.date LEFT OUTER JOIN holidays ON dates.date = holidays.holiday_date where reports.username='gautam' and dates.date>='2009-01-01' and dates.date<='2009-01-10'; I got the following output +----------+-----------+---------+------------+ | date | activity | hours | holiday | +----------+-----------+---------+------------+ |2009-01-05| coding | 1 | Holi | +----------+-----------+---------+------------+ |2009-01-10| coding | 4 | | +----------+-----------+---------+------------+ but I expected this +----------+-----------+---------+------------+ | date | activity | hours | holiday | +----------+-----------+---------+------------+ |2009-01-01| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-02| | | Diwali | +----------+-----------+---------+------------+ |2009-01-03| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-04| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-05| Coding | 1 | Holi | +----------+-----------+---------+------------+ |2009-01-06| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-07| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-08| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-09| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-10| Coding | 4 | | +----------+-----------+---------+------------+ How can I modify the above query to get the desired output(for a particular user (gautam in this case))?

    Read the article

  • Drupal db_query_range sql query

    - by hfidgen
    Hiya, I'm trying to get this working and the query executes but nothing comes back.. I've tried everything I can think of, can you spot what I'm doing wrong? Thanks in advance! $nido = $node->nid; $result = db_query_range(' SELECT i.nid, i.iid, a.fid, p.filename, p.filepath FROM {drup_image_attach} i LEFT JOIN {drup_image} a ON i.iid = a.nid LEFT JOIN {drup_files} p ON a.fid = p.fid WHERE i.nid = %d AND p.filename = "recipe_thumb"', $nido, 0, 10); echo "Filepath = " . $result ->filepath. "<br>"; echo "Filepath = " . $result ->filename . "<br>"; echo "IID = " . $result ->iid. "<br>"; echo "NID = " . $result ->nid . "<br>"; } EDIT - sorted out a couple of bits, but the output is still empty!

    Read the article

  • Alternative to sql NOT IN?

    - by Alex
    Hi, I am trying to make a materialized view in Oracle (I am a newbie, btw). For some reason, it doesn't like the presence of sub-query in it. I've been trying to use LEFT OUTER JOIN instead, but it's returning different data set now. Put simply, here's the code I'm trying to modify: SELECT * FROM table1 ros, table2 bal, table3 flx WHERE flx.name = 'XXX' AND flx.value = bal.value AND NVL (ros.ret, 'D') = Nvl (flx.attr16, 'D') AND ros.value = bal.segment3 AND ros.type IN ( 'AL', 'AS', 'PL' ) AND bal.period = 13 AND bal.code NOT IN (SELECT bal1.code FROM table2 bal1 WHERE bal1.value = flx.value AND bal1.segment3 = ros.value AND bal1.flag = bal.flag AND bal1.period = 12 AND bal1.year = bal.year) And here's one of my attempt: SELECT * FROM table1 ros, table2 bal, table3 flx LEFT OUTER JOIN table2 bal1 ON bal.code = bal1.code WHERE bal1.code is null AND bal1.segment3 = ros.value AND bal.segment3 = ros.value AND bal1.flag = bal.flag AND bal1.year = bal.year AND flx.name = 'XXX' AND flx.value = bal.value AND bal1.value = flx.value AND bal1.period_num = 12 AND NVL (ros.type, 'D') = NVL (flx.attr16, 'D') AND ros.value = bal.segment3 AND ros.type IN ( 'AL', 'AS', 'PL' ) AND bal.period = 13; This drives me nuts! Thanks in advance for the help :)

    Read the article

  • JPA2 Criteria API creates invalid SQL when using groupBy

    - by Stephan
    JPA2 with the Criteria API seems to generate invalid SQL for PostgreSQL. For this code: Root<DBObjectAccessCounter> from = query.from(DBObjectAccessCounter.class); Path<DBObject> object = from.get(DBObjectAccessCounter_.object); Expression<Long> sum = builder.sumAsLong(from.get(DBObjectAccessCounter_.count)); query.multiselect(object, sum).groupBy(object); I get the following exception: ERROR: column "dbobject1_.id" must appear in the GROUP BY clause or be used in an aggregate function The generated SQL is: select dbobjectac0_.object_id as col_0_0_, sum(dbobjectac0_.count) as col_1_0_, dbobject1_.id as id1001_, dbobject1_.name as name1013_, dbobject1_.lastChanged as lastChan2_1013_, dbobject1_.type_id as type3_1013_ from DBObjectAccessCounter dbobjectac0_ inner join DBObject dbobject1_ on dbobjectac0_.object_id=dbobject1_.id group by dbobjectac0_.object_id Obviously, the first item of the select statement (dbobjectac0_.object_id) does not match the group by clause. Simplified example It does not even work for this simple example: Root<DBObjectAccessCounter> from = query.from(DBObjectAccessCounter.class); Path<DBObject> object = from.get(DBObjectAccessCounter_.object); query.select(object).groupBy(object); which returns select dbobject1_.id as id924_, dbobject1_.name as name933_, dbobject1_.lastChanged as lastChan2_933_, dbobject1_.type_id as type3_933_ from DBObjectAccessCounter dbobjectac0_ inner join DBObject dbobject1_ on dbobjectac0_.object_id=dbobject1_.id group by dbobjectac0_.object_id Does anyone know how to fix this?

    Read the article

  • Counting computers for each lab

    - by Irvin
    Alright I have a problem with having to count PCs, and Macs from different labs. In each lab I need to display how many PC and Macs there is available. The data is coming from a SQL server, right am trying sub queries and the use of union, this the closest I can get to what I need. The query below shows me the number of PCs, and Macs in two different columns, but of course, the PCs will be in one row and the Macs on another right below it. Having the lab come up twice. EX: LabName -- PC / MAC Lab1 -- 5 / 0 Lab1 -- 0 / 2 Query SELECT Labs.LabName, COUNT(*),0 AS Mac FROM HardWare INNER JOIN Labs ON HardWare.LabID = Labs.LabID WHERE ComputerStatus = 'AVAILABLE' GROUP BY Labs.LabName UNION SELECT Labs.LabName, COUNT(*), (SELECT COUNT(Manufacturer)) AS Mac FROM HardWare INNER JOIN Labs ON HardWare.LabID = Labs.LabID WHERE ComputerStatus = 'AVAILABLE' AND Manufacturer = 'Apple' GROUP BY Labs.LabName ORDER BY Labs.LabName So is there any way to get them together in one row as in Lab1 -- 5 / 2 or is there a different way to write the query? anything will be a big help, am pretty much stuck here. Cheers

    Read the article

  • Linq is returning too many results when joined

    - by KallDrexx
    In my schema I have two database tables. relationships and relationship_memberships. I am attempting to retrieve all the entries from the relationship table that have a specific member in it, thus having to join it with the relationship_memberships table. I have the following method in my business object: public IList<DBMappings.relationships> GetRelationshipsByObjectId(int objId) { var results = from r in _context.Repository<DBMappings.relationships>() join m in _context.Repository<DBMappings.relationship_memberships>() on r.rel_id equals m.rel_id where m.obj_id == objId select r; return results.ToList<DBMappings.relationships>(); } _Context is my generic repository using code based on the code outlined here. The problem is I have 3 records in the relationships table, and 3 records in the memberships table, each membership tied to a different relationship. 2 membership records have an obj_id value of 2 and the other is 3. I am trying to retrieve a list of all relationships related to object #2. When this linq runs, _context.Repository<DBMappings.relationships>() returns the correct 3 records and _context.Repository<DBMappings.relationship_memberships>() returns 3 records. However, when the results.ToList() executes, the resulting list has 2 issues: 1) The resulting list contains 6 records, all of type DBMappings.relationships(). Upon further inspection there are 2 for each real relationship record, both are an exact copy of each other. 2) All relationships are returned, even if m.obj_id == 3, even though objId variable is correctly passed in as 2. Can anyone see what's going on because I've spent 2 days looking at this code and I am unable to understand what is wrong. I have joins in other linq queries that seem to be working great, and my unit tests show that they are still working, so I must be doing something wrong with this. It seems like I need an extra pair of eyes on this one :)

    Read the article

  • Select those objects whose related objects IDs are *all* in given string

    - by Jannis
    Hi Django people, I want to build a frontend to a recipe database which enables the user to search for a list of recipes which are cookable with the ingredients the user supplies. I have the following models class Ingredient(models.Model): name = models.CharField(max_length=100, unique=True) slug = models.SlugField(max_length=100, unique=True) importancy = models.PositiveSmallIntegerField(default=4) […] class Amount(models.Model): recipe = models.ForeignKey('Recipe') ingredient = models.ForeignKey(Ingredient) […] class Rezept(models.Model): name = models.CharField(max_length=100) slug = models.SlugField() instructions = models.TextField() ingredients = models.ManyToManyField(Ingredient, through=Amount) […] and a rawquery which does exactly what I want: It gets all the recipes whose required ingredients are all contained in the list of strings that the user supplies. If he supplies more than necessary, it's fine too. query = "SELECT *, COUNT(amount.zutat_id) AS selected_count_ingredients, (SELECT COUNT(*) FROM amount WHERE amount.recipe_id = amount.id) AS count_ingredients FROM amount LEFT OUTER JOIN amount ON (recipe.id = recipe.recipe_id) WHERE amount.ingredient_id IN (%s) GROUP BY amount.id HAVING count_ingredient=selected_count_ingredient" % ",".join([str(ingredient.id) for ingredient in ingredients]) rezepte = Rezept.objects.raw(query) Now, what I'm looking for is a way that does not rely on .raw() as I would like to do it purely with Django's queryset methods. Additionally, it would be awesome if you guys knew a way of including the ingredient's importancy in the lookup so that a recipe is still shown as a result even though one of its ingredients (that has an importancy of 0) is not supplied by the user.

    Read the article

  • Entity Framework Duplicate type name within an assembly (6.1.0)

    - by CodeMilian
    I am not sure what is going on but I keep getting the following exception when doing a query. "Duplicate type name within an assembly." I have not been able to find a solution on the web. I had resolved the issue by removing entity framework from all the projects in the solutions and re-installing using nugget. Then all of the sudden the exception is back. I have verified my table schema over and over and find nothing wrong with. This is the query causing the exception. var BaseQuery = from Users in db.Users join UserInstalls in db.UserTenantInstalls on Users.ID equals UserInstalls.UserID join Installs in db.TenantInstalls on UserInstalls.TenantInstallID equals Installs.ID where Users.Username == Username && Users.Password == Password && Installs.Name == Install select Users; var Query = BaseQuery.Include("UserTenantInstalls.TenantInstall"); return Query.FirstOrDefault(); As I mentioned previously the same query was working before. The data has not changed and the code has not changed.

    Read the article

  • A many-to-many relation joined disallows intellisense/lookup in joined table

    - by BerggreenDK
    I want to be able to select a product and retrieve all sub-parts(products) within it. My approach is to find the Product id and then retrieve the list of ProductParts with that as a parent and while fetching those, follow the key back to the Product child to get the name and details of each Part. I was hoping to use something similar to: part.linked_product_id.name I have two tables. One for [Product] and and a relation [ProductPart] that has two FK ID's to [Product] Table Product() { int ID; // (PRIMARY, NOT NULL) String Name; ... details removed for overview purpose... } Table ProductPart() { int Product_ID; // FK (linked with relation to Product/parent) int Part_Product_ID; // FK (linked with relation to Product/childen) ... details removed for overview purpose... } I have checked the SQL-diagram and it shows the two relations (both are one to many) and in my DBML they also looks right. Here is my LINQ to SQL snippet that doesnt work for me... wondering why my joins dont work as supposed. FaultySnippet() { ProductDataContext db = new ProductDataContext(); var list = ( from part in db.ProductParts join prod in db.Products on part.Part_Product_ID equals prod.ID where (part.Product_ID == product_ID) select new { Name = part.Part_Product_ID. ?? // <-- NO details from Joined table? ... rest of properties from ProductPart join... I hoped... } ); }

    Read the article

  • What's the most efficient query?

    - by Aaron Carlino
    I have a table named Projects that has the following relationships: has many Contributions has many Payments In my result set, I need the following aggregate values: Number of unique contributors (DonorID on the Contribution table) Total contributed (SUM of Amount on Contribution table) Total paid (SUM of PaymentAmount on Payment table) Because there are so many aggregate functions and multiple joins, it gets messy do use standard aggregate functions the the GROUP BY clause. I also need the ability to sort and filter these fields. So I've come up with two options: Using subqueries: SELECT Project.ID AS PROJECT_ID, (SELECT SUM(PaymentAmount) FROM Payment WHERE ProjectID = PROJECT_ID) AS TotalPaidBack, (SELECT COUNT(DISTINCT DonorID) FROM Contribution WHERE RecipientID = PROJECT_ID) AS ContributorCount, (SELECT SUM(Amount) FROM Contribution WHERE RecipientID = PROJECT_ID) AS TotalReceived FROM Project; Using a temporary table: DROP TABLE IF EXISTS Project_Temp; CREATE TEMPORARY TABLE Project_Temp (project_id INT NOT NULL, total_payments INT, total_donors INT, total_received INT, PRIMARY KEY(project_id)) ENGINE=MEMORY; INSERT INTO Project_Temp (project_id,total_payments) SELECT `Project`.ID, IFNULL(SUM(PaymentAmount),0) FROM `Project` LEFT JOIN `Payment` ON ProjectID = `Project`.ID GROUP BY 1; INSERT INTO Project_Temp (project_id,total_donors,total_received) SELECT `Project`.ID, IFNULL(COUNT(DISTINCT DonorID),0), IFNULL(SUM(Amount),0) FROM `Project` LEFT JOIN `Contribution` ON RecipientID = `Project`.ID GROUP BY 1 ON DUPLICATE KEY UPDATE total_donors = VALUES(total_donors), total_received = VALUES(total_received); SELECT * FROM Project_Temp; Tests for both are pretty comparable, in the 0.7 - 0.8 seconds range with 1,000 rows. But I'm really concerned about scalability, and I don't want to have to re-engineer everything as my tables grow. What's the best approach?

    Read the article

  • Why does this MySQL function return null?

    - by Shore
    Description: the query actually run have 4 results returned,as can be see from below, what I did is just concate the items then return, but unexpectedly,it's null. I think the code is self-explanatory: DELIMITER | DROP FUNCTION IF EXISTS get_idiscussion_ask| CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DETERMINISTIC BEGIN DECLARE done INT DEFAULT 0; DECLARE body varchar(600); DECLARE created DATETIME; DECLARE anonymous TINYINT(1); DECLARE screen_name varchar(64); DECLARE result TEXT; DECLARE cur1 CURSOR FOR SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=iask_id; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SET result = ''; OPEN cur1; REPEAT FETCH cur1 INTO body, created, anonymous, screen_name; SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>'); UNTIL done END REPEAT; CLOSE cur1; RETURN result; END | DELIMITER ; mysql> DELIMITER ; mysql> select get_idiscussion_ask(1); +------------------------+ | get_idiscussion_ask(1) | +------------------------+ | NULL | +------------------------+ 1 row in set (0.01 sec) mysql> SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=1; +------+---------------------+-----------+-------------+ | body | created | anonymous | screen_name | +------+---------------------+-----------+-------------+ | haha | 2009-05-27 04:57:51 | 0 | NULL | | haha | 2009-05-27 04:57:52 | 0 | NULL | | haha | 2009-05-27 04:57:52 | 0 | NULL | | haha | 2009-05-27 04:57:53 | 0 | NULL | +------+---------------------+-----------+-------------+ 4 rows in set (0.00 sec) For those who don't think the code is self-explanatory: Why the function returns NULL?

    Read the article

  • Conditionally Summing the same Column multiple times in a single select statement?

    - by btollett
    I have a single table that shows employee deployments, for various types of deployment, in a given location for each month: ID | Location_ID | Date | NumEmployees | DeploymentType_ID As an example, a few records might be: 1 | L1 | 12/2010 | 7 | 1 (=Permanent) 2 | L1 | 12/2010 | 2 | 2 (=Temp) 3 | L1 | 12/2010 | 1 | 3 (=Support) 4 | L1 | 01/2011 | 4 | 1 5 | L1 | 01/2011 | 2 | 2 6 | L1 | 01/2011 | 1 | 3 7 | L2 | 12/2010 | 6 | 1 8 | L2 | 01/2011 | 6 | 1 9 | L2 | 12/2010 | 3 | 2 What I need to do is sum the various types of people by date, such that the results look something like this: Date | Total Perm | Total Temp | Total Supp 12/2010 | 13 | 5 | 1 01/2011 | 10 | 2 | 1 Currently, I've created a separate query for each deployment type that looks like this: SELECT Date, SUM(NumEmployees) AS "Total Permanent" FROM tblDeployment WHERE DeploymentType_ID=1 GROUP BY Date; We'll call that query qSumPermDeployments. Then, I'm using a couple of joins to combine the queries: SELECT qSumPermDeployments.Date, qSumPermDeployments.["Total Permanent"] AS "Permanent" qSumTempDeployments.["Total Temp"] AS "Temp" qSumSupportDeployments.["Total Support"] AS Support FROM (qSumPermDeployments LEFT JOIN qSumTempDeployments ON qSumPermDeployments.Date = qSumTempDeployments.Date) LEFT JOIN qSumSupportDeployments ON qSumPermDeployments.Date = qSumSupportDeployments.Date; Note that I'm currently constructing that final query under the assumption that a location will only have temp or support employees if they also have permanent employees. Thus, I can create the joins using the permanent employee results as the base table. Given all of the data I currently have, that assumption holds up, but ideally I'd like to move away from that assumption. So finally, my question. Is there a way to simplify this down to a single query or is it best to separate it out into multiple queries - if for no other reason that readability.

    Read the article

< Previous Page | 516 517 518 519 520 521 522 523 524 525 526 527  | Next Page >