Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Database SQL functions ?

  1. #1
    Join Date
    May 2004
    Location
    France
    Posts
    250

    Question Database SQL functions ?

    Hi,

    What is the way in Girder 6 to access an external Database to make SQL queries ?

    In the past, there were these ways :
    Code:
    require('database')
    or
    require ( 'luasqlodbc')
    or
    require("odbc", "luaopen_luasqlodbc" )
    Thanks

  2. #2
    Join Date
    Jan 1998
    Location
    Jupiter, FL
    Posts
    13,550

    Default

    I'll compile that library and include it with the next release. I'll try to get this out before I leave this weekend, we have gotten completely swamped here with a few big hardware orders and the installation next week. It's good to be busy I guess
    Ron
    No support through PM

  3. #3
    Join Date
    Jan 1998
    Location
    Jupiter, FL
    Posts
    13,550

    Default

    OK I've compiled the ODBC library (for 64bit). Unzip the attached zip file into the <GIRDER INSTALLER>luasql directory.

    Then use this code to get started:

    Code:
    local ODBC = require('luasql.odbc')
    env = ODBC.odbc()
    print(env)
    this will be part of the installer next time round.
    Attached Files Attached Files
    Ron
    No support through PM

  4. #4
    Join Date
    May 2004
    Location
    France
    Posts
    250

    Default

    Thanks Ron for this quick answer.
    But I get an error when using the env:connect function


    Here is the sample script I use.
    My ODBC DSN works in Excel(MsQuery) 64 bit

    Code:
     -- Data Source Name local 
     odbc_dsn = 'girder'
     -- MySQL user name
     local odbc_user = 'girder'
     -- MySQL password
     local odbc_password = 'girder'
     -- MySQL query to run ('show tables' works well for me)
     local mysql_query = 'select * from temp'
     
    
     
    
     ODBC = require('luasql.odbc')
     local env = ODBC.odbc()
     if env then
      print ('MySQL environment object open = true')
     else
      print ('MySQL environment object open = error')
     end
     
    
     local conn = env:connect(odbc_dsn,odbc_user,odbc_password)
     if conn then
      print ('MySQL connection object open = true')
     else
      print ('MySQL connection object open = error')
     end
     
    
     local curr = conn:execute(mysql_query)
     if curr then
      print ('MySQL cursor object open = true')
     else
      print ('MySQL cursor object open = error')
     end
     
    
     local item = curr:fetch() 
     if item then
         print ('MySQL cursor object results --------*')
         while item do
             print( item )
             item = curr:fetch()
             if not item then
                 print ('end results --------*')
             end
         end
     else
         print ('MySQL cursor object returned nil -------*')
     end
     
    
     print ('MySQL cursor object closed =',curr:close())
     print ('MySQL connection object closed =',conn:close())
     print ('MySQL environment object closed =',env:close())

    The result :
    MySQL environment object open = true
    MySQL connection object open = error
    Scripting / [string "Scripting"]:26: attempt to index local 'conn' (a nil value)
    stack traceback:
    [string "Scripting"]:26: in main chunk

  5. #5
    Join Date
    Jan 1998
    Location
    Jupiter, FL
    Posts
    13,550

    Default

    try changing the connection part to this to see if an error is given:

    Code:
    local conn, err = env:connect(odbc_dsn,odbc_user,odbc_password)
     if conn then
      print ('MySQL connection object open = true')
     else
      print ('MySQL connection object open = error', err)
     end
    Ron
    No support through PM

  6. #6
    Join Date
    May 2004
    Location
    France
    Posts
    250

    Default

    As requested, I updated my script to get error details but the error is ' [' ...

    I tried with MySQL and a SQL server database.


    Code:
     -- Data Source Name 
     local odbc_dsn = 'girder'
     -- ODBC user name
     local odbc_user = 'user'
     -- ODBC  password
     local odbc_password = 'password'
     -- ODBC query to run ('show tables' works well for me)
     local odbc_query = 'select * from table'
     
    
     
    
     ODBC = require('luasql.odbc')
     local env = ODBC.odbc()
     if env  ~= nil then
         print ('ODBC environment object open = true')
     
    
         local conn,err = env:connect(odbc_dsn,odbc_user,odbc_password)
         if conn ~= nil then
             print ('ODBC connection object open = true')
     
    
             local curr,err = conn:execute(odbc_query)
             if curr ~= nil then
                 print ('ODBC cursor object open = true')
                 
                 local row,err = curr:fetch() 
                 if row ~= nil then
                     print ('ODBC row object results --------*')
                     while row do
                         print( row )
                         row,err = curr:fetch()
                         if not row then
                             print ('end results --------*')
                         end
                     end
                 else
                     print ('ODBC row object returned nil -------*', err)
                 end
                 
                 --Close cursor
                 local returnvalue ,err = curr:close() 
                 if returnvalue == true then   
                     print ('ODBC cursor object closed =')    
                 else
                     print ('ODBC cursor object closed =',err)    
                 end    
     
    
             else
                 print ('ODBC cursor object open = error =>',err)
             end
             
             --Close connection object
             local returnvalue ,err = conn:close() 
             if returnvalue == true then   
                 print ('ODBC connection object closed =')    
             else
                 print ('ODBC connection object closed =',err)    
             end  
         else
             print ('ODBC connection object open = error =>',err)
         end
         
         --Close environment object
         local returnvalue ,err = env:close() 
         if returnvalue == true then   
             print ('ODBC environment object closed =')    
         else
             print ('ODBC environment object closed =',err)    
         end  
     else
         print ('ODBC environment object open = error')
     end
    Last edited by rgouyet; September 7th, 2013 at 01:44 PM.

  7. #7
    Join Date
    Jan 1998
    Location
    Jupiter, FL
    Posts
    13,550

    Default

    I'm packing up for the PEAC trip. So I'm not sure I have time to look at this before. But I'll try. If you do not hear from me that is because I'm on the road.
    Ron
    No support through PM

  8. #8
    Join Date
    May 2004
    Location
    France
    Posts
    250

    Default

    Hi Ron,
    Did you have time to check the odbc issue ?
    Thanks

  9. #9
    Join Date
    Jan 1998
    Location
    Jupiter, FL
    Posts
    13,550

    Default

    Not yet sorry. We've been very very busy on catching up on work that accumulated during our week installing PEAC. Including a few rather large orders of PIR's!
    Ron
    No support through PM

  10. #10
    Join Date
    May 2004
    Location
    France
    Posts
    250

    Default

    Sorry to put this thread up ...

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •