How-To Tutorials

In this two-part tutorial by Jonathan Williamson, we are going to look at how to model a character head in Blender. Along with basic modeling tools we will also focus heavily on good topology and how to create a clean mesh that will deform well during animation. This tutorial will take you through the whole process from setting up a background image as a reference, to laying out the topology, to tweaking the final model proportions and mesh structure. First Steps: workspace and references Before we get started with Blender character modeling, the first thing we want to do is make our workspace more efficient. The way I like to do this is to simply split my view down the center, putting the resulting left viewport in front view (numpad 1) and the right viewport in side view (numpad 3). If you're not familiar with how to split your view, please reference this short video tutorial. This allows us to work from both sides of the model at the same time without having to switch our view constantly. It also gives us more views of the model to help with accuracy and proportion. Now that we have our workspace setup, let's go ahead and bring in our background image for reference. Today we are going to use a simple, rough drawing of mine that has a front and side view. Anytime you are working from references (which should be almost always!) try to get as many angles as possible. This is particularly important when we work from photo references. Here is the drawing: To place the reference into the background of your workspace: Go to View > Background Image Click Use and Load to navigate to your image. Do this with both viewports. The next step is to adjust the X and Y positions to line up your image, it's best to align the center of the head from both views with the Central Axis point (where each of the three axis' meet.) Modeling: mirroring and structure With our workspace and references set up it's time to start modeling. The first thing we want to do is add a mirror modifier to the default cube so that we only have to work on one side of the model; anything we do will be mirrored across the central axis. But, before we do that, we need to add a central loop of vertices to our cube, along with deleting one half. This way we don't mirror our cube on top of itself. You can do this by: Going into Edit Mode with Tab Hit Control + R to activate the Loop Cut tool. Cut a new loop, vertically, along the cube by clicking the MMB when you see the purple line with your mouse hovered over the cube. From the Front View, make sure everything is deselected with A and then select the left-most vertices and hit X > Delete Vertices The last thing we need to do before we start modeling is adding a mirror modifier for symmetry: Go to the Edit Buttons (F9) and click Add Modifier > Mirror Click Do Clipping We are now ready to really get down to business! Modeling: edgeloop structure The single most important thing to remember while modeling a character head is the structure of your mesh. This is referred to as "topology." Edgeloops, or continuous lines or circles of edges, are the primary concern with topology. Proper edgeloops allow your model to deform well during animation; they also make tweaking and detailing your model much easier! To get started: Select the back side of the cube X > Delete Vertices We do this because we want to work from a single face. What we are going to be doing is laying out a series of edgeloops to map out the structure we want for the mesh. Let's start at the chin by moving our remaining face with G to line up with the reference from both view. Due to the variations in our drawing it is going to be necessary to compensate between the views from time to time. What we are now going to do is use the Extrude tool to lay out our loops. To do this: Select the two outside vertices with Shift + RMB Hit E > Only Edges to extrude. Extruding will automatically place you into grab mode, which allows you to place the newly created edge where you want it. In this case, along the jaw bone. You can use Rotate, Scale and Grab to help you position the edges. When you're done you should have something like this: We can continue using this same technique to get the following for the top of the head: As you can see, we are starting to define the structure of the mesh and the shape of the head, much as a traditional artist would use reference lines to sketch out a head. Before we go too much further, we need to go ahead and map out the eye, as it is one of the most important areas of the head, and it's topology is essential the rest of the mesh. To do this we are going to add a circle from the Front View: From the Front View, left click in the center of the eye to position the 3D Cursor Hit Spacebar > Add > Mesh > Circle Use 8 Vertices and a Radius of 0.500 Next you want to use your translate tools (grab, rotate, scale) to postion each of the vertices to fit the shape of the eye socket: Now with everything selected (A): Hit E > Only Edges Then immediately hit S to scale in. Use this same technique for around the nose and the mouth: That's it for the structure, this will then allow us to connect all the areas and not have to worry so much about getting the topology right as we have just laid out the major areas.

Synchronizing objects We created tables, dimensions, and a cube; and new tables were automatically created for each dimension and cube. We then created mappings to map data from tables to tables, dimensions, and a cube. What happens if, let's say for example, a table definition is updated after we've defined it and created a mapping or mappings that include it? What if a dimensional object is changed? In that case, what happens to the underlying table? This is what we are going to discuss in this section. One set of changes that we'll frequently find ourselves making is changes to the data we've defined for our data warehouse. We may get some new requirements that lead us to capture a new data element that we have not captured yet. We'll need to update our staging table to store it and our staging mapping to load it. Our dimension mapping(s) will need to be updated to store the new data element along with the underlying table. We could make manual edits to all the affected objects in our project, but the Warehouse Builder provides us some features to make that easier. Changes to tables Let's start the discussion by looking at table updates. If we have a new data element that needs to be captured, it will mean finding out where that data resides in our source system and updating the associated table definition in our module for that source system. Updating object definitions There are a couple of ways to update table definitions. Our choice will depend on how the table was defined in the Warehouse Builder in the first place. The two options are: It could be a table in a source database system, in which case the table was physically created in the source database and we just imported the table definition into the Warehouse Builder. It could be a table we defined in our project in the Warehouse Builder and then deployed to the target database to create it. Our staging table would be an example of this second option. In the first case, we can re-import the source table using the procedures generally used for importing source metadata. When re-importing tables, the Warehouse Builder will do a reconciliation process to update the already imported table with any changes it detects in the source table. For the second case, we can manually edit the table definition in our project to reflect the new data element. For the first case where the table is in a source database system, the action we choose also depends on whether that source table definition is in an Oracle database or a third-party database. If it is in a third-party database, we're going to encounter an error. Hence, we'll be forced to make manual edits to our metadata for that source until that bug is fixed. If the table is in an Oracle database, re-importing the table definition would not be a problem and it will do the reconciliation process, picking up any new data elements or changes to the existing ones. For a hands-on example here, let's turn to our new project that we created earlier while discussing snapshots. We copied our POS_TRANS_STAGE table over to this project, so let's use that table as an example of a changing table, as we defined the table structure manually in the Warehouse Builder Design Center and then deployed it to the target database to actually create it. For this example, we won't actually re-deploy it because we'll be using that second project we created. It doesn't have a valid location defined, but we can still edit the table definition and investigate how to reconcile that edit in the next section. So, let's edit the POS_TRANS_STAGE table in the ACME_PROJ_FOR_COPYING project in the Design Center by double-clicking on it to launch it in the Data Object Editor. We'll just add a column called STORE_AREA_SIZE to the table for storing the size of the store in square feet or square meters. We'll click on the Columns tab, scroll it all the way to the end, enter the name of the column, then select NUMBER for the data type, and leave the precision and scale to the default (that is 0) for this example. We can validate and generate the object without having a valid location defined, so we'll do that. The validation and generation should complete successfully; and if we look at the script, we'll see the new column included. We now need a mapping that uses that table, which we have back in our original project. Let's use the copy and paste technique we used earlier to copy the STAGE_MAP mapping over to this new project. We'll open the ACME_DW_PROJECT project, answering Save to the prompt to save or revert. Then on the STAGE_MAP mapping entry, we'll select Copy from the pop-up menu. We'll open the ACME_PROJ_FOR_COPYING project and then on the Mappings node, select Paste on the pop-up menu. We ordinarily won't copy an object and paste it into a whole new project just for making changes. We're only doing it here so that we can make changes without worrying about interfering with a working project.

Modeling: the ear Ask just about any beginning modeler (and many experienced ones) and they'll tell you that the ear is a challenge! There are so many turns and folds in the human ear that it poses a modeling nightmare. But, that being said, it is also an excellent exercise in clean modeling. The ear alone, once successfully tackled, will make you a better modeler all around. The way we are going to go about this is much the same way we got started with the edgeloops: Position your 3D Cursor at the center of the ear from both the Front and the Side views Add a new plane with Spacebar > Add > Plane Extrude along the outer shape of the ear We are working strictly from the Side View for the first bit. Use the same process of extruding and moving to do the top, inside portion of the ear: Watch your topology closely, it can become very messy, very fast! Continue for the bottom: The next step is to rotate your view around with your MMB to a nice angle and Extrude out along the X-axis: Select the main loop of the ear E > Region Before placing the new faces, hit X to lock the movement to the X-axis. From here it's just a matter of shaping the ear by moving vertices around to get the proper depth and definition on the ear. It will also save you some time editing if: Select the whole ear by hovering your mouse over it and hitting L Hit R > Z to rotate along the Z-axis Then do the same along the Y-axis, R > Y This will just better position the ear. Connecting the ear to the head can be a bit of challenge, due to the much higher number of vertices it is made up of in comparison to parts of the head. This can be solved by using some cleaver modeling techniques. Let's start by extruding in the outside edge of the ear to create the back side: Now is where it gets tricky, best to just follow the screenshot: You will notice that I have used the direction of my edges coming in from the eye to increase my face count, thus making it easier to connect the ear. One of the general rules of thumb when it comes to good topology is to stay away from triangles. We want to keep our mesh comprised of strictly quads, or faces with four sides to them. Once again, we can use the same techniques seen before, and some of the tricks we just used on the ear to connect the back of the ear to the head: You will notice that I have disabled the mirror modifier's display while in Edit Mode, this makes working on the inside of the head much easier. This can be done via the modifier panel. Final: tweaking And that's it! After connecting the ear to the head the model is essentially finished. At this point it is a good idea to give your whole model the once over, checking it out from all different angles, perspective vs. orthographic modes, etc. If you find yourself needing to tweak the proportions (almost always do) a really easy way to do it is by using the Proportional Editing tool, which can be accessed by hitting O. This allows you to move the mesh around with a fall-off, basically a magnet, such that anything within the radius will move with your selection. Here is the final model: Conclusion Thank you all for reading this and I hope you have found it helpful in your head modeling endeavours. At this point, the best thing you can do is...do it all over again! Repetition in any kind of modeling always helps, but it's particularly true with head modeling. Also, always use references to help you along. You may hear some people telling you not to use references, that it makes your work stale and unoriginal. This is absolutely not true (assuming you're not just copying down the image and calling it your own...). References are an excellent resource, for everything from proportion, to perspective, to anatomy, etc. If used properly, it will show in your work, they really do help. From here, just keep hacking away at it, thanks for reading and best of luck! Happy blending! If you have read this article you may be interested to view : Modeling, Shading, Texturing, Lighting, and Compositing a Soda Can in Blender 2.49: Part 1 Modeling, Shading, Texturing, Lighting, and Compositing a Soda Can in Blender 2.49: Part 2 Creating an Underwater Scene in Blender- Part 1 Creating an Underwater Scene in Blender- Part 2 Creating an Underwater Scene in Blender- Part 3 Creating Convincing Images with Blender Internal Renderer-part1 Creating Convincing Images with Blender Internal Renderer-part2 Textures in Blender

In this article, we will begin to see the real power and flexibility the Warehouse Builder provides us for loading a data warehouse. When we complete the mappings in this article, we will have a complete collection of objects and mappings. We can deploy and run these to build and load our data warehouse. The basic procedure to build a mapping is the same—start with adding a source and a target, and then include any operators in between needed for data flow and transformation. Let's start this article with the STORE dimension and we'll see some new operators that are involved in transformations.Let's begin by creating a new mapping called STORE_MAP. In the Design Center, we will right-click on the Mappings node of the ACME_DW_PROJECT | Databases | Oracle | ACME_DWH database and select New.... Enter STORE_MAP for the name of the mapping and we will be presented with a blank Mapping Editor window. In this window, we will begin designing our mapping to load data into the STORE dimension. Adding source and target operators Once the POS_TRANS_STAGE staging table is loaded with data, this can be used to load data into our dimensions and cube. We'll now use this POS_TRANS_STAGE table as our source table. Let's drag this table onto the mapping from the Explorer window. The target for this mapping is going to be the STORE dimension, so we'll drag this dimension from Databases | Oracle | ACME_DWH | Dimensions onto the mapping and drop it to the right of the POS_TRANS_STAGE table operator. Remember that we build our mappings from the left to the right, with source on the left and target on the right. We'll be sure to leave some space between the two because we'll be filling that in with some more operators as we proceed. Now that we have our source and target included, let's take a moment to consider the data elements we're going to need for our target and where to get them from the source. Our target for this mapping, the STORE dimension, has the following attributes for the STORE level for which we'll need to have source data: NAME STORE_NUMBER ADDRESS1 ADDRESS2 CITY STATE ZIP_POSTALCODE COUNTY REGION_NAME For the REGION level, we'll need data for the following attributes: NAME DESCRIPTION COUNTRY_NAME For the COUNTRY level, we'll need data for the following attributes: NAME DESCRIPTION The complete and fully expanded STORE dimension in our mapping appears like the following screenshot: We might be tempted to include the ID fields in the above list of data elements for populating, but these are the attributes that will be filled in automatically by the Warehouse Builder. The Warehouse Builder fills them using the sequence that was automatically created for us when we built the dimension. We don't have to be concerned with connecting any source data to them. Now that we know what we need to populate in our STORE dimension, let's turn our attention over to the POS_TRANS_STAGE dimension for the candidate data elements that we can use. In this table, we see the following data elements for populating data in our STORE dimension: STORE_NAME STORE_NUMBER STORE_ADDRESS1 STORE_ADDRESS2 STORE_CITY STORE_STATE STORE_ZIPPOSTALCODE STORE_REGION STORE_COUNTRY It is easy to see which of these attributes will be used to map data to attributes in the STORE level of the STORE dimension. They will map into the corresponding attributes in the dimension in the STORE group. We'll need to connect the following attributes together: STORE_NAME to NAME STORE_NUMBER to STORE_NUMBER STORE_ADDRESS1 to ADDRESS1 STORE_ADDRESS2 to ADDRESS2 STORE_CITY to CITY STORE_STATE to STATE STORE_ZIPPOSTALCODE to ZIP_POSTALCODE STORE_REGION to REGION_NAME There is another attribute in our STORE dimension that we haven't accounted for yet—the COUNTY attribute. We don't have an input attribute to provide direct information about it. It is a special case that we will handle after we take care of these more straightforward attributes and will involve the lookup table that we discussed earlier in the introduction of this article. We're not going to directly connect the attributes mentioned in the list by just dragging a line between each of them. There are some issues with the source data that we are going to have to account for in our mapping. Connecting the attributes directly like that would mean the data would be loaded into the dimension as is, but we have investigated the source data and discovered that much of the source data contains trailing blanks due to the way the transactional system stores it. Some of the fields should be made all uppercase for consistency. Given this additional information, we'll summarize the issues with each of the fields that need to be corrected before loading into the target and then we'll see how to implement the necessary transformations in the mapping to correct them: STORE_NAME, STORE_NUMBER: We need to trim spaces and change these attributes to uppercase to facilitate queries as they are part of the business identifier STORE_ADDRESS1, ADDRESS2, CITY, STATE, and ZIP_POSTALCODE: We need to trim spaces and change the STATE attribute to uppercase STORE_REGION: We need to trim spaces and change this attribute to uppercase All of these needs can be satisfied and we can have the desired effect by applying pre-existing SQL functions to the data via Transformation Operators.

The Painter interface Painter defnes the fundamental interface for all objects that are meant to draw backgrounds or to render on a glass pane. This interface declares  only one method—public void paint(Graphics g, Rectangle rect)—for drawing inside the bounding rectangle (specifed by rect) of a component. The library provides a class that implements Painter and is used as a default background painter for widgets and containers. This is the BackgroundPainter class that has (you guessed it) just the one method paint, which either paints the background image if one has been assigned or fills in the bounding rectangle of the component with the color set in its style. When we want to paint a background ourselves, we can write our own class that implements Painter, and set it as the background painter for the relevant component. The DemoPainter MIDlet, discussed in the next section, shows how this is done. The DemoPainter application This application creates a combo box and uses a theme to set the style for the various elements that are displayed. When the application is compiled without setting a custom background painter, the combo box looks as shown in the following screenshot: The MIDlet code has the following statement commented out in the MIDlet. When uncommented, this statement sets an instance of ComboBgPainter as the background painter for the combo box. combobox.getStyle().setBgPainter(new ComboBgPainter(0x4b338c)); The recompiled application produces the following display showing the new background color: The class responsible for drawing the background is ComboBgPainter, which implements Painter. The constructor for this class takes the color to be used for background painting as its only parameter. The paint method determines the coordinates of the top-left corner of the rectangle to be painted and its dimensions. The rectangle is then flled using the color that was set through the constructor. class ComboBgPainter implements Painter{ private int bgcolor; public ComboBgPainter(int bgcolor) { this.bgcolor = bgcolor; } public void paint(Graphics g, Rectangle rect) { g.setColor(bgcolor); int x = rect.getX(); int y = rect.getY(); int wd = rect.getSize().getWidth(); int ht = rect.getSize().getHeight(); g.fillRect(x, y, wd, ht); }} Drawing a multi-layered background In actual practice, there is hardly any point in using a custom painter just to paint a background color, because the setBgColor method of Style will usually do the job. Themes too can be used for setting background colors. However, painters are very useful when intricate background patterns need to be drawn, and especially if multiple layers are involved. PainterChain, described in the next section, is a class designed for handling such requirements. The PainterChain class It is possible to use more than one painter to render different layers of a background. Such a set of painters can be chained together through the PainterChain class. The only constructor of this class has the form public PainterChain(Painter[] chain) where the parameter chain is an array of painters. The contents of chain will be called sequentially during the painting of a background, starting from the element at index 0 to the last one. There are two methods of the PainterChain class that provide support for adding painters to the array underlying the chain. A new painter can be added either to the top (the prependPainter method) or at the end (the addPainter method) of the array. The array itself can be accessed through the getChain method. PainterChain implements Painter so that the setBgPainter method can be used to set a PainterChain as well as a lone painter, which means the paint method also is present here. The function of paint in PainterChain is to call the paint methods of the painter array elements one by one starting at index 0. The DemoPainterChain application that comes up next shows how a chain of painters can be used to draw the multiple layers of a background. The DemoPainterChain application The DemoPainterChain example uses alphaList to show a painter chain in action. After organizing the form and the list, we set up a painter array to hold the three painters that we shall deploy. Painter[] bgPainters = new Painter[3]; Once we have the array, we create three painters and load them into the array. The frst (lowest) painter, which will fll the bounding rectangle for the list with a designated color, goes in at index 0. The next (middle) layer, at index 1, will draw an image at the center of the list. Finally, the topmost layer for writing a text a little below the center line of the list is inserted at index 2. bgPainters[0] = new Eraser(0x334026);try{ bgPainters[1] = new ImagePainter(Image.createImage( "/a.png"));}catch(java.io.IOException ioe){}bgPainters[2] = new TextPainter("This is third layer"); Now we are ready to instantiate a PainterChain object, and install it as a background painter for the list. PainterChain bgChain = new PainterChain(bgPainters);alphaList.getStyle().setBgPainter(bgChain); The list itself will be drawn on top of these three layers, and the background layers will be visible only because the list is translucent as determined by the transparencyvalue 100, set by the AlphaListRenderer instance used to render alphaList. The list now looks as shown in the following screenshot: A close inspection of the screenshot that we have just seen will show that the layers have indeed been drawn in the same sequence as we had intended. The three painters are very similar in structure to the ComboBgPainter class we came across in the previous example. The Eraser class here is virtually identical to ComboBgPainter. The other two classes work in the same way, except for the fact that TextPainter draws a line of text, while ImagePainter draws an image. class TextPainter implements Painter{ private String text; TextPainter(String text) { //set the text to be written this.text = text; } public void paint(Graphics g, Rectangle rect) { //get the dimension //of background int wd = rect.getSize().getWidth(); int ht = rect.getSize().getHeight(); //create and set font for text Font textFont = Font.createSystemFont( Font.FACE_PROPORTIONAL,Font.STYLE_BOLD,Font.SIZE_LARGE); g.setFont(textFont); //set text color g.setColor(0x0000aa); //position text slightly below centerline int textX = wd/2 - textFont.stringWidth(text)/2; int textY = ht/2 - textFont.getHeight()/2 + 3; //write text g.drawString(text, textX, textY); }}class ImagePainter implements Painter{ private Image bImage; ImagePainter(Image bImage) { //set the image to be drawn this.bImage = bImage; } public void paint(Graphics g, Rectangle rect) { //get the dimensions //of background int wd = rect.getSize().getWidth(); int ht = rect.getSize().getHeight(); //position image at center int imageX = wd/2 - bImage.getWidth()/2; int imageY = ht/2 - bImage.getHeight()/2; //draw image g.drawImage(bImage, imageX, imageY); }} When an image is used on the background of a form, we have seen that it is scaled to occupy the entire form real estate. But if the same image is used as an icon for a label, then it is drawn in its actual size. This task of scaling the image for backgrounds is taken care of by BackgroundPainter, which is used as the default bgPainter.

Registering event callbacks The three Java interfaces for retrieving event callbacks from within a report are ReportHyperlinkListener, ReportActionListener, and ReportMouseListener. The ReportHyperlinkListener and ReportActionListener require special properties defined in your report definition, while the ReportMouseListener is a lower level event that is always called within the report PreviewDialog. All the codes used in this article can be accessed from here. ReportHyperlinkListener The org.pentaho.reporting.engine.classic.core.modules.gui.base.event.ReportHyperlinkListener interface defines the following API callback method: void hyperlinkActivated(ReportHyperlinkEvent event); This method is called whenever someone clicks on an element within a report that has defined the link url style attribute. The ReportHyperlinkEvent provides additional metadata about the click, including the source node in the report, the hyperlink target definition, as well as the window and title hyperlink definition: // The PreviewPane objectpublic Object getSource();// The reporting node objectpublic RenderNode getSourceNode();// The defined style attribute urlpublic String getTarget();// The defined style attribute windowpublic String getWindow();// The defined style attribute titlepublic String getTitle(); ReportHyperlinkListener implementations are registered by calling PreviewPane.addReportHyperlinkListener(listener). The PreviewPane object is accessible after defining a PreviewDialog by calling PreviewDialog.getPreviewPane(). One use case for implementing ReportHyperlinkListener is to allow report linking from one high level report to more detailed reports within a Java application. Hyperlinks may appear differently when rendering in HTML and Swing. Make sure to preview your report to verify that it renders correctly. ReportActionListener In addition to receiving ReportHyperlinkListener events, the Swing PreviewDialog may also receive special events triggered only in the Swing environment. This is done by specifying the swing action attribute on an element. The swing action attribute type is a string. When an element within a report defines the action attribute, and a user clicks on that element, an event is fired and all registered ReportActionListener instances receive an event notification with the specified swing action attribute value. The org.pentaho.reporting.engine.classic.core.modules.gui.base.event.ReportActionListener interface defines the following API callback method: public void reportActionPerformed(final ReportActionEvent event); The ReportActionEvent object returned in the callback provides the following information: // The PreviewPane objectpublic Object getSource();// The reporting node objectpublic RenderNode getNode();// The action parameter specified in the report.public Object getActionParameter(); To register a ReportActionListener, you must call PreviewDrawablePanel.addReportActionListener(listener). The PreviewDrawablePanel is accessible using the PreviewPane.getReportPreviewArea() API call. ReportMouseListener The org.pentaho.reporting.engine.classic.core.modules.gui.base.event.ReportMouseListener interface provides the following callbacks: public void reportMouseClicked(ReportMouseEvent event);public void reportMousePressed(ReportMouseEvent event);public void reportMouseReleased(ReportMouseEvent event); These are triggered when a user has clicked, pressed, or released their mouse within a report. Each listener registered is called for every element found at the specific x, y location within the report. If there are two or more elements overlapping, multiple event calls will be made, one for each of the report elements. The ReportMouseEvent provides the following information when a callback occurs: // The PreviewPane objectpublic Object getSource();// The reporting node objectpublic RenderNode getSourceNode();// The original java.awt.event.MouseEventpublic MouseEvent getSourceEvent(); To register a ReportMouseListener, you must call PreviewDrawablePanel.addReportMouseListener(listener). The PreviewDrawablePanel is accessible using the PreviewPane.getReportPreviewArea() API call. By combining these callbacks with additional API calls using the PageDrawable API, you can resolve the elements at any particular x, y location within a report. The PageDrawable API defines the following methods: // Retrieves ReportNodes based on x and y location.// A namespace and name filter may be applied to only// retrieve nodes that define a certain attribute.public RenderNode[] getNodesAt (final double x,final double y, final String namespace, final String name);// Retrieves nodes within a window, starting at an x,y// location and stretching out to the defined pixel width// and height. A namespace and name filter may be applied// to only retrieve nodes that define a certain attribute.public RenderNode[] getNodesAt (final double x,final double y, final double width, final double height,final String namespace, final String name); The PageDrawable object is accessible using the PreviewDrawablePanel.getPageDrawable() API call.

What is content in the context of Drupal? We can certainly say that 'content' is any material that makes up the web page, be it Drupal-generated content, such as the banner and buttons, or user content, such as the text of a blog. Within Drupal, 'content' has more narrow parameters. When you create a story in Drupal, it is stored in a database as a node, and is assigned a node ID (nid). Some would say that, with respect to Drupal, content is limited to objects (stories, and so on) that can receive comments created by users, and are assigned a node id. Others say that it is any object in Drupal that can be on a page. These technical discussions can cause your eyes to glaze over. It would seem that the latter definition makes the most sense; however, there is one additional factor that we need to consider, and that is the layout of the Drupal admin functions. Drupal provides admin functions for creating and maintaining content, and these functions list only those objects that receive a node id. Other objects, such as Blocks, are created and maintained elsewhere. What are the types of content in Drupal? The following table lists the content types that ship with Drupal by default: Content Type Description Blog entry A blog, or weblog, is an author-specific content type that is used as a journal or diary, among other things, by individuals. In Drupal, each blog writer can, depending on the site's settings and their permissions, add attachments, HTML, or PHP code to their blog. A good example of a blog can be found at: http://googleblog.blogspot.com/, which demonstrates an interesting use of the blog content format. Book page A book is an organized set of book page types (actually any type can be used nowadays), which are intended to be used for collaborative authoring. Book pages may be added by different people in order to make up one single book that can then be structured into chapters and pages, or in whatever structure is most appropriate, provided it is in a hierarchical structure. Because pretty much any data type can be added to a book, there is plenty of scope for exciting content (think of narrated or visual content complementing dynamic book pages, created with PHP and Flash animations, to create a truly unique Internet-based book-the possibilities are endless!). A good example of a book is the documentation provided for developers on the Drupal site, found at: http://drupal.org/node/316. This has been built up over time by a number of different authors. You will notice that if you have the Book module enabled, an additional outline tag is presented above all/most of the site's posts. Clicking on this tab allows you to add that post to a book-in this way, books can be built up from content posted to the site. Forum topic Forum topics are the building blocks of forums. Forums can only consist of forum topics and their comments, unlike books, which can consist of pretty much any content type. Information in forums is categorized in a hierarchical structure, and they are extremely useful for hosting discussions as well as community-based support and learning. Forums are abundant on the Internet and you can also visit the Drupal forums to get a feel for how they operate. Page The page type is meant to allow you to add basic, run-of-the-mill web pages that can be found on any site. About us or Terms of use pages are good candidates for the page type, although you can spruce these up with a bit of dynamic content and HTML. Just look on any website to see examples of such pages. What about comments? Comments are not the same as the other node types discussed in the previous table. While there may be exceptions, the terms 'node' and 'content' are synonymous with respect to Drupal. While, technically, they are content, consider the fact that one cannot create a comment without first having another node to add the comment to. Instead, you can tack comments onto other content types, and these are very popular as a means to stimulate discussion among users. You can see comments in action by logging into the Drupal forums, http://drupal.org/forum, and posting or viewing comments on the various topics there. How to work with content types? It is possible to specify some default behavior for each of the content types. To do this, go to Content types under Content management to bring up the following page: Each content type has a set of editable configuration parameters, so to get a good idea of how they work, click on the edit in the Book page row. The edit page is broken up into four sections dealing with the following: Identification – Allows you to specify the human readable name and the name used internally by Drupal for the associated content type, as well as to add a description to be displayed on the content creation page. Submission form settings – Allows you to set the field names for the title and body (leaving the body blank removes the field entirely) as well as specify the minimum number of words required to make the posting valid. Again, it is possible to add in submission guidelines or notes to aid those users posting this content type. Workflow settings – Allows you to set default publishing options, multilingual support, and specify whether or not to allow file attachments. Comment settings – Allows you to specify default comment settings such as read or read/write, whether or not comments are allowed, whether they are to appear expanded or collapsed, in which order and how many, amongst other things.

Introduction LINQ, short for Language Integrated Query, provides an object oriented approach to not only querying relational databases but also any kind of source such as XML, Collection of objects, etc. LINQ to SQL provides (O/R) object-relational mapping and Visual Studio 2008 IDE provides a (O/R) designer. Visual Studio 2008 also has a web server control called LinqDataSource control. This control requires a DataContext which is provided by the LINQ-to-SQL classes, a class generator that maps SQL objects to the model. Without this control one may have to generate the classes from scratch or by using the SQLMetal.exe utility which generates tables and columns. The readers may benefit by reading my  previous article on Displaying SQL Server Data using a Linq data source. We will continue to use the PrincetonTemp table used in the previous article on MySQL Data Transfer using Sql Server Integration Services. We will map this table to LINQ via LinqDataSource Control and then use it as the source of data for the chart. Create a Framework 3.5 Web Site project Open Visual Studio 2008 from its shortcut on the desktop. Click File | New | Web Site...(or Shift+Alt+N) to open the New Web Site window. Change the default name of the site to a name of your choice (herein PieChartWithLinq) on your local web server as shown. Make sure you are creating a .NET Framework 3.5 web site as shown here. This is very similar to creating a web site that can reside on the file system. Add a LinqDataControl and provide the data context Drag and drop a LinqDataSource control from Toolbox | Data shown in the next figure on to the Default.aspx This creates an instance of the control LinqDataSource1 as shown. The source page Default.aspx will display the  control as shown in the next figure. When you try to configure a data source for this control using the smart tasks (see Figure 3) the program would take you to a window which does not allow you create a new data context. But if there are existing items the program allows you to choose. Create a data context for the LinqDataSource control We will add a new data context. Right click the web site in Solution Explorer and pick Add New Item... from the drop-down menu. This opens the Add New Item window as shown. Highlight the item Linq to SQL Classes in the Add New Item window. Replace the default name DataClasses.dbml with one of your own. Herein it is replaced with Princeton.dbml. When you click OK after renaming you will get a Microsoft Visual Studio warning as shown. Read the message on this window. Click Yes to add Princeton.dbml consisting of two items to the App_Code folder on the web site project as shown. One is a layout designer and the other a code page. The (O/R) designer containing two panes is shown in the next figure. In the left pane you drag and drop table (s) from the server explorer assuming you a made a connection with a database. To the right you can add a stored procedure from the server explorer. Read the instructions on this page. Click (View | Server Explorer) to display the connections in the server explorer as shown. In the next figure you can see the expanded node of TestNorthwind displaying the table PrincetonTemp in the SQL Server 2008 Hodentek2Mysorian. The database and the server names are the ones used for this article and you may have different names in your machine. The connection used is already present but you can create a new connection in the server explorer window if you choose to do so. Drag and drop the PrincetonTemp table to the left pane of the (O/R) designer as shown. Build the project. This will make the objects available for the LinqDataSource1 on the default page.

What’s Sinatra? Sinatra is not a framework but a library i.e. a set of classes that allows you to build almost any kind of web-based solution (no matter what the complexity) in a very simple manner, on top of the abstracted HTTP layer it implements from Rack. When you code in Sinatra you’re bound only by HTTP and your Ruby knowledge. Sinatra doesn’t force anything on you, which can lead to awesome or evil code, in equal measures. Sinatra apps are typically written in a single file. It starts up and shuts down nearly instantaneously. It doesn’t use much memory and it serves requests very quickly. But, it also offers nearly every major feature you expect from a full web framework: RESTful resources, templating (ERB, Haml/Sass, and Builder), mime types, file streaming, etags, development/production mode, exception rendering. It’s fully testable with your choice of test or spec framework. It’s multithreaded by default, though you can pass an option to wrap actions in a mutex. You can add in a database by requiring ActiveRecord or DataMapper. And it uses Rack, running on Mongrel by default. Blake Mizerany the creator of Sinatra says that it is better to learn Sinatra before Ruby on Rails: When you learn a large framework first, you’re introduced to an abundance of ideas, constraints, and magic. Worst of all, they start you with a pattern. In the case of Rails, that’s MVC. MVC doesn’t fit most web-applications from the start or at all. You’re doing yourself a disservice starting with it. Back into patterns, never start with them- Reference here Installing Sinatra      The simplest way to obtain Sinatra is through Rubygems. Open a command window in Windows and type: c:> gem install sinatra Linux/OS X the command would be: sudo gem install sinatra   Installing its Dependencies Sinatra depends on the Rack gem which gets installed along with Sinatra. Installing Mongrel (a fast HTTP library and server for Ruby that is intended for hosting Ruby web applications of any kind using plain HTTP - http://mongrel.rubyforge.org/) is quite simple. In the already open command window, type: c:> gem install mongrel What are Routes? The main feature of Sinatra is defining ‘routes’ as an HTTP verb for a path that executes an arbitrary block of Ruby code. Something like: verb ‘path’ do ... # return/render something end Sinatra’s routes are designed to respond to the HTTP request methods (GET, POST, PUT, DELETE). In Sinatra, a route is an HTTP method paired with an URL matching pattern. These URL handlers (also called "routing") can be used to match anything from a static string (such as /hello) to a string with parameters (/hello/:name) or anything you can imagine using wildcards and regular expressions. Each route is associated with a block. Let us look at an example: get '/' do .. show something ..endget '/hello/:name' do # The /hello portion matches that portion of the URL from the # request you made, and :name will absorb any other text you # give it and put it in the params hash under the key :nameendpost '/' do .. create something ..endput '/' do .. update something ..enddelete '/' do .. delete something ..end Routes are matched in the order they are defined. When a new request comes in, the first route that matches the request is invoked i.e. the handler (the code block) attached to that route gets executed. For this reason, you should put your most specific handlers on top and your most vague handlers on the bottom. A tiny web-application Here’s an example of a simple Sinatra application. Write a Ruby program myapp1.rb and store it in the folder: c:sinatra_programs Though the name of the folder is c:>sinatra_programs, we are going to have only one Sinatra program here. The program is: # myapp1.rbrequire 'sinatra' Sinatra applications can be run directly: ruby myapp1.rb [-h] [-x] [-e ENVIRONMENT] [-p PORT] [-s HANDLER] The above options are: -h # help -p # set the port (default is 4567) -e # set the environment (default is development) -s # specify rack server/handler (default is thin) -x # turn on the mutex lock (default off) – currently not used In the article- http://gist.github.com/54177, it states that using: require ‘rubygems’, is wrong. It is an environmental issue and not an app issue. The article mentions that you might It is an environmental issue and not an app issue. The article mentions that you might use: ruby -rubygems myapp1.rb Another way is to use RUBYOPT. Refer article – http://rubygems.org/read/chapter/3. By setting the RUBYOPT environment variable to the value rubygems, you tell Ruby to load RubyGems every time it starts up. This is similar to the -rubygems options above, but you only have to specify this once (rather than each time you run a Ruby script).

Linking servers provides an elegant solution when you are faced with running queries against databases on distributed servers or looking at your distributed assets on disparate databases. This article by Dr. Jay Krishnaswamy explains how to set up a MySQL linked server on SQL Server 2008 Enterprise. Configuring a linked MySQL server as well as querying a table on the MySQL linked server is described. The reader would benefit reviewing the first article on this series on MySQL Servers. Introduction MS SQL servers always provided remote access to servers through RPC mechanisms, but they had the limitation of being confined to invoking procedures on remote locations. A linked server (a virtual server) may be considered a more flexible way of achieving the same thing, with the added benefits of remote table access and distributed queries. Microsoft manages the link mechanism via OLE DB technology. Specifically, an OLE DB data source points to the specific database that can be accessed using OLEDB. In this article, we will be creating a MySQL linked server on SQL Server 2008 and querying a database [TestMove] table shown in the next listing. In reviewing the previous article it may be noticed that the Employees tables were moved to MySQL database TestMove. In running the commands from the mysql> prompt it is assumed that the MySQL Server has been started. Listing 1: employees table in TestMove mysql> show tables; +--------------------+ | Tables_in_testmove | +--------------------+ | employees | +--------------------+ 1 row in set (0.09 sec) mysql> Creating an ODBC DSN for MySQL In the previous article on MySQL Servers cited earlier, a DSN was created for moving data. Essentially the same DSN can be used. Herein follows a brief review of the DSN MySQL_Link created along the same lines as in the previously referenced article. The ODBC driver used for creating this ODBC DSN is the one installed on the machine when the MySQL Server was installed as shown. The final interactive window where you may test the connectivity is shown in the next figure. You may notice that the database Testmove has been named in the ODBC DSN. The name MySQL_LINK is the ODBC DSN. When you close the window after clicking the OK button, an ODBC DSN item will be added to the System DSN tab of the ODBC wizard as shown. Steps to create a MySQL linked server from Management Studio Right click the Linked Servers node to display a drop-down menu as shown in the next figure. Click on New Linked Server...item. This brings up the New Linked Server window as shown. The window is all empty except for a default Provider. The very first thing to do is to provide a name for this linked server. Herein it is LINKED_ MYSQL. We will be using one of the providers [Microsoft OLE DB Provider for ODBC] supported by SQL Server 2008. You may access the list of OLE DB Providers in the Providers sub-folder of the Linked Servers. Provide the other details as shown in the next figure. Make sure they are entered exactly as shown or according to how you have created the database on MySQL Server. Click on the Security list item under General to the left. This opens the 'Security' page of the New Linked Server wizard as shown. Change the login from the default "Be made without using a security context" to "Be made using this security context". Enter remote login. In this case, it is "root" for the remote login and the password is the one used during the ODBC DSN (also the password for server authentication) creation. Make no changes to the Server Options page. Click OK. This creates a linked server Linked_MySQL as shown expanded in the Linked Server's node as shown. You may need to right-click and refresh the Linked Servers' node to see the new linked server. As you can see in the figure, the 'User' tables are not displayed.     Running Queries and reviewing results Running system stored procedures can provide various levels of information and the database can be queried using the four-part syntax and the openquery() method. Information on the linked server It is easy to find how the linked server is configured using system stored procedure sp_linkedsrvlogin on the SQL Server 2008. Open a Query window from File | New | Query Current Connection to open the query window and type in the following statement. The next figure shows the statement as well as the returned values. SQL Server 2008 querying has the intellisense report and this must be put to good use. Exec sp_linkedsrvlogin This shows all servers both local and remote as shown in the next figure. Information about the tables on the remote server can also be accessed by running a stored procedure. Executing the stored procedure sp_tables_ex as shown in the next figure (which displays the statement and the result of executing the stored procedure) can be used to obtain table information. Querying the table on the database Data in the table on the linked server can be queried using the openquery() function. The syntax for this function shown next is very simple. openquery ('linked server', 'query') The next figure shows the result of running the openquery() function on the Linked_MySQL linked server. Although it should be possible to query the linked server using the four-part syntax as in: Select * from LINKED_MYSQL...employees The above statement returns an error. This is probably a limitation of a combination of MSDASQL and the ODBC driver which does not provide the schema information correctly(this is just the author's opinion). Are Remote Procedure Calls (RPC) allowed? The easiest way to test this is to send out a call by running the following query against the linked server. Execute('Select FirstName, LastName from employees') at Linked_MYSQL If the linked server is not configured for RPC, then the result you get by running the above query is as shown in the next figure. Turn on RPC Earlier on we skipped the Server Options page of the linked server. Back in the Management Studio right click linked server LINKED_MYSQL and from the drop-down choose to look at the properties at the bottom of the list. This brings up the LINKED_MYSQL properties window as shown. Click on Server Options. In the Server Options page change the values of RPC and RPCOUT to true, default for both being false. Now run the query that produced the error previously. The result is displayed in the next figure. You might have noted that only two columns were returned from the employees table. This was deliberate as trying to get all the column would produce an error due partly to the data types of data stored in the table and their compatibility with MSDASQL and the ODBC driver (Again, an author's opinion). Creating Linked Server using TSQL While the linked server can be created using the built-in wizard of the Management Studio, it can also be created using TSQL statements as in the following listing (run both statements, the first one creates the linked server and the second the logins). Listing 2:  Exec master.dbo.sp_addlinkedserver @server=N'MySQlbyCode', @srvprodcut=N'MySQL', @provider=N'MSDASQL', @datasrc=N'MySQL_link' Exec master.dbo.sp_addlinkedserverlogin @server=N'MySQlbyCode', @locallogin=NULL, @rmtuser=N'root', @rmtpassword=N'<your password>' @rmtsrvname=N'localhost' Summary The article described the steps involved in configuring a MySql Linked server on SQL Server 2008 using the built-in New Linked Server wizard as well as TSQL. Method to query the linked server as well as enabling RPC were described. If you have read this article you may be interested in the following: MySQL Data Transfer using SQL Server Integration Services (SSIS) Transferring Data from MS Access 2003 to SQL Server 2008 Exporting data from MS Access 2003 to MySQL

Weird Hap'nins requirements will be the need to: Get external feed sources and allocate them to menu links on the web site Create the means to automatically fetch and display article items located in the feeds Display blocks of latest content from each feed source on the front page Theme The theme chosen is "Strange Little Town", which is a contributed theme that fits the description of this unique web site. Build Weird Hap'nins Vaughan Pyre is a very ambitious webpreneur. What he really hopes for is a web site that is completely self-maintaining, and on which he can place some Google AdSense blocks. Clicks from the visitors to his site will ensure that he makes lots of money. For this, he needs a site where the content updates regularly with fresh content so that visitors will keep coming back to click on some more Google ads. Vaughan's ultimate objective is to create several of these web sites. Modules This is, surprisingly, a very simple site to build, and much of the requirements can be achieved by using the Core Aggregator module. Indeed, were it not for the fact that Vaughan needs the content to automatically update, we needn't use any module other than the Aggregator module. Optional Core modules We will be using the following Core modules, which can be enabled via the Modules page: Aggregator—for aggregating syndicated content (RSS, RDF, and Atom feeds) Contributed modules We will also be using the following contributed modules from Drupal.org. Install, and enable them via the Modules page: Poormanscron—internal scheduler for users without a cron application Configure the Poormanscron module First we need to enable the Poormanscron module, so that the incoming feeds will be able to self-refresh. From the Administer page, we will access the Poormanscron configuration page, mainly to set the time interval between runs of cron to update feed items, as shown in the following screenshot: In this case, we have left the Time intervals at the default value of 60 minutes. Configure the Aggregator module The Aggregator module should be configured to define the feed sources, how often they will be polled, and how they're categorized. For this, if we select the Feed aggregator link on the Administer page, then we should arrive at the following page: On the Settings page, we will define some more requirements, as follows: Allowed HTML tags—which are the tags that are embedded in the incoming feed that we want Drupal to accept. The allowed tags do not include image tags. So if any images are coming with the feed, then they will be excluded. However, we don't want this to happen, so we have added the image tag <img> to the list. Items shown in sources and categories pages—we have defined this to be 20 items, but you may select another figure. Discard items older than—we want the feed items to be completely refreshed every week so we have set this at 1 week. Category selection type—we are not categorizing the feeds, so we will leave this setting as it is. Basic content The site is built around the Aggregator module, and no other Content type will need to be created. Vaughan has decided to initially use three feeds obtained from www.newsfeedmaker.com, as follows: Bad News—http://www.newsfeedmaker.com/feed.php?code=ddb874f7 Crime—http://www.newsfeedmaker.com/feed.php?code=33a5a46a Paranormal—http://www.newsfeedmaker.com/feed.php?code=936f006a It is from these feeds that we will create the necessary content. Tips and trapsAn excellent source for "mashup" feeds on any topic is pipes.yahoo.com. Add feeds On the Add feeds page, which is under the Feed aggregator configuration page, we finally get to define our feeds, and how often we want them to be polled. We want our Bad News feed to be polled every hour, so we have configured it this way. The same procedure is followed to create the feeds for Crime and Paranormal.

There are a large number of posts on various difficulties experienced while transferring data from MySQL using Microsoft SQL Server Integration Services. While the transfer of data from MySQL to Microsoft SQL Server 2008 is not fraught with any blocking issues, transfer of data from SQL Server 2008 to MySQL has presented various problems. There are some workarounds suggested. In this article by Dr. Jay Krishnaswamy, data transfer to MySQL using SQL Server Integration Services will be described. If you are new to SQL Server Integration Services (SSIS) you may want to read a book by the same author on Beginners Guide to SQL Server Integration Services Using Visual Studio 2005, published by Packt. Connectivity with MySQL For data interchange with MySQL there are two options one of which can be accessed in the connection wizards of SQL Server Integration Services assuming you have installed the programs. The other can be used to set up a ODBC DSN as described further down. The two connection options are: MySQL Connector/ODBC 5.1 Connector/Net 5.2 New versions 6.0 & 6.1 In this article we will be using the ODBC connector for MySQL which can be downloaded from the MySQL Site. The connector will be used to create an ODBC DSN. Transferring a table from SQL Server 2008 to MySQL We will transfer a table in the TestNorthwind database on SQL Server 2008 (Enterprise & Evaluation) to MySQL server database. The MySQL database we are using is described in the article on Exporting data from MS Access 2003 to MySQL. In another article, MySQL Linked Server on SQL Server 2008, creating an ODBC DSN for MySQL was described. We will be using the DSN created in that article. Creating an Integration Services project in Visual Studio 2008 Start the Visual Studio 2008 program from its shortcut. Click File | New | Project... to open the New Project window and select an integration services template from the business intelligence projects by providing a suitable name. The project folder will have a file called Package.dtsx which can be renamed with a custom name. Add and configure an ADO.NET Source The Project's package designer will be open displaying the Control Flow tab. Drag and drop a Data Flow Task on to the control flow tabbed page. Click next on the Data Flow tab in the designer to display the Data Flow page. Read the instructions on this page. Drag and drop a ADO.NET Source from the Data Flow Sources items in the Toolbox. It is assumed that you can set up a connection manager to the resident SQL Server 2008 on your machine. The next figure shows the configured connection manager to the SQL Server 2008. The table (PrincetonTemp) that will be transferred is in the TestNorthwind database. The authentication is Windows and a .NET provider is used to access the data. You may also test the connection by clicking the Test Connection button. If the connection shown above is correctly configured, the test should indicate a successful connection. Right click the ADO.NET source and from the drop-down click Edit. The ADO.NET Source Editor gets displayed. As mentioned earlier you should be able to access the table and view objects on the database as shown in the next figure. We have chosen to transfer a simple table, PrincetonTemp from the TestNorthwind database on SQL Server 2008. It has a only couple of columns as shown in the Columns page of the ADO.NET Source Editor. The default for the Error page setting has been assumed, that is, if there is an error or truncation of data the task will fail. Add an ADO.NET destination and port the data from the source Drag and drop an ADO.NET destination item from under Data Flow Destinations items in the Toolbox on to the data flow page of the designer. There are two ways to arrange for the data to flow from source to the destination. The easy way is just drag the green dangling line from the source with your mouse and let go on the ADO.NET destination. A solid line will connect the source and the destination as shown. (For more resources on Microsoft, see here.) Configure a connection manager to connect to MySQL In the Connection Manager's pane under the Package designer right click to display a pop-up menu which allows you to make a new connection. When you agree to make a new ADO.NET Connection the Configure ADO.NET connection Manager's window shows up and click on New... button on this page. The connection manager's page gets displayed as shown. In the Providers drop-down you will see a number of providers. There are the two providers that you can use, the ODBC through the connector and the MySQL Data Provider. Click on the Odbc Data Provider. As mentioned previously we will be using the System DSN MySQL_Link created earlier for the other article shown in the drop-down list of available ODBC DSN's. Provide the USERID and Password; click the Test Connection button. If all the information is correct you should get a success message as shown. Close out of the message as well as the Configure ADO.NET Connection Manager windows. Right click the ADO.NET Destination to display its editor window. In the drop-down for connection manager you should be able to pick the connection Manager you created in the previous step (MySQL_INK.root) as shown. Click on the New... button to create a Table or View. You will get a warning message regarding not knowing the mapping to SSIS as shown. Click OK. The create table window gets displayed as shown. Notice that the table is displaying all the columns from the table that the source is sending out. If you were to click OK, you would get an error that the syntax is not correct as shown. Modify the table as shown to change the destination table name (your choice) and the data type. CREATE TABLE From2k8( "Id" INT, "Month" VARCHAR(10), "Temperature" DOUBLE PRECISION, "RecordHigh" DOUBLE PRECISION ) Click OK. Again you get the same error regarding syntax not being correct. Modify the Create Table statement further as shown. CREATE TABLE From2k8 ( Id INT, Month VARCHAR(10), Temperature DOUBLE PRECISION, RecordHigh DOUBLE PRECISION ) Click OK after the above modification. The table gets added to the ADO.NET Destination Manager Editor as shown. Click on the Mappings on the left side of the ADO.NET Destination Editor. The column mappings page gets displayed as shown. We accept the default settings for Error Output page. Click OK. Build the project and execute the package by right clicking the package and choosing Execute Package. The program runs and processes the package and ends up being unsuccessful with the error message in the Progress tab of the project as shown (only relevant message is shown here). .... ..... [SSIS.Pipeline] Information: Execute phase is beginning. [ADO NET Destination 1 [165]] Error: An exception has occurred during data insertion, the message returned from the provider is: ERROR [42000] [MySQL][ODBC 5.1 Driver] [mysqld-5.1.30-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Id", "Month", "Temperature", "RecordHigh") VALUES (1, 'Jan ', 4.000000000' at line 1 [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ADO NET Destination 1" (165) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (168). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. [SSIS.Pipeline] Information: Post Execute phase is beginning. ...... .... Task Data Flow Task failed .... Start the MySQL Server and login to it. Run the following commands as shown in the next figure. By setting the mode to 'ANSI' makes the syntax more standard like as MySQL can cater to clients using other SQL modes. This is why the above error is returned although the syntax itself appears correct. In fact a create statement run on command line to create a table directly on MySQL could not create a table and returned an error when SSIS was used to create the same table. After running the above statements, build the BI project and execute the package. This time the execution is will be successful and you can query the MySQL Server as in the following: Summary The article describes step by step transferring a table from SQL Server 2008 to MySQL using ODBC connectivity. For successful transfer, the data type differences between SQL Server 2008 and the MySQL version must be properly taken into consideration as well as correctly setting the SQL_Mode property of MySQL Server. Further resources on this subject: Easy guide to understand WCF in Visual Studio 2008 SP1 and Visual Studio 2010 Express MySQL Linked Server on SQL Server 2008 Displaying MySQL data on an ASP.NET Web Page Exporting data from MS Access 2003 to MySQL Transferring Data from MS Access 2003 to SQL Server 2008

In this two-part article by Hector R. Madrid, we will learn about the External Tables in Oracle 10g/11g Database. When working in data warehouse environments, the Extraction—Transformation—Loading (ETL) cycle frequently requires the user to load information from external sources in plain file format, or perform data transfers among Oracle database in a proprietary format. This requires the user to create control files to perform the load. As the format of the source data regularly doesn't fit the one required by the Data Warehouse, a common practice is to create stage tables that load data into the database and create several queries that perform the transformation from this point on, to take the data to its final destination. A better approach would be to perform this transformation 'on the fly' at load time. That is what External Tables are for. They are basically external files, managed either by means of the SQL*Loader or the data pump driver, which from the database perspective can be seen as if they were regular read-only tables. This format allows the user to think about the data source as if the data was already loaded into its stage table. This lets the user concentrate on the queries to perform the transformation, thus saving precious time during the load phase. The basics of an External Tables in Oracle10g/11g An External Table is basically a file that resides on the server side, as a regular flat file or as a data pump formatted file. The External Table is not a table itself; it is an external file with an Oracle format and its physical location. This feature first appeared back in Oracle 9i Release 1 and it was intended as a way of enhancing the ETL process by reading an external flat file as if it was a regular Oracle table. On its initial release it was only possible to create read-only External Tables, but, starting with 10g—it is possible to unload data to External Tables too. In previous 10g Releases, there was only the SQL*Loader driver could be used to read the External Table, but from 10g onwards it is now possible to load the table by means of the data pump driver. The kind of driver that will be used to read the External Table is defined at creation time. In the case of ORACLE_LOADER it is the same driver used by SQL*Loader. The flat files are loaded in the same way that a flat file is loaded to the database by means of the SQL*Loader utility, and the creation script can be created based on a SQL*Loader control file. In fact, most of the keywords that are valid for data loading are also valid to read an external flat file table. The main differences between SQL*Loader and External Tables are: When there are several input datafiles SQL*Loader will generate a bad file and a discard file for each datafile. The CONTINUEIF and CONCATENATE keywords are not supported by External Tables. The GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC are not supported for External Tables. LONG, nested tables, VARRAY, REF, primary key REF, and SID are not supported. For fields in External Tables the character set, decimal separator, date mask and other locale settings are determined by the database NLS settings. The use of the backslash character is allowed for SQL*Loader, but for External Tables this would raise an error. External Tables must use quotation marks instead.For example: SQL*Loader FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "" External Tables TERMINATED BY ',' ENCLOSED BY "'" A second driver is available, the ORACLE_DATAPUMP access driver, which uses the Data Pump technology to read the table and unload data to an External Table. This driver allows the user to perform a logical backup that can later be read back to the database without actually loading the data. The ORACLE_DATAPUMP access driver utilizes a proprietary binary format for the external file, so it is not possible to view it as a flat file. Let's setup the environment Let's create the demonstration user, and prepare its environment to create an External Table. The example that will be developed first refers to the External Table using the ORACLE_LOADER driver. create user EXTTABDEMO identified by ORACLE default tablespace USERS; alter user exttabdemo quota unlimited on users; grant CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE MATERIALIZED VIEW, ALTER SESSION, CREATE VIEW, CREATE ANY DIRECTORY to EXTTABDEMO; A simple formatted spool from this query will generate the required external table demonstration data. The original source table is the demonstration HR.EMPLOYEES table. select EMPLOYEE_ID ||','|| DEPARTMENT_ID ||','|| FIRST_NAME ||','|| LAST_NAME ||','|| PHONE_NUMBER ||','|| HIRE_DATE ||','|| JOB_ID ||','|| SALARY ||','|| COMMISSION_PCT ||','|| MANAGER_ID ||','|| EMAIL from HR.EMPLOYEES order by EMPLOYEE_ID The above query will produce the following sample data: The External Table directory is defined inside the database by means of a DIRECTORY object. This object is not validated at creation time, so the user must make sure the physical directory exists and the oracle OS user has read/write privileges on it. $ mkdir $HOME/external_table_dest SQL> CREATE DIRECTORY EXTTABDIR AS '/home/oracle/external_table_dest'; The above example was developed in a Linux environment, on Windows platforms the paths will need to be changed to correctly reflect how Oracle has been set up. Now, the first External Table can be created. A basic External Table Here is the source code of the External Table creation. The create table command syntax is just like any other regular table creation (A), (B), up to the point where the ORGANIZATION EXTERNAL (C) keyword appears, this is the point where the actual External Table definition starts. In this case the External Table is accessed by the ORACLE_LOADER driver (D). Next, the external flat file is defined, and here it is declared the Oracle DIRECTORY (E) where the flat file resides. The ACCESS PARAMETERS(F) section specifies how to access the flat file and it declares whether the file is a fixed or variable size record, and which other SQL*Loader loading options are declared. The LOCATION (H) keyword defines the name of the external data file. It must be pointed out that as this is an External Table managed by the SQL_LOADER driver the ACCESS_PARAMETERS section must be defined, in the case of External Tables based on the DATAPUMP_DRIVER this section is not required. The columns are defined only by name (G), not by type. This is permitted from the SQL*Loader perspective, and allows for dynamic column definition. This column schema definition is more flexible, but it has a drawback—data formats such as those in DATEcolumns must match the database date format, otherwise the row will be rejected. There are ways to define the date format working around this requirement. Assuming the date column changes from its original default format mask "DD-MON-RR" to "DD-MM-RR", then the column definition must change from a simple CHAR column to a DATE with format mask column definition. Original format: "HIRE_DATE" CHAR(255) Changed format: "HIRE_DATE" DATE "DD-MM-RR" When working with an External Table, the access parameter is not validated at creation time, so if there are malformed rows, or if there are improperly defined access parameters, an error is shown, similar to the one below. When working with an External Table, the access parameter is not validated at creation time, so if there are malformed rows, or if there are improperly defined access parameters, an error is shown, similar to the one below. ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached ORA-06512: at "SYS.ORACLE_LOADER", line 52 Once the data is created and all required OS privileges have been properly validated, the data can be seen from inside the database, just as if it were a regular Oracle table. This table is read only, so if the user attempts to perform any DML operation against it, it will result in this error: SQL> delete ext_employees; delete ext_employees * ERROR at line 1: ORA-30657: operation not supported on external organized table As the error message clearly states, this kind of table is only useful for read only operations. This kind of table doesn't support most of the operations available for regular tables, such as index creation, and statistics gathering, and these types of operations will cause an ORA-30657 error too. The only access method available for External Tables is Full Table Scan, so there is no way to perform a selective data retrieval operation. The External Tables cannot be recovered, they are just metadata definitions stored in the dictionary tables. The actual data resides in external files, and there is no way to protect them with the regular backup database routines, so it is the user's sole responsibility to provide proper backup and data management procedures. At the database level the only kind of protection the External Table receives is at the metadata level, as it is an object stored as a definition at the database dictionary level. As the data resides in the external data file, if by any means it were to be corrupted, altered, or somehow modified, there would be no way to get back the original data. If the external data file is lost, then this may go unnoticed, until the next SELECT operation takes place. This metadata for an External Table is recorded at the {USER | ALL | DBA}_TABLES view, and as this table doesn't actually require physical database storage, all storage related columns appear as null, as well as the columns that relate to the statistical information. This table is described with the {USER | ALL | DBA}_EXTERNAL_TABLES view, where information such as the kind of driver access, the reject_limit, and the access_parameters, amongst others, are described. SQL> DESC USER_EXTERNAL_TABLES Name Null? Type ------------------------------- -------- -------------- TABLE_NAME NOT NULL VARCHAR2(30) TYPE_OWNER CHAR(3) TYPE_NAME NOT NULL VARCHAR2(30) DEFAULT_DIRECTORY_OWNER CHAR(3) DEFAULT_DIRECTORY_NAME NOT NULL VARCHAR2(30) REJECT_LIMIT VARCHAR2(40) ACCESS_TYPE VARCHAR2(7) ACCESS_PARAMETERS VARCHAR2(4000) PROPERTY VARCHAR2(10) This is the first basic External Table, and as previously shown, its creation is pretty simple. It allows external data to be easily accessed from inside the database, allowing the user to see the external data just as if it was already loaded inside a regular stage table. Creating External Table metadata, the easy way To further illustrate the tight relationship between SQL*Loader and External Tables, the SQL*Loader tool may be used to generate a script that creates an External Table according to a pre-existing control file. SQL*Loader has a command line option named EXTERNAL_TABLE, this can hold one of three different parameters {NOT_USED | GENERATE_ONLY | EXECUTE}. If nothing is set, it defaults to the NOT_USED option. This keyword is used to generate the script to create an External Table, and the options mean: NOT_USED: This is the default option, and it means that no External Tables are to be used in this load. GENERATE_ONLY: If this option is specified, then SQL*Loader will only read the definitions from the control file and generate the required commands, so the user can record them for later execution, or tailor them to fit his/her particular needs. EXECUTE: This not only generates the External Table scripts, but also executes them. If the user requires a sequence, then the EXECUTE option will not only create the table, but it will also create the required sequence, deleting it once the data load is finished. This option performs the data load process against the specified target regular by means of an External Table, it creates both the directory and the External Table, and inserts the data using a SELECT AS INSERTwith the APPEND hint. Let's use the GENERATE_ONLY option to generate the External Table creation scripts: $ sqlldr exttabdemo/oracle employees external_table=GENERATE_ONLY By default the log file is located in a file whose extension is .log and its name equals that of the control file. By opening it we see, among the whole log processing information, this set of DDL commands: CREATE TABLE "SYS_SQLLDR_X_EXT_EMPLOYEES" ( "EMPLOYEE_ID" NUMBER(6), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25), "EMAIL" VARCHAR2(25), "PHONE_NUMBER" VARCHAR2(20), "HIRE_DATE" DATE, "JOB_ID" VARCHAR2(10), "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6), "DEPARTMENT_ID" NUMBER(4) ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY EXTTABDIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'EXTTABDIR':'employees.bad' LOGFILE 'employees.log_xt' READSIZE 1048576 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM REJECT ROWS WITH ALL NULL FIELDS ( "EMPLOYEE_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "FIRST_NAME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "LAST_NAME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "EMAIL" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "PHONE_NUMBER" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "HIRE_DATE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "JOB_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "SALARY" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "COMMISSION_PCT" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "MANAGER_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "DEPARTMENT_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ) ) location ( 'employees.txt' ) ) The more complete version is shown, some differences with the basic script are: All the column definitions are set to CHAR(255) with the delimiter character defined for each column If the current working directory is already registered as a regular DIRECTORY at the database level, SQL*Loader utilizes it, otherwise, it creates a new directory definition The script specifies where the bad files and log file are located It specifies that an all-null column record is rejected The more complete version is shown, some differences with the basic script are: All the column definitions are set to CHAR(255) with the delimiter character defined for each column If the current working directory is already registered as a regular DIRECTORY at the database level, SQL*Loader utilizes it, otherwise, it creates a new directory definition The script specifies where the bad files and log file are located It specifies that an all-null column record is rejected In the case of the EXECUTE keyword, the log file shows that not only are the scripts used to create the External Table, but also to execute the INSERT statement with the /*+ append */hint. The load is performed in direct path mode. All External Tables, when accessed, generate a log file. In the case of the ORACLE_LOADERdriver, this file is similar to the file generated by SQL*Loader. It has a different format in the case of ORACLE_DATAPUMP driver. The log file is generated in the same location where the external file resides, and its format is as follows: <EXTERNAL_TABLE_NAME>_<OraclePID>.log When an ORACLE_LOADER managed External Table has errors, it dumps the 'bad' rows to the *.bad file, just the same as if this was loaded by SQL*Loader. The ORACLE_DATAPUMP External Table generates a simpler log file, it only contains the time stamp when the External Table was accessed, and it creates a log file for each oracle process accessing the External Table. Unloading data to External Tables The driver used to unload data to an External Table is the ORACLE_DATAPUMP access driver. It dumps the contents of a table in a binary proprietary format file. This way you can exchange data with other 10g and higher databases in a preformatted way to meet the other database's requirements. Unloading data to an External Table doesn't make it updateable, the tables are still limited to being read only. Let's unload the EMPLOYEES table to an External Table: create table dp_employees organization external( type oracle_datapump default directory EXTTABDIR location ('dp_employees.dmp') ) as select * from employees; This creates a table named DP_EMPLOYEES, located at the specified EXTTABDIR directory and with a defined OS file name. In the next example, at a different database a new DP_EMPLOYEES table is created, this table uses the already unloaded data by the first database. This DP_EMPLOYEES External Table is created on the 11g database side. create table dp_employees( EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, JOB_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), DEPARTMENT_ID NUMBER(4) ) organization external ( type oracle_datapump default directory EXTTABDIR location ('dp_employees.dmp') ); This table can already read in the unloaded data from the first database. The second database is a regular 11g database. So this shows the inter-version upward compatibility between a 10g and an 11g database. SQL> select count(*) from dp_employees; COUNT(*) ---------- 107 Inter-version compatibility In, the previous example a 10g data pump generated an External Table that was transparently read by the 11g release. Let's create an 11g data pump External Table named DP_DEPARTMENTS: create table dp_departments organization external( type oracle_datapump default directory EXTTABDIR access parameters ( version '10.2.0' ) location ('dp_departments.dmp') ) as select * from departments Table created. SQL> select count(*) from dp_departments; COUNT(*) ---------- 27 In the previous example, it is important to point out that the VERSION keyword defines the compatibility format. access parameters ( version '10.2.0' ) If this clause is not specified then an incompatibility error will be displayed. SQL> select count(*) from dp_departments; select count(*) from dp_departments * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-39142: incompatible version number 2.1 in dump file "/home/oracle/external_table_dest/dp_departments.dmp" ORA-06512: at "SYS.ORACLE_DATAPUMP", line 19 Now let's use the 10g version to read from it. SQL> select count(*) from dp_departments; COUNT(*) ---------- 27 The VERSION clause is interpreted the same way as the VERSION clause for the data pump export, it has three different values: COMPATIBLE: This states that the version of the metadata corresponds to the database compatibility level. LATEST: This corresponds to the database version. VERSION NUMBER: This refers to a specific oracle version that the file is compatible with. This value cannot be lower than 9.2.0 Summary As we can see, External Tables can serve not only as improvements to the ETL process, but also as a means to manage database environments, and a means of reducing the complexity level of data management from the user's point of view. In the next part, we will see how External Tables can be used for data transformation and the enhancements Oracle 11g has brought about in External Tables.

  What is Packt's Article Network? Packt's Article Network is an extensive resource of interesting articles and book excerpts written by expert professionals and developers around the globe, specifically targeted for an audience with a penchant for technology. That is not to say that it is of no use for novices; we are quite sure we have enough material for anyone who cares to stay updated with the latest technological trends. How do I contribute to Packt's Article Network? You write, we publish!Simply write to us with an article idea or an outline, and if we find it relevant for our readers, you can go ahead and start working on it. We have a dedicated team of expert technical content editors, who'll work with you to bring out the best results. You can submit your ideas at articleideas@packtpub.com What are the guidelines for submitting articles? Articles should be about 1500 words long, and be a technical how-to or case study that would be of interest to other Article Network readers. All submitted articles must be original, not having been published before, and not under consideration for publication elsewhere. All articles will be edited as necessary for content, style, clarity, grammar, and spelling. If the articles contain images and screenshots, they should be in either PNG or GIF format. The maximum width of the images should be 500 pixels. The height can be of appropriate and proportionate level. Articles once submitted become the property of Packt Publishing. If I write for you, what do I get in return? As well as seeing your work published for the whole world to see, you will also receive two eBooks of your choice from our titles' list, for free. I don't want to continuously keep browsing the Article Network to find out about new articles. What are my options? Subscribe!We have an RSS feed on the Article Network page. You may also subscribe to our newsletter here, which features an article of the month amongst other relevant news from Packt. For more questions and/or feedback, please contact us at articleideas@packtpub.com