select insert - Page 139

How to insert image inline, in email composed with OWA?

- by Anon

When I am composing an email message in OWA 8.1.359.2, how do I insert an image in the body of the email, inline with text (not attachment)? Thanks

How to insert image inline, in email composed with OWA?

- by Anon

When I am composing an email message in OWA 8.1.359.2, how do I insert an image in the body of the email, inline with text (not attachment)? Thanks

How to use SQLAlchemy to dump an SQL file from query expressions to bulk-insert into a DBMS?

Please bear with me as I explain the problem, how I tried to solve it, and my question on how to improve it is at the end. I have a 100,000 line csv file from an offline batch job and I needed to insert it into the database as its proper models. Ordinarily, if this is a fairly straight-forward load, this can be trivially loaded by just munging the CSV file to fit a schema, but I had to do some external processing that requires querying and it's just much more convenient to use SQLAlchemy to generate the data I want. The data I want here is 3 models that represent 3 pre-exiting tables in the database and each subsequent model depends on the previous model. For example: Model C --> Foreign Key --> Model B --> Foreign Key --> Model A So, the models must be inserted in the order A, B, and C. I came up with a producer/consumer approach: - instantiate a multiprocessing.Process which contains a threadpool of 50 persister threads that have a threadlocal connection to a database - read a line from the file using the csv DictReader - enqueue the dictionary to the process, where each thread creates the appropriate models by querying the right values and each thread persists the models in the appropriate order This was faster than a non-threaded read/persist but it is way slower than bulk-loading a file into the database. The job finished persisting after about 45 minutes. For fun, I decided to write it in SQL statements, it took 5 minutes. Writing the SQL statements took me a couple of hours, though. So my question is, could I have used a faster method to insert rows using SQLAlchemy? As I understand it, SQLAlchemy is not designed for bulk insert operations, so this is less than ideal. This follows to my question, is there a way to generate the SQL statements using SQLAlchemy, throw them in a file, and then just use a bulk-load into the database? I know about str(model_object) but it does not show the interpolated values. I would appreciate any guidance for how to do this faster. Thanks!

Read the article

What is the best way to auto-generate INSERT statements for a SQL Server table?

- by JosephStyons

We are writing a new application, and while testing, we will need a bunch of dummy data. I've added that data by using MS Access to dump excel files into the relevant tables. Every so often, we want to "refresh" the relevant tables, which means dropping them all, re-creating them, and running a saved MS Access append query. The first part (dropping & re-creating) is an easy sql script, but the last part makes me cringe. I want a single setup script that has a bunch of INSERTs to regenerate the dummy data. I have the data in the tables now. What is the best way to automatically generate a big list of INSERT statements from that dataset? I'm thinking of something like in TOAD (for Oracle) where you can right-click on a grid and click Save As-Insert Statements, and it will just dump a big sql script wherever you want. The only way I can think of doing it is to save the table to an excel sheet and then write an excel formula to create an INSERT for every row, which is surely not the best way. I'm using the 2008 Management Studio to connect to a SQL Server 2005 database.

Read the article

Trying to insert a row using stored procedured with a parameter binded to an expression.

- by Arvind Singh

Environment: asp.net 3.5 (C# and VB) , Ms-sql server 2005 express Tables Table:tableUser ID (primary key) username Table:userSchedule ID (primary key) thecreator (foreign key = tableUser.ID) other fields I have created a procedure that accepts a parameter username and gets the userid and inserts a row in Table:userSchedule Problem: Using stored procedure with datalist control to only fetch data from the database by passing the current username using statement below works fine protected void SqlDataSourceGetUserID_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@CurrentUserName"].Value = Context.User.Identity.Name; } But while inserting using DetailsView it shows error Procedure or function OASNewSchedule has too many arguments specified. I did use protected void SqlDataSourceCreateNewSchedule_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@CreatedBy"].Value = Context.User.Identity.Name; } DetailsView properties: autogen fields: off, default mode: insert, it shows all the fields that may not be expected by the procedure like ID (primary key) not required in procedure and CreatedBy (user id ) field . So I tried removing the 2 fields from detailsview and shows error Cannot insert the value NULL into column 'CreatedBy', table 'D:\OAS\OAS\APP_DATA\ASPNETDB.MDF.dbo.OASTest'; column does not allow nulls. INSERT fails. The statement has been terminated. For some reason parameters value is not being set. Can anybody bother to understand this and help?

Read the article

how do I insert an hidden token in my form to prevent double posts? (PHP)

- by jpjp

I want to prevent users from accidentally posting a comment twice. I use the PRG (post redirect get) method, so that I insert the data on another page then redirect the user back to the page which shows the comment. This allows users to refresh as many times as they want. However this doesn't work when the user goes back and clicks submit again or when they click submit 100 times really fast. I don't want 100 of the same comments. I looked at related questions on SO and found that a token is best. But I am having trouble using it. //makerandomtoken(20) returns a random 20 length char. <form method="post" ... > <input type="text" id="comments" name="comments" class="commentbox" /><br/> <input type="hidden" name="_token" value="<?php echo $token=makerandomtoken(20); ?>" /> <input type="submit" value="submit" name="submit" /> </form> if (isset($_POST['submit']) && !empty($comments)) { $comments= mysqli_real_escape_string($dbc,trim($_POST['comments'])); //how do I make the if-statment to check if the token has been already set once? if ( ____________){ //don't insert comment because already clicked submit } else{ //insert the comment into the database } } So I have the token as a hidden value, but how do I use that to prevent multiple clicking of submit.

Read the article

How to insert and call by row and column into sqlite3 python, great tutorial problem.

- by user291071

Lets say i have a simple array of x rows and y columns with corresponding values, What is the best method to do 3 things? How to insert, update a value at a specific row column? How to select a value for each row and column, import sqlite3 con = sqlite3.connect('simple.db') c = con.cursor() c.execute('''create table simple (links text)''') con.commit() dic = {'x1':{'y1':1.0,'y2':0.0},'x2':{'y1':0.0,'y2':2.0,'y3':1.5},'x3':{'y2':2.0,'y3':1.5}} ucols = {} ## my current thoughts are collect all row values and all column values from dic and populate table row and columns accordingly how to call by row and column i havn't figured out yet ##populate rows in first column for row in dic: print row c.execute("""insert into simple ('links') values ('%s')"""%row) con.commit() ##unique columns for row in dic: print row for col in dic[row]: print col ucols[col]=dic[row][col] ##populate columns for col in ucols: print col c.execute("alter table simple add column '%s' 'float'" % col) con.commit() #functions needed ##insert values into sql by row x and column y?how to do this e.g. x1 and y2 should put in 0.0 ##I tried as follows didn't work for row in dic: for col in dic[row]: val =dic[row][col] c.execute("""update simple SET '%s' = '%f' WHERE 'links'='%s'"""%(col,val,row)) con.commit() ##update value at a specific row x and column y? ## select a value at a specific row x and column y?

Read the article

How do I execute a sql statement through a variable (dyname sql) that tries to do an insert into a variable table?

- by Testifier

If I do what I wanna do with a TEMPORARY TABLE, it works fine: DECLARE @CTRFR VARCHAR(MAX) SET @CTRFR = 'select blah blah blah' -- <-- very long select statement. this returns a 0 or some greater number. Please note! --> I NEED THIS NUMBER. IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo][#CTRFRResult]') AND type IN ( N'U' ) ) DROP TABLE [dbo].[#CTRFRResult] CREATE TABLE #CTRFRResult ( CTRFRResult VARCHAR(MAX) ) SET @CTRFR = 'insert into #CTRFRResult ' + @CTRFR EXEC(@CTRFR) The above works fine. The problem is that several databases are using the same TEMP table. Therefore I need to use a VARIABLE table (instead of a temporary table). What I have below is not working because it says that the table must be declared. DECLARE @CTRFRResult TABLE ( CTRFRResult VARCHAR(MAX) ) SET @CTRFR = 'insert into @CTRFRResult ' + @CTRFR -- I think the issue is here. EXEC(@CTRFR) Setting @CTRFR to 'insert into...' is not working because I'm assuming the table name is out of scope. How would I go about mimicking the temporary table code using a variable table?

Read the article

Date and Time Support in SQL Server 2008

- by Aamir Hasan

Using the New Date and Time Data Types Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} 1. The new date and time data types in SQL Server 2008 offer increased range and precision and are ANSI SQL compatible. 2. Separate date and time data types minimize storage space requirements for applications that need only date or time information. Moreover, the variable precision of the new time data type increases storage savings in exchange for reduced accuracy. 3. The new data types are mostly compatible with the original date and time data types and use the same Transact-SQL functions. 4. The datetimeoffset data type allows you to handle date and time information in global applications that use data that originates from different time zones. SELECT c.name, p.* FROM politics pJOIN country cON p.country = c.codeWHERE YEAR(Independence) < 1753ORDER BY IndependenceGO8. Highlight the SELECT statement and click Execute ( ) to show the use of some of the date functions.T-SQLSELECT c.name AS [Country Name], CONVERT(VARCHAR(12), p.Independence, 107) AS [Independence Date], DATEDIFF(YEAR, p.Independence, GETDATE()) AS [Years Independent (appox)], p.GovernmentFROM politics pJOIN country cON p.country = c.codeWHERE YEAR(Independence) < 1753ORDER BY IndependenceGO10. Select the SET DATEFORMAT statement and click Execute ( ) to change the DATEFORMAT to day-month-year.T-SQLSET DATEFORMAT dmyGO11. Select the DECLARE and SELECT statements and click Execute ( ) to show how the datetime and datetime2 data types interpret a date literal.T-SQLSET DATEFORMAT dmyDECLARE @dt datetime = '2008-12-05'DECLARE @dt2 datetime2 = '2008-12-05'SELECT MONTH(@dt) AS [Month-Datetime], DAY(@dt) AS [Day-Datetime]SELECT MONTH(@dt2) AS [Month-Datetime2], DAY(@dt2) AS [Day-Datetime2]GO12. Highlight the DECLARE and SELECT statements and click Execute ( ) to use integer arithmetic on a datetime variable.T-SQLDECLARE @dt datetime = '2008-12-05'SELECT @dt + 1GO13. Highlight the DECLARE and SELECT statements and click Execute ( ) to show how integer arithmetic is not allowed for datetime2 variables.T-SQLDECLARE @dt2 datetime = '2008-12-05'SELECT @dt2 + 1GO14. Highlight the DECLARE and SELECT statements and click Execute ( ) to show how to use DATE functions to do simple arithmetic on datetime2 variables.T-SQLDECLARE @dt2 datetime2(7) = '2008-12-05'SELECT DATEADD(d, 1, @dt2)GO15. Highlight the DECLARE and SELECT statements and click Execute ( ) to show how the GETDATE function can be used with both datetime and datetime2 data types.T-SQLDECLARE @dt datetime = GETDATE();DECLARE @dt2 datetime2(7) = GETDATE();SELECT @dt AS [GetDate-DateTime], @dt2 AS [GetDate-DateTime2]GO16. Draw attention to the values returned for both columns and how they are equal.17. Highlight the DECLARE and SELECT statements and click Execute ( ) to show how the SYSDATETIME function can be used with both datetime and datetime2 data types.T-SQLDECLARE @dt datetime = SYSDATETIME();DECLARE @dt2 datetime2(7) = SYSDATETIME();SELECT @dt AS [Sysdatetime-DateTime], @dt2 AS [Sysdatetime-DateTime2]GO18. Draw attention to the values returned for both columns and how they are different.Programming Global Applications with DateTimeOffset 2. If you have not previously created the SQLTrainingKitDB database while completing another demo in this training kit, highlight the CREATE DATABASE statement and click Execute ( ) to do so now.T-SQLCREATE DATABASE SQLTrainingKitDBGO3. Select the USE and CREATE TABLE statements and click Execute ( ) to create table datetest in the SQLTrainingKitDB database.T-SQLUSE SQLTrainingKitDBGOCREATE TABLE datetest ( id integer IDENTITY PRIMARY KEY, datetimecol datetimeoffset, EnteredTZ varchar(40)); Reference:http://www.microsoft.com/downloads/details.aspx?FamilyID=E9C68E1B-1E0E-4299-B498-6AB3CA72A6D7&displaylang=en

Read the article

ORDER BY 1,2,3

- by Tomaz.tsql

Only for lazy people -> how to order our output by defining numbers instead of column names: select * from ( select 1 as id, 'test' as text_name, 32 as seq union all select 3 as id, 'best' as text_name, 61 as seq union all select 4 as id, 'best' as text_name, 12 as seq union all select 4 as id, 'best' as text_name, 6 as seq union all select 2 as id, 'hest' as text_name, 21 as seq ) as x order by 2,3 --order by 1,2,3 you can specify the select list or you...(read more)

Read the article

Why is the value not passed to my controller page in codeigniter?

- by udaya

I am selecting state from country and city from state. This is my country select box: <td width=""><select name="country" onChange="getState(this.value)" class="text_box_width_190"> <option value="0">Select Country</option> <? foreach($country as $row) { ?> <option value="<?=$row['dCountry_id']?>"><?=$row['dCountryName']?></option> <? } ?> </select></td> This is my state select box: <select name="state" id="state" class="text_box_width_190" > <option value="0">Select State</option> </select> This is my city select box: <td width=""><div id="citydiv"><select name="city" class="text_box_width_190"> <option>Select City</option> </select></div></td> This is my script: <script type ="text/javascript"> function getXMLHTTP() { //fuction to return the xml http object var xmlhttp=false; try{ xmlhttp=new XMLHttpRequest(); } catch(e) { try{ xmlhttp= new ActiveXObject("Microsoft.XMLHTTP"); } catch(e){ try{ xmlhttp = new ActiveXObject("Msxml2.XMLHTTP"); } catch(e1){ xmlhttp=false; } } } return xmlhttp; } function getState(countryId) { var strURL="http://localhost/ssit/system/application/views/findState.php?country="+countryId; var req = getXMLHTTP(); if (req) { req.onreadystatechange = function() { if (req.readyState == 4) { // only if "OK" if (req.status == 200) { document.getElementById('statediv').innerHTML=req.responseText; } else { alert("There was a problem while using XMLHTTP:\n" + req.statusText); } } } req.open("GET", strURL, true); req.send(null); } } function getCity(countryId,stateId) { var strURL="http://localhost/ssit/system/application/views/findCity.php?country="+countryId+"&state="+stateId; var req = getXMLHTTP(); if (req) { req.onreadystatechange = function() { if (req.readyState == 4) { // only if "OK" if (req.status == 200) { document.getElementById('citydiv').innerHTML=req.responseText; } else { alert("There was a problem while using XMLHTTP:\n" + req.statusText); } } } req.open("GET", strURL, true); req.send(null); } } </script> This is my find state page: <? $country=intval($_GET['country']); $link = mysql_connect('localhost', 'root', ''); //changet the configuration in required if (!$link) { die('Could not connect: ' . mysql_error()); } mysql_select_db('ssit'); $query="Select dStateName,dState_id FROM tbl_state Where dCountry_id='1'"; $result=mysql_query($query); ?> <select name="state" onchange="getCity(<?=$country?>,this.value)"> <option value="0">Select State</option> <? while($row=mysql_fetch_array($result)) { ?> <option value=<?=$row['dState_id']?>><?=$row['dStateName']?></option> <? } ?> </select> This is my find city page: <? $countryId=intval($_GET['country']); $stateId=intval($_GET['state']); $link = mysql_connect('localhost', 'root', ''); //changet the configuration in required if (!$link) { die('Could not connect: ' . mysql_error()); } mysql_select_db('ssit'); $query="Select dCityName,dCity_id FROM tbl_city Where dState_id='30'"; $result=mysql_query($query); ?> <select name="city"> <option>Select City</option> <? while($row=mysql_fetch_array($result)) { ?> <option value><?=$row['dCityName']?></option> <? } ?> </select> When I post a country, I can receive it but I can't receive my state and city. How to receive them?

Read the article

PROBLEM: PHP strip_tags & multi-dimensional array form parameter

- by Tunji Gbadamosi

I'm having problems stripping the tags from the textual inputs retrieved from my form so as to do something with them in checkout.php. The input is stored in a multi-dimensional array. Here's my form: echo '<form name="choose" action="checkout.php" method="post" onsubmit="return validate_second_form(this);">'; echo '<input type="hidden" name="hidden_value" value="'.$no_guests.'" />'; if($no_guests >= 1){ echo '<div class="volunteer">'; echo '<fieldset>'; echo '<legend>Volunteer:</legend>'; echo '<label>Table:</label>'; echo '<select name="volunteer_table">'; foreach($tables as $t){ echo '<option>'.$t.'</option>'; } echo '</select><br><br>'; echo '<label>Seat number:</label>'; echo '<select name="volunteer_seat">'; foreach($seats as $seat){ echo '<option>'.$seat.'</option>'; } echo '</select><br><br>'; //echo '<br>'; echo '</fieldset>'; echo '</div>'; for($i=0;$i<$no_guests;$i++){ $guest = "guest_".$i; echo '<div class="'.$guest.'">'; echo '<fieldset>'; echo '<legend>Guest '.$i.':</legend>'; echo '<label>First Name:</label>'; echo '<input type="text" name="guest['.$i.']['.$first_name.']" id="fn'.$i.'">'; echo '<label>Surname:</label>'; echo '<input type="text" name="guest['.$i.']['.$surname.']" id="surname'.$i.'"><br><br>'; echo '<label>Date of Birth:</label> <br>'; echo '<label>Day:</label>'; echo '<select name="guest['.$i.'][dob_day]">'; for($j=1;$j<32;$j++){ echo"<option value='$j'>$j</option>"; } echo '</select>'; echo '<label>Month:</label>'; echo '<select name="guest['.$i.'][dob_month]">'; for($j=0;$j<sizeof($month);$j++){ $value = ($j + 1); echo"<option value='$value'>$month[$j]</option>"; } echo '</select>'; echo '<label>Year:</label>'; echo '<select name="guest['.$i.'][dob_year]">'; for($j=1900;$j<$year_limit;$j++){ echo"<option value='$j'>$j</option>"; } echo '</select> <br><br>'; echo '<label>Sex:</label>'; echo '<select name="guest['.$i.']['.$sex.']">'; echo '<option>Female</option>'; echo '<option>Male</option>'; echo '</select><br><br>'; echo '<label>Table:</label>'; echo '<select name="guest['.$i.']['.$table.']">'; foreach($tables as $t){ echo '<option>'.$t.'</option>'; } echo '</select><br><br>'; echo '<label>Seat number:</label>'; echo '<select name="guest['.$i.']['.$seat_no.']">'; foreach($seats as $seat){ echo '<option>'.$seat.'</option>'; } echo '</select><br><br>'; //echo '<br>'; echo '</fieldset>'; echo '</div>'; } } else{ echo '<div id="volunteer">'; echo '<fieldset>'; echo '<legend>Volunteer:</legend>'; echo '<label>Table:</label>'; echo '<select name="volunteer['.$table.']">'; foreach($tables as $t){ echo '<option>'.$t.'</option>'; } echo '</select><br><br>'; echo '<label>Seat number:</label>'; echo '<select name="volunteer['.$seat_no.']">'; foreach($seats as $seat){ echo '<option>'.$seat.'</option>'; } echo '</select><br><br>'; //echo '<br>'; echo '</fieldset>'; echo '</div>'; } echo '<input type="submit" value="Submit form">'; echo '</form>'; here's checkout.php: if(isset($_POST['guest'])){ foreach($_POST['guest'] as $guest){ $guest['first_name'] = strip_tags($guest['first_name']); $guest['surname'] = strip_tags($guest['surname']); } //$_SESSION['guest'] = $guests; }

Read the article

Insert Error:CREATE DATABASE permission denied in database 'master'. cannot attach the file

- by user1300580

i have a register page on my website I am creating and it saves the data entered by the user into a database however when I click the register button i am coming across the following error: Insert Error:CREATE DATABASE permission denied in database 'master'. Cannot attach the file 'C:\Users\MyName\Documents\MyName\Docs\Project\SJ\App_Data\SJ-Database.mdf' as database 'SJ-Database'. These are my connection strings: <connectionStrings> <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/> <add name="MyConsString" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|SJ-Database.mdf; Initial Catalog=SJ-Database; Integrated Security=SSPI;" providerName="System.Data.SqlClient" /> </connectionStrings> Register page code: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; public partial class About : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } public string GetConnectionString() { //sets the connection string from your web config file "ConnString" is the name of your Connection String return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString; } private void ExecuteInsert(string name, string gender, string age, string address, string email) { SqlConnection conn = new SqlConnection(GetConnectionString()); string sql = "INSERT INTO Register (Name, Gender, Age, Address, Email) VALUES " + " (@Name,@Gender,@Age,@Address,@Email)"; try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlParameter[] param = new SqlParameter[6]; //param[0] = new SqlParameter("@id", SqlDbType.Int, 20); param[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50); param[1] = new SqlParameter("@Gender", SqlDbType.Char, 10); param[2] = new SqlParameter("@Age", SqlDbType.Int, 100); param[3] = new SqlParameter("@Address", SqlDbType.VarChar, 50); param[4] = new SqlParameter("@Email", SqlDbType.VarChar, 50); param[0].Value = name; param[1].Value = gender; param[2].Value = age; param[3].Value = address; param[4].Value = email; for (int i = 0; i < param.Length; i++) { cmd.Parameters.Add(param[i]); } cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } catch (System.Data.SqlClient.SqlException ex) { string msg = "Insert Error:"; msg += ex.Message; throw new Exception(msg); } finally { conn.Close(); } } protected void cmdRegister_Click(object sender, EventArgs e) { if (txtRegEmail.Text == txtRegEmailCon.Text) { //call the method to execute insert to the database ExecuteInsert(txtRegName.Text, txtRegAge.Text, ddlRegGender.SelectedItem.Text, txtRegAddress.Text, txtRegEmail.Text); Response.Write("Record was successfully added!"); ClearControls(Page); } else { Response.Write("Email did not match"); txtRegEmail.Focus(); } } public static void ClearControls(Control Parent) { if (Parent is TextBox) { (Parent as TextBox).Text = string.Empty; } else { foreach (Control c in Parent.Controls) ClearControls(c); } } }

Read the article

OCR anything with OneNote 2007 and 2010

- by Matthew Guay

Quality OCR software can often be very expensive, but you may have one already installed on your computer that you didn’t know about. Here’s how you can use OneNote to OCR anything on your computer. OneNote is one of the overlooked gems in recent versions of Microsoft Office. OneNote makes it simple to take notes and keep track of everything with integrated search, and offers more features than its popular competitor Evernote. One way it is better is its high quality optical character recognition (OCR) engine. One of Evernote’s most popular features is that you can search for anything, including text in an image, and you can easily find it. OneNote takes this further, and instantly OCRs any text in images you add. Then, you can use this text easily and copy it from the image. Let’s see how this works and how you can use OneNote as the ultimate OCR. Please Note: This feature is available in OneNote 2007 and 2010. OneNote 2007 is included with Office 2007 Home and Student, Enterprise, and Ultimate, while OneNote 2010 is included with all edition of Office 2010 except for Starter edition. OCR anything First, let’s add something to OCR into OneNote. There are many different ways you can add items to OCR into OneNote. Open a blank page or one you want to insert something into, and then follow these steps to add what you want into OneNote. Picture Simply drag-and-drop a picture with text into a notebook… You can insert a picture directly from OneNote as well. In OneNote 2010, select the Insert tab, and then choose Picture. In OneNote 2007, select the Insert menu, select Picture, and then choose From File. Screen Clipping There are many times we’d like to copy text from something we see onscreen, but there is no direct way to copy text from that thing. For instance, you cannot copy text from the title-bar of a window, or from a flash-based online presentation. For these cases, the Screen Clipping option is very useful. To add a clip of anything onscreen in OneNote 2010, select the Insert tab in the ribbon and click Screen Clipping. In OneNote 2007, either click the Clip button on the toolbar or select the Insert menu and choose Screen Clipping. Alternately, you can take a screen clipping by pressing the windows key + S. When you click Screen Clipping, OneNote will minimize, your desktop will fade lighter, and your mouse pointer will change to a plus sign. Now, click and drag over anything you want to add to OneNote. Here we’re selecting the title of this article. The section you selected will now show up in your OneNote notebook, complete with the date and time the clip was made. Insert a file You’re not limited to pictures; OneNote can even OCR anything in most files on your computer. You can add files directly in OneNote 2010 by selecting File Printout in the Insert tab. In OneNote 2007, select the Insert menu and choose Files as Printout. Choose the file you want to add to OneNote in the dialog. Select Insert, and OneNote will pause momentarily as it processes the file. Now your file will show up in OneNote as a printout with a link to the original file above it. You can also send any file directly to OneNote via the OneNote virtual printer. If you have a file open, such as a PDF, that you’d like to OCR, simply open the print dialog in that program and select the “Send to OneNote” printer. Or, if you have a scanner, you can scan documents directly into OneNote by clicking Scanner Printout in the Insert tab in OneNote 2010. In OneNote 2003, to add a scanned document select the Insert menu, select Picture, and then choose From Scanner or Camera. OCR the image, file, or screenshot you put in OneNote Now that you’ve got your stuff into OneNote, let’s put it to work. OneNote automatically did an OCR scan on anything you inserted into OneNote. You can check to make sure by right-clicking on any picture, screenshot, or file you inserted. Select “Make Text in Image Searchable” and then make sure the correct language is selected. Now, you can copy text from the Picture. Simply right-click on the picture, and select “Copy Text from Picture”. And here’s the text that OneNote found in this picture: OCR anything with OneNote 2007 and 2010 - Windows Live Writer Not bad, huh? Now you can paste the text from the picture into a document or anywhere you need to use the text. If you are instead copying text from a printout, it may give you the option to copy text from this page or all pages of the printout. This works the exact same in OneNote 2007. In OneNote 2010, you can also edit the text OneNote has saved in the image from the OCR. This way, if OneNote read something incorrectly you can change it so you can still find it when you use search in OneNote. Additionally, you can copy only a specific portion of the text from the edit box, so it can be useful just for general copying as well. To do this, right-click on the item and select “Edit Alt Text”. Here is the window to edit alternate text. If you want to copy only a portion of the text, simply select it and press Ctrl+C to copy that portion. Searching OneNote’s OCR engine is very useful for finding specific pictures you have saved in OneNote. Simply enter your search query in the search box on top right, and OneNote will automatically find all instances of that term in all of your notebooks. Notice how it highlights the search term even in the image! This works the same in OneNote 2007. Notice how it highlighted “How-to” in a shot of the header image in our favorite website. In Windows Vista and 7, you can even search for things OneNote OCRed from the Start Menu search. Here the start menu search found the words “Windows Live Writer” in our OCR Test notebook in OneNote where we inserted the screen clip above. Conclusion OneNote is a very useful OCR tool, and can help you capture text from just about anything. Plus, since you can easily search everything you have stored in OneNote, you can quickly find anything you insert anytime. OneNote is one of the least-used Office tools, but we have found it very useful and hope you do too. Similar Articles Productive Geek Tips Add or Remove Apps from the Microsoft Office 2007 or 2010 SuiteRemove Office 2010 Beta and Reinstall Office 2007How To Create and Publish Blog Posts in Word 2010 & 2007How To Copy Worksheets in Excel 2007 & 2010Add Page Numbers to Documents in Word 2007 & 2010 TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window Latest Software Reviews Tinyhacker Random Tips DVDFab 6 Revo Uninstaller Pro Registry Mechanic 9 for Windows PC Tools Internet Security Suite 2010 Using TrueCrypt to Secure Your Data Quickly Schedule Meetings With NeedtoMeet Share Flickr Photos On Facebook Automatically Are You Blocked On Gtalk? Find out Discover Latest Android Apps On AppBrain The Ultimate Guide For YouTube Lovers

Read the article

How to select the first ocurrence in the auto-completion menu by pressing Enter?

- by janoChen

Every time there's is a pop up menu. I select the first occurrence and press enter but nothing happens (the word is not completed with he selected occurrence). The only way is to press Tab until you reach the term for a second time. Is there a way of selecting the first occurrence pressing Enter (or other Vim hotkey)? My .vimrc: " SHORTCUTS nnoremap <F4> :set filetype=html<CR> nnoremap <F5> :set filetype=php<CR> nnoremap <F3> :TlistToggle<CR> " press space to turn off highlighting and clear any message already displayed. nnoremap <silent> <Space> :nohlsearch<Bar>:echo<CR> " set buffers commands nnoremap <silent> <M-F8> :BufExplorer<CR> nnoremap <silent> <F8> :bn<CR> nnoremap <silent> <S-F8> :bp<CR> " open NERDTree with start directory: D:\wamp\www nnoremap <F9> :NERDTree /home/alex/www<CR> " open MRU nnoremap <F10> :MRU<CR> " open current file (silently) nnoremap <silent> <F11> :let old_reg=@"<CR>:let @"=substitute(expand("%:p"), "/", "\\", "g")<CR>:silent!!cmd /cstart <C-R><C-R>"<CR><CR>:let @"=old_reg<CR> " open current file in localhost (default browser) nnoremap <F12> :! start "http://localhost" file:///"%:p""<CR> " open Vim's default Explorer nnoremap <silent> <F2> :Explore<CR> nnoremap <C-F2> :%s/\.html/.php/g<CR> " REMAPPING " map leader to , let mapleader = "," " remap ` to ' nnoremap ' ` nnoremap ` ' " remap increment numbers nnoremap <C-kPlus> <C-A> " COMPRESSION function Js_css_compress () let cwd = expand('<afile>:p:h') let nam = expand('<afile>:t:r') let ext = expand('<afile>:e') if -1 == match(nam, "[\._]src$") let minfname = nam.".min.".ext else let minfname = substitute(nam, "[\._]src$", "", "g").".".ext endif if ext == 'less' if executable('lessc') cal system( 'lessc '.cwd.'/'.nam.'.'.ext.' &') endif else if filewritable(cwd.'/'.minfname) if ext == 'js' && executable('closure-compiler') cal system( 'closure-compiler --js '.cwd.'/'.nam.'.'.ext.' > '.cwd.'/'.minfname.' &') elseif executable('yuicompressor') cal system( 'yuicompressor '.cwd.'/'.nam.'.'.ext.' > '.cwd.'/'.minfname.' &') endif endif endif endfunction autocmd FileWritePost,BufWritePost *.js :call Js_css_compress() autocmd FileWritePost,BufWritePost *.css :call Js_css_compress() autocmd FileWritePost,BufWritePost *.less :call Js_css_compress() " GUI " taglist right side let Tlist_Use_Right_Window = 1 " hide tool bar set guioptions-=T "remove scroll bars set guioptions+=LlRrb set guioptions-=LlRrb " set the initial size of window set lines=46 columns=180 " set default font set guifont=Monospace " set guifont=Monospace\ 10 " show line number set number " set default theme colorscheme molokai-2 " encoding set encoding=utf-8 setglobal fileencoding=utf-8 bomb set fileencodings=ucs-bom,utf-8,latin1 " SCSS syntax highlight au BufRead,BufNewFile *.scss set filetype=scss " LESS syntax highlight syntax on au BufNewFile,BufRead *.less set filetype=less " Haml syntax highlight "au! BufRead,BufNewFile *.haml "setfiletype haml " Sass syntax highlight "au! BufRead,BufNewFile *.sass "setfiletype sass " set filetype indent filetype indent on " for snipMate to work filetype plugin on " show breaks set showbreak=-----> " coding format set tabstop=4 set shiftwidth=4 set linespace=1 " CONFIG " set location of ctags let Tlist_Ctags_Cmd='D:\ctags58\ctags.exe' " keep the buffer around when left set hidden " enable matchit plugin source $VIMRUNTIME/macros/matchit.vim " folding set foldmethod=marker set foldmarker={,} let g:FoldMethod = 0 map <leader>ff :call ToggleFold()<cr> fun! ToggleFold() if g:FoldMethod == 0 exe 'set foldmethod=indent' let g:FoldMethod = 1 else exe 'set foldmethod=marker' let g:FoldMethod = 0 endif endfun " save and restore folds when a file is closed and re-opened "au BufWrite ?* mkview "au BufRead ?* silent loadview " auto-open NERDTree everytime Vim is invoked au VimEnter * NERDTree /home/alex/www " set omnicomplete autocmd FileType python set omnifunc=pythoncomplete#Complete autocmd FileType javascript set omnifunc=javascriptcomplete#CompleteJS autocmd FileType html set omnifunc=htmlcomplete#CompleteTags autocmd FileType css set omnifunc=csscomplete#CompleteCSS autocmd FileType xml set omnifunc=xmlcomplete#CompleteTags autocmd FileType php set omnifunc=phpcomplete#CompletePHP autocmd FileType c set omnifunc=ccomplete#Complete " Remove trailing white-space once the file is saved au BufWritePre * silent g/\s\+$/s/// " Use CTRL-S for saving, also in Insert mode noremap <C-S> :update!<CR> vnoremap <C-S> <C-C>:update!<CR> inoremap <C-S> <C-O>:update!<CR> " DEFAULT set nocompatible source $VIMRUNTIME/vimrc_example.vim "source $VIMRUNTIME/mswin.vim "behave mswin " disable creation of swap files set noswapfile " no back ups wwhile editing set nowritebackup " disable creation of backups set nobackup " no file change pop up warning autocmd FileChangedShell * echohl WarningMsg | echo "File changed shell." | echohl None set diffexpr=MyDiff() function MyDiff() let opt = '-a --binary ' if &diffopt =~ 'icase' | let opt = opt . '-i ' | endif if &diffopt =~ 'iwhite' | let opt = opt . '-b ' | endif let arg1 = v:fname_in if arg1 =~ ' ' | let arg1 = '"' . arg1 . '"' | endif let arg2 = v:fname_new if arg2 =~ ' ' | let arg2 = '"' . arg2 . '"' | endif let arg3 = v:fname_out if arg3 =~ ' ' | let arg3 = '"' . arg3 . '"' | endif let eq = '' if $VIMRUNTIME =~ ' ' if &sh =~ '\<cmd' let cmd = '""' . $VIMRUNTIME . '\diff"' let eq = '"' else let cmd = substitute($VIMRUNTIME, ' ', '" ', '') . '\diff"' endif else let cmd = $VIMRUNTIME . '\diff' endif silent execute '!' . cmd . ' ' . opt . arg1 . ' ' . arg2 . ' > ' . arg3 . eq endfunction

Read the article

CBO????????

- by Liu Maclean(???)

???Itpub????????CBO??????????, ????????: SQL> create table maclean1 as select * from dba_objects; Table created. SQL> update maclean1 set status='INVALID' where owner='MACLEAN'; 2 rows updated. SQL> commit; Commit complete. SQL> create index ind_maclean1 on maclean1(status); Index created. SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN1',cascade=>true); PL/SQL procedure successfully completed. SQL> explain plan for select * from maclean1 where status='INVALID'; Explained. SQL> set linesize 140 pagesize 1400 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- Plan hash value: 987568083 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 11320 | 1028K| 85 (0)| 00:00:02 | |* 1 | TABLE ACCESS FULL| MACLEAN1 | 11320 | 1028K| 85 (0)| 00:00:02 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("STATUS"='INVALID') 13 rows selected. 10053 trace Access path analysis for MACLEAN1 *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for MACLEAN1[MACLEAN1] Column (#10): STATUS( AvgLen: 7 NDV: 2 Nulls: 0 Density: 0.500000 Table: MACLEAN1 Alias: MACLEAN1 Card: Original: 22639.000000 Rounded: 11320 Computed: 11319.50 Non Adjusted: 11319.50 Access Path: TableScan Cost: 85.33 Resp: 85.33 Degree: 0 Cost_io: 85.00 Cost_cpu: 11935345 Resp_io: 85.00 Resp_cpu: 11935345 Access Path: index (AllEqRange) Index: IND_MACLEAN1 resc_io: 185.00 resc_cpu: 8449916 ix_sel: 0.500000 ix_sel_with_filters: 0.500000 Cost: 185.24 Resp: 185.24 Degree: 1 Best:: AccessPath: TableScan Cost: 85.33 Degree: 1 Resp: 85.33 Card: 11319.50 Bytes: 0 ?????10053????????????,?????Density = 0.5 ?? 1/ NDV ??? ??????????????STATUS='INVALID"???????????, ????????????????? ????”STATUS”=’INVALID’ condition???2?,?status??????,??????dbms_stats?????????????,???CBO????INDEX Range ind_maclean1,???????,??????opitimizer?????? ?????????????????????????,????????,??????????status=’INVALID’???????card??,????????: [oracle@vrh4 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 17 19:15:45 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> show parameter optimizer_fea NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_features_enable string 11.2.0.2 SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.oracledatabase12g.com & www.askmaclean.com SQL> drop table maclean; Table dropped. SQL> create table maclean as select * from dba_objects; Table created. SQL> update maclean set status='INVALID' where owner='MACLEAN'; 2 rows updated. SQL> commit; Commit complete. SQL> create index ind_maclean on maclean(status); Index created. SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN',cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 2'); PL/SQL procedure successfully completed. ???????2?bucket????, ??????????????? ???Quest???Guy Harrison???????FREQUENCY????????,??????: rem rem Generate a histogram of data distribution in a column as recorded rem in dba_tab_histograms rem rem Guy Harrison Jan 2010 : www.guyharrison.net rem rem hexstr function is from From http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:707586567563 set pagesize 10000 set lines 120 set verify off col char_value format a10 heading "Endpoint|value" col bucket_count format 99,999,999 heading "bucket|count" col pct format 999.99 heading "Pct" col pct_of_max format a62 heading "Pct of|Max value" rem col endpoint_value format 9999999999999 heading "endpoint|value" CREATE OR REPLACE FUNCTION hexstr (p_number IN NUMBER) RETURN VARCHAR2 AS l_str LONG := TO_CHAR (p_number, 'fm' || RPAD ('x', 50, 'x')); l_return VARCHAR2 (4000); BEGIN WHILE (l_str IS NOT NULL) LOOP l_return := l_return || CHR (TO_NUMBER (SUBSTR (l_str, 1, 2), 'xx')); l_str := SUBSTR (l_str, 3); END LOOP; RETURN (SUBSTR (l_return, 1, 6)); END; / WITH hist_data AS ( SELECT endpoint_value,endpoint_actual_value, NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value, endpoint_number, endpoint_number, endpoint_number - NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0) bucket_count FROM dba_tab_histograms JOIN dba_tab_col_statistics USING (owner, table_name,column_name) WHERE owner = '&owner' AND table_name = '&table' AND column_name = '&column' AND histogram='FREQUENCY') SELECT nvl(endpoint_actual_value,endpoint_value) endpoint_value , bucket_count, ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT, RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max FROM hist_data; WITH hist_data AS ( SELECT endpoint_value,endpoint_actual_value, NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value, endpoint_number, endpoint_number, endpoint_number - NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0) bucket_count FROM dba_tab_histograms JOIN dba_tab_col_statistics USING (owner, table_name,column_name) WHERE owner = '&owner' AND table_name = '&table' AND column_name = '&column' AND histogram='FREQUENCY') SELECT hexstr(endpoint_value) char_value, bucket_count, ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT, RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max FROM hist_data ORDER BY endpoint_value; ?????,??????????FREQUENCY?????: ??dbms_stats ?????STATUS=’INVALID’ bucket count=9 percent = 0.04 ,??????10053 trace????????: SQL> explain plan for select * from maclean where status='INVALID'; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------- Plan hash value: 3087014066 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 837 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MACLEAN | 9 | 837 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_MACLEAN | 9 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("STATUS"='INVALID') ??????????????CBO???????STATUS=’INVALID’?cardnality?? , ??????????? ,??index range scan??Full table scan? ????????????????10053 trace: SQL> alter system flush shared_pool; System altered. SQL> oradebug setmypid; Statement processed. SQL> oradebug event 10053 trace name context forever ,level 1; Statement processed. SQL> explain plan for select * from maclean where status='INVALID'; Explained. SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for MACLEAN[MACLEAN] Column (#10): NewDensity:0.000199, OldDensity:0.000022 BktCnt:22640, PopBktCnt:22640, PopValCnt:2, NDV:2 ???NewDensity= bucket_count / SUM(bucket_count) /2 Column (#10): STATUS( AvgLen: 7 NDV: 2 Nulls: 0 Density: 0.000199 Histogram: Freq #Bkts: 2 UncompBkts: 22640 EndPtVals: 2 Table: MACLEAN Alias: MACLEAN Card: Original: 22640.000000 Rounded: 9 Computed: 9.00 Non Adjusted: 9.00 Access Path: TableScan Cost: 85.30 Resp: 85.30 Degree: 0 Cost_io: 85.00 Cost_cpu: 10804625 Resp_io: 85.00 Resp_cpu: 10804625 Access Path: index (AllEqRange) Index: IND_MACLEAN resc_io: 2.00 resc_cpu: 20763 ix_sel: 0.000398 ix_sel_with_filters: 0.000398 Cost: 2.00 Resp: 2.00 Degree: 1 Best:: AccessPath: IndexRange Index: IND_MACLEAN Cost: 2.00 Degree: 1 Resp: 2.00 Card: 9.00 Bytes: 0 ???????????2 bucket?????CBO????????????,???????????????????,???dbms_stats.DEFAULT_METHOD_OPT????????????????????? ???dbms_stats?????????????????????col_usage$??????predicate???????,??col_usage$??<????????SMON??(?):??col_usage$????>? ??????????dbms_stats????????,col_usage$????????????predicate???,??dbms_stats??????????????????, ?: SQL> drop table maclean; Table dropped. SQL> create table maclean as select * from dba_objects; Table created. SQL> update maclean set status='INVALID' where owner='MACLEAN'; 2 rows updated. SQL> commit; Commit complete. SQL> create index ind_maclean on maclean(status); Index created. ??dbms_stats??method_opt??maclean? SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN'); PL/SQL procedure successfully completed. @histogram.sql Enter value for owner: SYS old 12: WHERE owner = '&owner' new 12: WHERE owner = 'SYS' Enter value for table: MACLEAN old 13: AND table_name = '&table' new 13: AND table_name = 'MACLEAN' Enter value for column: STATUS old 14: AND column_name = '&column' new 14: AND column_name = 'STATUS' no rows selected ????col_usage$?????,????????status????? declare begin for i in 1..500 loop execute immediate ' alter system flush shared_pool'; DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; execute immediate 'select count(*) from maclean where status=''INVALID'' ' ; end loop; end; / PL/SQL procedure successfully completed. SQL> select obj# from obj$ where name='MACLEAN'; OBJ# ---------- 97215 SQL> select * from col_usage$ where OBJ#=97215; OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP ---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- --------- 97215 1 1 0 0 0 0 0 17-OCT-11 97215 10 499 0 0 0 0 0 17-OCT-11 SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN'); PL/SQL procedure successfully completed. @histogram.sql Enter value for owner: SYS Enter value for table: MACLEAN Enter value for column: STATUS Endpoint bucket Pct of value count Pct Max value ---------- ----------- ------- -------------------------------------------------------------- INVALI 2 .04 VALIC3 5,453 99.96 *************************************************

Read the article

How do i use the top keyword in an insert statement?

- by acidzombie24

I was using it as LIMIT when i got the exception Incorrect syntax near the keyword 'TOP'. Maybe i can omit it in this case without problem? but if i couldnt where do i put top? INSERT INTO [user_data] (...) SELECT ... @14 WHERE not exists (SELECT * FROM [user_data] WHERE [email] = @15 OR [name] = @16 OR [unconfirmed_email] = @17 TOP 1);

Read the article

sqlite insert into: do the column names have to match?

- by P a u l

(This question is specific to SQLite3, but it may be common to all SQL I don't know.) This doesn't seem to work. Is it because the column names don't match? (If they match, it works.) If this is true, how can I select from a different field in the source table? INSERT INTO [ImagePoint] (ImageID, PointID, X, Y) SELECT Image2ID, PointID, X, Y FROM Point

Read the article

ANY way to consolidate this code?

- by JM4

I am building a PHP registration form which takes the following fields for up to 20 athletes: First Name Middle Initial Last Name Federation Number Address City State Zip DOB SSN Phone Email I am only through 7 of the fields for each fighter and my php file is very large (over 40kb). Is there ANY way to consolidate this code at all? I am also having to validate the information on each field (as I said - 20 athletes x 12 fields = 240 validations on a single page). If I can send any further code let me know! <form id="Form" action="<?php $_SERVER['PHP_SELF']; ?>" method="post" name="Form" onsubmit="return Enroll_Form_Validator(this)"> <p class="title">Your Fighters' Information</p> <p>Please complete the following fields with your <span style="color:red;"> Fighters' Information</span> to continue your enrollment.</p> <br /> <?php // if $errors is not empty, the form must have failed one or more validation // tests. Loop through each and display them on the page for the user if (!empty($errors)) { echo "<div class='error'>Please fix the following errors:\n<ul>"; foreach ($errors as $error) echo "<li>$error</li>\n"; echo "</ul></div>"; } ?> <?php if ($_SESSION['Num_Fighters'] > "0") { ?> <table class="demoTable"> <tr> <td>First Name: </td> <td><input type="text" name="F1FirstName" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F1FirstName']; ?>" /></td> </tr> <tr> <td>Middle Initial: </td> <td><input type="text" name="F1MI" size="2" maxlength="1" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F1MI']; ?>" /></td> </tr> <tr> <td>Last Name: </td> <td><input type="text" name="F1LastName" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F1LastName']; ?>" /></td> </tr> <tr> <td>Federation No: </td> <td><input type="text" name="F1FedNum" maxlength="10" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F1FedNum']; ?>" /></td> </tr> <tr> <td>SSN: </td> <td><input type="text" name="F1SSN1" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F1SSN1']; ?>" /> - <input type="text" name="F1SSN2" size="2" maxlength="2" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F1SSN2']; ?>" /> - <input type="text" name="F1SSN3" size="4" maxlength="4" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F1SSN3']; ?>" /> </td> </tr> <tr> <td>Date of Birth</td> <td> <select name="F1DOB1"> <option value="">Month</option> <?php for ($i=1; $i<=12; $i++) { echo "<option value='$i'"; if ($fields["F1DOB1"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> / <select name="F1DOB2"> <option value="">Day</option> <?php for ($i=1; $i<=31; $i++) { echo "<option value='$i'"; if ($fields["F1DOB2"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> / <select name="F1DOB3"> <option value="">Year</option> <?php for ($i=date('Y'); $i>=1900; $i--) { echo "<option value='$i'"; if ($fields["F1DOB3"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> </td> </tr> <tr> <td>Address: </td> <td><input type="text" name="F1Address" value="<?php echo $fields['F1Address']; ?>" /></td> </tr> <tr> <td>City: </td> <td><input type="text" name="F1City" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F1City']; ?>" /></td> </tr> <tr> <td>State: </td> <td><select name="F1State"><option value="">Choose a State</option><?php showOptionsDrop($states_arr, null, true); ?></select></td> </tr> <tr> <td>Zip Code: </td> <td><input type="text" name="F1Zip" size="6" maxlength="5" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F1Zip']; ?>" /></td> </tr> <tr> <td>Contact Telephone No: </td> <td>( <input type="text" name="F1Phone1" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F1Phone1']; ?>" /> ) <input type="text" name="F1Phone2" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F1Phone2']; ?>" /> - <input type="text" name="F1Phone3" size="4" maxlength="4" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F1Phone3']; ?>" /> </td> </tr> <tr> <td>Email:</td> <td><input type="text" name="F1Email" value="<?php echo $fields['F1Email']; ?>" /></td> </tr> </table> <?php } ?> <br /> <?php if ($_SESSION['Num_Fighters'] > "1") { ?> <table class="demoTable"> <tr> <td>First Name: </td> <td><input type="text" name="F2FirstName" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F2FirstName']; ?>" /></td> </tr> <tr> <td>Middle Initial: </td> <td><input type="text" name="F2MI" size="2" maxlength="1" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F2MI']; ?>" /></td> </tr> <tr> <td>Last Name: </td> <td><input type="text" name="F2LastName" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F2LastName']; ?>" /></td> </tr> <tr> <td>Federation No: </td> <td><input type="text" name="F2FedNum" maxlength="10" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F2FedNum']; ?>" /></td> </tr> <tr> <td>SSN: </td> <td><input type="text" name="F2SSN1" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F2SSN1']; ?>" /> - <input type="text" name="F2SSN2" size="2" maxlength="2" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F2SSN2']; ?>" /> - <input type="text" name="F2SSN3" size="4" maxlength="4" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F2SSN3']; ?>" /> </td> </tr> <tr> <td>Date of Birth</td> <td> <select name="F2DOB1"> <option value="">Month</option> <?php for ($i=1; $i<=12; $i++) { echo "<option value='$i'"; if ($fields["F2DOB1"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> / <select name="F2DOB2"> <option value="">Day</option> <?php for ($i=1; $i<=31; $i++) { echo "<option value='$i'"; if ($fields["F2DOB2"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> / <select name="F2DOB3"> <option value="">Year</option> <?php for ($i=date('Y'); $i>=1900; $i--) { echo "<option value='$i'"; if ($fields["F2DOB3"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> </td> </tr> <tr> <td>Address: </td> <td><input type="text" name="F2Address" value="<?php echo $fields['F2Address']; ?>" /></td> </tr> <tr> <td>City: </td> <td><input type="text" name="F2City" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F2City']; ?>" /></td> </tr> <tr> <td>State: </td> <td><select name="F2State"><option value="">Choose a State</option><?php showOptionsDrop($states_arr, null, true); ?></select></td> </tr> <tr> <td>Zip Code: </td> <td><input type="text" name="F2Zip" size="6" maxlength="5" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F2Zip']; ?>" /></td> </tr> <tr> <td>Contact Telephone No: </td> <td>( <input type="text" name="F2Phone1" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F2Phone1']; ?>" /> ) <input type="text" name="F2Phone2" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F2Phone2']; ?>" /> - <input type="text" name="F2Phone3" size="4" maxlength="4" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F2Phone3']; ?>" /> </td> </tr> <tr> <td>Email:</td> <td><input type="text" name="F2Email" value="<?php echo $fields['F2Email']; ?>" /></td> </tr> </table> <?php } ?> <br /> <?php if ($_SESSION['Num_Fighters'] > "2") { ?> <table class="demoTable"> <tr> <td>First Name: </td> <td><input type="text" name="F3FirstName" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F3FirstName']; ?>" /></td> </tr> <tr> <td>Middle Initial: </td> <td><input type="text" name="F3MI" size="2" maxlength="1" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F3MI']; ?>" /></td> </tr> <tr> <td>Last Name: </td> <td><input type="text" name="F3LastName" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F3LastName']; ?>" /></td> </tr> <tr> <td>Federation No: </td> <td><input type="text" name="F3FedNum" maxlength="10" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F3FedNum']; ?>" /></td> </tr> <tr> <td>SSN: </td> <td><input type="text" name="F3SSN1" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F3SSN1']; ?>" /> - <input type="text" name="F3SSN2" size="2" maxlength="2" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F3SSN2']; ?>" /> - <input type="text" name="F3SSN3" size="4" maxlength="4" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F3SSN3']; ?>" /> </td> </tr> <tr> <td>Date of Birth</td> <td> <select name="F3DOB1"> <option value="">Month</option> <?php for ($i=1; $i<=12; $i++) { echo "<option value='$i'"; if ($fields["F3DOB1"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> / <select name="F3DOB2"> <option value="">Day</option> <?php for ($i=1; $i<=31; $i++) { echo "<option value='$i'"; if ($fields["F3DOB2"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> / <select name="F3DOB3"> <option value="">Year</option> <?php for ($i=date('Y'); $i>=1900; $i--) { echo "<option value='$i'"; if ($fields["F3DOB3"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> </td> </tr> <tr> <td>Address: </td> <td><input type="text" name="F3Address" value="<?php echo $fields['F3Address']; ?>" /></td> </tr> <tr> <td>City: </td> <td><input type="text" name="F3City" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F3City']; ?>" /></td> </tr> <tr> <td>State: </td> <td><select name="F3State"><option value="">Choose a State</option><?php showOptionsDrop($states_arr, null, true); ?></select></td> </tr> <tr> <td>Zip Code: </td> <td><input type="text" name="F3Zip" size="6" maxlength="5" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F3Zip']; ?>" /></td> </tr> <tr> <td>Contact Telephone No: </td> <td>( <input type="text" name="F3Phone1" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F3Phone1']; ?>" /> ) <input type="text" name="F3Phone2" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F3Phone2']; ?>" /> - <input type="text" name="F3Phone3" size="4" maxlength="4" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F3Phone3']; ?>" /> </td> </tr> <tr> <td>Email:</td> <td><input type="text" name="F3Email" value="<?php echo $fields['F3Email']; ?>" /></td> </tr> </table> <?php } ?> <br /> <?php if ($_SESSION['Num_Fighters'] > "3") { ?> <table class="demoTable"> <tr> <td>First Name: </td> <td><input type="text" name="F4FirstName" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F4FirstName']; ?>" /></td> </tr> <tr> <td>Middle Initial: </td> <td><input type="text" name="F4MI" size="2" maxlength="1" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F4MI']; ?>" /></td> </tr> <tr> <td>Last Name: </td> <td><input type="text" name="F4LastName" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F4LastName']; ?>" /></td> </tr> <tr> <td>Federation No: </td> <td><input type="text" name="F4FedNum" maxlength="10" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F4FedNum']; ?>" /></td> </tr> <tr> <td>SSN: </td> <td><input type="text" name="F4SSN1" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F4SSN1']; ?>" /> - <input type="text" name="F4SSN2" size="2" maxlength="2" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F4SSN2']; ?>" /> - <input type="text" name="F4SSN3" size="4" maxlength="4" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F4SSN3']; ?>" /> </td> </tr> <tr> <td>Date of Birth</td> <td> <select name="F4DOB1"> <option value="">Month</option> <?php for ($i=1; $i<=12; $i++) { echo "<option value='$i'"; if ($fields["F4DOB1"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> / <select name="F4DOB2"> <option value="">Day</option> <?php for ($i=1; $i<=31; $i++) { echo "<option value='$i'"; if ($fields["F4DOB2"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> / <select name="F4DOB3"> <option value="">Year</option> <?php for ($i=date('Y'); $i>=1900; $i--) { echo "<option value='$i'"; if ($fields["F4DOB3"] == $i) echo " selected"; echo ">$i</option>"; } ?> </select> </td> </tr> <tr> <td>Address: </td> <td><input type="text" name="F4Address" value="<?php echo $fields['F4Address']; ?>" /></td> </tr> <tr> <td>City: </td> <td><input type="text" name="F4City" onkeyup="if(!this.value.match(/^([a-z]+\s?)*$/i))this.value=this.value.replace(/[^a-z ]/ig,'').replace(/\s+/g,' ')" value="<?php echo $fields['F4City']; ?>" /></td> </tr> <tr> <td>State: </td> <td><select name="F4State"><option value="">Choose a State</option><?php showOptionsDrop($states_arr, null, true); ?></select></td> </tr> <tr> <td>Zip Code: </td> <td><input type="text" name="F4Zip" size="6" maxlength="5" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F4Zip']; ?>" /></td> </tr> <tr> <td>Contact Telephone No: </td> <td>( <input type="text" name="F4Phone1" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F4Phone1']; ?>" /> ) <input type="text" name="F4Phone2" size="3" maxlength="3" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F4Phone2']; ?>" /> - <input type="text" name="F4Phone3" size="4" maxlength="4" onkeyup="this.value=this.value.replace(/[^0-9]/ig, '')" value="<?php echo $fields['F4Phone3']; ?>" /> </td> </tr> <tr> <td>Email:</td> <td><input type="text" name="F4Email" value="<?php echo $fields['F4Email']; ?>" /></td> </tr> </table> <?php } ?> <div align="right"><input class="enrbutton" type="submit" name="submit" value="Continue" /></div> </form> This only goes through 4 athletes and I need it to capture 20. Any ideas? I am forced to keep all 200+ elements in SESSION assuming somebody enrolls 20 athletes.

Read the article

How to use JQuery to set the value of 2 html form select elements depending on the value of another

- by Chris Stevenson

My Javascript and JQuery skills are poor at best and this is ** I have the following three elements in a form : <select name="event_time_start_hours"> <option value="blank" disabled="disabled">Hours</option> <option value="blank" disabled="disabled"> </option> <option value="01">1</option> <option value="02">2</option> <option value="03">3</option> <option value="04">4</option> <option value="05">5</option> <option value="06">6</option> <option value="07">7</option> <option value="08">8</option> <option value="09">9</option> <option value="10">10</option> <option value="11">11</option> <option value="12">12</option> <option value="midnight">Midnight</option> <option value="midday">Midday</option> </select> <select name="event_time_start_minutes"> <option value="blank" disabled="disabled">Minutes</option> <option value="blank" disabled="disabled"> </option> <option value="00">00</option> <option value="15">15</option> <option value="30">30</option> <option value="45">45</option> </select> <select name="event_time_start_ampm"> <option value="blank" disabled="disabled">AM / PM</option> <option value="blank" disabled="disabled"> </option> <option value="am">AM</option> <option value="pm">PM</option> </select> Quite simply, when either 'midnight' or 'midday' is selected in "event_time_start_hours", I want the values of "event_time_start_minutes" and "event_time_start_ampm" to change to "00" and "am" respectively. My VERY poor piece of Javascript says this so far : $(document).ready(function() { $('#event_time_start_hours').change(function() { if($('#event_time_start_hours').val('midnight')) { $('#event_time_start_minutes').val('00'); } }); }); ... and whilst I'm not terribly surprised it doesn't work, I'm at a loss as to what to do next. I want to do this purely for visual reasons for the user as when the form submits I ignore the "minutes" and "am/pm". I'm trying to decide whether it would be best to change the selected values, change the selected values and then disable the element or hide them altogether. However, without any success in getting anything to happen at all I haven't been able to try the different approaches to see what feels right. I've ruled out the obvious things like a duplicate element ID or simply not linking to JQuery. Thank you.

Read the article

How to select chosen columns from two different entities into one DTO using NHibernate?

- by Pawel Krakowiak

I have two classes (just recreating the problem): public class User { public virtual int Id { get; set; } public virtual string FirstName { get; set; } public virtual string LastName { get; set; } public virtual IList<OrgUnitMembership> OrgUnitMemberships { get; set; } } public class OrgUnitMembership { public virtual int UserId { get; set; } public virtual int OrgUnitId { get; set; } public virtual DateTime JoinDate { get; set; } public virtual DateTime LeaveDate { get; set; } } There's a Fluent NHibernate map for both, of course: public class UserMapping : ClassMap<User> { public UserMapping() { Table("Users"); Id(e => e.Id).GeneratedBy.Identity(); Map(e => e.FirstName); Map(e => e.LastName); HasMany(x => x.OrgUnitMemberships) .KeyColumn(TypeReflector<OrgUnitMembership> .GetPropertyName(p => p.UserId))).ReadOnly().Inverse(); } } public class OrgUnitMembershipMapping : ClassMap<OrgUnitMembership> { public OrgUnitMembershipMapping() { Table("OrgUnitMembership"); CompositeId() .KeyProperty(x=>x.UserId) .KeyProperty(x=>x.OrgUnitId); Map(x => x.JoinDate); Map(x => x.LeaveDate); References(oum => oum.OrgUnit) .Column(TypeReflector<OrgUnitMembership> .GetPropertyName(oum => oum.OrgUnitId)).ReadOnly(); References(oum => oum.User) .Column(TypeReflector<OrgUnitMembership> .GetPropertyName(oum => oum.UserId)).ReadOnly(); } } What I want to do is to retrieve some users based on criteria, but I would like to combine all columns from the Users table with some columns from the OrgUnitMemberships table, analogous to a SQL query: select u.*, m.JoinDate, m.LeaveDate from Users u inner join OrgUnitMemberships m on u.Id = m.UserId where m.OrgUnitId = :ouid I am totally lost, I tried many different options. Using a plain SQL query almost works, but because there are some nullable enums in the User class AliasToBean fails to transform, otherwise wrapping a SQL query would work like this: return Session .CreateSQLQuery(sql) .SetParameter("ouid", orgUnitId) .SetResultTransformer(Transformers.AliasToBean<UserDTO>()) .List<UserDTO>() I tried the code below as a test (a few different variants), but I'm not sure what I'm doing. It works partially, I get instances of UserDTO back, the properties coming from OrgUnitMembership (dates) are filled, but all properties from User are null: User user = null; OrgUnitMembership membership = null; UserDTO dto = null; var users = Session.QueryOver(() => user) .SelectList(list => list .Select(() => user.Id) .Select(() => user.FirstName) .Select(() => user.LastName)) .JoinAlias(u => u.OrgUnitMemberships, () => membership) //.JoinQueryOver<OrgUnitMembership>(u => u.OrgUnitMemberships) .SelectList(list => list .Select(() => membership.JoinDate).WithAlias(() => dto.JoinDate) .Select(() => membership.LeaveDate).WithAlias(() => dto.LeaveDate)) .TransformUsing(Transformers.AliasToBean<UserDTO>()) .List<UserDTO>();

Read the article

T-SQL Tuesday #025 – CHECK Constraint Tricks

- by Most Valuable Yak (Rob Volk)

Allen White (blog | twitter), marathoner, SQL Server MVP and presenter, and all-around awesome author is hosting this month's T-SQL Tuesday on sharing SQL Server Tips and Tricks. And for those of you who have attended my Revenge: The SQL presentation, you know that I have 1 or 2 of them. You'll also know that I don't recommend using anything I talk about in a production system, and will continue that advice here…although you might be sorely tempted. Suffice it to say I'm not using these examples myself, but I think they're worth sharing anyway. Some of you have seen or read about SQL Server constraints and have applied them to your table designs…unless you're a vendor ;)…and may even use CHECK constraints to limit numeric values, or length of strings, allowable characters and such. CHECK constraints can, however, do more than that, and can even provide enhanced security and other restrictions. One tip or trick that I didn't cover very well in the presentation is using constraints to do unusual things; specifically, limiting or preventing inserts into tables. The idea was to use a CHECK constraint in a way that didn't depend on the actual data: -- create a table that cannot accept data CREATE TABLE dbo.JustTryIt(a BIT NOT NULL PRIMARY KEY, CONSTRAINT chk_no_insert CHECK (GETDATE()=GETDATE()+1)) INSERT dbo.JustTryIt VALUES(1) I'll let you run that yourself, but I'm sure you'll see that this is a pretty stupid table to have, since the CHECK condition will always be false, and therefore will prevent any data from ever being inserted. I can't remember why I used this example but it was for some vague and esoteric purpose that applies to about, maybe, zero people. I come up with a lot of examples like that. However, if you realize that these CHECKs are not limited to column references, and if you explore the SQL Server function list, you could come up with a few that might be useful. I'll let the names describe what they do instead of explaining them all: CREATE TABLE NoSA(a int not null, CONSTRAINT CHK_No_sa CHECK (SUSER_SNAME()<>'sa')) CREATE TABLE NoSysAdmin(a int not null, CONSTRAINT CHK_No_sysadmin CHECK (IS_SRVROLEMEMBER('sysadmin')=0)) CREATE TABLE NoAdHoc(a int not null, CONSTRAINT CHK_No_AdHoc CHECK (OBJECT_NAME(@@PROCID) IS NOT NULL)) CREATE TABLE NoAdHoc2(a int not null, CONSTRAINT CHK_No_AdHoc2 CHECK (@@NESTLEVEL>0)) CREATE TABLE NoCursors(a int not null, CONSTRAINT CHK_No_Cursors CHECK (@@CURSOR_ROWS=0)) CREATE TABLE ANSI_PADDING_ON(a int not null, CONSTRAINT CHK_ANSI_PADDING_ON CHECK (@@OPTIONS & 16=16)) CREATE TABLE TimeOfDay(a int not null, CONSTRAINT CHK_TimeOfDay CHECK (DATEPART(hour,GETDATE()) BETWEEN 0 AND 1)) GO -- log in as sa or a sysadmin server role member, and try this: INSERT NoSA VALUES(1) INSERT NoSysAdmin VALUES(1) -- note the difference when using sa vs. non-sa -- then try it again with a non-sysadmin login -- see if this works: INSERT NoAdHoc VALUES(1) INSERT NoAdHoc2 VALUES(1) GO -- then try this: CREATE PROCEDURE NotAdHoc @val1 int, @val2 int AS SET NOCOUNT ON; INSERT NoAdHoc VALUES(@val1) INSERT NoAdHoc2 VALUES(@val2) GO EXEC NotAdHoc 2,2 -- which values got inserted? SELECT * FROM NoAdHoc SELECT * FROM NoAdHoc2 -- and this one just makes me happy :) INSERT NoCursors VALUES(1) DECLARE curs CURSOR FOR SELECT 1 OPEN curs INSERT NoCursors VALUES(2) CLOSE curs DEALLOCATE curs INSERT NoCursors VALUES(3) SELECT * FROM NoCursors I'll leave the ANSI_PADDING_ON and TimeOfDay tables for you to test on your own, I think you get the idea. (Also take a look at the NoCursors example, notice anything interesting?) The real eye-opener, for me anyway, is the ability to limit bad coding practices like cursors, ad-hoc SQL, and sa use/abuse by using declarative SQL objects. I'm sure you can see how and why this would come up when discussing Revenge: The SQL.;) And the best part IMHO is that these work on pretty much any version of SQL Server, without needing Policy Based Management, DDL/login triggers, or similar tools to enforce best practices. All seriousness aside, I highly recommend that you spend some time letting your mind go wild with the possibilities and see how far you can take things. There are no rules! (Hmmmm, what can I do with rules?) #TSQL2sDay

Read the article

XSLT big integer (int64) handling msxml

- by Farid Z

When trying to do math on an big integer (int64) large number in xslt template I get the wrong result since there is no native 64-bit integer support in xslt (xslt number is 64-bit double). I am using msxml 6.0 on Windows XP SP3. Are there any work around for this on Windows? <tables> <table> <table_schem>REPADMIN</table_schem> <table_name>TEST_DESCEND_IDENTITY_BIGINT</table_name> <column> <col_name>COL1</col_name> <identity> <col_min_val>9223372036854775805</col_min_val> <col_max_val>9223372036854775805</col_max_val> <autoincrementvalue>9223372036854775807</autoincrementvalue> <autoincrementstart>9223372036854775807</autoincrementstart> <autoincrementinc>-1</autoincrementinc> </identity> </column> </table> </tables> This test returns true due to overflow (I am assuming) but actually is false if I could tell the xslt processor somehow to use int64 rather than the default 64-bit double for the data since big integer is the actual data type for the numbers in the xml input. <xsl:when test="autoincrementvalue = (col_min_val + autoincrementinc)"> <xsl:value-of select="''"/> </xsl:when> here is the complete template <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >  <xsl:output omit-xml-declaration='yes' method='text' /> <xsl:param name="stmtsep">;</xsl:param> <xsl:param name="schemprefix"></xsl:param> <xsl:template match="tables"> <xsl:variable name="identitycount" select="count(table/column/identity)"></xsl:variable> <xsl:for-each select="table/column/identity"> <xsl:variable name="table_schem" select="../../table_schem"></xsl:variable> <xsl:variable name="table_name" select="../../table_name"></xsl:variable> <xsl:variable name="tablespec"> <xsl:if test="$schemprefix"> <xsl:value-of select="$table_schem"/>.</xsl:if><xsl:value-of select="$table_name"/></xsl:variable> <xsl:variable name="col_name" select="../col_name"></xsl:variable> <xsl:variable name="newstart"> <xsl:choose> <xsl:when test="autoincrementinc > 0"> <xsl:choose> <xsl:when test="col_max_val = '' and autoincrementvalue = autoincrementstart"> <xsl:value-of select="''"/> </xsl:when> <xsl:when test="col_max_val = ''"> <xsl:value-of select="autoincrementstart"/> </xsl:when> <xsl:when test="autoincrementvalue = (col_max_val + autoincrementinc)"> <xsl:value-of select="''"/> </xsl:when> <xsl:when test="(col_max_val + autoincrementinc) < autoincrementstart"> <xsl:value-of select="autoincrementstart"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="col_max_val + autoincrementinc"/> </xsl:otherwise> </xsl:choose> </xsl:when> <xsl:when test="autoincrementinc < 0"> <xsl:choose> <xsl:when test="col_min_val = '' and autoincrementvalue = autoincrementstart"> <xsl:value-of select="''"/> </xsl:when> <xsl:when test="col_min_val = ''"> <xsl:value-of select="autoincrementstart"/> </xsl:when> <xsl:when test="autoincrementvalue = (col_min_val + autoincrementinc)"> <xsl:value-of select="''"/> </xsl:when> <xsl:when test="(col_min_val + autoincrementinc) > autoincrementstart"> <xsl:value-of select="autoincrementstart"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="col_min_val + autoincrementinc"/> </xsl:otherwise> </xsl:choose> </xsl:when> </xsl:choose> </xsl:variable> <xsl:if test="not(position()=1)"><xsl:text> </xsl:text></xsl:if> <xsl:choose>  <xsl:when test="$newstart != '' and $newstart != autoincrementvalue">alter table <xsl:value-of select="$tablespec"/> alter column <xsl:value-of select="$col_name"/> restart with <xsl:value-of select="$newstart"/><xsl:if test="$identitycount>1">;</xsl:if></xsl:when> <xsl:otherwise>-- reseed <xsl:value-of select="$tablespec"/> is not necessary</xsl:otherwise> </xsl:choose> </xsl:for-each> </xsl:template> </xsl:stylesheet>

Read the article

in Open Office Calc, how do I drag and drop cells to insert rather than replace their destination?

- by joachim

I want to rearrange rows with the mouse in Calc. In Excel, I select the whole row, then drag and drop it while holding SHIFT. This causes the drag and drop cursor to turn into a bar rather than cells, and the cells are inserted at the bar's position. Is there a way to accomplish the same sort of thing in Calc without going round the houses inserting columns before the drag operation?

Read the article

Performance Enhancement in Full-Text Search Query

- by Calvin Sun

Ever since its first release, we are continuing consolidating and developing InnoDB Full-Text Search feature. There is one recent improvement that worth blogging about. It is an effort with MySQL Optimizer team that simplifies some common queries’ Query Plans and dramatically shorted the query time. I will describe the issue, our solution and the end result by some performance numbers to demonstrate our efforts in continuing enhancement the Full-Text Search capability. The Issue: As we had discussed in previous Blogs, InnoDB implements Full-Text index as reversed auxiliary tables. The query once parsed will be reinterpreted into several queries into related auxiliary tables and then results are merged and consolidated to come up with the final result. So at the end of the query, we’ll have all matching records on hand, sorted by their ranking or by their Doc IDs. Unfortunately, MySQL’s optimizer and query processing had been initially designed for MyISAM Full-Text index, and sometimes did not fully utilize the complete result package from InnoDB. Here are a couple examples: Case 1: Query result ordered by Rank with only top N results: mysql> SELECT FTS_DOC_ID, MATCH (title, body) AGAINST ('database') AS SCORE FROM articles ORDER BY score DESC LIMIT 1; In this query, user tries to retrieve a single record with highest ranking. It should have a quick answer once we have all the matching documents on hand, especially if there are ranked. However, before this change, MySQL would almost retrieve rankings for almost every row in the table, sort them and them come with the top rank result. This whole retrieve and sort is quite unnecessary given the InnoDB already have the answer. In a real life case, user could have millions of rows, so in the old scheme, it would retrieve millions of rows' ranking and sort them, even if our FTS already found there are two 3 matched rows. Apparently, the million ranking retrieve is done in vain. In above case, it should just ask for 3 matched rows' ranking, all other rows' ranking are 0. If it want the top ranking, then it can just get the first record from our already sorted result. Case 2: Select Count(*) on matching records: mysql> SELECT COUNT(*) FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); In this case, InnoDB search can find matching rows quickly and will have all matching rows. However, before our change, in the old scheme, every row in the table was requested by MySQL one by one, just to check whether its ranking is larger than 0, and later comes up a count. In fact, there is no need for MySQL to fetch all rows, instead InnoDB already had all the matching records. The only thing need is to call an InnoDB API to retrieve the count The difference can be huge. Following query output shows how big the difference can be: mysql> select count(*) from searchindex_inno where match(si_title, si_text) against ('people') +----------+ | count(*) | +----------+ | 666877 | +----------+ 1 row in set (16 min 17.37 sec) So the query took almost 16 minutes. Let’s see how long the InnoDB can come up the result. In InnoDB, you can obtain extra diagnostic printout by turning on “innodb_ft_enable_diag_print”, this will print out extra query info: Error log: keynr=2, 'people' NL search Total docs: 10954826 Total words: 0 UNION: Searching: 'people' Processing time: 2 secs: row(s) 666877: error: 10 ft_init() ft_init_ext() keynr=2, 'people' NL search Total docs: 10954826 Total words: 0 UNION: Searching: 'people' Processing time: 3 secs: row(s) 666877: error: 10 Output shows it only took InnoDB only 3 seconds to get the result, while the whole query took 16 minutes to finish. So large amount of time has been wasted on the un-needed row fetching. The Solution: The solution is obvious. MySQL can skip some of its steps, optimize its plan and obtain useful information directly from InnoDB. Some of savings from doing this include: 1) Avoid redundant sorting. Since InnoDB already sorted the result according to ranking. MySQL Query Processing layer does not need to sort to get top matching results. 2) Avoid row by row fetching to get the matching count. InnoDB provides all the matching records. All those not in the result list should all have ranking of 0, and no need to be retrieved. And InnoDB has a count of total matching records on hand. No need to recount. 3) Covered index scan. InnoDB results always contains the matching records' Document ID and their ranking. So if only the Document ID and ranking is needed, there is no need to go to user table to fetch the record itself. 4) Narrow the search result early, reduce the user table access. If the user wants to get top N matching records, we do not need to fetch all matching records from user table. We should be able to first select TOP N matching DOC IDs, and then only fetch corresponding records with these Doc IDs. Performance Results and comparison with MyISAM The result by this change is very obvious. I includes six testing result performed by Alexander Rubin just to demonstrate how fast the InnoDB query now becomes when comparing MyISAM Full-Text Search. These tests are base on the English Wikipedia data of 5.4 Million rows and approximately 16G table. The test was performed on a machine with 1 CPU Dual Core, SSD drive, 8G of RAM and InnoDB_buffer_pool is set to 8 GB. Table 1: SELECT with LIMIT CLAUSE mysql> SELECT si_title, match(si_title, si_text) against('family') as rel FROM si WHERE match(si_title, si_text) against('family') ORDER BY rel desc LIMIT 10; InnoDB MyISAM Times Faster Time for the query 1.63 sec 3 min 26.31 sec 127 You can see for this particular query (retrieve top 10 records), InnoDB Full-Text Search is now approximately 127 times faster than MyISAM. Table 2: SELECT COUNT QUERY mysql>select count(*) from si where match(si_title, si_text) against('family‘); +----------+ | count(*) | +----------+ | 293955 | +----------+ InnoDB MyISAM Times Faster Time for the query 1.35 sec 28 min 59.59 sec 1289 In this particular case, where there are 293k matching results, InnoDB took only 1.35 second to get all of them, while take MyISAM almost half an hour, that is about 1289 times faster!. Table 3: SELECT ID with ORDER BY and LIMIT CLAUSE for selected terms mysql> SELECT <ID>, match(si_title, si_text) against(<TERM>) as rel FROM si_<TB> WHERE match(si_title, si_text) against (<TERM>) ORDER BY rel desc LIMIT 10; Term InnoDB (time to execute) MyISAM(time to execute) Times Faster family 0.5 sec 5.05 sec 10.1 family film 0.95 sec 25.39 sec 26.7 Pizza restaurant orange county California 0.93 sec 32.03 sec 34.4 President united states of America 2.5 sec 36.98 sec 14.8 Table 4: SELECT title and text with ORDER BY and LIMIT CLAUSE for selected terms mysql> SELECT <ID>, si_title, si_text, ... as rel FROM si_<TB> WHERE match(si_title, si_text) against (<TERM>) ORDER BY rel desc LIMIT 10; Term InnoDB (time to execute) MyISAM(time to execute) Times Faster family 0.61 sec 41.65 sec 68.3 family film 1.15 sec 47.17 sec 41.0 Pizza restaurant orange county california 1.03 sec 48.2 sec 46.8 President united states of america 2.49 sec 44.61 sec 17.9 Table 5: SELECT ID with ORDER BY and LIMIT CLAUSE for selected terms mysql> SELECT <ID>, match(si_title, si_text) against(<TERM>) as rel FROM si_<TB> WHERE match(si_title, si_text) against (<TERM>) ORDER BY rel desc LIMIT 10; Term InnoDB (time to execute) MyISAM(time to execute) Times Faster family 0.5 sec 5.05 sec 10.1 family film 0.95 sec 25.39 sec 26.7 Pizza restaurant orange county califormia 0.93 sec 32.03 sec 34.4 President united states of america 2.5 sec 36.98 sec 14.8 Table 6: SELECT COUNT(*) mysql> SELECT count(*) FROM si_<TB> WHERE match(si_title, si_text) against (<TERM>) LIMIT 10; Term InnoDB (time to execute) MyISAM(time to execute) Times Faster family 0.47 sec 82 sec 174.5 family film 0.83 sec 131 sec 157.8 Pizza restaurant orange county califormia 0.74 sec 106 sec 143.2 President united states of america 1.96 sec 220 sec 112.2 Again, table 3 to table 6 all showing InnoDB consistently outperform MyISAM in these queries by a large margin. It becomes obvious the InnoDB has great advantage over MyISAM in handling large data search. Summary: These results demonstrate the great performance we could achieve by making MySQL optimizer and InnoDB Full-Text Search more tightly coupled. I think there are still many cases that InnoDB’s result info have not been fully taken advantage of, which means we still have great room to improve. And we will continuously explore the area, and get more dramatic results for InnoDB full-text searches. Jimmy Yang, September 29, 2012

Search Results

Search found 29702 results on 1189 pages for 'select insert'.

Page 139/1189 | < Previous Page | 135 136 137 138 139 140 141 142 143 144 145 146 | Next Page >

- by Anon

- by Anon

- by Mahmoud Abdelkader

- by JosephStyons

- by Arvind Singh

- by jpjp

- by user291071

- by Testifier

- by Aamir Hasan

- by Tomaz.tsql

- by udaya

- by Tunji Gbadamosi

- by user1300580

- by Matthew Guay

- by janoChen

- by Liu Maclean(???)

- by acidzombie24

- by P a u l

- by JM4

- by Chris Stevenson

- by Pawel Krakowiak

- by Most Valuable Yak (Rob Volk)

- by Farid Z

- by joachim

- by Calvin Sun

< Previous Page | 135 136 137 138 139 140 141 142 143 144 145 146 | Next Page >