SQLite DB

Lar_123 · **Posted:** May 8th, 2008, 2:10 am

Okay, update. Trying a few things. Getting 'null' returns or a crash from the Preview mode.

Again, thanks to Olaf for some additional pointers. ( pun allowed )
Full text of his advise is again attached as text (html). I'll include/quote short comments here.

First, we were trying:
int32Success = MyDLL.CallFn( ________ ) // returning '1' would be 'good'

Olaf suggests then.

Quote:

sqliteDBPath = "C:\Nwind.db";
errNumber = MyDLL.CallFn("sqlite3_open", false, "ulong","string", sqliteDBPath,
"ulong*", DBHdl );
//
if (errNumber != 0) {
//the Zero is the SQLite_OK-value,
//so handle your error here, if SQLite returns an error
}

So now we're checking for error message instead, and '0' would be 'good'.
In OpusScript... the Path of course should have '\\'. I tried it both ways in case the DLL call need std path syntax. My test DB name is corrected: NWind.db in the path I used.

One thing I learned: I need to set up a Page (or Publication) Variable named 'errNumber' to "receive" the values from the call. Same thing would apply to 'int32Success' statement from previous testing. Also, the DBHdl won't pass it seems unless I create a Opus var of that name.

Olaf also suggested trying variations of 'ulong':
"ulong*" "ulong *" "* ulong" and "*ulong", or maybe something different entirely.

So, I get the CallFn to process, but it returns errNumber = null.
If I use "ulong" without the '*', Opus Preview of Pub crashes. ( I wonder if the 'crash' is actually a good sign)

I also tried "sqlite3_open16" in the CallFn, but that made no difference.

Olaf provides some insight into how he thinks DLL calls from Opus 'might' operate.... syntax. See attached.

I will also post his comments on DLL developer tools... over here:
topic: Writing a DLL
viewtopic.php?t=2674&highlight=dll

...back to the drawing board. :arrow:

