SqlCreateSession to an MSACCESS d.b.

General discussion forum about all databases other than SqlBase.
gigit
Italy
Posts: 170
Joined: 14 Jun 2017, 15:04
Location: Italy

SqlCreateSession to an MSACCESS d.b.

Post by gigit » 29 Jun 2021, 15:01

Hi to all.
Connecting to a MSACCESS d.b. via SqlCreateSession with version 4.1 is ok but migrating the application to v. 6.0 sp8 generates a problem when inserting numeric data.
The application runs connecting to a SqlBase d.b. with native routers, and , when needed, connects to an MSACCES d.b. with

Code: Select all

Function: fConnectTrans
	Description: Create  session 
	Returns
	Parameters
		String: FileDb
		Receive Session Handle: hSess
	Static Variables
	Local variables
	Actions
		! Connection values for MICROSOFT ACCESS AUTORIZATION
		Set SqlDatabase = FileDb
		Set SqlUser = 'admin'
		Set SqlPassword = ''
		! OK per ACCESS
		If SqlCreateSession( hSess, "Provider=" || sAccessJetProvider || ";Data Source=" || FileDb ||";" )
			Return TRUE
		Else
			Call SalMessageBox( 'Error creating SqlSession database '||FileDb, 'ATTENZIONE, ERRORE', 
					MB_Ok )
			Return FALSE

The instruction


Code: Select all

Call fConnectTrans ('g:\\database\\access\\clienti2.mdb', hSession2)
Call fConnectCursor( hSession2, hSqlSess2 )
Call SqlPrepareAndExecute( hSqlSess2, 'insert into scadenze (n_scad, dt_scad, n_importo, d_descr, segno, abicab, c_conto, n_valore)
		values (:nScad, :dfDtScad, :dfImporto, :dfDescr, :cmbSegno, :tblBanche.colAbiCab, :tblBanche.colConto, :nValore) ' )
where nValore is number and the value is 31.90 .
With td 4.1all runs well, with 6.0 the decimal point is ignored even if 31.90 or 31,90 and In the d.b. is saved as 319.00
dfImporto is a number data field nd the behavior is the same.

If I do the insert via SqlTalk or other d.b. tools all is ok.
I didn't try with select or update because I don't need to do it.

Any hint?
Thank you.

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 338
Joined: 05 Mar 2017, 20:57
Location: Stroud, England <--> Tauranga, New Zealand

Re: SqlCreateSession to an MSACCESS d.b.

Post by Steve Leighton » 30 Jun 2021, 01:30

.
Have you tried nValore as a literal rather than a bind i.e.

Call SqlPrepareAndExecute( hSqlSess2, 'insert into scadenze (n_scad, dt_scad, n_importo, d_descr, segno, abicab, c_conto, n_valore)
values (:nScad, :dfDtScad, :dfImporto, :dfDescr, :cmbSegno, :tblBanche.colAbiCab, :tblBanche.colConto, ' || SalNumberToStrX( nValore, 2 ) || ' ) ' )
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

gigit
Italy
Posts: 170
Joined: 14 Jun 2017, 15:04
Location: Italy

Re: SqlCreateSession to an MSACCESS d.b.

Post by gigit » 30 Jun 2021, 09:48

Hi Steve,
thank you for your reply.
That way it runs.

Code: Select all

Set sImporto=SalNumberToStrX( dfImporto, 2 )
Set sValore=SalNumberToStrX( nValore, 2 )
Call SqlPrepareAndExecute( hSqlSess2, 'insert into scadenze (n_scad, dt_scad, n_importo, d_descr, segno, abicab, c_conto, n_valore)values (:nScad, :dfDtScad, :sImporto, :dfDescr, :cmbSegno, :tblBanche.colAbiCab, :tblBanche.colConto, :sValore) ' )
n_importo and n_valore are stored as single in Access and now data are ok.

But why?
The connection is not properly set?

gigit
Italy
Posts: 170
Joined: 14 Jun 2017, 15:04
Location: Italy

Re: SqlCreateSession to an MSACCESS d.b.

Post by gigit » 30 Jun 2021, 10:12

I am trying also with UDL file

Code: Select all

!!CB!! 134
Function: fConnectTrans
	Description: Crea la sessione 
	Returns
	Parameters
		String: FileDb
		Receive Session Handle: hSess
	Static Variables
	Local variables
	Actions
		! Connection values for MICROSOFT ACCESS AUTORIZATION

		If SqlCreateSession( hSess, 'clienti2.udl' )
			Return TRUE
		Else
			Call SalMessageBox( 'Errore in creazione SqlSession database '||FileDb, 'ATTENZIONE, ERRORE', 
					MB_Ok )
			Return FALSE

but no way. Data are saved as integer. The UDL file is created using

Code: Select all

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Database\ACCESS\clienti2.mdb;Persist Security Info=False

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 338
Joined: 05 Mar 2017, 20:57
Location: Stroud, England <--> Tauranga, New Zealand

Re: SqlCreateSession to an MSACCESS d.b.

Post by Steve Leighton » 01 Jul 2021, 02:39

.
Sorry. No idea about MS Access - I'm bias for SQLBase :)
Maybe update the MS Access OLEDB driver ? 'Microsoft.Jet.OLEDB.4.0'
Otherwise just stick with literals ( or swap to SQLBase )
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

gigit
Italy
Posts: 170
Joined: 14 Jun 2017, 15:04
Location: Italy

Re: SqlCreateSession to an MSACCESS d.b.

Post by gigit » 01 Jul 2021, 08:40

Hi Steve, thank you. I have been working with SqlBase and SqlServer since 1995 but, sometimes, I have to connect to other d.b. at the customer. It's a dirty job but somebody gotta do it.... :D
This problem comes only with the connect transaction, while with the normal SqlConnect not.
As I said the program runs on a SqlBase d.b. and opens a transaction on access.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest