Source language: Translate to:

DBPro and MS SQL 2012 Express

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

DBPro and MS SQL 2012 Express

Postby dpayer » Thu Nov 08, 2012 1:29 pm

I started an application in Access and had most of my functions save one done that worked perfectly using NB and DBPro.

The last function had to do with distance between two GPS locations and this involves trig functions and GreatCircle calculations that Excel does well but Access does not, so I decided to move to the free version of MS SQL because we use that on many of the servers at my work place and it has a direct function for computing distances between 2 GPS locations.

Neobook and DBPro can connect to MS SQL just fine with either a direct connection or via ODBC but I found out quickly that many commands in DBPro do not work in MS SQL

Adding tables - you need to have the right permission of the user and the definitions of fields that are in the drop downs of DBPro are not accurate for MS SQL. You will probably need to create the tables in the SQL server database directly or like myself you may be able to import your Access database into the SQL server database. These can be done via the free Management Console

Listing of tables - does not work - you will have to find a SQL statement equivalent
Code: Select all
GoSub "OpenGPSDatabase"
dbpGetTableNames "[DBName]" "[#13]" "[Listoftables]"
Alertbox "test" "List of tables: |[listoftables]"
This opens the database but does not put anything in the list of tables variable

Empty a table (dbpEmptyTable) doesn't work, use TRUNCATE TABLE instead

Often you cannot simply name a table, you must include as a prefix the database name and the other prefix given when the table is created. Example: Database=GPS Table created as Exceptions but lists as dbo.Exceptions. So when referencing it in DBPro, you must open the table as GPS.dbo.Exceptions.

Some queries will work within the SQL Management Console with the expected results. When you enact those queries with dbpExecSQL they do not. The SELECT . . . .INTO is one such query.
Code: Select all
dbpExecSQL "[DBName]" "SELECT TerminalID, HomeLatLong INTO GPS.dbo.TEMPHomeLatLong FROM GPS.dbo.CurrentExceptions" "GPS.dbo.TEMPHomeLatLong"

This generates a "Cannot perform this operation on a closed dataset" even though the database is open, and the table is open (in this case GPS.dbo.CurrentExceptions). In the console this query CREATES the table (GPS.dbo.TEMPHomeLatLong). In NB DBPro it somehow wants to open the table that doesn't exist (GPS.dbo.TEMPHomeLatLong). You cannot have that table exist when you do a SELECT ..... INTO statement in MS SQL 2012.

SQL statements generated in the MS Access query analyzer will work for Access databases but the syntax is wrong for SQL databases. If you decide to use MS SQL 2012 (it is free after all and VERY fast), you may want to use their free management console (600 meg download!) to use their query analyzer.

I have run into some errors doing imports of CSVs into a table but it is not consistent. The error displayed is not a NB error, it seems to be directly from the database server. It says "Bookmark is invalid". This is possibly a problem with 32bit processes interacting with a 64 bit process. The import works but displays those errors.

Lessons learned are that the connectivity with the DB works fine. At times you will need to use direct SQL statements instead of commands in NB's DBPro but I think most problems can be resolved. I will post more things as I see them. If others are using MS SQL 2012 and run into things, post them here so we can learn from each other.

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: DBPro and MS SQL 2012 Express

Postby dpayer » Fri Nov 09, 2012 8:55 am

dpayer wrote:Some queries will work within the SQL Management Console with the expected results. When you enact those queries with dbpExecSQL they do not. The SELECT . . . .INTO is one such query.
Code: Select all
dbpExecSQL "[DBName]" "SELECT TerminalID, HomeLatLong INTO GPS.dbo.TEMPHomeLatLong FROM GPS.dbo.CurrentExceptions" "GPS.dbo.TEMPHomeLatLong"

This generates a "Cannot perform this operation on a closed dataset" even though the database is open, and the table is open (in this case GPS.dbo.CurrentExceptions). In the console this query CREATES the table (GPS.dbo.TEMPHomeLatLong). In NB DBPro it somehow wants to open the table that doesn't exist (GPS.dbo.TEMPHomeLatLong). You cannot have that table exist when you do a SELECT ..... INTO statement in MS SQL 2012.
David P.


I found a way around this problem. I created a stored procedure (nice easy tutorial here: http://youtu.be/rcM9jDl494I) and then I call the procedure.

Once again, you cannot simply call the procedure by its name, it must include DATABASENAME.PREFIX.STOREPRONAME

In my case, the DB is named GPS, the stored procedure was named TMPHomeList by me but the system gave it a prefix (visible in the Mangement Console) of dbo so I call the procedure using:
Code: Select all
dbpExecProc "[DBName]" "GPS.dbo.TMPHomeList" ""


(I use a subroutine to open the database so it will be easier to move to another machine. I set the name of the DB with a variable: [DBName] )

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: DBPro and MS SQL 2012 Express

Postby Neosoft Support » Fri Nov 09, 2012 11:51 am

Each database engine seems to have its own quirks and limitations when connected through ADO/ODBC. Some db engines interpret SQL commands differently or ignore them altogether. DBPro is optimized for MS Access since that db engine is installed with Windows, and it's the one most people use. Other db engines may require some experimentation to determine what works and what doesn't.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: DBPro and MS SQL 2012 Express

Postby dpayer » Tue Dec 04, 2012 9:01 am

I will use this thread to post issues I run into with MS SQL 2012 Express to keep them in one place for future review.

"Invalid Bookmark" errors

The MS SQL engine itself will display errors under certain circumstances. I ran into one such error when doing an import of a CSV file into a table. It reported "Invalid Bookmark" but the file was imported in its entirety and it worked properly.

I decided I could live with the results but not the error displaying. So there is an SQL command that can be issued to turn off some reporting errors. I surrounded my import command and associated statements with that instruction and it removed the errors. I then turn the warnings back on so I can catch other errors.

Code: Select all
dbpExecSQL "[DBName]" "set ANSI_WARNINGS  OFF" ""

dbpImportFromCSV "[DBName]" "details................."
dbpExecSQL "[DBName]" "details................."
dbpExecProc "[DBName]" "details................."
dbpExecSQL "[DBName]" "details................."

dbpExecSQL "[DBName]" "set ANSI_WARNINGS  ON" ""


I open my database with a subroutine and set the name of the db in a variable because I will need to move this to a machine with a different MS SQL install. I want to make the connection string something that is supplied in a config file.

I have found that even the syntax for things like deletes or updates are unique for the engine you are using. I am needing to use more dbpExecSQL based statements and stored procedures than native DBPro statements. Mr. Google has been my friend in this project. :lol:

David P
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: DBPro and MS SQL 2012 Express

Postby Neosoft Support » Tue Dec 04, 2012 11:44 am

I have found that even the syntax for things like deletes or updates are unique for the engine you are using.


I wonder what the logic of that is?
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: DBPro and MS SQL 2012 Express

Postby dpayer » Tue Dec 04, 2012 12:43 pm

Neosoft Support wrote:
I have found that even the syntax for things like deletes or updates are unique for the engine you are using.


I wonder what the logic of that is?


Yes, it is odd. Example:
Code: Select all
Access:    DELETE [table.*]     FROM table     WHERE criteria

MS SQL:    DELETE FROM table WHERE predicates;


and then there are instructions like this:
Code: Select all


Access: UPDATE table     SET newvalue     WHERE criteria;

MS SQL:

[ WITH <common_table_expression> [...n] ]
UPDATE
    [ TOP ( expression ) [ PERCENT ] ]
    { { table_alias | <object> | rowset_function_limited
         [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
      }
      | @table_variable   
    }
    SET
        { column_name = { expression | DEFAULT | NULL }
          | { udt_column_name.{ { property_name = expression
                                | field_name = expression }
                                | method_name ( argument [ ,...n ] )
                              }
          }
          | column_name { .WRITE ( expression , @Offset , @Length ) }
          | @variable = expression
          | @variable = column = expression
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
        } [ ,...n ]

    [ <OUTPUT Clause> ]
    [ FROM{ <table_source> } [ ,...n ] ]
    [ WHERE { <search_condition>
            | { [ CURRENT OF
                  { { [ GLOBAL ] cursor_name }
                      | cursor_variable_name
                  }
                ]
              }
            }
    ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ server_name . database_name . schema_name .
    | database_name .[ schema_name ] .
    | schema_name .
    ]
    table_or_view_name}
       


And some very helpful things that work in Access, don't work in MS SQL like the variable for record counts:

[GPS.CurrentExceptions.$RecCount]

should give the number of records for the table named 'CurrentExceptions' in the database 'GPS'. The value of that variable does not get generated. I am sure I could do a SELECT COUNT command to get what I need though.

So it gets a bit complicated when you need to throw all this into a dbExecSQL statement or a procedure.

Anyway.... DBPro is still a great product to use data from even these big data engines and manipulate as needed!

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest