SQLite with VBScript UDF?

[bart.sm...@gmail.com]

Using SQLite with a home-grown VB6 wrapper. Use this in Excel via this wrapper (an ActiveX dll) and in a .xlam add-in. Now in 32 bit Excel I can write SQLite UDF's in VBA and this is very useful. In 64 bit Excel I can't do this though as VB6 is 32 bits. So, I was thinking I could use VBScript.
Is there anybody in this group who has done this or do you think this is possible?
I know VBScript has the GetRef function, but not sure I can use that to pass the address of the UDF to SQLite.
All a bit complex, but maybe somebody could advise here and thanks in advance.

RBS

[JJ]

I'm not an expert at COM and ActiveX, and I might mention the wrong
technical terms here... but I think the SQLite wrapper should be made as a
DLL server, so that processes which have different "bitness" than the COM
object, can use it.

It's like Office Excel. When the Excel process is 32-bit, 64-bit process can
communicate with it for e.g. open a workbook, calculate and fill some cells,
then save it. i.e. mixing "bitness". This is because Excel itself is a DLL
server.

The diagram is something like this:

+----------------------+
| 32-bit Excel |
| +------------------+ |
| | 32-bit Excel COM | |
| +--------+---------+ |
+ | |
+----------|-----------+
|
IPC
|
+----------|-----------+
+ | |
| +--------+---------+ |
| | 64-bit Excel COM | |
| +------------------+ |
| 64-bit App |
+----------------------+

The IPC between two COM objects are handled transparently by the COM
library. Using RPC, I believe.

[bart.sm...@gmail.com]

When I use it with Excel 64 bits the dll is loaded via a COM exe and that works fine, so I can use it with Excel 64 bits. There only is a problem with UDF's in the 64 bit Excel as the dll can't handle the 64 bit address pointers of the UDF.
I was hoping that 32 bit VBscript could help me out here.

RBS

[GS]

Bart,
I do know you can write UDFs using x64 Excel and compiled in VBA7.
These, of course, would only be usable in x64 Excel. Perhaps Peter
Thornton could be worthwhile contacting about this...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[bart.sm...@gmail.com]

These UDF's are really little to do with Excel UDF's. They are bits of code that interact with SQLite after the address pointer of that procedure is passed to SQLite.

RBS

[GS]

So then I assume you're looking for a generic solution that you can use
in both x64/x86 Excel as well as VB6 apps, as apposed to an x64 Excel
specific solution!?

[bart.sm...@gmail.com]

On Monday, 21 November 2016 21:11:44 UTC, GS wrote:
> > These UDF's are really little to do with Excel UDF's. They are bits
> > of code that interact with SQLite after the address pointer of that
> > procedure is passed to SQLite.
> >
> > RBS
>

In 32 bit Excel there is no problem. The SQLite UDF can be written in VBA and I can get the Procedure pointer (via AddressOf) and pass it to VB6 to pass it to SQLite. In Excel 64 bit I can't do this, so I am looking for something else, eg VBScript.
Unfortunately VB6 doesn't allow code import (as you can in VBA) as it will need to be compiled.

[GS]

> In 32 bit Excel there is no problem. The SQLite UDF can be written in
> VBA and I can get the Procedure pointer (via AddressOf) and pass it
> to VB6 to pass it to SQLite. In Excel 64 bit I can't do this, so I am
> looking for something else, eg VBScript.
> Unfortunately VB6 doesn't allow code import (as you can in VBA) as it
> will need to be compiled.

So using VBScript may give you a generic solution in that if you can
make it work for x64 then why not also for x86!?

[bart.sm...@gmail.com]

Yes, could also use it for x86, but VBA is a bit faster, has more options and also a lot more people know VBA than VBScript, although the 2 are similar.
Problem for now is that I have no idea how to make this work with VBScript.
Could I do this via GetRef?

RBS

[GS]

> Yes, could also use it for x86, but VBA is a bit faster, has more
> options and also a lot more people know VBA than VBScript, although
> the 2 are similar.

