• Welcome, Guest. Please login.
 
May 11, 2021, 08:01:52 PM

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Topics - Mark Strickland

1
There was a previous post from late 2008 describing a problem with a JOIN where the column names match.

http://www.sqlitening.com/support/index.php?topic=2601.msg13022#msg13022

I have tried all of the suggestions and partially solved the problem by changing the name of one column.  I am now trying to JOIN a single table multiple times to the main row but I have the same problem.  Because I want to JOIN the same table multiple times I cannot change any column names.

Here is the SQL statement:

    SQL = "SELECT RxRecs.Rowid, * FROM RxRecs " + _
           " INNER JOIN RfRecs2   ON RfRecs2.RfRxNum           = RxRecs.RxNum " + _
           " INNER JOIN Patients  ON Patients.PtNum            = RxRecs.RxPtNum " + _
           " INNER JOIN Doctors   ON Doctors.DrNum             = RxRecs.RxDrNum " + _
           " INNER JOIN Drugs     ON Drugs.DgNum               = RxRecs.RxDgNum " + _
           " INNER JOIN SigRecs   ON SigRecs.SgNum             = RxRecs.RxSgNum " + _
           " INNER JOIN PriceTbls ON PriceTbls.PrcNum          = RxRecs.RxPrcNum " + _
           " LEFT  JOIN Insurances AS I1 ON I1.InsNum          = Patients.PtIns1PlanN " + _
           " LEFT  JOIN Insurances AS I2 ON I2.InsNum          = Patients.PtIns1PlanN " + _
           " LEFT  JOIN Insurances AS RfI1 ON RfI1.InsNum      = RfRecs2.RfClm1InsN " + _
           " LEFT  JOIN Insurances AS RfI2 ON RfI2.InsNum      = RfRecs2.RfClm2InsN  WHERE " + _
           " RxRecs.RxNum = 239363"


This works fine in SQLmaestro.

The need for this more complex query with JOINs came about because of a speed issue to read these tables in multiple single table SELECTs when the DB is accessed over a LAN network.

Is there any way to work around this problem?
2
As I have been working with SQLiteing my SQLite query tool has proved to be very valuable.

There are TWO SEPARATE problems for folks trying to use SQLitening and learn SQL at the same time.

If you get a query tool that can connect directly to a SQLite DB you can figure out the SQL code THEN put it in your PB program using SQLiteing with almost a cut and paste.  At that point you will know your SQL code is working.

You can also do the opposite by doing things in your PB program to output the generated SQL statements by displaying them to cut and paste back into your SQL query tool or even have the PB program simply stuff them directly into the clipboard for a direct paste.

I use SQLMaestro but below is a link to a large number of options and many are free.

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

These will let you play with SQL statements and even use the EXPLAIN QUERY PLAN (mentioned in a previous post) to help you figure out how SQLite will approach finding and returning your data.
3
I have had a problem trying to reproduce some code in SQLiteing to match the functionality of Cheetah and how indexes work.  Since SQLite does not have CURSORS I have had to be more creative.

In SQL you cannot force the use of a specific index.  SQL engines are generally smart enough to determine when to use an available index file.  SQLite will do that but it has more limitations than some other SQL engines.

Consider an index that looks like this that indexes the LAST and FIRST names of a patient:

CREATE INDEX PAT_NAME
  ON PAT
  (PAT_LNAME, PAT_FNAME);


If you do this SELECT SQLite will NOT use the index.

SELECT * FROM PAT WHERE PAT_LNAME >= 'SMITH'


This is because the index is a compound key (multiple columns) and the inequality is on a column that is NOT the last column in the index.

SQLite will do a serial read on the table from start to finish to find all of the matches.  Without and ORDER BY clause they will be returned in the order they are found in the table.

If the index had a single column it would work similar to Cheetah and start with the first key and read forward by the index from that position.

Here is some SQLite info that helps explain things:
http://www.sqlite.org/optoverview.html

To see what YOUR query is going to do put this in front of your SELECT statement:

explain query plan



explain query plan
SELECT * FROM PAT WHERE PAT_LNAME >= 'SMITH'


You can also just use EXPLAIN but you will get some SQLite internal information that is less useful.
4
Since we are starting to play with SQL I think possibly a new forum topic may be in order.  There have been several posts from people wanting to know how to get started with SQL.  Because of the non procedural nature of SQL it involves significantly different thinking when it comes to file access.

Here is an example of something I did in Cheetah and had to re-think in SQL.  It would be nice to have a place to post these kinds of "SQL Tips and Tricks" as they relate specifically to SQLite and SQLitening.

My Cheetah algorithm to search for a person in a list of names:
1) Find a record by a partial name entered by the user (PATIENT NAME)
2) Read the next 4 records in key sequence by PATIENT NAME
3) Read the previous 4 records in key sequence by PATIENT NAME
4) Display a list of 7 records with the one you searched for in the CENTER of the list

Because SQL is non procedural this is how I am getting the list.  I still have to process the list procedurally because if you are at the front or back of the file there may not be 4 records before or after the desired record and you will have to figure out what goes in the center of the result set.  It may be possible to do this in PURE SQL but I have not yet figured it out.


select * from

  (select 'A' as src,* from pat where upper(pat_lname) <= 'SCHEFFLER' and pat_number <> '00010965'
     order by pat_lname desc limit 4)
 
union

select * from
  (select 'B' as scr,* from pat where pat_number = '00010965')
 
union

select * from
  (select 'C' as scr,* from pat where upper(pat_lname) >= 'SCHEFFLER' and pat_number <> '00010965'
     order by pat_lname asc limit 4)

  order by pat_lname



The 'A', 'B', 'C' column names are to show the source of the record in the result set.

I also discovered some other interesting "features" of SQLite if index files are used or not used based on the index structure and the nature of the query.

If we had a place I would post the parts of a working example and the DB.  Others could post things they have found and the orginal topic could be kept pure for the development activity of SQLitening.


Food for thought.

5
Fred,

You may want to consider how you are handling error on some of the functions to allow the return of an error code rather than just ending the program.  Some functions have he ModChar feature and do handle errors gracefully.

slFN will abort if an invalid field name is passed.  I would prefer some sort of option to simply report an error and let me handle that gracefully.

Maybe a config option could change the abort behavior and report an error in the slGetError function rather than abort.  Alternatively you could add a parm to the function call like the ModChar in other functions.  Because these functions in question try to return a value (not an error) you could return the error in an alternate parm.


MyField = slFN("col_name",error_long)
if error_long then .....


Thanks,

Mark


6
I have been trying to use an explicit record set number and have found a few "funny" things.

1) If you do an   slCloseSet(setnumber)   when no set is open you get a GPF.

2) There is no way (I can see) to determine if a set is currently open.

3) It appears that doing an slSEL with a set number that is currently open will not return any records (gets a -14 error).

My intent is to use multiple record sets for query's that need to be "left open" based on the program logic.
i.e. -- SELECT A, B, C from Table1 where ...  and begin processing rows from that set.  In some cases while processing that set (Table1) I will need rows from a different table.

Any assistance would be appreciated.

Thanks, Mark
7
What would be the best way to create a new "empty" DB file?

Paul's old code would simply create the DB if it did not exist.  Since SQLitening, in the server mode, cannot create a DB file and the DB is in the "home" directory of the server what would you consider a good practice to create a new DB file?

I could create it in a "direct connect" mode and copy it to the server "home" directory but I don't see an easy way to know where the "home" directory exists.

Does the QUERY_SERVICE_CONFIG return the path in the lpBinaryPathName data item where the DB file would be stored?

Would it make sense to allow the slOpen to have the Create flag?

If you have a better idea I am open to what ever will work.

I wrote a Cheetah to SQLite converter tool based on Paul's old code and it had an option to create the DB if desired.  I have converted this code to SQLitening and this is the last thing to fix.  Once I fix this last "feature" I will post the updated code to the forum for all to use.

Thanks,

Mark

8
You've got Questions? We've got Answers! / A thought
March 21, 2008, 12:28:01 AM
As I have started to convert some live code with Cheetah I can see the need for a minor enhancement.

When doing a SELECT it would be useful to know how many rows exist in the record set before trying to navigate the set.  This would be especially useful when looking for a "direct hit" on a single row in a table.

Would it be hard to add a new parm in the sql3_select function?  Or maybe a new function that would return the row count for a specified record set.

Thanks.
9
I upgraded to version 0010 but I am still having trouble with a Database Locked error.

Here is a sequence that works --

1) Start Server
2) Start a session, do a single SELECT, destroy record set, disconnect, end session, exit program.
3) Repeat #2

This will work so long as you do a single SELECT.  If you do two selects (destroying the record set each time), disconnect, end session, exit program then the next time you start the program (leaving the server running) the start session gives the Database Locked error.

Also an UPDATE will NOT work if a select is done first.

I have verified that the record set variable is reset to zero when the destroy function returns.

I will email you a ZIP file of the project with all of the parts (DLL's, server, etc).

10
If a new sql3_select call is done BEFORE destroying a previous record set (same rs "handle") will this function destroy the previous record set and release the memory before calling a new one?  If not it probably should because it would simplify housekeeping tasks especially in code converted that use some other tools like Cheetah where this was not a requirement.

Thanks again for all of the work on the SQLite C/S code.
11
I get an error SQLite Busy (#5) after disconnecting then reconnecting.  The error happens when reconnecting.  The SERVER shows DISCONNECTED after the first disconnect.

Here is the exit code (copied from one of the example programs):


    'SQL - Close SQL DB
    IF gSQLsession THEN

        IF gRS THEN
            sql3_rsDestroy gSQLsession, gRS
        END IF

        ' Disconnect from the server (if connected)
        sql3_disconnect gSQLsession

        ' End the session
        sql3_EndSession gSQLsession

    END IF


Any thoughts?
12
This is a very ;D preliminary version of a Cheetah to SQLite Data Migration Tool

It does not yet handle all Cheetah datatypes and may have some bugs.  You can create or add to a SQLite Physical DB simply by picking a Cheetah DB and loading it.  It always attempts to DROP any table before creating it and adding the data.  This allows a conversion to be started again to correct any problems or errors.  The resulting Table Name and Column Names are created directly from the Cheetah DB File Name and Field Names.  Cheetah field names with dashes are converted to underscores.

The Cheetah DB is read by record number from 1 to the end and skips deleted records.

Single quotes and backslashes are escaped (doubled up) if found in the data to work with INSERT.  No other changes are currently done to the data values.

This uses Paul's SQLite Client Server code not a direct connect to the SQLite DB.

Still in the works:
1) Create indexes based on Cheetah Index File info from a selection of file names
2) Complete the other Cheetah data types
3) Still need to prove some special Cheetah datatypes, like dates, are setup in SQLite correctly
4) Run with a script or command file to load a group of Cheetah DB's into a single SQLite DB
5) More error checking and reporting
6) Speed - Maybe it could be faster

In the event that a row creates an error while INSERTING the conversion of the Cheetah DB stops and the form will be resized to show a text box with the SQL INSERT statement in error.

The ZIP file contains the FF project and the SQLite Client Server modules.  Just Un-ZIP into a directory of your choice and it is ready to run.

Please post suggestions or bugs you find.

I hope to get at least items 1-3 above in the next week and post an update here.  Item 4 will take a little longer but will be done.


[attachment deleted by admin]
13
Paul,

I tried out a few of the early SQLite Client Server examples and I am excited.  Are you going to post more examples as you go?

Thanks, Mark