Clarion and Native SQL 1

[matthew230]

Hi,

I am not a Clarion programmer. I am looking at a Clarion built application with SQL Server back end.

The problem is that we would like to improve the speed one quick way would be to replace some rather inefficient client based processes with a few quick SQL statements. But how ?

I've been playing with PROP:SQL, it is fine for basic statements, but as soon as I try something slightly complicated the driver has some kind of brain freeze and refuses to co-operate.

Does anyone know how I can send native SQL statements to the database ? Currently using the clarion SQL Server driver, perhaps the wrong one?

If anyone is curious to what doesn't work, try creating a temp table with PROP:SQL.

Any help much appreciated.

Thanks

Matt

 

[ShankarJ]

Hi Matt,

You have not mentioned the Clarion version you are using since SQL support has been improved over the versions. The last stable version is C6.3.9058 and I am using C6.3.9056.

A PROP:SQL call sends whatever you put in it back to the SQL server to process. So, unless you post an example of what you are trying to do, I cannot help you.

I use PROP:SQL a lot using a Dummy Table with the /TURBOSQL switch and have no problems with it.

Regards

 

[matthew230]

Hi ShankarJ

Thank you for your response.

The version is 6.3.9058

One area problems are occuring is using a SQL variable.

The native statement would be:

declare @temp1 as int
set @temp1 = 10
select @temp1

or perhaps an update statement where table1 and columnint need to be replaced with proper available entities (use a table with an int column)

declare @temp1 as int
set @temp1 = 10
update table1
set columnint = columnint + @temp1

At the risk of patronising, once the variable is delcared, then SQL will kick back an error as the database will hold the variable until the connection is dropped. I've noticed that the Clarion format is to open a connection when the program is opened and only dropped once the program is exited.

Any help greatly appreciated.

Matt

 

[ShankarJ]

Hi Matt,

I still do not understand why you need to use SQL variables unless you are using them in a Stored Procedure. For SELECT,INSERT,UPDATE & DELETE statements, you send the variables as values i.e.

DummyFile{PROP:SQL} = 'SELECT * FROM Table WHERE ID = ' & locID

The same can be executed with a BIND() i.e.

BIND('locID', locID)
DummyFile{PROP:SQL} = 'SELECT * FROM Table WHERE ID = &locID'

Notice the & before the locID which says the locID is a label bound to a variable.

Regards

 

[matthew230]

Hi,

Using SQL variables makes life a little easier with dynamic queries, however I accept there are other ways of doing it.

What about temp tables ?

For example:

CREATE TABLE #temptable1(
col1 CHAR(50))
INSERT INTO #temptable1 SELECT column1 FROM table1
WHERE column1 LIKE 'a%'
SELECT * FROM #temptable1
DROP TABLE #temptable1

 

[ShankarJ]

Hi Matt,

There are scoping issues since Clarion try to execute everything within a Cursor. You could buy MAV Direct ODBC from

http://www.ingasoftplus.com

to avoid the use of cursors OR you could wrap you SQL commands within a temporary procedure i.e.

DummyFile{PROP:SQL} = 'DROP PROCEDURE MyTempProc'
IF ERRORCODE()
...
END

DummyFile{PROP:SQL} = 'CREATE PROCEDURE MyTempProc AS
<SQL Statements to execute separated
by ;>'
IF ERRORCODE()
...
END

DummyFile{PROP:SQL} = 'CALL MyTempProc'
IF ERRORCODE()
...
ELSE
LOOP
NEXT(DummyFile)

IF ERRORCODE() THEN BREAK.

... your processing ...
END
END

Regards

 

[matthew230]

ShankarJ,

According to the clarion developers this is working ! Now I can sit here and feed them SQL Statements all day long and introduce them to a world without cursoring !

Where are you based ? Could have some work for you in the future. Clarion programmers with proper SQL skills are rare.

Matthew

 

[ShankarJ]

Hi Mathew,

Sorry. Been out of the country for a while. Glad it worked out for you. I am based in Dubai, United Arab Emirates. Thanks for the offer but I have my plate full . If you need help or sub-contract work there are Clarion newsgroups for that - Check out

http://www.softvelocity.com/Community/discussion_boards.htm

for more info or login into

http://discuss.softvelocity.com/.

Regards