SQL Test app

Public forum to share code snippets, screen shorts, experiences, etc.
Post Reply
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

SQL Test app

Post by OhioJoe »

Trying to set up a basic SQL test app. Using SQLite3 ODBC. Here is the code and the compile errors. Thanks in advance for any help.

VO Dialect has been selected.

Code: Select all

FUNCTION Start( ) AS VOID
	LOCAL cDB AS STRING 
	LOCAL oConnection AS SQLConnection
	LOCAL oStatement  AS SQLStatement 
	LOCAL cStatement  AS STRING    
	
	* trying to open an SQLite3 database
	* here is the ODBC driver:    
	* http://www.ch-werner.de/sqliteodbc/
 

	cDB := "c:\fr\FPTEST.DB"  // used the attached sample db
	SQLConnectErrorMsg( TRUE )  
	oConnection := SQLConnection{"SQLite3", "", "" }	    
	oConnection:connect()

  	cStatement := "ATTACH DATABASE " + cDB + " AS JoeDb" 
 	oStatement := SQLStatement{cStatement , oConnection }
 	IF !oStatement:Execute()
		System.Console.WriteLine("Did not connect")
	ENDIF    
RETURN


Here are the errors:
error XS0246: The type or namespace name 'SQLConnection' could not be found (are you missing a using directive or an assembly reference?) 3,2 Start.prg Start
error XS0246: The type or namespace name 'SQLStatement' could not be found (are you missing a using directive or an assembly reference?) 4,2 Start.prg Start
error XS0012: The type 'SQLConnection' is defined in an assembly that is not referenced. You must add a reference to assembly 'JoeSQLTest, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'. 14,2 Start.prg Start
error XS0246: The type or namespace name 'SQLConnection' could not be found (are you missing a using directive or an assembly reference?) 14,17 Start.prg Start
error XS0012: The type 'SQLConnection' is defined in an assembly that is not referenced. You must add a reference to assembly 'JoeSQLTest, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'. 15,2 Start.prg Start
error XS0012: The type 'SQLStatement' is defined in an assembly that is not referenced. You must add a reference to assembly 'JoeSQLTest, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'. 18,3 Start.prg Start
error XS0246: The type or namespace name 'SQLStatement' could not be found (are you missing a using directive or an assembly reference?) 18,17 Start.prg Start
error XS0012: The type 'SQLConnection' is defined in an assembly that is not referenced. You must add a reference to assembly 'JoeSQLTest, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'. 18,43 Start.prg Start
error XS0012: The type 'SQLStatement' is defined in an assembly that is not referenced. You must add a reference to assembly 'JoeSQLTest, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'. 19,7 Start.prg Start
warning XS0168: The variable 'oConnection' is declared but never used 3,2 Start.prg Start
warning XS0168: The variable 'oStatement' is declared but never used 4,2 Start.prg Start
Compilation failed (9 errors, 2 warnings)
Here are the references:

System
System.Core
XSharp.Core
XSharp.RT
XSharp.SQLClasses

(I've tried to attach the project and test DB but I get a message saying "invalid extension" when I select the .viaef and the .db file.)

Thank you again. I once started a project to convert from DBF to the VO SQL classes using SQLite ODBC and ran into some difficulties converting all the app features, so I abandoned the project, But most of the app worked in VO. I tried exporting it to X# and it did compile with no errors. But the app didn't work (windows just disappeared when I tried to open them) so I've decided to start over using only what's available in X#.

If I'm taking the wrong approach, please advise. The reason I'm using SQLite is simple: No server. Nothing for the user to install. Cuts down on support time.

Goal is to have the same database with different "front-ends" in Windows and in a browser. So both Windows and web users will be happy.
Joe Curran
Ohio USA
User avatar
wriedmann
Posts: 3739
Joined: Mon Nov 02, 2015 5:07 pm
Location: Italy

Re: SQL Test app

Post by wriedmann »

Hi Joe,
you need System and System.Data in both the references and the using statements.
The same is true for the SQLite dataprovider: you need the correct DLL and namespace.
SQLite also needs different engines for 32 and 64 bit environments.
Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
wolfgang@riedmann.it
https://www.riedmann.it - https://docs.xsharp.it
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

Re: SQL Test app

Post by OhioJoe »

Thank you, Wolfgang, but I got the same result.
Here's a zip file with the project and the db in case anyone wishes to try.
Attachments
JoeSQLTest.zip
(95.25 KiB) Downloaded 252 times
Joe Curran
Ohio USA
FFF
Posts: 1562
Joined: Fri Sep 25, 2015 4:52 pm
Location: Germany

Re: SQL Test app

Post by FFF »

Joe,
add
USING XSHARP.VO.SDK
at the top of your file and it will compile...
Regards
Karl
(on Win8.1/64, Xide32 2.20, X#2.20.0.3)
User avatar
Chris
Posts: 4809
Joined: Thu Oct 08, 2015 7:48 am
Location: Greece

Re: SQL Test app

Post by Chris »

Hi Joe,

Even better, go to the app properties dialog, Compiler page and check the option "/ins Enable implicit namespace". This tells the compiler to automatically insert implicitly the USING statement that Karl mentioned.

This USING statement is needed because the class names are not simply SQLConnection and SQLStatement as in VO (where we were a small community with very few third party libraries), but as in typical .Net convention, the names include a namespace, which in this case is "XSharp.VO.SDK", making the full class names XSharp.VO.SDK.SQLConnection and XSharp.VO.SDK.SQLStatement. The equivalent classes when you use the regular VO SDK libraries are VO.SQLConnection and VO.SQLStatement (the namespace is simply "VO" for them). Those longer class names help avoiding name conflicts with the other millions of classes included in system and 3rd party classes available in the framework.

As for the problem running your test app, please start it under the debugger (Debug->Start Integrated Debugging in XIDE), this should give you an error dialog with information about when the app crashed and why. There might be info available also in the Windows Event Log.
Chris Pyrgas

XSharp Development Team
chris(at)xsharp.eu
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

Re: SQL Test app

Post by OhioJoe »

Thank you once again, everyone.
This code compiles and works:

Code: Select all

	LOCAL cDB AS STRING 
	LOCAL oConnection AS SQLConnection
	LOCAL oStatement  AS SQLStatement   
	LOCAL cStatement  AS STRING   
	
	* trying to open an SQLite3 database
	* here is the ODBC driver:    
	* http://www.ch-werner.de/sqliteodbc/
 
  // open everything
	cDB := "c:\sqlprgs\joe.db"
	SQLConnectErrorMsg( TRUE )  
	oConnection := SQLConnection{"SQLite3", "", "" }	    
	oConnection:connect()
	IF !oConnection:Connected
  		System.Console.Writeline("didn't connect")
		RETURN
	ELSE
  		System.Console.Writeline("connected")
  	ENDIF    

	cStatement := "ATTACH DATABASE '" + cDB + "' AS JoeDB" 
	oStatement := SQLStatement{cStatement , oConnection }

	IF !oStatement:Execute()
		System.Console.Writeline( "didn't execute " + cStatement)
 	ENDIF    
 	oStatement:FreeStmt( SQL_DROP ) 
 	
* 	XTester( 1, oConnection )
*	XTester( 2, oConnection )
*	XTester( 3, oConnection )
*	XTester( 4, oConnection )
	XTester( 5, oConnection )
RETURN   
That "XTester" function tests various routines such as Seek() and file loops. Only one works, so you'll be hearing again from me! (Aren't you guys glad you met me ? :) )
When I went through this in VO several years ago I found that several of the methods didn't work according to the documentation so there's lots of work-arounds and sub-classes in the old app. I will go through it again using the X# SQL classes. If I run into anything, I'll post it here. Thank you again.
Joe Curran
Ohio USA
Post Reply