Page 1 of 2

Database With ODBC + FNXbasic : It works

Posted: Fri Aug 10, 2012 7:12 am
by Bruno FR28
Bonjour/ hello

I propose my first try for using ODBC with FNXbasic. the file ZIP contains :
- a file .inc to declare const� and function ODBC
- a file .bas which connect and select a Access Database
- a file .doc to create the DNS for this example (DNS "test")
- a file .mdb (sample.mdb)
- a file .exe

Warning : the all ODBC functions are not declare in the .inc, Only the main for usual query.

test on Win XP SP2 French

Cordialement/Regards
Bruno.

Database With ODBC + FNXbasic : It works

Posted: Fri Aug 10, 2012 3:20 pm
by cvirus
Nice work i have tested and it works like a charm, the next thing is to implement the update the insert delect and all that stuff to make a complete working database system.

I have notice that the .inc file contains the constants and also the API calls.

Thanks

Database With ODBC + FNXbasic : It works

Posted: Fri Aug 10, 2012 6:22 pm
by Bruno FR28
Hello Cvirus

easy for Insert, Update.. put this code before the comment "ODBC - tape 6", in the ODBCAPI.bas

` end of previous select , remark SQL CLOSE, and not SQLDROP
SQLFreeStmt.SQLStmt=glSQLstmt
SQLFreeStmt.EndOption =SQL_CLOSE
SQLFreeStmt.execute

` an update for example, but could be insert or delete.
SQL ="update table1 set col1=col1+1 where col1 = (select max(col1) from table1)"
SQLExecDirect.SQLStmt =glSQLStmt
SQLExecDirect.sqlString =SQL
SQLExecDirect.sqlstrlen =len(SQL)
SQLExecDirect.execute

` no data return, so no loop to get data. just test the SQL syntax
if SQLExecDirect.result <> SQL_SUCCESS then
  SQLError.env = glEnv
  SQLError.dbc = glDbc
  SQLError.SQLStmt = glSQLstmt 
  SQLError.Buflen = 255
  SQLError.Outlen = varptr(lgcolval)
  SQLerror.execute

  wstr= "Erreur SQL "+ CR
  wstr= wstr + "ODBC State : "+ SQLError.SQLstate + CR
     wstr= wstr + "ODBC Native Error : "+ str$(SQLError.NativeError) + CR
  Wstr= wstr + "ODBC Message : " + SQLError.buffer

  i=MessageDlg(wstr,"Erreur SQL", mbok+mbiconhand)
end if

`ODBC tape 6 : ....

after each execute, the max col1 increase.

Cordialement/Regards
Bruno

PS : I collect all the remarks, and i will write a new version.

Database With ODBC + FNXbasic : It works

Posted: Fri Aug 10, 2012 6:28 pm
by cvirus
This is very usefull but a bit confused, maybe youi should turn that into ann lib and use functions to do the job, maybe more friendly that way, or as soon as you finish write a tuto to explain that stuff.

Database With ODBC + FNXbasic : It works

Posted: Fri Aug 10, 2012 8:15 pm
by Bruno FR28
oh !� �I recognize that it is hard at the beginning but it`s the normal use of the ODBC Library....

But to work with DataBase, you need know 2 things :
1- SQL for working data
2 - the library to use SQL in your program here ODBC

it is not very easy, otherwise why professional developper? but it is possible for everyone with some time



Bruno

Database With ODBC + FNXbasic : It works

Posted: Fri Aug 10, 2012 8:32 pm
by cvirus
Are a prof. Developer? I know that with some time everybody can make an app with odbc, but like that is a bit hard because the sql commands are not very clear.  I can make an app like that in no time with java, bwcause the sintax is clear.  ;D

Database With ODBC + FNXbasic : It works

Posted: Fri Aug 10, 2012 9:02 pm
by Bruno FR28
I was... i stopped writting program 12 years ago.

the SQL and ODBC are 2 different things : ODBC make you program discuss with the database Engine (give orders and get the results), and the SQL manage the Data ( tell to the Data Engine what doing).
i suppose in Java , you have many classes to replace ODBC but the difficulty of SQL stay anyway.

Bruno.
is my english comprehensible ?

Database With ODBC + FNXbasic : It works

Posted: Fri Aug 10, 2012 9:54 pm
by cvirus
In java there are several ways to connect to diferente databases, and its a lot easy,  a know that odbc and sql are two diferent things but thanks, and yes your english is nice.

Database With ODBC + FNXbasic : It works

Posted: Sat Aug 11, 2012 8:45 am
by Bruno FR28
Bonjour/hello

I read the FNXbasicHelp, and there is noway to declare user class. only record for data, but no method. and Type is just to expand existing type.

I must to write function "to the old way" (connect(db) instead db.connect for example).

Bye
Bruno

Database With ODBC + FNXbasic : It works

Posted: Sun Sep 08, 2013 9:03 am
by martiekr
Hi Bruno,

SQL is just like every other language: you have to learn it.
It is not difficult, just different :D .
I`m a programmer for almost 40 years now and i started using SQL 10 years ago (MS-Acces). Now i am working with Database-angines like MySQL, PostgreSQL, DB2 (and MS-Access) all using SQL.
I can create very complex SQL statements (multiple tabels + joins).
Btw. having a MS-Access around can be very handy as it has a (graphical) query builder. So you put the tables in the querybuilder, drag the JOIN connect between the tables(columns) and there you go, the (complex) SQL is ready to use (90% of the time werking correct).

Grtz.
Martie
[quote author=6B6F7F747F7928221A0 link=1344582769/6#6 date=1344632541]I was... i stopped writting program 12 years ago.

the SQL and ODBC are 2 different things : ODBC make you program discuss with the database Engine (give orders and get the results), and the SQL manage the Data ( tell to the Data Engine what doing).
i suppose in Java , you have many classes to replace ODBC but the difficulty of SQL stay anyway.

Bruno.
is my english comprehensible ?[/quote]