This is an interesting project! I'm just not understanding why you
can't work with x64 VBA since MSO has been VBA7 since v2010! I was
thinking PeterT might have some suggestions...

> Problem for now is that I have no idea how to make this work with
> VBScript.
> Could I do this via GetRef?

I don't know enough about VBScript to speak to this, but I'll look this
up in the CHM and get back to you...

[GS]

>> Problem for now is that I have no idea how to make this work with
>> VBScript.
>> Could I do this via GetRef?
>
> I don't know enough about VBScript to speak to this, but I'll look
> this up in the CHM and get back to you...

Script56.chm states this for the GetRef() function...

Returns a reference to a procedure that can be bound to an event.

Set object.eventname = GetRef(procname)

..and gives the following example for a webpage...

Set Window.Onload = GetRef("GetRefTest")

..where 'GetRefTest' is defined as a Function.

[bart.sm...@gmail.com]

Yes, I found that as well.
As GetRef produces an object not sure this can be used to provide an address pointer to a procedure. Would be very interesting indeed if this could work!
Can't see much about all this on the internet and not sure if anybody has used
VBScript as a store for SQLite UDF's.
Thanks in any case for your input.

RBS

[GS]

Perhaps Olaf can shed some insight!
Please post your findings...
Good luck!

[Mayayana]

<bart.sm...@gmail.com> wrote

| As GetRef produces an object not sure this can be used to provide an
address pointer to a procedure. Would be very interesting indeed if this
could work!
| Can't see much about all this on the internet and not sure if anybody has
used
| VBScript as a store for SQLite UDF's.

I don't know anything about SQLite, but for
what it's worth...

I've never seen any point to GetRef. My sense is
that it's included to maintain consistency. Their
example is senseless. They show this:

Set Window.Onload = GetRef("GetRefTest")

Which calls GetRefTest onload. But that can be
done with Sub window_onload. It can also be
done with unload= in the body tag.

With VBScript there are no pointers and no
direct datatype access. All variables are variants
or objects. Since it's interpreted there's no way
to handle addresses. The only partial exception is
that a method that takes a defined datatype will
sometimes work with a literal. For instance,
VBS can handle a dispatch function, but what
if the function is contains a long parameter?
You can sometimes send return = SomeFunction(10)
where x = 10: return = someFunction(x) would not
work. (I can't think of an example offhand, but I've
run into that with some Windows functionality that's
script-accessible but not designed for script.

[bart.sm...@gmail.com]

> Since it's interpreted there's no way to handle addresses.

I was thinking about that. Excel or for example or a VB6 ActiveX dll are running
and you can than get the address of a procedure that is loaded in memory.
With VBScript however nothing runs till the .vbs file with the procedure you want to use as a UDF is actually called. Would it actually be possible to call
the .vbs file first and then directly after get the address of that procedure you want to use as a UDF. All looks a bit complex to me.

RBS

[Mayayana]

<bart.sm...@gmail.com> wrote

| With VBScript however nothing runs till the .vbs file with the procedure
you want to use as a UDF is actually called. Would it actually be possible
to call
| the .vbs file first and then directly after get the address of that
procedure you want to use as a UDF. All looks a bit complex to me.
|

I'm not at all familiar with SQLite/UDF, so I
really don't know what you're trying to do.
But if you want VBS to handle it then you'll
need variant datatypes and a dispatch call.

You can keep VBS going, although it's awkward.
Example: I have a component for file downloading
that I made for VBS. The script needs to stay alive
while the component goes online.
I use WScript.CreateObject, which provides an
optional parameter for an event object. I then
provide an event.

Set obj = WScriptCreateObject("Server.Class", "SC_")

obj.DoSomethingThatTakesTime
While obj.Busy = True
Wend

Sub SC_OnFinish()
' This event firing sets Busy property to False,
' so script loops until the job is done and then
' any required action can be done in this sub.
End Sub

As you can see, keeping the script alive is possible
but awkward. It also requires a call to
WScript.DisconnectObject obj
at the end when using an event object.
I usually try to use an HTA if I need
that kind of complexity.

[bart.sm...@gmail.com]

Let me try to explain.
In it's most simplest form you have a UDF (for now I am doing this in Excel VBA, but it would be just as simple in VB6, except most users won't have access to that) like this:

Sub AddOne(ByVal lPtr_ObjContext As Long, _
ByVal lArgCount As Long, _
ByVal lPtr_ObjSQLite3_Value As Long)

Dim lPtrValue As Long

lPtrValue = MemLong(lPtr_ObjSQLite3_Value)

sqlite3_result_int ByVal lPtr_ObjContext, ByVal sqlite3_value_int(lPtrValue) + 1

End Sub

All it does is take a value (either a supplied literal or a value from a SQLite table) and add one.
So now in SQL you can do for example this:

select AddOne(1) as OnePlusOne
and the result will be 2

or if we take a value from a SQLite table:

select AddOne(field1) from table1

Before the UDF can be used by SQLite it needs to be registered with SQLite
with this SQLite function:

sqlite3_create_function_v2(lDBHandle As Long,
lPtrFunctionName As Long,
lArgs As Long,
lTextRep As Long,
lPtrApp As Long,
lPtrXFunc As Long,
lPtrXStep As Long,
lPtrXFinal As Long,
lPtrDestroy As Long) As Long

All this will be done in VB6, so in that ActiveX dll and this is all fairly simple and VBScript doesn't come into this.

The problem is to get lPtrXFunc, which is the address pointer to the UDF. In VB6 this is simply done with AddressOf.

Then also for the UDF to work VBScript will then need to be able to handle SQLite functions as in the above UDF sqlite3_result_int and sqlite3_value_int.

That is about it. All very neat and useful as it can add very powerful options
to your SQL.

Hope this clarifies.


RBS

[Mayayana]

<bart.sm...@gmail.com> wrote

| The problem is to get lPtrXFunc, which is the address pointer to the UDF.
In VB6 this is simply done with AddressOf.
|

From what you're saying I don't
see how VBS could be useful. Even if you could
get the function address to VBS, it would just be
a number in the form of a variant. VBS can only
handle dispatch COM calls. It has no awareness of
memory addresses.

I wonder why you can't use an AxEXE. It wouldn't
conflict with 64-bit, assuming the function parameters
are not 64-bit. But I'm afraid I'm over my head here
ad may be wasting your time. I have zero eperience
with MS Office proramming or SQLite.

| Then also for the UDF to work VBScript will then need to be able to handle
SQLite functions as in the above UDF sqlite3_result_int and
sqlite3_value_int.
|

That, too, would not be feasible. VBS can't handle
data types.

[bart.sm...@gmail.com]

> I wonder why you can't use an AxEXE.

Problem is that if the UDF is in 64 bit VBA then the function pointer of the UDF will be a 64 bit value and my VB6 ActiveX dll can't handle that. Unless I am over looking something this seems to be the problem. My VB6 dll can otherwise handle 64 bit Excel provided it is loaded by a VB6 COM exe.
Doesn't look this VBScript idea is feasible then.

RBS

[Schmidt]

Am 23.11.2016 um 02:42 schrieb bart.sm...@gmail.com:

> My VB6 dll can otherwise handle 64 bit Excel provided it is loaded by a VB6 COM exe.
> Doesn't look this VBScript idea is feasible then.

If you host that Script within the 32Bit-environment (your VB6-Dll),
then it is possible to delegate an SQLite-UDF-callback into it.

Below comes an example, which is using the vbRichClient5's built-in
ActiveScripting-Support, to show that such a thing will work:
(this is a VB6-Demo though, which requires a registered
vbRichClient5-package, downloadable from http://vbRichClient.com)

http://vbRichClient.com/Downloads/ScriptUDFs.zip

Here's a ScreenShot of the above Demo-App:
http://vbRichClient.com/Downloads/ScriptUDFs.png

For the Scripters here, who are interested in "what's going on
under the covers"...

The vbRichClient5 is a COM-lib, which has a built-in SQLite-wrapper,
so it's easy to address and use also from VBScript (per passed Objects)

And to delegate SQLite-CallBacks to VBScript-Functions, one needs
to define the Callback-Proc in a *generic* manner elsewhere first
(I did it per VB6 in vbRichClient5).

Generically defined means, that there needs to be a Helper-Object,
which wraps the needed SQLite-APIs for UDF-Handling - and then
this Helper-instance gets passed to VBScript, to implement and
handle the "UDF-matters for the concrete Function in question".

In the above (zipped) Demo, one of the Script-Functions is defined as:

Sub AddSomethingTo(ByVal ParamCount, ByVal UDF)
If ParamCount = 2 Then 'the "normal case"
UDF.SetResultDouble UDF.GetDouble(1) + UDF.GetDouble(2)
'parameter-indices are one-based
Else 'an example, how to raise an error, if something cannot be
handled by your function
UDF.SetResultError "AddSomething: we need two parameters!"
End If
End Sub

In fact, any VBScript-handled SQLite-UDF will have (aside from the name)
always the same Function-Signature with only the shown two Arguments:
- ParamCount (that's how many the User passed into the SQL-Function)
- UDF (here, a vbRichClient5-defined Helper-Class of type cUDF)

Since you are using the RC5 in your solution already, adapting it
to something similar in your own solution should be relatively easy.

HTH

Olaf

[GS]

Olaf,
I was pretty sure you had a solution and I was hoping you'd chime in on
this. Thanks so much for sharing...

[bart.sm...@gmail.com]

Hi Olaf,

Thanks for that and will give it a go.
One possible problem I can see is that I do use vbRichClient5, but not for SQLite. I wrote my own SQLite wrapper, which allowed me to put SQLite UDF's in
VBA (that is 32 bit VBA).
Could your VBScript example work with this VB6 wrapper?

RBS

[bart.sm...@gmail.com]

This looks all very neat, nice work!

It needs this adding to the form code to avoid problems when closing the form:

Private Sub Form_Terminate()
New_c.CleanupRichClientDll
End Sub

Just thinking now if I could make this work with my own SQLite wrapper.
Not sure, but it looks it could work.
My script code will of course be different and not sure now about IFunction.
I suppose this class interacts with SQLite?

RBS



On Saturday, 26 November 2016 14:23:45 UTC, Schmidt wrote:

[bart.sm...@gmail.com]

Not tried it yet, but I think I should be able to make this work by keeping
a generic procedure in a normal module (not a class module) and pass the address
of that generic procedure (with AddressOf) to sqlite3_create_function_v2. Then delegate from there to the scripting code.
As you say I will need the 5th arg of sqlite3_create_function_v2, *pApp, which can be used as in the SQLite documentation:

The fifth parameter is an arbitrary pointer. The implementation of the function can gain access to this pointer using sqlite3_user_data().

I don't think I need IFunction, but will try it all out tonight.

RBS

[Schmidt]

Am 28.11.2016 um 16:39 schrieb bart.sm...@gmail.com:
> ...I should be able to make this work by keeping a generic

> procedure in a normal module (not a class module) and pass the address
> of that generic procedure (with AddressOf) to sqlite3_create_function_v2.

> Then delegate from there to the scripting code...

Yep, and the *pApp doesn't necessarily need to be a real Pointer -
it can be used as "just another 32Bit-Int" as well (e.g. to just
pass an index, which in case of "calling back" could then be used
to "identify the function in question" (in an UDT-Array or something).

Into the Scripting-Code, you'll still need to pass some Wrapper-Obj,
similar to the cUDFMethods in my example... (to be able to access
the Params of the UDF, as well as setting the Result of the UDF
conveniently per COM-method from within VBScript).

Olaf

[bart.sm...@gmail.com]

> Into the Scripting-Code, you'll still need to pass some Wrapper-Obj,
similar to the cUDFMethods in my example

This is the bit I am not sure about.
cUDFMethods in vbRichClient5 is a class with the Read-Only context property, which I presume is the same as SQLite's sqlite3_context*
Then there are lots of functions and subs that get things out of SQLite and put things into SQLite. Does my object (I presume a class) have to match this?
Trouble is I have no idea how cActiveScript.Run works under the hood.
So basically what should I code to replace cUDFMethods?

RBS

[Schmidt]

Am 29.11.2016 um 14:59 schrieb bart.sm...@gmail.com:
>> Into the Scripting-Code, you'll still need to pass some Wrapper-Obj,
> similar to the cUDFMethods in my example
>
> This is the bit I am not sure about.
> cUDFMethods in vbRichClient5 is a class with the Read-Only context property, which I presume is the same as SQLite's sqlite3_context*
> Then there are lots of functions and subs that get things out of SQLite and put things into SQLite. Does my object (I presume a class) have to match this?

If you want to read SQLite-Param-Values from inside VBScript (or
set SQLite-UDF-Results from inside that VBScript) you will have to.
VBScript cannot call a "Declared Flat-API" directly (as VBA can) - it
will always have to work through a COM-Wrapper-Object, which (behind
an appropriate Method) performs the Flat-API-Call for you then.

> Trouble is I have no idea how cActiveScript.Run works under the hood.

cActiveScript is roughly compatible to the MS-ScriptControl (though not
dependent on it - it only needs VBScript.dll or JScript.dll, which come
preinstalled on any Windows-System).

The Run-Method takes the Function-Name as the first (String)argument,
and then a list of Variant-Parameters in a ParamArray - so it's a
bit like "CallByName" if you want... (only that it calls into priorily
added ScriptCode-Functions or -Subs).

> So basically what should I code to replace cUDFMethods?

Maybe look at, how you accomplished an 'AddSomething' UDF-Method,
using only the Flat-API in VBA - then try to hide all the Flat-SQLite-
APIs behind a Class (its Methods) to ensure the needed indirection.

That should give you an idea, what belongs into such an UDF-Helper-
Object (to be usable from within a VBScript).

Olaf

[bart.sm...@gmail.com]

I suppose I can just code my own class with exactly the same members (as cUDFMethods) that do exactly the same things. This shouldn't be a problem.
I can't use vbRichClient5's cUDFMethods as I have my own connection to SQLite
that doesn't use cConnection.
I guess that should work.

RBS

[bart.sm...@gmail.com]

On Tuesday, 29 November 2016 21:01:18 UTC, Schmidt wrote:
> Am 29.11.2016 um 14:59 schrieb bart.sm...@gmail.com:
> >> Into the Scripting-Code, you'll still need to pass some Wrapper-Obj,
> > similar to the cUDFMethods in my example
> >
> > This is the bit I am not sure about.
> > cUDFMethods in vbRichClient5 is a class with the Read-Only context property, which I presume is the same as SQLite's sqlite3_context*
> > Then there are lots of functions and subs that get things out of SQLite and put things into SQLite. Does my object (I presume a class) have to match this?
>
> If you want to read SQLite-Param-Values from inside VBScript (or
> set SQLite-UDF-Results from inside that VBScript) you will have to.
> VBScript cannot call a "Declared Flat-API" directly (as VBA can) - it
> will always have to work through a COM-Wrapper-Object, which (behind
> an appropriate Method) performs the Flat-API-Call for you then.
>
> > Trouble is I have no idea how cActiveScript.Run works under the hood.
> cActiveScript is roughly compatible to the MS-ScriptControl (though not
> dependent on it - it only needs VBScript.dll or JScript.dll, which come
> preinstalled on any Windows-System).
>
> The Run-Method takes the Function-Name as the first (String)argument,
> and then a list of Variant-Parameters in a ParamArray - so it's a
> bit like "CallByName" if you want... (only that it calls into priorily
> added ScriptCode-Functions or -Subs).
> VBScript cannot call a "Declared Flat-API" directly (as VBA can) - it
will always have to work through a COM-Wrapper-Object

I access all SQLite's functionality through a .tlb. Could VBScript do that?

RBS

[Schmidt]

Am 29.11.2016 um 22:35 schrieb bart.sm...@gmail.com:

> I access all SQLite's functionality through a .tlb. Could VBScript do that?

VBScript can be brought to parse *.tlbs, but mainly to expand
its understanding of Const- and Enum-defs (contained in these tlb).

It cannot call "tlb-module-functions" (IMO) - because VBScript
calls Methods over the IDispatch-Interface (which is only
available behind Class-implementations).

Olaf

[Schmidt]

Am 29.11.2016 um 22:01 schrieb bart.sm...@gmail.com:
> I suppose I can just code my own class with exactly the same members (as cUDFMethods) that do exactly the same things. This shouldn't be a problem.

I'd think so too - whilst the RichClient might provide the
Script-Execution-engine, SQLite-wise it can only be used
on its own cConnection...

Well, you could get the SQLite-connection-Handle per
cConnection.DBHdl - and then use that in your own Wrapper-
Classes - but that'd be a mixing of functionality which
makes only sense for a shorter testing-period I guess...

Olaf

[bart.sm...@gmail.com]

Thanks, will give that a try.
There are 2 other alternative approaches that I can see that are easy.
1. Use TCL for the UDF's. I understand this is well integrated with SQLite and it might be faster as well.
2. Go back to using all vbRichClient5 functionality, including cConnection etc.
No idea though then how to use UDF's from VBA. Would it be possible you could add the option to use a procedure address pointer with Cnn.AddUserDefinedFunction, instead of a class that implements IFunction?

RBS

[Schmidt]

Am 30.11.2016 um 09:17 schrieb bart.sm...@gmail.com:

> There are 2 other alternative approaches that I can see that are easy.
> 1. Use TCL for the UDF's. I understand this is well integrated with SQLite and it might be faster as well.

It might - never tried it, would be interesting to see an example...

> 2. Go back to using all vbRichClient5 functionality, including cConnection etc.
> No idea though then how to use UDF's from VBA. Would it be possible you could add the option to use a procedure address pointer with Cnn.AddUserDefinedFunction, instead of a class that implements IFunction?

A VBA-Class (of a 32Bit-Excel-Installation) can Implement IFunction
perfectly fine (and thus would be usable as an "SQLite-UDF-Container"
without problems, no delegation to VBScript needed from there).

The other alternative (in case you want to go "low-level") would be,
to use the already mentioned Cnn.DBHdl-property of the cConnection -
(then doing your own thing - against the Flat-API in vb_cairo_sqlite.dll)

Olaf

[bart.sm...@gmail.com]

On Wednesday, 30 November 2016 09:50:38 UTC, Schmidt wrote:
> Am 30.11.2016 um 09:17 schrieb bart.sm...@gmail.com:
>
> > There are 2 other alternative approaches that I can see that are easy.
> > 1. Use TCL for the UDF's. I understand this is well integrated with SQLite and it might be faster as well.
>
> It might - never tried it, would be interesting to see an example...
>
> > 2. Go back to using all vbRichClient5 functionality, including cConnection etc.
> > No idea though then how to use UDF's from VBA. Would it be possible you could add the option to use a procedure address pointer with

> A VBA-Class (of a 32Bit-Excel-Installation) can Implement IFunction
perfectly fine

OK, didn't realise that and that is interesting. Will give the cUDF class a
go first and take it from there. Thanks for the assistance.

RBS

[bart.sm...@gmail.com]

In vbRichClient5 class cUDFMethods I can see only a read-only Context property.
How does the class get to know about sqlite3_value* and sqlite3_context* ?
The UDF procedures will need to know both.
I can make Get and Let properties for both in my "copy class", but I would like
to stay as close as possible to your class layout, although I am not if that is in fact necessary.
Let me know if you prefer if this thread was now moved somewhere else as it has now little to do with VBScript.

RBS

On Wednesday, 30 November 2016 05:51:24 UTC, Schmidt wrote: