Creating a new function for SQLServer

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Creating a new function for SQLServer

Tom Robinson
Hi,

Does anyone have experience adding functions to Glorp.  I find myself needing to use the following expression in a query: right(version, length(version), - patindex("%0.%", version). Right now, I'm trying to implement #right: as a PrefixFunction by adding it to SQLServerPlatform>>#initializeFunctions using the following code:

    functions at: #right: put: ((PrefixFunction named: 'RIGHT') type: (self varchar: 255)).

My test code looks like this:

     | session query results |
     session := StoreLoginFactory currentStoreSession.
     query := Query read: Store.Glorp.StorePackage
          where: [:ea| (ea version right: 5) = '57540'].
     results := session execute: query.
     results

The result is an error: Unhandled exception: Invalid data type. It appears that the Query is trying to convert the argument 5 to the varchar type to be returned. This is happening in the processing of binding, before any attempt is made to generate SQL. Is there a way to specify an argument type or is that something that needs to be added? Is there something simple that I'm doing wrong?

Thanks,

Tom

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/94bb95cc-e5c5-4ddd-8751-8dac41d75595%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

RE: Creating a new function for SQLServer

Wallen, David

Hi Tom,

 

Sorry, I was away all week. Your function seems to work. However, I get an empty list for results—perhaps the record isn’t in my repository.

Here’s what I tried:

1. Add Tom’s new function in method, SQLServerPlatform>>initializeFunctions.

2. Connect to the SQL 2012 repository (which sends SQLServerPlatform new initializeFunctions).

3. Inspect the workspace code

 

Here is the query it generates.

SELECT  TOP 1 t1.identityName

FROM NEWBERN2.BERN.TW_DatabaseIdentifier t1  OrderedCollection ()

(0.594 s)

SELECT t1.primaryKey, t1.name, t1.timeStamp, t1.version, t1.userName, t1.trace, t1.blessingLevel, t1.commentID, t1.propertiesID, t1.binFile, t1.parcelID

FROM NEWBERN2.BERN.TW_Package t1

WHERE (RIGHT(t1.version,5) = :1)  OrderedCollection ('57540')

(0.016 s)

 

Dave Wallen 
Software Engineer
 

https://no-cache.hubspot.com/cta/default/431576/c36613c8-e7ed-43e4-96aa-21da1a227630.png

blank
office:
513-612-2003
website: www.cincomsmalltalk.com
email: [hidden email]
 

 
 
 https://no-cache.hubspot.com/cta/default/431576/0488e96d-1f19-4f15-9229-6366998fd492.png   https://no-cache.hubspot.com/cta/default/431576/98535c75-36a7-4ad2-886f-59aed10b7d2d.png   https://no-cache.hubspot.com/cta/default/431576/1b963ee4-96da-4a8d-92b5-dc1c5570e9ad.png   https://no-cache.hubspot.com/cta/default/431576/dc3c920a-1832-4080-afb7-2e78ad67d354.png   https://no-cache.hubspot.com/cta/default/431576/df552d48-897f-4524-babf-2e9a83340091.png

 

--- CONFIDENTIALITY STATEMENT ---

This e-mail transmission contains information that is intended to be privileged and confidential. It is intended only for the addressee named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited, please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

 

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Tom Robinson
Sent: Sunday, August 18, 2019 7:24 PM
To: glorp-group
Subject: Creating a new function for SQLServer

 

Hi,

 

Does anyone have experience adding functions to Glorp.  I find myself needing to use the following expression in a query: right(version, length(version), - patindex("%0.%", version). Right now, I'm trying to implement #right: as a PrefixFunction by adding it to SQLServerPlatform>>#initializeFunctions using the following code:

 

    functions at: #right: put: ((PrefixFunction named: 'RIGHT') type: (self varchar: 255)).

 

My test code looks like this:

 

     | session query results |

     session := StoreLoginFactory currentStoreSession.

     query := Query read: Store.Glorp.StorePackage

          where: [:ea| (ea version right: 5) = '57540'].

     results := session execute: query.

     results

 

The result is an error: Unhandled exception: Invalid data type. It appears that the Query is trying to convert the argument 5 to the varchar type to be returned. This is happening in the processing of binding, before any attempt is made to generate SQL. Is there a way to specify an argument type or is that something that needs to be added? Is there something simple that I'm doing wrong?

 

Thanks,

 

Tom

 

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/94bb95cc-e5c5-4ddd-8751-8dac41d75595%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/CY4PR13MB1143CFAF5AECF628D861B7F9C9A40%40CY4PR13MB1143.namprd13.prod.outlook.com.