Issue SQLCONNECT results in C5 fatal error

[cfsjohn]

I have setup an ODBC driver to access a database outside my app/outside my FoxPro database.
See the image below. Note that I have erased the Host, and Userid values for this posting.

I go into VFP9 command window and issue:
lnhandle=SQLCONNECT("Spillman","userid","password")
where userid and password are the values from the ODBC setup.

VFP9 crashes.
Fatal error: Exception code=C0000005 @ 02/09/18 02:09:36 PM. Error log file: C:\Program Files (x86)\Microsoft Visual FoxPro 9\vfp9err.log
I am totally lost.

 

[JRB-Bldr]

Since it appears as though you have already set up an ODBC Data Source Connection in your Windows, you might want to look at:
How to Connect to a SQL Server from Visual FoxPro

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

When user and password already are part of the ODBC DSN, then just try lnhandle=SQLCONNECT("Spillman")

If that doesn't work, the driver likely doesn't cooperate with VFP. At the moment of SQLConnect not much is happening, all you get back is a connection/command handle. Errors happening while you query with SQLExec would most likely be fixable with the help of detail error messages, but not the connection itself.

I remember MySQLs drivers also offer a very generic ODBC property called OPTION which you can set to a sum of bit flag values having a certain meaning about how data is handled and returned. But even if you set that all wrong the connection still works and problems only occur when querying.

Very general an ODBC driver usable for VFP should be 32bit and ANSI.

Bye, Olaf.

 

[cfsjohn]

JRB-Bldr,
I have read that so many times I can pretty much quote it. In fact I have the book, "Client/Server Applications with Visual FoxPro and SQL Server" which tells me the same things.

I am making the assumption (and could be totally wrong), that because Spillman supplied an ODBC driver to access Spillman's database, that neither VFP's native ODBC driver (meaning the one that ships with VFP) nor MS-SQL Server are involved in this process. Am I wrong about that?

Olaf,
If you notice the screen print shows that I installed the 32 bit ODBC driver. As for the ANSI suggestion, perhaps you are referring to the ODBC configuration value for "Client Character Set". According to Spillman's instructions, I am not supposed to change that value. Are you saying that I may need to change it?

To reiterate and trying to be perfectly clear, after installing the 32 bit ODBC Driver and then configuring the ODBC driver as defined in Spillman's instructions, and without doing anything else, when I simply open VFP9 and go to the command window and enter lnhandle=SQLCONNECT("Spillman","userid","password") or lnhandle=SQLCONNECT("Spillman")
VFP9 crashes with a fatal error.

Perhaps Olaf is correct in saying "the driver doesn't likely cooperate with VFP". If so, what would you do if faced with that? Is there something/someway to find out if the driver even works, perhaps using something besides VFP?

Thanks,
John

 

[atlopes]

You may try to connect via ADODB object (that is, indirectly with the Microsoft ODBC Provider).

Just an example for you to test:

LOCAL ADO AS ADODB.Connection
LOCAL Result AS ADODB.Recordset
LOCAL MaxTest AS Integer

m.ADO = CREATEOBJECT("ADODB.Connection")
m.ADO.Open("Provider=MSDASQL;DSN=Spillman;UID=[highlight #FCE94F]**********[/highlight];PWD=[highlight #FCE94F]*********[/highlight]")
m.Result = m.ADO.Execute("SELECT * FROM [highlight #FCE94F]some_table[/highlight]")
m.Result.Movefirst()
m.MaxTest = 10
DO WHILE !m.Result.Eof AND m.MaxTest > 0
	? m.Result.Fields(0).Name,"=",m.Result.Fields(0).Value
	m.Result.Movenext()
	m.MaxTest = m.MaxTest - 1
ENDDO

 

[Mike Lewis]

I think the main point that Olaf was making was that there is nothing that you can do to the SQLCONNECT() function that would generate a C5 error. Even if you passed completely crazy parameters, that error shouldn't happen. Thus the problem must lie in either the driver itself or something in the way that it is configured. Just because Mr Spillman provided a driver and gave you instructions for configuring it, that doesn't necessarily mean that those items are 100% correct.

One way to confirm that is to go into the DSN Configuration form (within the ODBC Data Sources Administrator applet), and then click Test Data Source button. That should at least give you a good clue as to the what is causing the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[cfsjohn]

Atlopes, I did that test. It works. I pulled multiple rows from a Spillman table.

Mike, I did not understand that is what Olaf was telling me but I totally agree and that is logical to me as well. VFP should not crash.

I went to the ODBC Data Sources Administrator applet and can not find any Test Data Source button.

My latest evaluation on all this would be there is something wrong with the ODBC driver or the setup. Looking at the windows event log it appears the driver's dll (see the Faircom line in the screen print below) is the culprit but I have no idea why. I will contact Spillman Monday.

I probably should know this but don't: Can I use a different driver like the ADO thing that Atlopes had me test with or MS-SQL Server instead of the driver supplied by Spillman? I guess I don't understand exactly what dictates the ODBC Driver that must be used. Spillman advertises they use a MS-SQL Server database for their systems.

Thanks,
John

 

[Mike Lewis]

John, the fact that Atlopes' solution worked and your original approach gave an error only confirms that the problem lies in the ODBC driver. Atlopes is using OLE-DB, which does not use ODBC.

You could therefore solve your problem by using the Atlopes approach, but then you would have to approach your data handling in VFP in an entirely different way - one that you might find intrinsically more difficult (as I do).

A better approach would be to download a fresh copy of the required OdBC driver from an appropriate public source. Provided it is a 32-bit driver, is should work in VFP in just the same way as the Spillman driver. The fact that it might be a later version shouldn't make any difference to your code.

If that still doesn't work, by all means contact Spillman.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[atlopes]

Can I use a different driver like the ADO thing that Atlopes had me test with or MS-SQL Server instead of the driver supplied by Spillman? I guess I don't understand exactly what dictates the ODBC Driver that must be used. Spillman advertises they use a MS-SQL Server database for their systems.

I'll try to answer as comprehensively as I can.

a) The solution I suggested still uses the Faircom ODBC driver. The difference comes from the layers that come between VFP and the ODBC driver, so that VFP does not talk to the driver directly, anymore.

What you presented

VFP
Faircom ODBC Driver

What I suggested

VFP
ADODB
Microsoft OLE Provider for ODBC
Faircom ODBC Driver

So, there is an interoperability issue between VFP and the Faircom ODBC Driver. When VFP connects to a data source, a lot of traffic pops up between the two parts, because VFP needs to know a few things about how the driver operates. In this process, something messy fires the C5 error you're experiencing. Who's to blame? I don't think there will an easy answer for that, but I suspect that your vendor will claim his innocence.

b) If you can use the ADODB classes, then it's ok, you may forget the direct connection to the Faircom ODBC Driver. You can even instantiate a CursorAdapter to work with data, the object operates on ADO connections, also.

c) I think that what Spillman (who/what/ever it is) may have stated is that his/their solution incorporates an SQL server, but not from Microsoft. That is, not MSSQL Server. The SQL Server they are using, as it seems, comes from Faircom (in the 80's I used their c-Tree database when working in C, but haven't heard from them for quite a long time - so your post brings back some really ancient memories).

 

[cfsjohn]

atlopes,

WOW. I had no idea. FYI: Spillman is a Jail Management System and has been around since the 80's. I am trying to pull a roster of who is booked in, for one of my customer sites. I won't go into a big long spill about the "Crystal Reports" concept where instead of simply exporting a roster to my app, companies now give you tools (like the ODBC driver) to "go get what you want". That concept may be OK for users of the Spillman JMS but for vendors like myself, dealing with a different JMS at every customer site, it means I have to learn a lot about every JMS's schema. From my point of view, "Gee thanks".

I will do some reading up on ADO. It will take doing that in order to see if that is a solution, AND I will contact Spillman and as Mike Lewis suggested I am going to try getting the latest driver (you would think that is what they would have given me).

Thanks for all the info. At least I now understand where the problem is.

John

 

[Olaf Doschke]

The thread has advanced more than I could catch up with. Just one single point about ANSI drivers Unicode driver. What Imean by that is what you can see from the variety of MySQL drivers available, see

https://stackoverflow.com/questions/25889046/difference-between-ansi-and-unicode-drivers-of-mysql

Drivers for the database you want to use might also be available in these different flavors. It isn't just about the codepage default, that can also be choosen by options for MySQL, too, still there are these two major families besides 32 vs 64 bitness. If that's not available, Mike has understood me well in telling there's nothing you can fix by changing any configuration options, they all only influence how queries are interpreted and how results are prepared for you, not about connectinng itself.

I think atlopes identified theproblem ratheris thecompatibility of VFP about different ODBC versions. I didn't think of going hoops over OLEDB/ODBC Bridge drivers, but it makes sense this unbinds you from incompatibilities.

VFP is a legacy system, it'sto be expected you will get less interop as things advance and VFP not.

Bye, Olaf.

 

[cfsjohn]

Thanks Olaf. I just downloaded a newer version of the driver and it worked. I have a handle.

I am now trying to issue a line of SQL that the customer gave me:

SELECT "jlinmate"."curbook", "jlinmate"."namenum", "nmmain"."last", "jllocatn"."name", "nmmain"."first", "nmmain"."street", "nmmain"."city", "nmmain"."state", "nmmain"."zip", "nmmain"."birthd", "nmmain"."race", "nmmain"."sex", "nmmain"."ssnum", "nmlocal"."type", "nmlocal"."localid", "nmmain"."stateid", "jlinmate"."here", "jlbktype"."descrpt"
FROM {oj ("admin"."nmlocal" "nmlocal" RIGHT OUTER JOIN "admin"."nmmain" "nmmain" ON "nmlocal"."number"="nmmain"."number") INNER JOIN (((("admin"."jllcbddt" "jllcbddt" INNER JOIN "admin"."jlbook" "jlbook" ON "jllcbddt"."inmate"="jlbook"."inmate") INNER JOIN "admin"."jlinmate" "jlinmate" ON "jllcbddt"."inmate"="jlinmate"."num") INNER JOIN "admin"."jllocatn" "jllocatn" ON "jllcbddt"."locatn"="jllocatn"."num") INNER JOIN "admin"."jlbktype" "jlbktype" ON "jlbook"."type"="jlbktype"."code") ON "nmmain"."number"="jlinmate"."namenum"}
WHERE "jlinmate"."here"='Y'
ORDER BY "jlinmate"."namenum"

Of course I need to convert his SQL into a string and then do the following which should put the results into a cursor named lcinmates_all:
lnresult = SQLEXEC(lnhandle,sqlstr, 'lcinmates_all')

I admit the syntax of his SQL statement has me a bit bluffed (all those double quotation marks).
Here is the string I built:
sqlstr="SELECT jlinmate.curbook,"+;
"jlinmate.namenum,"+;
"nmmain.last,"+;
"jllocatn.name,"+;
"nmmain.first,"+;
"nmmain.street,"+;
"nmmain.city,"+;
"nmmain.state,"+;
"nmmain.zip,"+;
"nmmain.birthd,"+;
"nmmain.race,"+;
"nmmain.sex,"+;
"nmmain.ssnum,"+;
"nmlocal.type,"+;
"nmlocal.localid,"+;
"nmmain.stateid,"+;
"jlinmate.here,"+;
"jlbktype.descrpt "+;
"FROM {oj (admin.nmlocal nmlocal RIGHT OUTER JOIN admin.nmmain nmmain ON nmlocal.number=nmmain.number) "+;
"INNER JOIN ((((admin.jllcbddt jllcbddt INNER JOIN admin.jlbook jlbook ON jllcbddt.inmate=jlbook.inmate) "+;
"INNER JOIN admin.jlinmate jlinmate ON jllcbddt.inmate=jlinmate.num) "+;
"INNER JOIN admin.jllocatn jllocatn ON jllcbddt.locatn=jllocatn.num) "+;
"INNER JOIN admin.jlbktype jlbktype ON jlbook.type=jlbktype.code) ON nmmain.number=jlinmate.namenum} "+;
"WHERE jlinmate.here='Y' "+;
"ORDER BY jlinmate.namenum"
lnresult = SQLEXEC(lnhandle,sqlstr, 'lcinmates_all')
?lnresult

lnresult is -1 which means I am not getting any results.

I am sure I have probably done something stupid with regard to building the value for sqlstr but if you care to see if perhaps there is something illogical about the SQL statement the customer said would work, I would be thankful. There are certainly some joins in there that make no sense to me.

John

 

[atlopes]

If you can be sure that the provided SQL statement should/must work

LOCAL SQLstr AS String

TEXT TO m.SQLstr NOSHOW
SELECT "jlinmate"."curbook", "jlinmate"."namenum", "nmmain"."last", "jllocatn"."name", "nmmain"."first", "nmmain"."street", "nmmain"."city",
  "nmmain"."state", "nmmain"."zip", "nmmain"."birthd", "nmmain"."race", "nmmain"."sex", "nmmain"."ssnum", "nmlocal"."type", "nmlocal"."localid",
  "nmmain"."stateid", "jlinmate"."here", "jlbktype"."descrpt"
FROM {oj ("admin"."nmlocal" "nmlocal" RIGHT OUTER JOIN "admin"."nmmain" "nmmain" ON "nmlocal"."number"="nmmain"."number")
  INNER JOIN (((("admin"."jllcbddt" "jllcbddt" INNER JOIN "admin"."jlbook" "jlbook" ON "jllcbddt"."inmate"="jlbook"."inmate")
  INNER JOIN "admin"."jlinmate" "jlinmate" ON "jllcbddt"."inmate"="jlinmate"."num")
  INNER JOIN "admin"."jllocatn" "jllocatn" ON "jllcbddt"."locatn"="jllocatn"."num")
  INNER JOIN "admin"."jlbktype" "jlbktype" ON "jlbook"."type"="jlbktype"."code") ON "nmmain"."number"="jlinmate"."namenum"}
WHERE "jlinmate"."here"='Y'
ORDER BY "jlinmate"."namenum"
ENDTEXT

will assign the value you were given verbatim.

 

[Olaf Doschke]

Well, you haven't translated the SQL 1:1, every place you used double quotes as string delimiter of a part of the string it didn't became part of the SQL string.

The easiest way to have an SQL statement the way the target database needs it is to store it into a file and load it via lcSQL = FILETOSTR("c:\path\to\some.sql") or to put it into a TEXT..ENDTEXT block, because within that you're able to use both double and single quotation marks and also the third string delimiter VFP uses, square brackets. Besides enabling to write multiline strings, though that's possible with the normal string delimiters, too.

The syntax you got is something I'd associate with ODBC specific syntax, which may not apply when you go the rout atlopes laid out, as there are many layers involved, which are not all ODBC anymore.

Typically several syntax is optional, eg MySQL also recommends backticks as name delimiter, but they are only necessary, if your names contain spaces. Double quotes as name delimiters are quite normal, MSSQL and Access also support it, but it's optional as long as normal names are involved. Especially curly brackets are something I associate with ODBC specific commands. If the programmer you got this from is using ODBC from a low level like C++ that would explain a lot.

That aside, simply tryto put theSQL you got 1:1 in here:

TEXT TO sqlstr NOSHOW
SELECT "jlinmate"."curbook", "jlinmate"."namenum", "nmmain"."last", "jllocatn"."name", "nmmain"."first", "nmmain"."street", "nmmain"."city", "nmmain"."state", 
"nmmain"."zip", "nmmain"."birthd", "nmmain"."race", "nmmain"."sex", "nmmain"."ssnum", "nmlocal"."type", "nmlocal"."localid", "nmmain"."stateid", "jlinmate"."here", 
"jlbktype"."descrpt"
FROM {oj ("admin"."nmlocal" "nmlocal" RIGHT OUTER JOIN "admin"."nmmain" "nmmain" ON "nmlocal"."number"="nmmain"."number") 
INNER JOIN (((("admin"."jllcbddt" "jllcbddt" INNER JOIN "admin"."jlbook" "jlbook" ON "jllcbddt"."inmate"="jlbook"."inmate") 
INNER JOIN "admin"."jlinmate" "jlinmate" ON "jllcbddt"."inmate"="jlinmate"."num") 
INNER JOIN "admin"."jllocatn" "jllocatn" ON "jllcbddt"."locatn"="jllocatn"."num") 
INNER JOIN "admin"."jlbktype" "jlbktype" ON "jlbook"."type"="jlbktype"."code") ON "nmmain"."number"="jlinmate"."namenum"}
WHERE "jlinmate"."here"='Y'
ORDER BY "jlinmate"."namenum"
ENDTEXT
lnresult = SQLEXEC(lnhandle,sqlstr, 'lcinmates_all')
?lnresult

Try to execute that. Besides make this a norm:

lnresult = SQLEXEC(lnhandle,sqlstr, resultaliasname)
If lnresult<0
   AERROR(laError)
   set step on
ENDIF

If an sql command error is reported, you get the error message from the remote database in the laError array and can inspect it in the locals window of the debugger, or write some code outputting the array elements into an error log file.

Bye, Olaf.

 

[Olaf Doschke]

Atlopes,

if you edit your post and add in line breaks in the SQL, the code section will collapse and the whole thread will become readable again.
Thanks in advance.

Bye. Olaf.

 

[atlopes]

Done, Olaf.

 

[Olaf Doschke]

Thanks!

 

[cfsjohn]

I did exactly as you described. I used the Text...Endtext format for now but will look into using the filetostr as a longer term solution.

I included the AERROR handling as described by Olaf as that will be especially helpful in getting this tested.

I can't thank all of you enough. When I posted this issue yesterday, I had little hope of getting this accomplished over the weekend. As of now, I am looking at data from the target database!

As always, thank you for your expert guidance,
John