Wednesday, January 30, 2013

Basic Format Specifiers in iOS SDK

Format specifiers are the percent character, followed by a letter, such as %d or %f that tell NSLog or printf() to print the value or result of a variable, value, and/or expression.
Here is a list of Objective-C format specifiers:

%@Objective-C object, printed as the string returned by descriptionWithLocale: if available, or description otherwise. Also works with CFTypeRef objects, returning the result of the CFCopyDescription function.
%%'%' character.
%d, %DSigned 32-bit integer (int).
%u, %UUnsigned 32-bit integer (unsigned int).
%xUnsigned 32-bit integer (unsigned int), printed in hexadecimal using the digits 0–9 and lowercase a–f.
%XUnsigned 32-bit integer (unsigned int), printed in hexadecimal using the digits 0–9 and uppercase A–F.
%o, %OUnsigned 32-bit integer (unsigned int), printed in octal.
%f64-bit floating-point number (double).
%e64-bit floating-point number (double), printed in scientific notation using a lowercase e to introduce the exponent.
%E64-bit floating-point number (double), printed in scientific notation using an uppercase E to introduce the exponent.
%g64-bit floating-point number (double), printed in the style of %e if the exponent is less than –4 or greater than or equal to the precision, in the style of %f otherwise.
%G64-bit floating-point number (double), printed in the style of %E if the exponent is less than –4 or greater than or equal to the precision, in the style of %f otherwise.
%c8-bit unsigned character (unsigned char), printed by NSLog() as an ASCII character, or, if not an ASCII character, in the octal format \\ddd or the Unicode hexadecimal format \\udddd, where d is a digit.
%C16-bit Unicode character (unichar), printed by NSLog() as an ASCII character, or, if not an ASCII character, in the octal format \\ddd or the Unicode hexadecimal format \\udddd, where d is a digit.
%sNull-terminated array of 8-bit unsigned characters. Because the %s specifier causes the characters to be interpreted in the system default encoding, the results can be variable, especially with right-to-left languages. For example, with RTL, %s inserts direction markers when the characters are not strongly directional. For this reason, it’s best to avoid %s and specify encodings explicitly.
%SNull-terminated array of 16-bit Unicode characters.
%pVoid pointer (void *), printed in hexadecimal with the digits 0–9 and lowercase a–f, with a leading 0x.
%a64-bit floating-point number (double), printed in scientific notation with a leading 0x and one hexadecimal digit before the decimal point using a lowercase p to introduce the exponent.
%A64-bit floating-point number (double), printed in scientific notation with a leading 0X and one hexadecimal digit before the decimal point using a uppercase P to introduce the exponent.
%F64-bit floating-point number (double), printed in decimal notation.

Friday, January 25, 2013

Private assembly and Global assembly in DotNet



What is an assembly in .NET?

An assembly is a fundamental unit of any .NET application. It contains the code that is executed by CLR (common language runtime). I would like to limit the details to what is required to create and use an assembly. For more information about all nitty gritties of an assembly, please refer to MSDN documentation.
However, it is important to know a few details about assemblies before we delve into creating and using it.
  • An assembly contains name, version, types (classes and others) created in it and details about other assemblies it references.
  • An assembly may be either an executable file - .EXE or a dynamic link library - .DLL

Structure of an Assembly

The following is the content of an assembly. Each assembly contains first three parts. Fourth part may not be present in all assemblies. It is used primarily for localization - using resources according to the country or region.
  • Assembly Metadata or Manifest
  • Type Metadata
  • MSIL Code
  • Resources

Assembly Metadata or Manifest

This contains information about the assembly. Remember, assemblies in .NET are self-describing. They contain all the information that .NET needs to use them. Assembly metadata contains the following details of an assembly:
  • Assembly name
  • Version number of the assembly, which has four numbers in the format major.minor.revison.build
  • Culture - language assembly supports
  • Strong name - required only for global assemblies
  • List of files in the assembly. An assembly can be made up of multiple files
  • Type reference information - informs which type is in which file of the assembly
  • Information about referenced assemblies - Contains list of other assemblies referenced by this assembly. For each assembly referenced we have assembly name, version, culture and public key (if assembly is a global assembly)

Type metadata

This section of an assembly contains information about all classes, structure etc. created in the assembly.

MSIL Code

MSIL code of the assembly is placed in third part of the assembly. This MSIL is converted to native code by CLR at runtime.

Resource

This section contains messages and pictures used by assembly.

How to create an assembly in C#

The following are the steps to create a private assembly (by default all assemblies are private) using Visual C# 2005 Express Edition.
  1. Select File->New Project
  2. From Templates, select Class Library
  3. Enter name CounterLibrary
  4. A class library is created using a single class Class1
  5. Rename class to Counter and add the following code.
    namespace CounterLibrary
    {
        public class Counter
        {
            protected int v = 0;
            public Counter(int v)
            {
                this.v = v;
            }
            public int Value
            {
                get
                {
                    return v;
                }
            }
        }
    }
    
  6. Save project using File->Save All. When prompted to enter location for project, select the folder where you want to save your project. I use c:\csharp. Do not select checkbox forCreate directory for solution
  7. Build (not run) the project using Build->Build Solution
After the above process, we get CounterLibrary.dll assembly placed in c:\csharp\counterlibrary\bin\release directory.

Using a private assembly in a console application developed in C#

Now, let us use the class library created in C# in a console application. Though I am using a console application in C#, you can use any language supported by .NET. 
  1. Start Visual C# 2005 Express Edition
  2. Create a new console application using File -> New Project
  3. From template select Console Application as type of project
  4. Give name UseCounter for application.
    A new application is created with a single class with Main() method.
  5. Go to Solution Explorer and select project
  6. Right click on it and select Add References from the context menu.
  7. From dialog box, select Browse tab and select c:\csharp\counterlibrary\bin\release\counterlibrary.dll
  8. Solution explorer displays counterlibrary as one of the references under references node in solution explorer
  9. Add the following code in Main() method of Program.cs
    using System;
    namespace UseCounter
    {
        class Program
        {
            static void Main(string[] args)
            {
                counterlibrary.Counter c = new counterlibrary.Counter(100);
                c.Inc();
                Console.WriteLine(c.Value);
            }
        }
    }
    
As you do the above, you can notice that a copy of counterlibrary.dll is copied into BIN directory of UseCounter application. This is the case with any private library. Whenever an application makes a reference to it, a copy of private assembly is copied into it's bin directory.

If you do not see .DLL file that is copied to BIN directory of console application (UseCounter), close the application and reopen it.

Making a private assembly a global assembly

A global assembly is a public assembly that is shared by multiple applications. Unlike private assembly, a global assembly is not copied to bin directory of each application that references it. Global assembly instead is placed in GAC (Global Assembly Cache) and it can be referenced anywhere within the system. So only one copy is stored, but many applications can use that single copy.
In order to convert a private assembly to global assembly, we have to take the following steps.
  • Create a strong name
  • Associate strong name with assembly
  • Place assembly in GAC

Creating a strong name

Any assembly that is to be placed in GAC, must have a strong name. Strong name is a combination of public key and private key. The relationship between public and private keys are such, given one you cannot get the other, but any data that is encrypted with private key can be decrypted only with the corresponding public key.
Take the following steps to invoke SN (Strong Name) tool to create strong name.
  1. Go to command prompt using Microsoft .NET Framework SDK v2.0 -> SDK Command prompt
  2. Go to c:\csharp\counterlibrary folder and enter the following command.
  3. sn -k srikanth.key
    
    The above command writes private and public key pair into srikanth.key file.

Associate strong name with assembly

Once private and public keys are generated using SN tool, use the following procedure to sign counterlibrary with the key file.
  1. Open counterlibrary project.
  2. Select project properties using Project -> counterlibrary properties
  3. Select Signing tab in project properties window
  4. Check Sign the assembly check box
  5. Select srikanth.key file using Choose a strong name key file combo box
  6. Close properties window
  7. Build the solution again using Build->Build Solution
Now, counterlibrary.dll is associated with a public key and also digitally signed with private key. This ensures no one can modify this assembly as any change to assembly should re-sign the assembly with private key of the user who created it first. This protects the assembly from getting tampered with by others. A global assembly needs this projection as it is placed in common place.
You can verify whether the assembly is associated with public key using ILDASM (IL Disassembler) program provided by .NET Framework.
  • Start ILDASM using .NET Framework SDK v2.0->Tools->MSIL Disassembler
  • Select counterlibrary.dll using File->Open
  • Once assembly is opened, double click on Manifest section of the assembly to see the public key associated with the assembly.

Place assembly in GAC

In order to make an assembly a global assembly, the assembly must be associated with a strong name and then placed in Global Assembly Cache (GAC).
GAC is a folder with name Assembly in windows folder of your system. So, place counterlibrary.dll in GAC using GACUTIL tool as follows.
c:\csharp\counterlibrary\bin\Release>gacutil -i counterlibrary.dll
After you install global assembly into GAC, you can see counterlibrary.dll in windows/assembly folder.
Once, you place an assembly in GAC, any reference to the assembly will not create a copy of the assembly in BIN directory of the application. Instead all application that reference the assembly use the same copy that is placed in GAC. 


Thursday, January 24, 2013

Getting Hardware Info in VB.Net



Function for getting hardware specific info such as CPU ID, Motherboard Serial Number, Drive Serial Numbers and MAC address.



'*******************************************************************************************
Imports System
Imports System.Management
Public Class clsComputerInfo
Friend Function GetProcessorId() As String
Dim strProcessorId As String = String.Empty
Dim query As New SelectQuery("Win32_processor")
Dim search As New ManagementObjectSearcher(query)
Dim info As ManagementObject
For Each info In search.Get()
strProcessorId = info("processorId").ToString()
Next
Return strProcessorId
End Function
Friend Function GetMACAddress() As String
Dim mc As ManagementClass = New ManagementClass("Win32_NetworkAdapterConfiguration")
Dim moc As ManagementObjectCollection = mc.GetInstances()
Dim MACAddress As String = String.Empty
For Each mo As ManagementObject In moc
If (MACAddress.Equals(String.Empty)) Then
If CBool(mo("IPEnabled")) Then MACAddress = mo("MacAddress").ToString()
mo.Dispose()
End If
MACAddress = MACAddress.Replace(":", String.Empty)
Next
Return MACAddress
End Function
Friend Function GetVolumeSerial(Optional ByVal strDriveLetter As String = "C") As String
Dim disk As ManagementObject = New ManagementObject(String.Format("win32_logicaldisk.deviceid=""{0}:""", strDriveLetter))
disk.Get()
Return disk("VolumeSerialNumber").ToString()
End Function
Friend Function GetMotherBoardID() As String
Dim strMotherBoardID As String = String.Empty
Dim query As New SelectQuery("Win32_BaseBoard")
Dim search As New ManagementObjectSearcher(query)
Dim info As ManagementObject
For Each info In search.Get()
strMotherBoardID = info("SerialNumber").ToString()
Next
Return strMotherBoardID
End Function
End Class
'*******************************************************************************************

Friday, January 18, 2013

Parsing XML data with NSXMLParser


Objective C NSXMLParser is an event driven parser. When an instance of this class pareses an XM document it notifies its delegate about the items (elements, attributes, etc) that it encounters during XML processing. 

XML document structure used in this example:

<users>
 <user>
  <userName>AAaa</userName>
  <firstName>AA</firstName>
  <lastName>aa</lastName>
 </user>
 <user>
  <userName>BBbb</userName>
  <firstName>BB</firstName>
  <lastName>bb</lastName>
 </user>
...
</users>




Create a delegate for NSXMLParser

Let's create a delegate for the parser XMLParser.h - it implements the three events reported by NSXMLParser object:

#import <Foundation/Foundation.h>

@class User;
@interface XMLParser : NSObject {
NSMutableString *currentElementValue; // an ad hoc string to hold element value
User *user; // user object
NSMutableArray *users; // array of user objects
}

@property (nonatomic, retain) User *user;
@property (nonatomic, retain) NSMutableArray *users;

- (XMLParser *) initXMLParser;

@end

The delegate implementation – XMLParser.m file:

#pragma mark Parse the start of an element
#pragma mark -

- (void)parser:(NSXMLParser *)parser didStartElement:(NSString *)elementName
namespaceURI:(NSString *)namespaceURI
qualifiedName:(NSString *)qualifiedName
attributes:(NSDictionary *)attributeDict {
if ([elementName isEqualToString:@"user"]) {
NSLog(@"user element found – create a new instance of User class...");
user = [[User alloc] init];
//We do not have any attributes in the user elements, but if
// you do, you can extract them here:
// user.att = [[attributeDict objectForKey:@"<att name>"] ...];
}
}

#pragma mark Parse an element value
#pragma mark -

- (void)parser:(NSXMLParser *)parser foundCharacters:(NSString *)string {
if (!currentElementValue) {
// init the ad hoc string with the value
currentElementValue = [[NSMutableString alloc] initWithString:string];
} else {
// append value to the ad hoc string
[currentElementValue appendString:string];
}
NSLog(@"Processing value for : %@", string);
}

#pragma mark Parse the end of an element
#pragma mark -

- (void)parser:(NSXMLParser *)parser didEndElement:(NSString *)elementName
namespaceURI:(NSString *)namespaceURI
qualifiedName:(NSString *)qName {
if ([elementName isEqualToString:@"users"]) {
// We reached the end of the XML document
return;
}
if ([elementName isEqualToString:@"user"]) {
[users addObject:user];
// release user object
[user release];
user = nil;
} else {
[user setValue:currentElementValue forKey:elementName];
}
[currentElementValue release];
currentElementValue = nil;
}

NSXMLParser call example

NSData *data contains our XML document.

#import "XMLParser.h"

- (void) doParse:(NSData *)data {
NSXMLParser *nsXmlParser = [[NSXMLParser alloc] initWithData:data]; // create and init NSXMLParser object
XMLParser *parser = [[XMLParser alloc] initXMLParser]; // create and init our delegate
[nsXmlParser setDelegate:parser]; // set delegate
BOOL success = [nsXmlParser parse]; // parsing...
if (success) {
NSLog(@"No errors - user count : %i", [parser [users count]]);
// get array of users here
NSMutableArray *users = [parser users];
} else {
NSLog(@"Error parsing document!");
}
[parser release];
[nsXmlParser release];
}




Thursday, January 17, 2013

SQL Basics


This page contains the essential commands to manage databases using SQL (Structured Query Language). I use it as a refference for myself. I don't pretend to be an SQL-expert. Others may use this reference freely. If you have any suggestions or comments please e-mail me!
Some commands depend on the type of server used. I experiment with four servers, Microsoft SQL Server, Borland Interbase Server, MySQL Server en Sybase SQL Anywhere. If a command is specific to one server I indicate this with an icon.
  1. mssql.gif indicates that the command works with MS SQL Server.
  2. ibsql.gif indicates the command works with Borland InterBase Server.
  3. mysql.gifindicates that the command works with MySQL Server.
  4. indicates that the command works with Sybase SQL Anywhere.
The commands are written in bold, italicised text (databasenames, fieldnames, values, ...) is up to you to fill in. Note also that I speak of fields and records instaid off rows and coloms. In MS SQL Server manuals I see they use rows and coloms, but I prefer to use fields and records, probably because that was the way in dBase and MS Access, which I used for years.

1. List all the databases on the server:

sp_databases mssql.gif - 284 Bytes
show databases mysql.gif - 322 Bytes

2. Select a database to use:

use databasename mssql.gif - 284 Bytes mysql.gif - 322 Bytes

3. List tables in the database:

show tables mysql.gif - 322 Bytes
sp_help mssql.gif - 284 BytesThere is another, more complicated, command to retrieve the names of the tables in the database, but where you get a list of ALL tables, including system tables using sp_help, you can specify to view only user defined tables
select * from SYSOBJECTS mssql.gif - 284 Bytes
where TYPE = 'U'
order by NAMEibsql.gif - 299 Bytes If you want to see the tables of an InterBase database use the menu "Metadata", "Show..." and select "Tables" in the "View Information on"-dropdown. Click "OK" and you see the tables of the active database. Using ISQL you can use show tables
 To see the tables in Sybase press functionkey F7.

4. List fields in a table:

describe tabelname mysql.gif - 322 Bytes
sp_help tablename mssql.gif - 284 Bytesibsql.gif - 299 Bytes To see the fields of an InterBase database use the menu "Metadata", "Show..." and select "Tables" in the "View Information on"-dropdown. Type the name of the database in the "Object name"-textfield. Click "OK" and you see the fields of the specified table. Using ISQL you can use show table tablename
 To see the fields in a Sybase table press functionkey F7, select the table and click "Columns".

5. Create a database:

create database databasename mssql.gif - 284 Bytes
on
(name='databasename_dat',
filename='c:\mssql7\data\databasename_dat.mdf',
size=2mb,
filegrowth=1mb)
log on
(name='databasename_log',
filename='c\mssql7\data\databasename_log.ldf',
size=2mb,
filegrowth=1mb)

6. Create a table in the database en store values in it:

create table tablename mssql.gif - 284 Bytes ibsql.gif - 299 Bytes mysql.gif - 322 Bytes 
(fieldname1 datatype1, fieldname2 datatype2, ...)
insert into tablename
values(fieldvalue1, fieldvalue2, ...)

7. Constraints:

  • Primary key: constraint constraintname primary key (non)clustered (fieldname1, fieldname2, ...)
  • Foreign key: constraint constraintname foreign key (fieldname) references tablename(fieldname)
  • No dulicates: constraint constraintname unique nonclustered (fieldname)
  • Check: Add values to the fieldnamelist in the 'create table'-command:
    (fieldname1 datatype1 check (fieldname1 in ('value1', 'value2', ...)), fieldname2 datatype2, ...)
  • Default value: Add default value to the fieldlist in the 'create table'-command:
    (fieldname1 datatype1, fieldname2 datatype2 default 'value', fieldname3 datatype3, ...)

    8. Select all records from table:

    select * from tablename mssql.gif - 284 Bytes ibsql.gif - 299 Bytes mysql.gif - 322 Bytes 
    order by fieldnameNote that this command could take some time and put a lot of stress on the processor and memory of the server if you try to view a large database this way. On my server (Pentium MMX 233 Mhz - 64 Mb memory) it took 1:25 minutes to retrieve 25000 records from a database and it gave warnigs of shortage in virtual memory, so please don't try this on a production database (;-). Usualy it is better to select fields and conditions to limit the amount of records and the stress on the server.

    9. Select set of records from table:

    select fieldname1, fieldname2, ... from tablename mssql.gif - 284 Bytes ibsql.gif - 299 Bytes mysql.gif - 322 Bytes 
    where fieldname = "condition"
    order by fieldname
    Comparison Operators that can be used in the condition are:
    =, !=, <>, <, >, <=, >= and LIKE.
    With LIKE you can specify e.g. all the names beginning with S as 'name LIKE "s%"'.
    You can also use boolean operators to specify more then one condition (OR, AND, NOT, BETWEEN, IN). With 'BETWEEN' you give a minimum and maximum value to the condition, with 'IN' you can give a list of values.

    Example:

    select Firstname, Lastname, Department from Employees
    where Lastname LIKE "Van%" AND
    Badge BETWEEN 121990 and 141990 AND
    Department IN ("Sales", "Logistics")
    order by Lastname
    This statement retrieves all the employees where Lastname begins with "Van", whose badgenumbers are between 121990 and 141990, who work at the Sales- or Logisticsdepartment, and displays their Lastname, Firstname and Dapartment ordered by Lastname.

    Note theat the 'ORDER BY' statemant can have more then one fieldname and can also take 'asc' or 'desc' as last argument (for ascending or descnding order).

    10. Retrieve unique values

    select distinct fieldname from table mssql.gif - 284 Bytes ibsql.gif - 299 Bytes mysql.gif - 322 Bytes 

    11. Add records to a table:

    insert into tablename (fieldname1, fieldname2, ...) mssql.gif - 284 Bytes ibsql.gif - 299 Bytes mysql.gif - 322 Bytes 
    values ('value1', 'value2', ...)
    If you leave out the fieldnames then the values must match the number of fields in the table. If you only want to add a value to some fields you have to specify them and the values must mach the number of specified fields. The unspecified fields are filled with NULL or the default constraint defined in the table. You could concider to specify defaults like "unknown" or "N/A" to avoid a NULL-value in the field.

    12. Updating records in a table;

    update tablename mssql.gif - 284 Bytes ibsql.gif - 299 Bytes mysql.gif - 322 Bytes 
    set fieldname1='value1', fieldname2='value2', ...
    where fieldname3 = condition
    If you ommit the 'where'-statement the update will be performed on ALL the records in the table!

    13. Deleting a record from a table

    delete from tablename mssql.gif - 284 Bytes ibsql.gif - 299 Bytes mysql.gif - 322 Bytes 
    where fieldname=condition
    Be arefull in using this command!! Ommitting the 'where'-statement will erae ALL the records in the table and leave you with an empty table!!!


    14. Adding a field to the records in a table:

    alter table tablename mssql.gif - 284 Bytes ibsql.gif - 299 Bytes mysql.gif - 322 Bytes 
    add fieldname datatype
    The new field is filled with NULL and can be filled using the update-command after adding the field.


    15. Changing the width of a field

    alter table tablename 
    alter column fieldname newdatatypealter table tablename   
    modify fieldname newdatatype

    16. Removing field from the records

    alter table tablename 
    drop column fieldnamealter table tablename   
    drop fieldname

    17. Combine two querries:

    select fieldname from tablename
    union
    select fieldname2 from tablename2
    order by fieldname
    This union will remove all duplicates. To retain duplicates you have to use 'union all'

    18. Basic SQL-functions:

  • select avg(fieldname) from tablename
    Returns the arithmeticaverage of the fields.
  • select count(*) from tablename
    where fieldname=condition
    Returns the number of records that match the condition.
  • select max(fieldname) from tablename
    Returns the largest value of fieldname.
  • select min(fieldname) from tablename
    Returns the smallest value of fieldname.
  • select sum(fieldname) from tablename
    Returns the summation value of fieldname.
  • select convert(newdatatypefieldname) from tablename
    converts one datatype into another.

    19. String functions

  • ASCII returns ASCII code of leftmost character of a characterexpression.
  • CHAR converts an ASCII code to a character.
  • SOUNDEX returns four-digit code used to compare two strings with DIFFERENCE.
  • DIFFERENCE returns difference between values of two character expressions returned by SOUNDEX.
  • LEFT returns part of character string, starting at number of character from the left specified in the argument.
  • LOWER converts uppercase strings to lowercase.
  • UPPER converts lowercase strings to uppercase.
  • LTRIM removes leading spaces from a string.
  • RTRIM removes trailing spaces from a string.
  • CHARINDEX returns starting position of specified character expression in a string.
  • PATINDEX rerurns starting position of first occurence of a substring in a string.
  • REPLICATE returns multiple sets of characters specified in the first argument. The second argument specifies number of sets. (eg. select replicate ('a', 5) returns 'aaaa')
  • REVERSE returns reverse order of a string of characters.
  • RIGHT returns part of character string, starting at number of character from the right specified in the argument.
  • SPACE returns a string of spaces, length specified in argument.
  • STR converts numeric data to character data.
  • STUFF inserts a string into another string.
  • SUBSTRING returns a part of a string (arguments are startpoint and length).
  • + (concatenetion) concatenates two or more strings.

    20. Arithmetic functions:

    ACOS, ASIN, ATAN, ATAN2, COS, COT, SIN, TAN, DEGREES, RADIANS, CEILING, FLOOR, EXP, LOG, LOG10, PI(), POWER, ABS, RAND, ROUND, SIGN, SQRT.

    21. TEXT and IMAGE functions:

  • SET TEXTSIZE specifies number of bytes displayed for data stored as TEXT or as IMAGE.
  • TEXTPTR returns pointer to first database page of stoed text.
  • READTEXT extracts substring from data stored as TEXT or as IMAGE.
  • TEXTVALID check validity of textpointer.

    22. Date functions:

  • DATENAME returns specified part of date as characterstring.
  • DATEPART returns specified part of date as integer value.
  • GETDATE returns current date and time.
  • DATEADD returns value of the date with an additional date interval added.
  • DATEDIFF returns difference between parts of two specified dates.

    23. Views:

    Note that a view under SQL is the same as a query you create with Access.

    23.1 Create views:

    create view viewname as
    select fieldname1, fieldname2, ... from tablename1, tablename2, ...
    where fieldname = conditionYou can't edit a view using SQL. You can use the Enterprise Manager of MS SQL to edit a view or you can delete a view and recreate it with the same name.
    You can use alternative names for the columns in a view:
    create view viewname (col1, col2, col3, ...)as
    select fieldname1, fieldname2, ... from tablename1, tablename2, ...
    where fieldname = condition

    23.2 Display definition of a view:

    sp_helptext viewnameTo prevent the possibility to view the definition of a view you can use encryption:
    create view viewname with encryption as
    select fieldname1, fieldname2, ... from tablename1, tablename2, ...
    where fieldname = condition

    23.3 Display associations of a view:

    sp_depends viewname

    23.4 Delete a view from the database:

    drop view viewname1, viewname2, ...

    23.5 Insert records through a view:

    insert into viewname
    values ('value1', 'value2', ...)You can insert rows through the view that DON'T match the WHERE-statement inthe view definition, but then you can't retrieve the new row with the view. If you want to prevent this you can use the check option:
    create view viewname as
    select fieldname1, fieldname2, ... from tablename1, tablename2, ...
    where fieldname = condition with check option

    23.6 Delete records through a view:

    delete from viewname where fieldname = conditionYou can't delete records through a view that don't match the WHERE-statement in the view definition.

    23.7 Updating records through a view:

    update viewname set fieldname='value' where fieldname=conditionYou can update a record through a view so that it doesn't match the WHERE-statement anymore. You can't update a view if the updated columns belong to different tables.

    24. Indexes:

    24.1 Simple index:

    create index index_name
    on table_name (fieldname)

    24.2 Unique index:

    create unique index index_name
    on table_name (fieldname)This kind of index enforces integrity of the table by disallowing duplicete values in the indexed field.

    24.3 Clustered index:

    create clustered index index_name
    on table_name (fieldname)A clustered index forces SQL Server to phisicaly store the table data in the exact order of of the index. This improves the performance of the table. You can only have obne clustered index on a table, the selected fieldname should be choosen carefully and every table should have an clustered index.

    24.4 Display index info:

    sp_helpindex tablename

    24.5 Deleting an index:

    drop index table_name.index_name

    24.6 Primary and foreign keys:

    You can specify a primary key on a table while creating it (see constraint), or you can add a primary key to an existing table by altering it. alter table table_name add constraint constraint_name primary key

    24.7 Display primary and foreign keys info:

    sp_helpconstraint table_name

    24.6 Deleting primary and foreign keys:

    alter table table_name drop constraint constraint_name

    25. Transaction:

    A transaction is a series of SQL-statements performed at once, with the ability to undo the changes if something goes wrong during the processing of the statements. A transaction is enclosed in "begin tran" and "commit tran".Example:
    begin tran
     update tablename
     set fieldname = newvalue
     where fieldname = condition
    
     update tablename2
     set fieldname =  newvalue
     where fieldname = condition
    
     if @@error != 0
     begin
      rollback tran
      print 'Error occured, no rows updated'
      return
     end
    commit tran
    You can nest one transaction ino another by using named transactions. Be shure however to include a 'commit tran' for every 'begin tran' to avoid leaving a transaction open.
    Example:
    begin tran tran1_name
     update tablename
     set fieldname = newvalue
     where fieldname = condition
    
      begin tran tran2_name
       update tablename
       set fieldnname = newvalue
       where fieldname = condition
       if @@error != 0
       begin
        rollback tran tran2_name
        print 'Failed to update tablename'
        return
       end
      commit tran tran2_name
    commit tran tran1_name
    

    26. Rules:

    A rule controls the values that can be entered into a field of a table. To apply a rule to a field you have to take two steps: create the rule and bind the rule to the field.

    26.1 Create a rule:

    create rule rule_name
    as @fieldname in ('value1','value2','value3')

    26.2 Bind a rule:

    sp_bindrule 'rule_name', 'table_name.field_name'

    26.3 Unbind a rule:

    sp_unbindrule table_name.field_name

    26.4 Drop a rule:

    drop rule rule_name
    !!You first have to unbind the rule before you can drop it!!To display rule bindings you can use 'sp_help tablename', to display the rules you can use 'sp_helptext rule_name' and to rename a rule you can use 'sp_rename rule_name, new_name'.

    27. Defaults:

    A default is create to provide a value for a field if the user doesn't fill in one. Just like creating rules you have to make to steps to apply ad efault: create it and bind it to a field in a table.

    27.1 Create a default:

    create default default_name as value

    27.2 Bind a default:

    sp_bindefault default_name, 'tablename.fieldname'

    27.3 Unbind a default:

    sp_unbindefault 'tablename.fieldname'

    27.4 Drop a default:

    drop default default_name
    !!You first have to unbind the default before you can drop it!!To display default bindings you can use 'sp_help tablename', to display the default you can use 'sp_helptext default_name' and to rename a default you can use 'sp_rename default_name, new_name'.

    28. Stored procedures:

    Stored procedures are routines or series of SQL-commands that run on the server side. The benefits are performance, because the server typically is the most powerfull computer on the network, and security, because you can control add/change/delete/list operations.

    28.1 Create a stored procedure:

    create procedure procedure_name
    as procedure_commandsTo execute the procedure you use 'exec procedure_name'. You can use parameters in procedures to pass values to the procedure:
    create procedure procedure_name (@p1 type, @p2 type, ...)
    as insert into table_name
    values (@p1, @p2, ...)
    Now you can execute this procedure by passing values to it:
    exec procedure_name (value1, value2, ...)
    You can also use variables in a procedure. You first have to declare them (declare @var_name var_type) and then you can assign a value to it (select @var_name = expression).

    28.2 Display a stored procedure:

    sp_helptext procedure_name

    28.3 Delete a stored procedure:

    drop procedure procedure_nameIf you want to alter a procedure, you first have to drop it and then recreate it under the same name.

    28.4 Procedure Auto Execution:

    You can automaticaly run a procedure every time the server is started by using:sp_makestartup procedure_name
    sp_unmakestartup procedure_name removes the procedure from the startupprocedures
    sp_helpstartup displays the procedures currently running at startup

    28.5 Flow-control statements:

    The following statements can be used in stored procedures to control the flow of the procedure:
    • if ... else ...
    • begin ... end
    • while ...
    • break
    • continue
    • print
    • goto
    • return
    • raiserror
    • waitfor
    • case ... when

    29. Datatypes:

    This is an overview of the different datatypes used by the different servers.
    MySQLBorland InterBase ServerMS SQL ServerSybase SQL
    tinyint-tinyinttinyint
    smallintsmallintsmallintsmallint
    mediumint---
    intintintint
    bigint---
    -numericnumericnumeric
    decimaldecimaldecimaldecimal
    --realreal
    floatfloatfloatfloat
    doubledouble precision-double
    datedate-date
    --smalldatetime-
    datetime-datetime-
    timestamp-timestamptimestamp
    time--time
    year---
    char(n)char(n)char(n)char(n)
    varchar(n)varchar(n)varchar(n)varchar(n)
    ---long varchar
    tinytext---
    text-text-
    --ntext-
    mediumtext---
    longtext---
    -nchar(n)nchar(n)-
    -nchar varying(n)nvarchar(n)-
    --binary(n)binary(n)
    --varbinary(n)-
    ---long binary
    tinyblob---
    --image-
    blobblob--
    mediumblob---
    longblob---
    enum---
    set---
    --bit-
    --uniqueidentifier-
    --money-
    --smallmoney-
  • Swift Operators - Basic Part 3 (Range operators in swift)

    Range Operators: Closed Range operators  Half-Open Range Operators One-Sided Ranges Closed Range Operators:  a...b It defines...