IBM Informix. IBM Informix Database Extensions User s Guide. Version 11.1 G

IBM

Informix

IBM

Informix

Database

Extensions

User’s

Guide

Version11.1

IBM

Informix

IBM

Informix

Database

Extensions

User’s

Guide

Version11.1

Note:

Beforeusingthisinformationandtheproductitsupports,readtheinformationin“Notices”onpageB-1.

ThisdocumentcontainsproprietaryinformationofIBM.Itisprovidedunderalicenseagreementandisprotected bycopyrightlaw.Theinformationcontainedinthispublicationdoesnotincludeanyproductwarranties,andany statementsprovidedinthispublicationshouldnotbeinterpretedassuch.

Contents

Introduction

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. ix

InThisIntroduction. . . ix

AboutThisPublication. . . ix

TypesofUsers . . . ix

NewFeatures . . . x

DocumentationConventions . . . x

TypographicalConventions . . . x

Feature,Product,andPlatformMarkup . . . xi

ExampleCodeConventions . . . xi

AdditionalDocumentation . . . xi

CompliancewithIndustryStandards. . . xii

SyntaxDiagrams . . . xii

HowtoReadaCommand-LineSyntaxDiagram. . . xiii

KeywordsandPunctuation. . . xiv

IdentifiersandNames . . . xiv

IBMWelcomesYourComments . . . xv

Part

1.

Large

Objects

Management

Chapter

1.

About

Large

Object

Locator

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 1-1

InThisChapter. . . 1-1 UsingLargeObjectLocator. . . 1-2 LargeObjectLocatorDataTypes . . . 1-2 LargeObjectLocatorFunctions . . . 1-2 Limitations . . . 1-3 InstallationandRegistration . . . 1-3

Chapter

2.

Large

Object

Locator

Data

Types

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 2-1

InThisChapter. . . 2-1 lld_locator . . . 2-1 lld_lob. . . 2-2

Chapter

3.

Large

Object

Locator

Functions

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 3-1

InThisChapter. . . 3-1 Interfaces . . . 3-1 APILibrary . . . 3-2 ESQL/CLibrary . . . 3-2

ErrorUtilityFunctions . . . 3-25 lld_error_raise() . . . 3-26 lld_sqlstate() . . . 3-27 SmartLargeObjectFunctions. . . 3-28 LOCopy . . . 3-29 LOToFile. . . 3-30 LLD_LobType . . . 3-31

Chapter

4.

Large

Object

Locator

Example

Code

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 4-1

InThisChapter. . . 4-1 UsingtheSQLInterface . . . 4-1 Usingthelld_lobType . . . 4-1 Usingthelld_locatorType . . . 4-4 UsingtheAPI . . . 4-7 Creatingthelld_copy_subsetFunction . . . 4-7 Usingthelld_copy_subsetRoutine . . . 4-10

Chapter

5.

Large

Object

Locator

Error

Handling

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 5-1

InThisChapter. . . 5-1 HandlingLargeObjectLocatorErrors . . . 5-1 HandlingExceptions . . . 5-1 ErrorCodes . . . 5-2

Part

2.

MQ

Messaging

Chapter

6.

About

the

MQ

DataBlade

Module

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 6-1

InThisChapter. . . 6-1 AboutIBMWebSphereMQandMQDataBladeModule . . . 6-1 UsingMQDataBladeTablesandFunctions . . . 6-1 Limitations . . . 6-2 SoftwareRequirements . . . 6-2 PreparingtouseMQDataBladeModule . . . 6-2 InstallingWMQ. . . 6-2 ConfiguringtheWMQQueues . . . 6-2 ConfiguringDynamicServer . . . 6-3 RegisteringtheMQDataBladeModule . . . 6-9 Verification . . . 6-9 InsertingDataintoaQueue . . . 6-9 ReadinganEntryfromaQueue . . . 6-10 ReceivinganEntryfromaQueue . . . 6-10 PublishingandSubscribingtoaQueue . . . 6-10

Chapter

7.

MQ

DataBlade

Tables

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 7-1

InThisChapter. . . 7-1 MQDataBladeTables. . . 7-1 WorkingwithTables . . . 7-1 SchemaMapping . . . 7-1 GeneralTableBehavior . . . 7-2 CreatingandBindingaTable . . . 7-2 UsingINSERTandSELECT. . . 7-2 RetrievingtheQueueElement . . . 7-3 SpecialConsiderations . . . 7-3 TableErrors . . . 7-4

Chapter

8.

MQ

DataBlade

Functions

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 8-1

InThisChapter. . . 8-2

Syntax. . . 8-3 Usage . . . 8-3 ReturnCodes . . . 8-4 Example . . . 8-4 MQCreateVtiReceive() . . . 8-5 Syntax. . . 8-5 Usage . . . 8-5 ReturnCodes . . . 8-6 Example . . . 8-6 MQPublish(). . . 8-7 Syntax. . . 8-7 Usage . . . 8-7 ReturnCodes . . . 8-8 Examples . . . 8-8 MQPublishClob(). . . 8-10 Syntax . . . 8-10 Usage. . . 8-11 ReturnCodes . . . 8-12 Examples . . . 8-12 MQRead() . . . 8-14 Syntax . . . 8-14 Usage. . . 8-14 ReturnCodes . . . 8-15 Examples . . . 8-15 MQRead() . . . 8-16 Syntax . . . 8-16 Usage. . . 8-16 ReturnCodes . . . 8-17 Examples . . . 8-17 MQReadClob() . . . 8-18 Syntax . . . 8-18 Usage. . . 8-19 ReturnCodes . . . 8-19 Examples . . . 8-19 MQReceive() . . . 8-20 Syntax . . . 8-20 Usage. . . 8-21 ReturnCodes . . . 8-21 Examples . . . 8-22 MQReceiveClob(). . . 8-23 Syntax . . . 8-23 Usage. . . 8-23 ReturnCodes . . . 8-24 Examples . . . 8-24

Examples . . . 8-32 MQUnsubscribe(). . . 8-33 Syntax . . . 8-33 Usage. . . 8-34 ReturnCodes . . . 8-34 Examples . . . 8-34 MQVersion() . . . 8-35 Syntax . . . 8-35 Example. . . 8-35

Chapter

9.

MQ

DataBlade

Module

Error

Handling

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 9-1

InThisChapter. . . 9-1 ErrorCodes . . . 9-1

Part

3.

Binary

Data

Types

Chapter

10.

About

the

Binary

DataBlade

Module

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 10-1

InThisChapter . . . 10-1 BinaryDataBladeModuleOverview . . . 10-1 BinaryDataBladeModuleLimitations . . . 10-1 SoftwareRequirements . . . 10-1 RegisteringtheBinaryDataBladeModule . . . 10-2 UnregisteringtheBinaryDataBladeModule . . . 10-2

Chapter

11.

Storing

and

Indexing

Binary

Data

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 11-1

InThisChapter . . . 11-1 BinaryDataTypes . . . 11-1 binaryvarDataType. . . 11-1 binary18DataType . . . 11-1 ASCIIRepresentationofBinaryDataTypes . . . 11-1 BinaryDataTypeExamples . . . 11-1 InsertingBinaryData . . . 11-2 IndexingBinaryData . . . 11-3

Chapter

12.

Binary

DataBlade

Functions

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 12-1

InThisChapter . . . 12-1 BitwiseOperationFunctions . . . 12-1 bit_and(). . . 12-2 bit_complement(). . . 12-3 bit_or() . . . 12-4 bit_xor() . . . 12-5 SupportingFunctionsforBinaryDataTypes. . . 12-6 bdtrelease(). . . 12-7 bdttrace() . . . 12-8 LENGTH() . . . 12-9 OCTET_LENGTH(). . . 12-10

Part

4.

Basic

Text

Search

Chapter

13.

About

the

Basic

Text

Search

DataBlade

Module

.

.

.

.

.

.

.

.

.

.

.

. 13-1

InThisChapter . . . 13-1 OverviewoftheBasicTextSearchDataBladeModule. . . 13-1 Thebts_contains()SearchPredicate. . . 13-1 BasicTextSearchDataBladeModuleFunctions . . . 13-2 RequirementsandRestrictionsfortheBasicTextSearchDataBladeModule . . . 13-2

PreparingtheBasicTextSearchDataBladeModule . . . 13-3 DefiningthebtsExtensionVirtualProcessorClass. . . 13-3 CreatinganextspaceforthebtsIndex . . . 13-3 CreatingtheIndexbySpecifyingthebtsAccessMethod. . . 13-4 TheOperatorClass . . . 13-5

Chapter

14.

Basic

Text

Search

Queries

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 14-1

InThisChapter . . . 14-1 SearchingwithBasicTextSearch. . . 14-1 BasicTextSearchQueryRestrictions . . . 14-1 BasicTextSearchQuerySyntax . . . 14-1 BasicTextSearchQueryTypes . . . 14-2 BasicTextSearchQueryTerms . . . 14-2 BasicTextSearchQueryTermModifiers . . . 14-3 BooleanOperators . . . 14-5 GroupingWordsandPhrases. . . 14-6 Stopwords . . . 14-6 EscapingSpecialCharacters . . . 14-7

Chapter

15.

Basic

Text

Search

Functions

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. 15-1

InThisChapter . . . 15-1 bts_index_compact(). . . 15-2 bts_release() . . . 15-3 bts_tracefile() . . . 15-4 bts_tracelevel(). . . 15-5

Chapter

16.

Basic

Text

Search

DataBlade

Module

Performance

.

.

.

.

.

.

.

.

.

. 16-1

InThisChapter . . . 16-1 OptimizingthebtsIndex . . . 16-1 DeletingRowsFromthebtsIndexManuallyWhenUsingDeferredMode. . . 16-1 DeletingRowsFromthebtsIndexAutomaticallywithImmediateMode . . . 16-1 DiskSpaceforthebtsIndex . . . 16-2 TransactionswithBasicTextSearch. . . 16-2

Chapter

17.

Basic

Text

Search

DataBlade

Module

Error

Codes

.

.

.

.

.

.

.

.

.

.

. 17-1

InThisChapter . . . 17-1 ErrorCodes . . . 17-1

Part

5.

Hierarchical

Data

Type

Chapter

18.

Node

DataBlade

Module

for

Querying

Hierarchical

Data

.

.

.

.

.

.

.

. 18-1

NodeDataBladePrerequisites . . . 18-1

Length()NodeDataBladeFunction . . . 19-16 LessThan()NodeDataBladeFunction. . . 19-17 LessThanOrEqual()NodeDataBladeFunction . . . 19-18 NewLevel()NodeDataBladeFunction . . . 19-19 NodeRelease()NodeDataBladeFunction . . . 19-20 NotEqual()NodeDataBladeFunction. . . 19-21

Part

6.

Web

Feature

Service

for

Geospatial

Data

Chapter

20.

Web

Feature

Service

DataBlade

Module

Administration

.

.

.

.

.

.

.

. 20-1

Requirements . . . 20-1 TheWFSDriverCGIProgram. . . 20-1 ConfiguringtheWFSDriverProgram . . . 20-2 WFSDataBladeModuleTransactions . . . 20-2 ImplementingSecurityintheWFSDataBladeModule . . . 20-2

Chapter

21.

Web

Feature

Service

DataBlade

Module

Reference

.

.

.

.

.

.

.

.

.

. 21-1

DescribeFeatureTypeElement. . . 21-1 GetCapabilitiesElement . . . 21-2 GetFeature . . . 21-2 WFSTransactions. . . 21-4 InsertElement. . . 21-4 UpdateElement . . . 21-6 DeleteElement . . . 21-6 NativeElement . . . 21-7 WFSTransactionResponseDocument . . . 21-7 WFSConfigProgram. . . 21-8 WFSExplodeUDR . . . 21-8 WFSpwcryptprogram . . . 21-9 WFSRegisterUDR . . . 21-9 WFSSetupProgram . . . 21-9

Part

7.

Appendixes

Appendix.

Accessibility

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. A-1

AccessibilityfeaturesforIBMInformixDynamicServer . . . A-1 AccessibilityFeatures . . . A-1 KeyboardNavigation . . . A-1 RelatedAccessibilityInformation. . . A-1 IBMandAccessibility . . . A-1 DottedDecimalSyntaxDiagrams . . . A-1

Notices

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. B-1

Trademarks . . . B-3

Index

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. X-1

Introduction

InThisIntroduction. . . ix

AboutThisPublication. . . ix

TypesofUsers . . . ix

NewFeatures . . . x

DocumentationConventions . . . x

TypographicalConventions . . . x

Feature,Product,andPlatformMarkup . . . xi

ExampleCodeConventions . . . xi

AdditionalDocumentation . . . xi

CompliancewithIndustryStandards. . . xii

SyntaxDiagrams . . . xii

HowtoReadaCommand-LineSyntaxDiagram. . . xiii

KeywordsandPunctuation. . . xiv

IdentifiersandNames . . . xiv

IBMWelcomesYourComments . . . xv

In

This

Introduction

ThisintroductionintroducestheIBM InformixDatabaseExtensionsUser'sGuide.

Readthischapterforanoverview oftheinformationprovided inthispublication and foranunderstandingoftheconventionsusedthroughout.

About

This

Publication

Thispublicationexplainshow tousethefollowingIBM® _Informix®_DataBlade™

modules thatcomewithIBM InformixDynamicServer:

v LargeObject Locator,afoundationDataBlademodulefor largeobjects

managementthatcanbeusedbyothermodules thatcreateorstorelarge-object data.

v MQDataBlademodule,whichallowsIBMInformix databaseapplicationsto

communicatewith otherMQSeries®_{applicationswith MQmessaging.}

v BinaryDataBladeModulethatincludesbinarydatatypesthatallowyoutostore

binary-encodedstrings,whichcanbe indexedforquickretrieval.

v BasicTextSearchDataBlademodule,whichallowsyoutosearchwordsand

New

Features

Version11.10includesnewfeaturesthatenhancethefunctionalityofthedatabase serverand makeiteasier touse.Thefollowingtableprovidesinformationabout thenew featuresforIBM InformixDynamicServer,Version11.10,whichthis publication covers.For acomprehensivelistofnewfeaturesforthisrelease,seethe

IBM InformixGettingStarted Guide.Thistopiclists newfeaturesrelevanttothis publication.

NewFeature Reference

BinaryDataTypes Chapter10,“AbouttheBinaryDataBlade Module,”onpage10-1

BasicTextSearch Chapter13,“AbouttheBasicTextSearch DataBladeModule,”onpage13-1 HierarchicalDataType Chapter18,“NodeDataBladeModulefor

QueryingHierarchicalData,”onpage18-1 WebFeatureServiceforSpatialData Chapter20,“WebFeatureService

DataBladeModuleAdministration,”on page20-1

Documentation

Conventions

Thissectiondescribes thefollowingconventions,whichare usedintheproduct documentationforIBMInformixDynamic Server:

v Typographicalconventions

v Feature,product,and platformconventions

v Syntaxdiagrams

v Command-lineconventions

v Examplecodeconventions

Typographical

Conventions

Thispublicationusesthefollowingconventionstointroducenewterms,illustrate screendisplays,describecommandsyntax,andsoforth.

Convention Meaning

KEYWORD KeywordsofSQL,SPL,andsomeotherprogramminglanguagesappear inuppercaselettersinaseriffont.

italics Withintext,newtermsandemphasizedwordsappearinitalics.Within syntaxandcodeexamples,variablevaluesthatyouaretospecify appearinitalics.

boldface Namesofprogramentities(suchasclasses,events,andtables), environmentvariables,filenames,pathnames,andinterfaceelements (suchasicons,menuitems,andbuttons)appearinboldface.

monospace Informationthattheproductdisplaysandinformationthatyouenter appearinamonospacetypeface.

KEYSTROKE _{Keysthatyouaretopressappearinuppercaselettersinasansserif} font.

> Thissymbolindicatesamenuitem.Forexample,“ChooseTools> Options”meanschoosetheOptionsitemfromtheToolsmenu.

Feature,

Product,

and

Platform

Markup

Feature, product,andplatformmarkupidentifies paragraphsthatcontain

feature-specific,product-specific,orplatform-specificinformation.Someexamples of thismarkupfollow:

Dynamic Server

Identifies informationthatisspecific toIBMInformixDynamicServer

End ofDynamicServer WindowsOnly

Identifies informationthatisspecific totheWindows operatingsystem

End ofWindowsOnly

Thismarkupcanapplytooneormoreparagraphswithina section.Whenan entiresection appliestoa particularproductorplatform,thisisnotedaspartof theheadingtext,forexample:

TableSorting(Windows)

Example

Code

Conventions

Examples ofSQLcodeoccur throughoutthispublication.Exceptasnoted,thecode isnotspecifictoanysingleIBMInformix applicationdevelopmenttool.

IfonlySQLstatementsare listedintheexample,theyare notdelimitedby semicolons. Forinstance,youmightseethecode inthefollowingexample:

CONNECT TO stores_demo ...

DELETE FROM customer WHERE customer_num = 121 ...

COMMIT WORK DISCONNECT CURRENT

TousethisSQLcodefor aspecificproduct, youmust applythesyntaxrulesfor thatproduct. Forexample,ifyouareusingDB–Access,youmustdelimit multiple statementswith semicolons.IfyouareusinganSQLAPI, youmust useEXECSQL

For additionaldocumentationaboutIBM InformixDynamicServerand related products,includingreleasenotes,machinenotes,and documentationnotes,go to theonline productlibrarypageathttp://www.ibm.com/software/data/informix/ pubs/library/.Alternatively,youcanaccessorinstalltheproductdocumentation fromtheQuickStartCDthatisshippedwiththeproduct.

Compliance

with

Industry

Standards

TheAmericanNationalStandardsInstitute(ANSI)andtheInternational Organization ofStandardization(ISO)havejointlyestablished aset ofindustry standards fortheStructuredQuery Language(SQL). IBMInformixSQL-based productsare fullycompliantwith SQL-92EntryLevel (publishedasANSI X3.135-1992), whichisidenticaltoISO9075:1992.Inaddition,manyfeaturesof IBM Informixdatabaseserverscomplywith theSQL-92IntermediateandFull Level andX/OpenSQLCommonApplicationsEnvironment (CAE)standards.

Syntax

Diagrams

Thisguideusessyntaxdiagrams builtwith thefollowingcomponentstodescribe thesyntax forstatementsandallcommandsotherthansystem-levelcommands.

Table1.SyntaxDiagramComponents

ComponentrepresentedinPDF ComponentrepresentedinHTML Meaning

>>--- Statementbegins.

---> Statementcontinuesonnext line.

>--- Statementcontinuesfrom previousline.

--->< Statementends.

---SELECT--- Requireditem.

’---LOCAL---’ Optionalitem. +--DISTINCT---+ ’---UNIQUE---’

Requireditemwithchoice. Oneandonlyoneitemmust bepresent.

+--FOR UPDATE---+ ’--FOR READ ONLY--’

Optionalitemswithchoice areshownbelowthemain line,oneofwhichyoumight specify.

.---NEXT---.

+---PRIOR---+ ’---PREVIOUS---’

Thevaluesbelowthemain lineareoptional,oneof whichyoumightspecify.If youdonotspecifyanitem, thevalueabovethelinewill beusedasthedefault.

Table1.SyntaxDiagramComponents (continued)

ComponentrepresentedinPDF ComponentrepresentedinHTML Meaning

.---,---. V |

+---index_name---+ ’---table_name---’

Optionalitems.Severalitems areallowed;acommamust precedeeachrepetition.

>>-| Table Reference |->< _{Referencetoasyntax} segment. Table Reference |--+---view---+--| +---table---+ ’----synonym---’ Syntaxsegment.

How

to

Read

a

Command-Line

Syntax

Diagram

Thefollowingcommand-line syntaxdiagramusessomeoftheelementslisted in thetableinSyntaxDiagrams.

CreatingaNo-Conversion Job

onpladm create job job

-p project

-n -d device -D database

-t table



(1) Setting the Run Mode

-S server -T target

-f d p a l c u n N

Toseehowtoconstruct acommandcorrectly,start atthetopleftof themain diagram.Followthediagramtotheright,includingtheelementsthatyouwant. The elementsinthisdiagramarecasesensitivebecause theyillustrateutility syntax. Othertypesofsyntax,suchasSQL,arenotcasesensitive.

The CreatingaNo-ConversionJob diagramillustratesthefollowingsteps: 1. Typeonpladmcreatejoband thenthenameof thejob.

2. Optionally, type-pandthen thenameoftheproject.

3. Typethefollowingrequiredelements:

v -n

v -dand thenameofthedevice

v

-D andthenameof thedatabase

v -tand thenameofthetable

4. Optionally, youcanchooseoneormoreof thefollowingelementsand repeat

themanarbitrarynumberoftimes: v -Sand theservername

v -Tand thetarget servername

v Therunmode.Toset therunmode,followtheSettingtheRunMode

segmentdiagramtotype-f,optionallytyped,p,ora,andthen optionally type loru.

5. Followthediagramtotheterminator.

Keywords

and

Punctuation

Keywords arewordsreservedforstatementsandallcommandsexcept system-levelcommands.Whena keywordappearsin asyntaxdiagram,itis shown inuppercaseletters. Whenyouusea keywordina command,youcan writeitin uppercaseorlowercaseletters, butyoumustspellthekeywordexactly asitappears inthesyntaxdiagram.

Youmust alsouseanypunctuationinyourstatementsand commandsexactlyas shown inthesyntaxdiagrams.

Identifiers

and

Names

Variablesserveasplaceholders foridentifiersandnamesinthesyntaxdiagrams and examples.Youcanreplace avariable withanarbitraryname,identifier,or literal, dependingonthecontext.Variablesarealsousedtorepresentcomplex syntax elementsthatareexpandedinadditionalsyntaxdiagrams.Whenavariable appears inasyntax diagram,an example,or text,itisshowninlowercase italic.

The followingsyntax diagramusesvariablestoillustratethegeneralformofa simple SELECTstatement.

Whenyouwritea SELECTstatementofthisform,youreplace thevariables

column_name andtable_namewith thenameofa specificcolumnand table.

IBM

Welcomes

Your

Comments

Wewanttoknowaboutanycorrectionsorclarificationsthatyouwouldfind usefulinourpublications, whichwillhelpus improvefutureversions. Includethe followinginformation:

v

Thenameand versionofthepublicationthatyouareusing

v Sectionand pagenumber

v Yoursuggestionsaboutthepublication

Sendyour commentstousat thefollowinge-mailaddress:

[email protected]

Thise-mailaddressisreservedfor reportingerrorsandomissionsinour documentation. Forimmediatehelpwith atechnicalproblem,contactIBM TechnicalSupport.Forinstructions,seetheIBMInformixTechnicalSupport websiteathttp://www.ibm.com/planetwide/.

Chapter

1.

About

Large

Object

Locator

InThisChapter. . . 1-1 UsingLargeObjectLocator. . . 1-2 LargeObjectLocatorDataTypes . . . 1-2 LargeObjectLocatorFunctions . . . 1-2 Limitations . . . 1-3 TransactionRollback . . . 1-3 ConcurrentAccess . . . 1-3 InstallationandRegistration . . . 1-3

In

This

Chapter

Thischapterprovidesanoverviewof theIBM InformixLargeObject Locator DataBlademodule.

LargeObjectLocator enablesyoutocreateasingle consistentinterfacetolarge objects.Itextendstheconceptoflargeobjectstoinclude datastoredoutsidethe database.

IBM InformixDynamicServer storeslargeobjectdata(datathatexceedsalength of 255bytesorcontainsnon-ASCIIcharacters)incolumns inthedatabase.Youcan accessthisdatausingstandardSQLstatements.Theserveralsoprovidesfunctions forcopyingdatabetweenlargeobjectcolumns andfiles.SeeIBM InformixGuideto SQL:Syntax andIBMInformixGuidetoSQL:Tutorialformoreinformation.

With LargeObjectLocator youcreatea referencetoa largeobjectand storethe reference asarowinthedatabase.Theobjectitselfcanresideoutside thedatabase: forexample,ona filesystem (oritcouldbe aBLOBor CLOBtype columninthe database).Thereference identifiesthetype,oraccessprotocol,of theobjectand points toitsstorage location.For example,youcouldidentifyanobjectasafile and provideapathnametoitoridentifyitasabinary orcharactersmartlarge objectstoredinthedatabase.Smart largeobjectsare acategoryoflargeobjectsthat include CLOBand BLOBdatatypes,whichstoretextandimages.Smartlarge objectsare storedandretrievedinpieces, andhavedatabasepropertiessuchas crash recoveryandtransactionrollback.

Using

Large

Object

Locator

LargeObjectLocator isimplementedthroughtwodatatypesandaset of functions,describednext.

Large

Object

Locator

Data

Types

LargeObjectLocator definestwodatatypes, lld_locatorandlld_lob.

Youusethelld_locatortype toidentifytheaccessprotocolfora largeobjectand to point toitslocation.Thistype isa rowtype,storedasa rowinthedatabase.You caninsert,select, delete,and updateinstancesoflld_locatorrowsinthedatabase usingstandardSQLINSERT,SELECT,DELETE,andUPDATEstatements.

Youcanalsopass anlld_locatorrowtovariousLargeObject Locatorfunctions.For example,tocreate,delete,orcopyalargeobject, andtoopena largeobjectfor readingorwriting,youpassanlld_locatorrowtotheappropriateLargeObject Locator function.See“lld_locator”onpage2-1foradetaileddescriptionofthis datatype.

The lld_lobtypeenablesLargeObjectLocator toreferencesmartlargeobjects, whichare storedasBLOBorCLOBdatainthedatabase.Thelld_lob typeis identical totheBLOB andCLOBtypesexceptthat,inaddition topointingtothe data,ittrackswhethertheunderlyingsmartlargeobjectcontainsbinaryor characterdata.

See “lld_lob”onpage2-2for acompletedescriptionofthis datatype.

Large

Object

Locator

Functions

LargeObjectLocator providesa setoffunctionssimilartoUNIXI/Ofunctionsfor manipulatinglargeobjects.Youusethesamefunctionsregardlessof howorwhere theunderlyinglargeobjectisstored.

The LargeObjectLocator functionscanbedividedintofourmaincategories: v

Basicfunctionsforcreating,opening,closing,deleting,andreadingfromand

writingtolargeobjects.

v Clientfunctionsforcreating,opening,anddeletingclient filesandforcopying

largeobjectstoandfromclientfiles.Afteryouopenaclientfile,youcanusethe basicfunctionstoreadfromandwritetothefile.

v Utilityfunctionsforraising errorsandconverting errorstotheirSQLstate

equivalents.

v Smartlargeobjectfunctionsforcopyingsmartlargeobjectstofilesand toother

smartlargeobjects.

Thereare threeinterfacestotheLargeObject Locatorfunctions: v AnAPIlibrary

v AnESQL/Clibrary

v AnSQLinterface

All LargeObjectLocatorfunctionsareimplemented asAPIlibraryfunctions.You cancallLargeObjectLocatorfunctionsfromuser-definedroutineswithin an

All LargeObjectLocatorfunctions,exceptlld_error_raise(),areimplementedas ESQL/Cfunctions.YoucanusetheLargeObjectLocator functionstobuild ESQL/Capplications.

Alimited setoftheLargeObjectLocatorfunctionsareimplemented as user-definedroutines thatyoucanexecutewithinSQLstatements.See“SQL Interface” onpage3-2foralistoftheLargeObjectLocatorfunctionsthatyoucan executedirectlyinSQLstatements.

Chapter3,“LargeObject LocatorFunctions,”onpage3-1,describesalltheLarge Object Locatorfunctionsandthethreeinterfacesindetail.

Limitations

Certain limitationsareinherentinusinglargeobjectswitha database,becausethe objectsthemselves,exceptforsmartlargeobjects,arenotstoredinthedatabase and arenotsubjecttodirectcontrolbytheserver.Twospecific areasofconcernare transactionrollbackand concurrencycontrol.

Transaction

Rollback

Becauselargeobjects,otherthansmartlargeobjects,arestored outsidethe database,anychangestothemtakeplaceoutsidetheserver’scontrolandcannot be rolledbackif atransactionisaborted.For example,whenyouexecute

lld_create(),itcallsanoperatingsystemroutinetocreatethelargeobjectitself.If yourollbackthetransactioncontaining thecalltolld_create(),theserverhasno way ofdeletingtheobjectthatyouhavejustcreated.

Therefore,youare responsibleforcleaningupanyresourcesyouhaveallocatedif an erroroccurs. Forexample,ifyoucreatealargeobjectandthetransactionin whichyoucreateitisaborted,youshoulddeletetheobjectyouhavecreated. Likewise,if youhaveopeneda largeobjectandthetransactionisaborted(oris committed), youshouldclosethelargeobject.

Concurrent

Access

For thesamereason,LargeObjectLocatorprovides nodirectway ofcontrolling concurrentaccesstolargeobjects.Ifyouopenalargeobjectforwriting,itis possibletohavetwoseparateprocessesoruserssimultaneouslyalterthelarge object. Youmust provideameans,suchaslockinga row,toguaranteethat multiple userscannotaccessalargeobjectsimultaneouslyforwriting.

Chapter

2.

Large

Object

Locator

Data

Types

InThisChapter. . . 2-1 lld_locator . . . 2-1 lld_lob. . . 2-2

In

This

Chapter

ThischapterdescribestheLargeObjectLocatordatatypes, lld_locatorandlld_lob.

lld_locator

Thelld_locatordatatype identifiesa largeobject.Itspecifiesthekindoflarge objectandprovides apointertoitslocation.lld_locatorisarow typeandis definedasfollows:

create row type informix.lld_locator {

lo_protocol char(18 lo_pointer informix.lld_lob0 lo_location informix.lvarchar }

lo_protocol identifies thekindoflargeobject.

lo_pointer isapointer toasmartlargeobject,orisNULLifthelargeobjectis anykindoflargeobjectotherthanasmartlargeobject.

lo_location isapointer tothelargeobject, ifitisnotasmartlargeobject.Set toNULLifit isa smartlargeobject.

Inthelo_protocolfield,specifythekindoflargeobjecttocreate.Thekindoflarge objectyouspecifydeterminesthevaluesoftheothertwofields:

v

Ifyouspecifyasmartlargeobject:

– usethelo_pointerfieldtopointtoit.

– specifyNULLforthelo_locationfield.

v Ifyouspecifyanyotherkindoflargeobject:

– specifyNULLforthelo_pointerfield.

Tip: Althoughthelld_locatortype isnotcurrentlyextensible,itmightbecomeso

later.Toavoidfuturenamespacecollisions,theprotocols establishedbyLarge ObjectLocator allhaveanIFXprefix.

Table2-1.Fieldsoflld_locatorDataType

lo_protocol lo_pointer lo_location Description

IFX_BLOB Pointertoasmartlargeobject NULL Smartlargeobject IFX_CLOB Pointertoasmartlargeobject NULL Smartlargeobject

IFX_FILE NULL pathname Fileaccessibleonserver

Important: Thelo_protocolfieldiscaseinsensitive.Itisshown inuppercaseletters

fordisplaypurposesonly.

The lld_locatortype isan instanceofa rowtype.Youcaninsert arowintothe database usingan SQLINSERTstatement,oryoucanobtainarowbycallingthe DataBladeAPImi_row_create()function.SeetheIBMInformix ESQL/C

Programmer'sManual forinformationonrowtypes.SeetheIBMInformix DataBlade APIProgrammer'sGuideforinformationonthemi_row_create()function.

Toreference anexistinglargeobject,youcaninsert anlld_locatorrowdirectlyinto a tableinthedatabase.

Tocreatea largeobject,and areferencetoit,youcancallthelld_create() function and passanlld_locatorrow.

Youcanpassanlld_locatortype totheseLargeObjectLocatorfunctions,described in Chapter3,“LargeObject LocatorFunctions,”onpage3-1:

v lld_copy(),page3-5 v lld_create(),page3-7 v lld_delete(),page3-9 v lld_open(),page3-10 v lld_from_client(),page3-20 v lld_to_client(),page3-24

lld_lob

The lld_lobdatatypeisauser-definedtype.Youcanuseittospecifythelocation of asmartlargeobjectand tospecifywhethertheobjectcontainsbinaryor characterdata.

The lld_lobdatatypeisdefinedforusewiththeAPIasfollows:

typedef struct {

MI_LO_HANDLE lo; mi_integer type; } lld_lob_t;

ItisdefinedforESQL/Casfollows:

typedef struct {

lo isapointer tothelocationofthesmartlargeobject.

type isthetypeoftheobject.For anobjectcontaining binarydata,set

typetoLLD_BLOB;foranobjectcontainingcharacterdata,settype

toLLD_CLOB.

Thelld_lob typeisequivalenttotheCLOBorBLOBtype inthatitpointstothe locationofa smartlargeobject.Inaddition,itspecifieswhethertheobjectcontains binaryor characterdata.Youcanpass thelld_lobtype asthelo_pointerfieldofan lld_locatorrow.Youshouldset thelld_lob_t.typefieldtoLLD_BLOBforbinary dataand toLLD_CLOBforcharacterdata.

See “Usingthelld_lobType”onpage4-1forexamplecodethatusesthelld_lob type.

LOBLocator providesexplicit castsfrom: v aCLOBtypetoanlld_lobtype.

v aBLOBtype toanlld_lobtype.

v anlld_lobtypetotheappropriateBLOBorCLOBtype.

Tip: Ifyouattempttocastanlld_lob typecontainingbinarydataintoa CLOB

typeoran lld_lobtypecontainingcharacterdataintoa BLOBtype,Large ObjectLocator returnsanerrormessage.

Youcanpassanlld_lob typetothesefunctions,describedinChapter3, “Large Object LocatorFunctions,”onpage3-1:

v LOCopy,page3-29

v LOToFile,page3-30

v LLD_LobType,page3-31

NotethatLOCopyand LOToFileareoverloadedversionsof built-inserver functions.Theonlydifferenceisthatyoupass anlld_lobtotheLargeObject Locator versionsofthese functionsand aBLOBor CLOBtype tothebuilt-in versions.

Chapter

3.

Large

Object

Locator

Functions

InThisChapter. . . 3-1 Interfaces . . . 3-1 APILibrary . . . 3-2 ESQL/CLibrary . . . 3-2 SQLInterface . . . 3-2 WorkingwithLargeObjects . . . 3-2 lld_close(). . . 3-4 lld_copy(). . . 3-5 lld_create() . . . 3-7 lld_delete() . . . 3-9 lld_open() . . . 3-10 lld_read() . . . 3-12 lld_seek() . . . 3-13 lld_tell() . . . 3-15 lld_write() . . . 3-16 ClientFileSupport . . . 3-17 lld_create_client(). . . 3-18 lld_delete_client(). . . 3-19 lld_from_client() . . . 3-20 lld_open_client() . . . 3-22 lld_to_client() . . . 3-24 ErrorUtilityFunctions . . . 3-25 lld_error_raise() . . . 3-26 lld_sqlstate() . . . 3-27 SmartLargeObjectFunctions. . . 3-28 LOCopy . . . 3-29 LOToFile. . . 3-30 LLD_LobType . . . 3-31

In

This

Chapter

ThischapterbrieflydescribesthethreeinterfacestoLargeObjectLocator and describes indetailalltheLargeObjectLocatorfunctions.

Interfaces

API

Library

All LargeObjectLocatorfunctionsexceptthesmartlargeobjectfunctionsare implemented asAPIfunctionsdefinedinheaderandlibraryfiles(lldsapi.hand

lldsapi.a).

YoucancalltheLargeObject LocatorAPIfunctionsfromyourown user-defined routines.YouexecuteLargeObjectLocatorAPIfunctionsjustasyoudofunctions provided bytheIBMInformixDataBladeAPI. SeetheIBMInformixDataBladeAPI Programmer'sGuideformoreinformation.

See “UsingtheAPI”onpage4-7foranexampleofa user-definedroutinethatcalls LargeObjectLocatorAPIfunctionstocopypartofa largeobjecttoanotherlarge object.

ESQL/C

Library

All LargeObjectLocatorfunctionsexceptlld_error_raise()and thesmartlarge objectfunctionsareimplemented asESQL/Cfunctions,definedinheaderand libraryfiles(lldesql.hand lldesql.so).

Wherever possible,theESQL/CversionsoftheLargeObjectLocatorfunctions avoidserverinteractionbydirectlyaccessingtheunderlyinglargeobject.

See theIBM InformixESQL/CProgrammer'sManualformoreinformationonusing theESQL/CinterfacetoexecuteLargeObjectLocatorfunctions.

SQL

Interface

The followingLargeObjectLocator functionsareimplementedasuser-defined routines thatyoucanexecutewithinSQLstatements:

v LLD_LobType() v LLD_Create() v LLD_Delete() v LLD_Copy() v LLD_FromClient() v LLD_ToClient() v LOCopy() v LOToFile()

See thefollowingthree-volumeset forfurtherinformationabouttheIBMInformix SQLinterface:

v IBMInformixGuidetoSQL:Reference

v IBMInformixGuidetoSQL:Syntax

v

IBMInformixGuidetoSQL:Tutorial

Working

with

Large

Objects

Thissectiondescribes functionsthatallowyouto: v createlargeobjects.

v copyalargeobject.

Generally,youusethefunctionsdescribedinthissection inthefollowingorder. 1. Youuselld_create() tocreatealargeobject. Itreturnsa pointertoan

lld_locatorrowthatpoints tothelargeobject.

Ifthelargeobjectalreadyexists,youcaninsertanlld_locatorrow intoatable inthedatabasetopointtotheobjectwithoutcallinglld_create().

2. Youcanpassthelld_locatortypetothelld_open()function toopenthelarge

objectyoucreated.ThisfunctionreturnsanLLD_IOstructurethatyoucan passtovariousLargeObjectLocatorfunctionstomanipulatedataintheopen object(seeStep3).

Youcanalsopassthelld_locatortypetothelld_copy(),lld_from_client(),or

lld_to_client()functionstocopythelargeobject.

3. Afteryouopena largeobject,youcanpass theLLD_IOstructureto:

v lld_tell()toreturn thecurrentpositionwithin thelargeobject.

v lld_seek() tochangethecurrentpositionwithin theobject.

v lld_read() toreadfromlargeobject.

v

lld_write() towritetothelargeobject.

v lld_close()tocloseanobject.Youshouldclosea largeobjectif the

transactioninwhichyouopenit isabortedorcommitted.

Tip: Todeletealargeobject, youcanpassthelld_locatorrowtolld_delete()any

timeafteryoucreateit.For example,ifthetransactioninwhichyoucreated theobjectisabortedandtheobjectisnota smartlargeobject, youshould deletetheobjectbecausetheserver’srollbackonthetransactioncannotdelete anobjectoutsidethedatabase.

Thefunctionswithin thissectionare presentedinalphabeticalorder,notinthe orderin whichyoumightusethem.

lld_close()

Thisfunction closesthespecifiedlargeobject.

Syntax

API:

mi_integer lld_close (conn, io, error) MI_CONNECTION* conn; LLD_IO* io; mi_integer* error; ESQL/C:

int lld_close (LLD_IO* io, int* error);

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.IntheESQL/Cversionofthis function,you must alreadybe connectedtoaserver.

io isapointer toanLLD_IOstructure createdwitha previouscallto thelld_open()function.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode.

Usage

The lld_close()function closestheopenlargeobjectandfrees thememory allocatedfortheLLD_IOstructure, whichyoucannotuseagainafter thiscall.

Return

codes

For anAPIfunction,returnsMI_OKif thefunctionsucceedsand MI_ERRORifitfails.

For anESQL/Cfunction,returns0 ifthefunctionsucceeds and-1if itfails.

Context

lld_copy()

Thisfunctioncopiesthespecifiedlargeobject.

Syntax

API:

MI_ROW* lld_copy(conn, src, dest, error); MI_CONNECTION* conn, MI_ROW* src, MI_ROW* dest, mi_integer* error ESQL/C:

ifx_collection_t* lld_copy (src, dest, error); EXEC SQL BEGIN DECLARE SECTION;

PARAMETER ROW src; PARAMETER ROW dest; EXEC SQL END DECLARE SECTION; int* error;

SQL:

CREATE FUNCTION LLD_Copy (src LLD_Locator, dest LLD_Locator) RETURNS LLD_Locator;

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()function.Thisparameterisfor theAPIinterfaceonly.IntheESQL/CandSQLversionsofthis function,youmustalreadybeconnected toaserver.

src isapointer tothelld_locatorrow,identifyingthesourceobject.

dest isapointer toanlld_locatorrow,identifying thedestination object. Ifthedestinationobjectitselfdoesnotexist, itiscreated.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode. TheSQLversion ofthisfunctiondoesnothaveanerrorparameter.

Usage

Thisfunctioncopiesanexistinglargeobject.

Ifthedestinationobjectexists,passapointertoitslld_locatorrowasthedest

parameter.

Ifyouarecopyingtoasmartlargeobject,specifyNULLforthelo_pointerand

lo_locationfields ofthelld_locatorrowthatyoupassasthedestparameter.The

lld_copy()function returnsanlld_locatorrowwitha pointertothenewsmart largeobjectinthelo_pointerfield.

The serverdeletes anewsmartlargeobjectattheendofa transactionifthereare nodiskreferencestoitand ifitisclosed.Therefore,aftercopyingtoanewly created smartlargeobject, eitheropenitorinsert itintoatable.

Iflld_copy()creates anew smartlargeobject, itusessystemdefaultsfor required storage parameterssuchassbspace.Ifyouwanttooverride theseparameters,you canusetheserverlargeobjectinterfacetocreatethesmartlargeobjectandspecify theparametersyouwantinanMI_LO_SPECstructure.Youcanthen call

lld_copy()and setthelo_pointerfieldof thelld_locatorrow topointtothenew smartlargeobject.

Likewise, ifprotocolsare addedtoLargeObject Locatorfornewtypesoflarge objects,theseobjectsmight requirecreationattributesor parametersforwhich LargeObjectLocator suppliespredefined defaultvalues.Aswithsmartlarge objects,youcancreatetheobjectwithlld_copy()and acceptthedefaultvalues,or youcanusethecreationroutinesspecific tothenewprotocolandsupplyyour own attributesandparameters.Afteryoucreatetheobject,youcancalllld_copy()

and passitanlld_locatorrow thatpointstothenewobject.

Return

codes

On success,thisfunction returnsapointertoanlld_locatorrow,specifyingthe locationofthecopyofthelargeobject.Ifthedestinationobjectalreadyexists,

lld_copy()returnsa pointertotheunalteredlld_locatorrowyoupassedinthedest

parameter.Ifthedestinationobjectdoesnotalreadyexist,lld_copy()returnsa pointer toanlld_locatorrow,pointingtothenewobjectitcreates.

On failure,thisfunctionreturnsNULL.

Context

lld_from_client(), page3-20

lld_create()

Thisfunctioncreates anew largeobjectwith theprotocoland locationyouspecify.

Syntax

API:

MI_ROW* lld_create(conn, lob, error) MI_CONNECTION* conn MI_ROW* lob; mi_integer* error; ESQL/C:

ifx_collection_t* lld_create (lob, error); EXEC SQL BEGIN DECLARE SECTION;

PARAMETER ROW lob;

EXEC SQL END DECLARE SECTION; int* error;

SQL:

CREATE FUNCTION LLD_Create (lob LLD_Locator) RETURNS LLD_Locator;

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.IntheESQL/CandSQLversionsofthis function,youmustalreadybeconnected toaserver.

lob isapointer toanlld_locatorrow,identifying theobjecttocreate.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode. TheSQLversion ofthisfunctiondoesnothaveanerrorparameter.

Usage

Youpassan lld_locatorrow,with thefollowingvalues,asthelobparameterto

lld_create():

Inthelo_protocolfield,specifythetypeoflargeobjecttocreate.

For anytype oflargeobjectotherthanasmartlargeobject: v specifyNULLforthelo_pointerfield.

Whenyoucreatea smartlargeobject,lld_create() usessystem defaultsforrequired storage parameterssuchassbspace.Ifyouwanttooverride theseparameters,you canusetheserverlargeobjectinterfacetocreatethesmartlargeobjectandspecify theparametersyouwantinanMI_LO_SPECstructure.Youcanthen call

lld_create() andset thelo_pointerfieldofthelld_locatorrowtopointtothenew smartlargeobject.

Likewise, ifprotocolsare addedtoLargeObject Locatorfornewtypesoflarge objects,theseobjectsmight requirecreationattributesor parametersforwhich LargeObjectLocator suppliespredefined defaultvalues.Aswithsmartlarge objects,youcancreatetheobjectwithlld_create() andacceptthedefaultvalues,or youcanusethecreationroutinesspecific tothenewprotocolandsupplyyour own attributesandparameters.Afteryoucreatetheobject,youcancalllld_create()

and passitanlld_locatorrow thatpointstothenewobject.

Return

codes

On success,thisfunction returnsapointertoanlld_locatorrow specifyingthe locationofthenewlargeobject. Fora smartlargeobject, lld_create()returnsa pointer tothelocationofthenewobjectinthelo_pointerfieldofthelld_locator row.For allotherobjects,itreturnsa pointertotheunalteredlld_locatorrowyou passed inthelobparameter.

The lld_openfunction canusethelld_locatorrowthatlld_create()returns.

On failure,thisfunctionreturnsNULL.

Context

lld_delete(),page3-9

lld_delete()

Thisfunctiondeletes thespecified largeobject.

Syntax

API:

mi_integer lld_delete(conn, lob, error) MI_CONNECTION* conn; LLD_Locator lob; mi_integer* error; ESQL/C:

int lld_delete (lob, error); EXEC SQL BEGIN DECLARE SECTION;

PARAMETER ROW lob;

EXEC SQL END DECLARE SECTION; int* error;

SQL:

CREATE FUNCTION LLD_Delete (lob LLD_Locator) RETURNS BOOLEAN;

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.IntheESQL/CandSQLversionsofthis function,youmustalreadybeconnected toaserver.

lob isapointer toanlld_locatorrow,identifying theobjecttodelete.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode. TheSQLversion ofthisfunctiondoesnothaveanerrorparameter.

Usage

For largeobjectsotherthansmartlargeobjects,thisfunction deletesthelarge objectitself,notjustthelld_locatorrowreferencingit.Forsmartlargeobjects,this function doesnothing.

Todeletea smartlargeobject,deleteallreferencestoit,includingthelld_locator row referencingit.

Return

codes

lld_open()

Thisfunction opensthespecifiedlargeobject.

Syntax

API:

LLD_IO* lld_open(conn, lob, flags, error) MI_CONNECTION* conn; MI_ROW* lob; mi_integer flags, mi_integer* error); ESQL/C:

LLD_IO* lld_open(lob, flags, error); EXEC SQL BEGIN DECLARE SECTION; PARAMETER ROW lob;

EXEC SQL END DECLARE SECTION; int flags;int* error;

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.IntheESQL/Cversionofthis function,you must alreadybe connectedtoaserver.

lob isapointer toanlld_locatorrow,identifying theobjecttoopen.

flags isaset offlagsthatyoucansettospecifyattributesofthelarge objectafteritisopened.Theflags areasfollows:

LLD_RDONLY

opensthelargeobjectforreadingonly.Youcannotusethe

lld_writefunction towritetothespecified largeobject whenthisflag isset.

LLD_WRONLY

opensthelargeobjectforwritingonly.Youcannotusethe

lld_read() functiontoread fromthespecifiedlargeobject whenthisflag isset.

LLD_RDWR

opensthelargeobjectforbothreadingandwriting.

LLD_TRUNC

clearsthecontentsofthelargeobjectafteropening.

LLD_APPEND

seekstotheendofthelargeobjectforwriting.Whenthe objectisopened, thefilepointerispositionedat the beginningoftheobject.Ifyouhaveopenedtheobjectfor readingorreadingandwriting,youcanseekanywherein thefileandread.However, anytimeyoucalllld_write()to writetotheobject,thepointermovestotheendofthe objecttoguaranteethatyoudo notoverwriteanydata.

LLD_SEQ

opensthelargeobjectforsequentialaccessonly.You cannotusethelld_seek()function withthespecifiedlarge objectwhenthisflagisset.

Usage

Inthelobparameter,youpassanlld_locatorrow toidentifythelargeobjectto open.Inthelo_protocolfieldofthis row,youspecifythetype ofthelargeobjectto open.Thelld_open()functioncalls anappropriateopenroutinebasedonthetype youspecify.Forexample,fora file,lld_open()usesanoperatingsystemfile function toopenthefile,whereas,fora smartlargeobject, itcallstheserver’s

mi_lo_open()routine.

LargeObjectLocator doesnotdirectlysupporttwofundamentaldatabasefeatures, transactionrollbackand concurrencycontrol.Therefore,ifthetransactioninwhich youcalllld_open()isaborted,youshouldcalllld_close()toclosetheobjectand reclaimanyallocatedresources.

Yourapplication shouldalso providesomemeans,suchaslockinga row,to guarantee thatmultipleuserscannotwritetoalargeobjectsimultaneously.

See “Limitations”onpage1-3formoreinformationabouttransactionrollbackand concurrencycontrol.

Return

codes

On success,thisfunction returnsapointertoanLLD_IOstructureit allocates.The

LLD_IOstructureisprivate,andyoushouldnotdirectlyaccessit ormodifyits contents. Instead,youcanpass theLLD_IOstructure’spointertoLargeObject Locator routinessuchaslld_write(),lld_read(),andsoon,thataccessopenlarge objects.

Alargeobjectremainsopenuntilyouexplicitlycloseit withthelld_close()

function. Therefore,if youencountererrorconditions afteropening alargeobject, youareresponsibleforreclaimingresourcesbyclosingit.

On failure,thisfunctionreturnsNULL.

Context

lld_close(), page3-4

lld_create(), page3-7

lld_read(), page3-12

lld_read()

Thisfunction readsfroma largeobject,startingatthecurrentposition.

Syntax

API:

mi_integer lld_read (io, buffer, bytes, error)

LLD_IO* io, void* buffer, mi_integer bytes, mi_integer* error); ESQL/C:

int lld_read (LLD_IO* io,

void* buffer, int bytes, int* error);

io isapointer toanLLD_IOstructure createdwitha previouscallto thelld_open()function.

buffer isapointer toabufferintowhichtoread thedata.Thebuffer must beatleast aslargeasthenumberofbytesspecified inthe

bytesparameter.

bytes isthenumberofbytestoread.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode.

Usage

Before callingthisfunction,youmustopenthelargeobjectwitha callto

lld_open()andset theLLD_RDONLYorLLD_RDWRflag.Thelld_read() function begins readingfromthecurrentposition.Bydefault,whenyouopenalargeobject, thecurrentpositionisthebeginningof theobject.Youcancalllld_seek()tochange thecurrentposition.

Return

codes

On success,thelld_read()function returnsthenumber ofbytesthatithasread fromthelargeobject.

On failure,foranAPIfunction,itreturnsMI_ERROR;foranESQL/Cfunction,it returns-1.

Context

lld_open(), page3-10

lld_seek(), page3-13

lld_seek()

Thisfunctionsetsthepositionforthenextread orwriteoperationtoorfroma largeobjectthatisopenforreadingorwriting.

Syntax

API:

mi_integer lld_seek(conn, io, offset, whence, new_offset, error) MI_CONNECTION* conn LLD_IO* io; mi_int8* offset; mi_integer whence; mi_int8* new_offset; mi_integer* error; ESQL/C:

int lld_seek(io,offset, whence, new_offset, error) LLD_IO* io;

EXEC SQL BEGIN DECLARE SECTION; PARAMETER int8* offset; EXEC SQL END DECLARE SECTION; EXEC SQL BEGIN DECLARE SECTION;

PARAMETER int8* new_offset; EXEC SQL END DECLARE SECTION;

int whence; int* error;

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.TheESQL/Cversionofthisfunction is based ontheassumptionthatyouarealreadyconnectedtoa server.

io isapointer toanLLD_IOstructure createdwitha previouscallto thelld_open()function.

offset isapointer totheoffset.Itdescribes wheretoseekintheobject.Its value dependsonthevalueof thewhence parameter.

v Ifwhence isLLD_SEEK_SET,theoffsetismeasuredrelativetothe

beginningoftheobject.

v Ifwhence isLLD_SEEK_CUR,theoffsetisrelativetothecurrent

positionintheobject.

Return

codes

For anAPIfunction,returnsMI_OKif thefunctionsucceedsand MI_ERRORifitfails.

For anESQL/Cfunction,returns0 ifthefunctionsucceeds and-1if thefunction fails.

Context

lld_open(), page3-10 lld_read(), page3-12 lld_tell(),page3-15 lld_write(),page3-16

lld_tell()

Thisfunctionreturnstheoffsetforthenextread orwriteoperationonan open largeobject.

Syntax

API:

mi_integer lld_tell(conn, io, offset, error) MI_CONNECTION* conn; LLD_IO* io, mi_int8* offset; mi_integer* error; ESQL/C:

int lld_tell (io, offset, error); LLD_IO* io;

EXEC SQL BEGIN DECLARE SECTION; PARAMETER int8* offset; EXEC SQL END DECLARE SECTION;

int* error;

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.IntheESQL/Cversionofthis function,you must alreadybe connectedtoaserver.

io isapointer toanLLD_IOstructure createdwitha previouscallto thelld_open()function.

offset isapointer toanint8thatyouallocate.Thefunction returnsthe offsetinthis int8.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode.

Usage

Before callingthisfunction,youmustopenthelargeobjectwitha callto

lld_open().

Return

codes

For anAPIfunction,returnsMI_OKif thefunctionsucceedsand MI_ERRORifitfails.

For anESQL/Cfunction,returns0 ifthefunctionsucceeds and-1if thefunction fails.

lld_write()

Thisfunction writesdatatoanopenlargeobject,startingatthecurrentposition.

Syntax

API:

mi_integer lld_write (conn, io, buffer, bytes, error) MI_CONNECTION* conn; LLD_IO* io; void* buffer; mi_integer bytes; mi_integer* error; ESQL/C:

int lld_write (LLD_IO* io, void* buffer, int bytes, int* error);

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.IntheESQL/Cversionofthis function,you must alreadybe connectedtoaserver.

io isapointer toanLLD_IOstructure createdwitha previouscallto thelld_open()function.

buffer isapointer toabufferfromwhichtowritethedata.Thebuffer must beatleast aslargeasthenumberofbytesspecified inthe

bytesparameter.

bytes isthenumberofbytestowrite.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode.

Usage

Before callingthisfunction,youmustopenthelargeobjectwitha callto

lld_open()andset theLLD_WRONLYorLLD_RDWRflag.Thelld_write()

function beginswritingfromthecurrentposition.Bydefault,whenyouopena largeobject, thecurrentpositionisthebeginningoftheobject.Youcancall

lld_seek() tochangethecurrentposition.

Ifyouwanttoappenddatatotheobject,specifytheLLD_APPENDflagwhenyou opentheobjecttosetthecurrentpositiontotheendoftheobject.Ifyouhave done soandhaveopened theobjectforreadingandwriting,youcanstill use

lld_seek tomovearoundintheobjectand readfromdifferentplaces.However,as soon asyoubegintowrite,thecurrentpositionismovedtotheendoftheobject toguarantee thatyoudonotoverwriteanyexistingdata.

Return

codes

On success,thelld_write()function returnsthenumberof bytesthatit haswritten.

On failure,foranAPIfunction itreturnsMI_ERROR;foranESQL/Cfunction,it returns-1.

Context

Client

File

Support

Thissectiondescribes theLargeObjectLocator functionsthatprovideclientfile support.Thesefunctionsallowyoutocreate,open,anddeleteclientfilesand to copy largeobjectsto andfromclientfiles.

Theclient functionsmake iteasiertocodeuser-definedroutinesthatinputor outputdata.Theseuser-definedroutines, inmanycases,operateonlargeobjects. They alsoinputdatafromoroutputdatatoclientfiles.Developerscancreatetwo versions ofauser-definedroutine:oneforclientfiles,whichcalls

lld_open_client(),andoneforlargeobjects,whichcalls lld_open().Afterthelarge objectorclientfileisopen,youcanuseanyoftheLargeObjectLocatorfunctions thatoperateonopenobjects,suchaslld_read(),lld_seek(),andsoon.Thus,the remainingcodeof theuser-definedfunction canbe thesameforbothversions.

YoushouldusetheLargeObjectLocator clientfunctionswith care.Youcanonly accessclientfilesif youareusingtheclientmachineonwhichthefilesarestored. Ifyouchangeclientmachines,youcannolongeraccessfilesstoredontheoriginal client machine.Thus,anapplicationthatstoresclientfilenamesin thedatabase might findata laterdatethatthefilesareinaccessible.

lld_create_client()

Thisfunction createsanew clientfile.

Syntax

API:

mi_integer lld_create_client(conn, path, error); MI_CONNECTION* conn

mi_string* path; mi_integer* error; ESQL/C:

int lld_create_client (char* path, int* error);

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.IntheESQL/Cversionofthis function,you must alreadybe connectedtoaserver.

path isapointer tothepathnameoftheclientfile.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode.

Usage

Thisfunction createsafileonyourclient machine.Usethelld_open_client()

function toopenthefileforreadingor writingandpassitthesamepathnameas youpassedtolld_create_client().

LargeObjectLocator doesnotdirectlysupporttransactionrollback,exceptfor smartlargeobjects.Therefore,ifthetransactioninwhichyoucall

lld_create_client() isaborted,youshouldcalllld_delete_client()todeletethe objectandreclaimanyallocatedresources.

See “TransactionRollback”onpage1-3for moreinformation.

Return

codes

For anAPIfunction,returnsMI_OKif thefunctionsucceedsand MI_ERRORifitfails.

For anESQL/Cfunction,returns0 ifthefunctionsucceeds and-1if thefunction fails.

Context

lld_delete_client()

Thisfunctiondeletes thespecified clientfile.

Syntax

API:

mi_integer lld_delete_client(conn, path, error) MI_CONNECTION* conn;

mi_string* path; mi_integer* error; ESQL/C:

int lld_delete_client (char* path,int* error);

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.IntheESQL/Cversionofthis function,you must alreadybe connectedtoaserver.

path isapointer tothepathnameoftheclientfile.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode.

Usage

Thisfunctiondeletes thespecified clientfileandreclaimsanyallocatedresources.

Return

codes

For anAPIfunction,returnsMI_OKif thefunctionsucceedsand MI_ERRORifitfails.

For anESQL/Cfunction,returns0 ifthefunctionsucceeds and-1if thefunction fails.

Context

lld_from_client()

Thisfunction copiesa clientfiletoalargeobject.

Syntax

API:

MI_ROW* lld_from_client(conn, src, dest, error); MI_CONNECTION* conn,

mi_string* src, MI_ROW* dest, mi_integer* error ESQL/C:

ifx_collection_t* lld_from_client (src, dest, error); char* src;

EXEC SQL BEGIN DECLARE SECTION; PARAMETER ROW dest;

EXEC SQL END DECLARE SECTION; int* error;

SQL:

CREATE FUNCTION LLD_FromClient(src LVARCHAR, dest LLD_Locator)

RETURNS LLD_Locator;

conn istheconnectiondescriptorestablished bya previouscalltothe

mi_open()ormi_server_connect()functions.Thisparameter isfor theAPIinterfaceonly.IntheESQL/CandSQLversionsofthis function,youmustalreadybeconnected toaserver.

src isapointer tothesourcepathname.

dest isapointer tothedestination lld_locatorrow.Ifthedestination objectitselfdoesnotexist, itiscreated.

error isanoutputparameterinwhichthefunctionreturnsanerrorcode. TheSQLversion ofthisfunctiondoesnothaveanerrorparameter.

Usage

Thisfunction copiesanexistinglargeobject.

Ifthedestinationobjectexists,passapointertoitslld_locatorrowasthedest

parameter.

Ifthedestinationobjectdoesnotexist,passanlld_locatorrowwith thefollowing valuesasthedestparametertolld_from_client().

Inthelo_protocolfield,specifythetypeoflargeobjecttocreate.

Ifyouarecopyingtoanytype oflargeobjectotherthanasmartlargeobject: v

specifyNULLforthelo_pointerfield.

v pointtothelocationofthenew objectinthelo_locationfield.

The lld_from_client()function createsthetypeoflargeobjectthatyouspecify, copiesthesourcefiletoit,andreturnstherow youpassed,unaltered.