[ attach not working? I'll try it later.]

Lar_123 · **Posted:** May 8th, 2008, 6:40 am

Attach failed to add. So quoted.

Quote:

From: Olaf Schmidt
Sent: Wednesday, May 07, 2008 9:24 AM
To: Larry
Subject: Re: Thanks RE: I am trying to use dhSQLite COM.dll without
VisualStudio or other Dev tools

Hi Larry,

Larry wrote:
> I have sent an email to DW support... asking them to look at the
> current discussions, and suggesting they think about assisting their
> customers to embed such DB.
>
> Many of your comments here... apply to those 'more technical'
> than I am, and to the DigitalWorkshop team. I do 'okay' at the 'high
> level'.
>
> Let's let the discussion 'evolve' a bit and see what kind of progress
> or interest develops.

Ok, just a note to your Dll-Call.
It seems, that at least the Dll-Loading works.

You then wrote this line:
int32Success = MyDLL.CallFn(SYSTEM_PUBLICATION_DIR+"Resource\\","sqlite3_open","int32DBHdl",int32Success );

I'd say, try it this way:
sqliteDBPath = "C:\Nwind.db";
errNumber = MyDLL.CallFn("sqlite3_open", false, "ulong","string", sqliteDBPath,
"ulong*", DBHdl );
//
if (errNumber != 0) {
//the Zero is the SQLite_OK-value,
//so handle your error here, if SQLite returns an error
}

As I read somewhat in this forum, it seems you have to call external Dlls always with a "three-param-prefix" before the real Parameter-Pairs are added.

- param 1 is the external function-name
- param2 to define true when the call is involving a GUI-interaction (in your case false)
- and param 3 is the type of the Function-return-value.

Then the "normal" Params are added as Pairs:
first the type-description - always given as a string;
and then the other part of the param-pair,
the script-variable, which transports something into the Dll.

Not yet found, how you can tell the Dll-Calling-Mechanism, that a Param is not an [In]-Value (usually the Data behind a variable), but instead an [Out]-Value (which usually is the pointer of the Variable and not its content - used, to let the Dll fill something in into such an "Out-Param-Pointer").

Would be good, if Digital would have published some docs, how this Dll-Calling-Mechanism would have to be used from inside Opus, especially how to define the Out-Params - e.g. in your Call above, you want to *receive* something in your DBHdl-Param-Variable - there needs to be a way, to tell the Calling-Mechanism, that it has to handle the DBHdl-Variable differently, because we want something placed "behind the pointer".

Therefore I've simply wrote "ulong*", as a simple first try, since this is the way how you would define a pointer-type in C, but maybe it is
"ulong *" (with a space) or
"*ulong" or
"* ulong" or
…not supported this way at all. That's what you would have to try on your own.
- - - - - - - - - - - - -

And BTW, no need to install huge .NET-environments, just to compile a small Helper-Dll (and yes, C# is not of much use for Opus, because the C# MSIL-compiler normally generates so called managed Dlls, only usable by .NET-Apps, requiring the appropriate framework preinstalled on a WinSystem.

Please look at e.g. tinycc - a superfast C-Compiler, coming in a 200kByte-Zip-File, able to compile small Std-Dlls, defined in plain C, why not use that - your Script-language seems Java- or C-like - so this matches very well IMO.

http://download.savannah.nongnu.org/rel ... 32-bin.zip

(small Hello-Dll-Examples are included - together with a short description, how to compile these Dll-examples, and how to automatically create Def-Files from already compiled Dlls with tiny_impdef).

And for those who want to write Std-Dlls in Basic-language, there is Freebasic available, the compiler also XCopy- installable - but this is the much greater package compared with tinycc - but the compiled Dlls are small too and probably somewhat easier createable (without Def-Files, but as I read from your reply you would need these Def-Files anyway for Opus) http://www.freebasic.net/ the compiler-environment http://www.radasm.com/fbedit/index.html and a good working IDE

- - - - - - - - - - - -
Regards,
Olaf

[ Edit LL: Olaf can be found at http://www.thecommon.net/2.html -- as he wrote earlier, datenhaus DirectCOM.dll for dhSQLite is not suited to Opus since Opus does not use COM/ActiveX. However these discussions are helpful for sorting out ‘what can be done’. Thanks, Olaf.]

Paul · **Posted:** May 8th, 2008, 11:10 pm

Did you get any reply from support?
Duncan hasn't posted anything for 6 months (I guess he's busy??); I thought he would comment more on DLL integration, and hopefully how to go about incorporating sqLite with Opus via the DLL interface.

Paul

Lar_123 · **Posted:** May 8th, 2008, 11:45 pm

Paul,

I was not getting email reply from support several days ago. I resent and copied 'sales' to pass-it-on. They thanked me for the heads-up on this thread, and that support has it.

'No'. No reply. I can appreciate DW will not be able to support and know the workings of so many possible DLLs. In this case, I am confident I've provided enough groundwork and outlined the facts... so we all can make progress toward an embedded DB. Something I assert would benefit DW. So it's just a matter of TIME and their priorities IMosO (one-sided opinion).

Olaf's been great, from the outside at that. A few more emails than I've posted here. Encouraging... that it can be done. It will be a missed Opportunity if we stall-out here.

My next steps with SQLite are to gather an example of their command documentation and try to draw the parallel to Opus. Actually... Olaf has pretty much done that repeatedly. Other Opus users/posts also hint at structures... just not solved here.

Lar_123 · **Posted:** May 9th, 2008, 8:08 am

Some minor progress I think.
This was only a test to see if Opus could call the sqlite3.dll.
As noted up-in-the-thread, a bit of coding will be needed to "use" SQLite.

I think the sqlite3_open() command worked to return '0' ( no error code ). I got past the prior 'null' return.

Code below. More learning to do. As it is the code is not difficult, just needed to get it right. (where have I heard that before? :roll:

)

Code:

// some general notes at bottom
function testDB_DLL()
{
var DBHdl  = null ;     
var mysqliteDBPath = SYSTEM_PUBLICATION_DIR +"Opus_Pub_Resource\\"+"NWind.db" ;
var MyDLL = LoadDLL( SYSTEM_PUBLICATION_DIR +"Opus_Pub_Resource\\"+ "sqlite3.dll" );
   if ( MyDLL )
   {
   DBHdl  = MyDLL.CallFn("sqlite3_open", false, "ulong", "string", mysqliteDBPath, "string", "ulong*", DBHdl  );  //     
   var altLIB = MyDLL.CallFn("sqlite3_libversion", false, "string", "string", mysqliteDBPath );  
   var tempclose1 = MyDLL.CallFn("sqlite3_close", false, "slong", "string", mysqliteDBPath ) ;
displayTest01 =  "results:  "+MyDLL +"   "+  DBHdl  +"   "+  altLIB   +"   "+  tempclose1 ;
// displayTest01 has given:    [DLLObject]      0            3.5.8             21
     }  //end if
     else
     {
     displayTest02 = "if/else is 'ELSE' for MyDLL ";
     }  //end else
} //end func testDB_DLL
//
// seems to work to at least test DB Open and DB Close with sqlite, not using ODBC
// This is only a 1st step. Will need a "wrapper" for sqlite.dll... perhaps a plug-in to Opus??
// or ??
// Set up for this test:
// Used Publication Properties >>> Additional Resources
//    to add:   NWind.db,  sqlite3.dll,   sqlite3.def
//
// results:  [DLLObject]      0       3.5.8       21
//    seem to be valid.      DBHdl    version   closecode/(minor)error?

Olaf Schmidt's help was significant. Thanks again.

Lar_123 · **Posted:** May 15th, 2008, 7:27 am

A quick update on the success.
Last week...

Quote:

Some minor progress I think.
This was only a test to see if Opus could call the sqlite3.dll.
As noted up-in-the-thread, a bit of coding will be needed to "use" SQLite.

Well it turns out Olaf dedicated some good amount of his time to looking deeper into this. In short he has written a working "wrapper". It seems to me to certainly be more than a prototype. And I'm hopeful the DW team will take this up seriously.

What I have tested so far is that the wrapper takes care of opening, managing the handles, checking for errors, and passing returned query values to an Array[i][j], and closing the DB. Olaf provided a couple sample OpusScript functions as a place to write or modify SQL statement and to place results in Listbox or other.

All-in-all this is quite promising, and quite far along. Thank you Olaf Schmidt.

My 'need' for a DB is rather basic and plain to start with, but I now see it expanding to implementing more of a rules DB.

Paul · **Posted:** May 15th, 2008, 7:56 am

Hi Larry,
What you have achieved so far is very impressive.
If it's not too much trouble, can you post the wrapper (and maybe a simple pub) for us to experiment with?

cheers
Paul

Lar_123 · **Posted:** May 15th, 2008, 9:29 am

Paul,

It's not my achievement. It is all Olaf.

I would prefer DW to pick this up and make this a tightly integrated plug-in. It should be 'cleaner' in the long run... if/when it is coupled/integrated and supported.

That is NOT to say it is not 'clean now'. It is. Very.

Olaf has already said in his emails that he

Quote:

Hope, the result is useful for the "Opus-Script-Community"

That said, I prefer not to post someone else's code. It is much better to clarify first what release/restrictions may apply. Olaf is travelling this week, so stay tuned.

What Olaf has done is some initial set-up through kernel32.dll. Then a series of opus script functions that include "CallFn" commands to sqlite3.dll and attention to the various handles there. Right now, these are all simply in OpusScript .ils files. Two files... one as wrapper, and the other serving more as sample or "starter" scripts.

Yes.... all amazing.

Just tonight I verified some more what "configuration" works. I have the "wrapper" script object placed on an Opus Master Page... which serves to assure it is available for any subsequent pages using that MP. (the Resource folder or other 'place' needs to have the DB_file and the 430KB sqlite3.dll)

The rest is standard Opus work... plus whatever SQL statement desired. Instead of working with Record_Sets, Olaf has data going to Array noted earlier. So I am still getting used to this (versus reference by RecordSet.column-name)

Paul, please add to the comments over in the Lounge... Poll/Survey as to importance/value of DB/embedded-DB.

Paul · **Posted:** May 15th, 2008, 11:42 pm

Hi Larry,
The possibilities that the DLL interface promised is the main reason I have continued with Opus.
Thanks for the information about the wrapper -- interesting how a stranger to Opus can make more effort to incorporate sqLite than DW has.
I'll be interested to see how Olaf's input pans out, although I'm reconsidering using Opus for future projects.

Paul

Paul · **Posted:** May 19th, 2008, 9:46 pm

I've waited as long as I could.
Why? -- No input from DW and no-one else willing to share information that might help to get sqLite working with Opus.
I needed this weeks ago; I've just finished porting a reasonably complex Opus pub to other applications that let me use sqLite (amongst other things) in my pubs.
These applications are not as easy as Opus to work with, but at least they do what I need them to do.

Paul

Lar_123 · **Posted:** May 19th, 2008, 11:26 pm

Paul

Sorry to learn you've mounted frustration with this and reached the end. Understandable, and others have it seems gone to alternative DBs or whatever.

As I said, Olaf was out last week... and I just received an email from him today. We are a 'go' to use his code. That was his intention, but I wanted to be sure.

I have spent the morning cleaning up the Test scripts I played with, and am preparing a sample Pub... that should give a solid springboard for all. In the process I am attempting to pave the road a bit.

I should have something up today (my day).

Lar_123 · **Posted:** May 20th, 2008, 5:42 am

Update.

I have started a new Thread so others wanting to just get started with SQLite do not have to wade through the discussion. Find it here:

viewtopic.php?t=3263

I am unable to include an Attached file there for some reason. I have a Demo IMP prepared that shows working connection to SQLite DB and includes the "wrapper" and "starter" scripts. This is really simple once you get into it. (well, not the right words... but you'll see what I mean.)

Larry

mackavi · **Posted:** May 20th, 2008, 5:55 am

Morning Lar,

You too have no email :-(

was going to send you a thnx for the heads up on the words DLL...

Send me the SQL DLL and I''l uploaded it to the Interaktiv temp folder for you to link to until the quota issue is resolved.

general(at)interaktiv.co.uk

Mack

Lar_123 · **Posted:** May 20th, 2008, 6:09 am

D1. ( done )

'Yeah', I do everything I can to keep email inbox manageable. Got it down to a reasonable level, but I don't know "how many babies I've thrown away" (as in 'throw out the baby with the bath-water' for those who don't know the expression.).

Hope to get all that Attached up here soon.

Digital Workshop

SQLite DB

Who is online