DBKit- Trying to get a SQL function in a query

Thread View: comp.sys.next.programmer

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by Mark N. Roder Wed, 09 Sep 1992 19:40

#902

Author: Mark N. Roder
Date: Wed, 09 Sep 1992 19:40

51 lines
2109 bytes


	I am trying to find a easy answer to the following problem.  I am  
doing a phone-book type application and want to use the SQL function  
soundex to do fuzzy type of searchs on last name.

	I (will) have a trigger to create a soundex value in the table  
whenever a insert/update is done.  We will call that field sl, with the  
name field being name.  The type of query I want to do is like this:
	SELECT name FROM entries
	WHERE sl LIKE SOUNDEX('looking_for_this_name')

How can I do this with DBKit?.  

	The way I look at it, there is 2 different ways to approach this.

	1) try to get a value back from a SQL function.  I can then use  
that in the building of the qualifier.  In looking at the docs, the  
evaluateString: method would do the trick, but I don't know how to get the  
value back from that.  I can do the following from withn isql:
> 1> SELECT SOUNDEX('Anderson')
> 2> go
>        
>  ----- 
>  A536  
> 
> (1 row affected)
> 1> 
	Now, If I send it that string using evaluateString, how do I get  
the value back?   This would also be nice in finding things like MAX, MIN,  
AVG,etc


	2) Have some way of building the qualifier that has something like  
the following:
sprintf(buff,"SOUNDEX('%s')")
dbQualifier = [[DBQualifier allocFromZone:[self zone] ] 
                initForEntity:[dbModule entity]
                fromDescription:"%@ LIKE %XXX", SoundsLike,buff];
					^^^^^^ don't know 

	Has anybody solved this (probably simple) problem already?  

Thanks for the help


--
Mark Roder
NeXT Consultant, Mayo Foundation
NeXT Mail: 	roder@mayo.edu
phone:		(507) 284-5306

Re: DBKit- Trying to get a SQL function in a query

#924

Author: Jon C. Buffington
Date: Thu, 10 Sep 1992 13:12

83 lines
3310 bytes

In article <1992Sep9.194037.14018@bmw.mayo.edu> roder@mayo.edu (Mark N.  
Roder) writes:
> 
> 
> 	I am trying to find a easy answer to the following problem.  I am  
> doing a phone-book type application and want to use the SQL function  
> soundex to do fuzzy type of searchs on last name.
> 
> 	I (will) have a trigger to create a soundex value in the table  
> whenever a insert/update is done.  We will call that field sl, with the  
> name field being name.  The type of query I want to do is like this:
> 	SELECT name FROM entries
> 	WHERE sl LIKE SOUNDEX('looking_for_this_name')
> 
> How can I do this with DBKit?.  
> 
> 	The way I look at it, there is 2 different ways to approach this.
> 
> 	1) try to get a value back from a SQL function.  I can then use  
> that in the building of the qualifier.  In looking at the docs, the  
> evaluateString: method would do the trick, but I don't know how to get  
the  
> value back from that.  I can do the following from withn isql:
> > 1> SELECT SOUNDEX('Anderson')
> > 2> go
> >        
> >  ----- 
> >  A536  
> > 
> > (1 row affected)
> > 1> 
> 	Now, If I send it that string using evaluateString, how do I get  
> the value back?   This would also be nice in finding things like MAX,  
MIN,  
> AVG,etc

You must tell DBKit about the properties you expect back even though you  
are bypassing the sql composition component of DBKit. DBkit needs to know  
where to copy the results into the client program. Here is a containerless  
binder I use to assign sequence numbers from an oracle server to new rows  
in a recordlist:

	aBinder = [[DBBinder allocFromZone:[self zone]] init];
	[aBinder setFlushEnabled:NO];
	...
	const char *currStatement = "SELECT PURCHASE_GEN.NEXTVAL FROM  
USER_TABLES";
	
	seqNoProp = [[fetchGroup entity] propertyNamed:"IDNO"];
	
		// -- show binder were to store returned values
		//
	nextValProp = [[DBExpression allocFromZone:[self zone]]
		initForEntity:[fetchGroup entity]
		fromDescription:"NEXTVAL"];
	[aBinder addProperty:nextValProp];
	[aBinder setDatabase:[[fetchGroup module] database]];	
	[aBinder setRecordPrototype:[[_Seqno allocFromZone:[aBinder  
scratchZone]] init]];
	[aBinder associateRecordIvar:"seqno" withProperty:nextValProp];
	
		// -- use containerless binder
		//
	if ( [aBinder evaluateString:currStatement] ) {
		[aBinder fetch];
	}

		//	-- set value in recordlist
		//
	aValue = [[DBValue allocFromZone:[aBinder scratchZone]] init];
	[aValue setIntValue:[[aBinder recordPrototype] seqno]];
	[[fetchGroup recordList] setValue:aValue
		forProperty:seqNoProp at:index];

> 
> --
> Mark Roder
> NeXT Consultant, Mayo Foundation
> NeXT Mail: 	roder@mayo.edu
> phone:		(507) 284-5306
-- 
Jon Buffington <internet: jonb@infoman.com uucp: uunet!infoman!jonb>
NeXT VAD	Atlanta, GA			NeXTmail accepted

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads