Ingres and Visual Basic

[TrUz]

Hi,

I am trying to execute some Ingres commands through Visual Basic using Shell but it is not having any of it. If I create a batch and use the following command: sql MyDatabase < MyFile.in this works fine.

If I do the same in Visual Basic I get an error but I am not sure what it is as the DOS box comes up and goes in a split second.

Does anyone have any suggestions?

Many Thanks,

TrUz

 

[JIMA]

Hi,

One of the causes could be the namespace that VB runs the script in not having access to the Ingres environment variables or Symbol table. Another possibility is that the user that VB is running the script as doesn't have permissions to do the specified actions within the SQL script. Check the permissions on your tables etc.

I think you can use the -u flag on the SQL command to force the script to be run by a particular user so you can ensure your script is run by a user with appropriate permissions. I'd need to look that up to be 100% sure though.

If you run the script and look in the %II_SYSTEM%\ingres\files\errlog.log file you might find some useful information such as highlighting a failed connection, invalid user etc.

You could also try redirecting the output from the SQL script into another file from your VB command e.g issue the command:

sql mydb<myscript.sql >myerrorfile.txt

That should get you an output of the script together with any errors. Make sure you have a \p after each line of SQL to ensure it's printed out.

A final thing you could try is to get VB to write a batch file that will run the SQL. The VB will then execute the batch file which will in turn run the SQL. Bit messy though.

Hope that's of some help, let me know how you get on.

Jim

 

[TrUz]

Hi JIMA,

That is what I am currently doing at the moment - getting VB to write the batch file, execute it then delete the batch file. The application will be running under the admin account so user permissions is not a problem here.

I will have a go at trying to capture the error so you can take a look.

Thanks for your help.

EDIT: Ok here is the error I am getting.

xxx     ::[II\NMSVR\3c8    , 00000000]: Wed Jul 19 08:18:26 2006 E_GC0130_GCN_BAD_SYNTAX	User supplied database name is not formatted properly. Format for database name is: vnode::dbname/class. Vnode:: and /class are optional.
xxx     ::[II\NMSVR\3c8    , 00000002]: Wed Jul 19 08:18:26 2006 E_CLFE07_BS_READ_ERR	Read from peer process failed; it may have exited.

Seems it is telling me the Database name is not formatted properly. But I am using 'sql mydb < myscript.sql' - Cannot see why it will not work.

TrUz

 

[JIMA]

Hi,

I had another look at this and eventually got it working.

First, the vnode error you're getting is, I think, a red-herring caused by the redirection. If you issue:

sql mydb<myscript >myoutputfile

through the Shell command then VB turns the > to 0> (stdout) when it executes the command. This causes the command to fail with the vnode error.

I got the SQL to work by stating the full path name of the .sql file and vnode in the batch script that I fire off using the Shell command:

So, in my batch script I have:

sql mynode::mydb<c:\shelltest\mytest.sql

That seemed to work when run using either Shell or Winexec from VB.

I couldn't get the output of the SQL redirected though, could be a limitation of the Shell command or something I'm doing wrong.

Hope that helps, let me know how you get on.

Jim

 

[TrUz]

Getting it working from a Batch file is not a problem that is fine. What I am trying to do is this:

Dim RetVal as Double
RetVal = Shell("sql mydb < C:\myscript.sql", vbNormalFocus)

This is when it dies for no apparent reason.

EDIT: If I do just:

Dim RetVal as Double
RetVal = Shell("sql mydb", vbNormalFocus)

It opens up a session ready for me to enter commands?

This is getting annoying now.

TrUz

 

[JIMA]

OK, try using the cmd.exe command to run your sql, providing the /c switch and then the command to run.

Shell "cmd.exe /c sql mynode::mydb<c:\myscript.sql",vbHide"

It sure is annoying, particularly as it's so difficult to trap errors so you can get some clue as to what's happening.

Try that and let me know what happens.

 

[TrUz]

OK, try using the cmd.exe command to run your sql, providing the /c switch and then the command to run.

Shell "cmd.exe /c sql mynode::mydb<c:\myscript.sql",vbHide"

It sure is annoying, particularly as it's so difficult to trap errors so you can get some clue as to what's happening.

Try that and let me know what happens.

Dude, that **** works!!!

It did not even cross my mind to try that.

Many thanks for you help.

A very happy TrUz.