Page 2 of 10

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 3:33 pm
by Roberto Lopez
Roberto Lopez wrote: So, could be easy to make some sort of intermediate layer to unify functions to access SQL (local via SqLite or remote via MySql or PostgreSql).
Some years ago, I've created a small code layer called 'MiniSql'.

Perhaps, this code could be useful to you in some way.

I'm sure that I have a backup of it, but, I can't find it :)

So, if someone has it, please upload!

Regards,

Roberto.

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 5:26 pm
by sudip
Hello Master Roberto,

Is the following code you are talking about?

Code: Select all

/*
 * HMG - Harbour Win32 GUI library
 * 
 * MiniSql (a Simple MySql Access Layer)
 *
 * Copyright 2002-2005 Roberto Lopez <harbourminigui@gmail.com>
 * http://harbourminigui.googlepages.com/
 *
*/

*------------------------------------------------------------------------------*
Function SqlDoQuery( nHandle , cQuery )
*------------------------------------------------------------------------------*
Local nNumRows
Local nNumFields
Local nQueryResultHandle
Local aQuery := {}
Local aRow := {}
Local i

	if __mvexist ('SqlTrace')
		If SqlTrace == .T.
			MsgInfo (cQuery)
		EndIf
	endif

	SqlQuery ( nHandle , cQuery ) ; SqlErrorCheck ( nHandle )

	nQueryResultHandle := sqlStoreR( nHandle ) ; SqlErrorCheck ( nHandle )

	nNumRows := sqlNRows( nQueryResultHandle ) ; SqlErrorCheck ( nHandle )
	nNumFields := sqlNumFi( nQueryResultHandle ) ; SqlErrorCheck ( nHandle )

	For i := 1 To nNumRows

		aRow := sqlFetchR(nQueryResultHandle) ; SqlErrorCheck ( nHandle )

		aadd ( aQuery , aRow )

	Next i

Return aQuery

*------------------------------------------------------------------------------*
Function SqlDoCommand ( nHandle , cQuery )
*------------------------------------------------------------------------------*
Local ar

	if __mvexist ('SqlTrace')
		If SqlTrace == .T.
			msginfo(cQuery)
		EndIf
	endif

	SqlQuery ( nHandle , cQuery ) ; SqlErrorCheck ( nHandle )

	ar := SqlAffRows(nHandle)  ; SqlErrorCheck ( nHandle )

Return ar

*------------------------------------------------------------------------------*
Procedure SqlErrorCheck ( nHandle )
*------------------------------------------------------------------------------*
Local SqlCurrentError

	SqlCurrentError := SqlGetErr ( nHandle )

	If .Not. Empty ( SqlCurrentError )

		SqlQuery ( nHandle , 'UNLOCK TABLES')
		SqlClose( nHandle )

		MsgStop (SqlCurrentError ,"MiniSql")		

	EndIf

Return

#ifndef __XHARBOUR__

#pragma BEGINDUMP

#define HB_OS_WIN_32_USED

#include "hbapi.h"

HB_FUNC_EXTERN( MYSQL_GET_SERVER_VERSION      ); HB_FUNC( SQLVERSION      ) { HB_FUNC_EXEC( MYSQL_GET_SERVER_VERSION      ); }
HB_FUNC_EXTERN( MYSQL_REAL_CONNECT            ); HB_FUNC( SQLCONNECT      ) { HB_FUNC_EXEC( MYSQL_REAL_CONNECT            ); }
HB_FUNC_EXTERN( MYSQL_CLOSE                   ); HB_FUNC( SQLCLOSE        ) { HB_FUNC_EXEC( MYSQL_CLOSE                   ); }
HB_FUNC_EXTERN( MYSQL_COMMIT                  ); HB_FUNC( SQLCOMMIT       ) { HB_FUNC_EXEC( MYSQL_COMMIT                  ); }
HB_FUNC_EXTERN( MYSQL_ROLLBACK                ); HB_FUNC( SQLROLLBACK     ) { HB_FUNC_EXEC( MYSQL_ROLLBACK                ); }
HB_FUNC_EXTERN( MYSQL_SELECT_DB               ); HB_FUNC( SQLSELECTD      ) { HB_FUNC_EXEC( MYSQL_SELECT_DB               ); }
HB_FUNC_EXTERN( MYSQL_QUERY                   ); HB_FUNC( SQLQUERY        ) { HB_FUNC_EXEC( MYSQL_QUERY                   ); }
HB_FUNC_EXTERN( MYSQL_STORE_RESULT            ); HB_FUNC( SQLSTORER       ) { HB_FUNC_EXEC( MYSQL_STORE_RESULT            ); }
HB_FUNC_EXTERN( MYSQL_USE_RESULT              ); HB_FUNC( SQLUSERES       ) { HB_FUNC_EXEC( MYSQL_USE_RESULT              ); }
HB_FUNC_EXTERN( MYSQL_FREE_RESULT             ); HB_FUNC( SQLFREER        ) { HB_FUNC_EXEC( MYSQL_FREE_RESULT             ); }
HB_FUNC_EXTERN( MYSQL_FETCH_ROW               ); HB_FUNC( SQLFETCHR       ) { HB_FUNC_EXEC( MYSQL_FETCH_ROW               ); }
HB_FUNC_EXTERN( MYSQL_DATA_SEEK               ); HB_FUNC( SQLDATAS        ) { HB_FUNC_EXEC( MYSQL_DATA_SEEK               ); }
HB_FUNC_EXTERN( MYSQL_NUM_ROWS                ); HB_FUNC( SQLNROWS        ) { HB_FUNC_EXEC( MYSQL_NUM_ROWS                ); }
HB_FUNC_EXTERN( MYSQL_FETCH_FIELD             ); HB_FUNC( SQLFETCHF       ) { HB_FUNC_EXEC( MYSQL_FETCH_FIELD             ); }
HB_FUNC_EXTERN( MYSQL_FIELD_SEEK              ); HB_FUNC( SQLFSEEK        ) { HB_FUNC_EXEC( MYSQL_FIELD_SEEK              ); }
HB_FUNC_EXTERN( MYSQL_NUM_FIELDS              ); HB_FUNC( SQLNUMFI        ) { HB_FUNC_EXEC( MYSQL_NUM_FIELDS              ); }
HB_FUNC_EXTERN( MYSQL_FIELD_COUNT             ); HB_FUNC( SQLFICOU        ) { HB_FUNC_EXEC( MYSQL_FIELD_COUNT             ); }
HB_FUNC_EXTERN( MYSQL_LIST_FIELDS             ); HB_FUNC( SQLLISTF        ) { HB_FUNC_EXEC( MYSQL_LIST_FIELDS             ); }
HB_FUNC_EXTERN( MYSQL_ERROR                   ); HB_FUNC( SQLGETERR       ) { HB_FUNC_EXEC( MYSQL_ERROR                   ); }
HB_FUNC_EXTERN( MYSQL_LIST_DBS                ); HB_FUNC( SQLLISTDB       ) { HB_FUNC_EXEC( MYSQL_LIST_DBS                ); }
HB_FUNC_EXTERN( MYSQL_LIST_TABLES             ); HB_FUNC( SQLLISTTBL      ) { HB_FUNC_EXEC( MYSQL_LIST_TABLES             ); }
HB_FUNC_EXTERN( MYSQL_AFFECTED_ROWS           ); HB_FUNC( SQLAFFROWS      ) { HB_FUNC_EXEC( MYSQL_AFFECTED_ROWS           ); }
HB_FUNC_EXTERN( MYSQL_GET_HOST_INFO           ); HB_FUNC( SQLHOSTINFO     ) { HB_FUNC_EXEC( MYSQL_GET_HOST_INFO           ); }
HB_FUNC_EXTERN( MYSQL_GET_SERVER_INFO         ); HB_FUNC( SQLSRVINFO      ) { HB_FUNC_EXEC( MYSQL_GET_SERVER_INFO         ); }
HB_FUNC_EXTERN( MYSQL_ESCAPE_STRING           ); HB_FUNC( DATATOSQL       ) { HB_FUNC_EXEC( MYSQL_ESCAPE_STRING           ); }
HB_FUNC_EXTERN( MYSQL_ESCAPE_STRING_FROM_FILE ); HB_FUNC( FILETOSQLBINARY ) { HB_FUNC_EXEC( MYSQL_ESCAPE_STRING_FROM_FILE ); }

#pragma ENDDUMP

#endif
If it is the program you are talking about, I am also uploading the folder containing this file also.
MiniSql.zip
(5.48 KiB) Downloaded 330 times
I found it from Minigui\Samples\Advansed\Minisql folder. And I hope this can be easily compiled with HMG official with some minor changes.

With best regards.

Sudip

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 5:34 pm
by Roberto Lopez
sudip wrote:Hello Master Roberto,

Is the following code you are talking about?
<...>
Yes it is.

You'll must find a '.ch' file, a sample and some little docs too.

Could be you so kind to upload all the files (if available) ?

Regards,

Roberto.

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 5:37 pm
by sudip
Yes Master Roberto,

I am uploading it.
MiniSql.zip
(5.48 KiB) Downloaded 326 times
With best regards.

Sudip

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 6:11 pm
by Rathinagiri
Oh! Nice. It is a treasure to dig in. :)

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 6:16 pm
by Roberto Lopez
sudip wrote:Yes Master Roberto,

I am uploading it.
MiniSql.zip
With best regards.

Sudip
OOPS!!!

Sorry, I've not seen it :)

Regards,

Roberto.

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 6:22 pm
by Roberto Lopez
rathinagiri wrote:Oh! Nice. It is a treasure to dig in. :)
I've done for simplicity and speed and I've used in a couple of projects.

It uses direct C calls to access MySql API (not TMySql).

When I left the code, a couple of things should be added yet.

1. Field type detection for automatic conversion (in result recordset).

2. Create a local temporary table to store the recordset, so, it could be accessed as a normal table.

Regards,

Roberto.

Re: My First SQLite Project

Posted: Tue Sep 15, 2009 2:02 am
by sudip
Hello Master Roberto,

The main thing for which I liked VFP for it's embedded SQL. Thank you so much. You made it available to harbour.
1. Field type detection for automatic conversion (in result recordset).

2. Create a local temporary table to store the recordset, so, it could be accessed as a normal table.
Now, if we can add those 2 points. It will be even better. For long time I was looking for temporary table with HMG (as I used it in VFP Cursor). Temporary table creation is already available with Harbour, but I can't create it with HMG Official. So, I am very happy to read your post.

Thank you again.

With best regards.

Sudip

Re: My First SQLite Project

Posted: Tue Sep 15, 2009 3:51 am
by Roberto Lopez
sudip wrote:Temporary table creation is already available with Harbour, but I can't create it with HMG Official.
Why not?

I've was thinking in a temporary 'disk-file' standard table.

What do you was thinking?

Regards,

Roberto.

Re: My First SQLite Project

Posted: Tue Sep 15, 2009 4:16 am
by Rathinagiri
Roberto Lopez wrote: It uses direct C calls to access MySql API (not TMySql).
:o

Is it? Fine.