Open, Read, Write/Update to a Microsoft SQL Server database.

[nrh7]

Greetings, I excuse myself if this is not the right forum to ask this.

One of my client has seen the power of iNomads and they gave me a request, to make iNomads panels that open, read and update a database on their Microsoft SQL Server database.

I'm VERY newbie on this topic, barely have managed to run iNomands on a local network, so this client has a desktop software made on Visual Basic 6, but they loved how easily I managed to open pvx panels on Google Chrome via iNomads.

So, they installed their SQL Server database in my laptop, I was looking at the manual and ended up reading about the ADO command but I haven't been able to connect to the SQL Server database, everything I tried gives me an error 15.

For this test I'm only trying to OPEN and CLOSE it without actually reading anything, just trying to simply open it but it's not working:
The commented lines (starting with ! ) are all the examples I've tried and failed.

SADI\SQLEXPRESS is supposed to be the server name, dbo.D_Inventory is the Table I want to connect to (open then close)

LET CHAN=HFN
! OPEN (CHAN,OPT="CONNECT='Provider=SADI\SQLEXPRESS'")"[ADO]SADI\SQLEXPRESS;dbo.D_Inventory"
! OPEN (CHAN)"[ADO]SADI\SQLEXPRESS"
! OPEN (CHAN)"[ADO]SADI\SQLEXPRESS[dbo.D_INVENTORY][]"
! OPEN (CHAN)"[ADO]SADI\SQLEXPRESS.WMPOS[dbo.D_INVENTORY][]"
! OPEN (CHAN,IOL=*)"[ tag ] SADI\SQLEXPRESS.WMPOS ; dbo.D_INVENTORY [;fileopt ]"
CLOSE (CHAN)

I know nothing about this, I also read about ODBC but I haven't even been able to learn where to activate the ODBC license, let alone use that feature.

I got this far by reading the "Microsoft SQL Server Interface" part of the pvx manual.

For now it's just OPEN CLOSE, after is, OPEN, READ (a record), WRITE (to update it) then CLOSE.


Thanks for any help, I may be able to sell a few Web and Wayfarer Licenses if I can pull this off.

[Devon Austen]

Here is an example open using [ADO] for a SQL Server database

open (1,iol=*,opt=";USER=username;PSWD=password;Connect='Provider=SQLOLEDB;';DB=db_name;KEY=key_field;CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]server_url;table_name"

Note that server_url is not the name of the SQL server but of the url of the machine it is running on. So if this is all running on the same machine right now then use localhost for the server_url.

For the table name I think you want to leave off the dbo. so just use D_INVENTORY.

[nrh7]

Thanks for the info!

Playing around with the command you shared, I tested many codes but im sharing the following codes, all gave error 15:

OPEN (CHAN,IOL=*,OPT=";USER=sa;PSWD=sa123@123@;Connect='Provider=SQLOLEDB;';DB=WMPOS;KEY=key_field;CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]SADI\Lenovo;D_INVENTORY"
! DUMP -  OSerr: HY000: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

OPEN (CHAN,IOL=*,OPT=";USER=sa;PSWD=sa123@123@;Connect='Provider=SQLOLEDB;';DB=WMPOS;KEY=key_field;CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]SADI;D_INVENTORY"
! DUMP - OSerr: HY000: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

OPEN (CHAN,IOL=*,OPT=";USER=sa;PSWD=sa123@123@;Connect='Provider=SQLOLEDB;';DB=WMPOS;KEY=key_field;CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]SADI\SQLEXPRESS;D_INVENTORY"
! DUMP -  OSerr: HY000: Login failed for user 'sa'.

OPEN (CHAN,IOL=*,OPT=";USER=sa;PSWD=sa123@123@;Connect='Provider=SQLOLEDB;';DB=WMPOS;KEY=key_field;CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]SADI\SQLEXPRESS;D_INVENTORY"
! DUMP -  OSerr: HY000: Login failed for user 'sa'.

OPEN (CHAN,IOL=*,OPT=";USER=sa;PSWD=sa123@123@;Connect='Provider=SQLOLEDB;';DB=SADI\SQLEXPRESS;KEY=key_field;CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]SADI\SQLEXPRESS;D_INVENTORY"
! DUMP -  OSerr: HY000: Login failed for user 'sa'.

=============================================================================

The SQL Server and PVX are on the same machine for this test.

Computer Name: SADI
Local PC User: Lenovo (no password)

SQL Server User: sa
SQL Server Pass: s123@123@

SQL Server Name: SADI\SQLEXPRESS
Database Name:  WMPOS
Table Name: D_INVENTORY

Not sure what the "key_field" is but the primary key for D_INVENTORY is PK_D_INVENTORY.

I'm still missing something 


Thanks for all the help

[Devon Austen]

I assume what you would want based on the info provided is

OPEN (CHAN,IOL=*,OPT=";USER=sa;PSWD=sa123@123@;Connect='Provider=SQLOLEDB;';DB=WMPOS;KEY=PK_D_INVENTORY;CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]SADI;D_INVENTORY"

some of the errors you were getting were login errors so you may want to confirm user name and password.

[nrh7]

Greetings

This literal code:

OPEN (CHAN,IOL=*,OPT=";USER=sa;PSWD=sa123@123@;Connect='Provider=SQLOLEDB;';DB=WMPOS;KEY=PK_D_INVENTORY;CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]SADI;D_INVENTORY"

Still gave me the error 15 but with this dump information instead:

Error Status
 Error #15: Operating system command failed
 [Last OS err:No such file or directory]

Error Info
   Err: 15
   Erx: 150000
   Ret: 258
   OSerr: HY000: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
   Program: C:\CentralDrugInventory\test
   EntryPoint: 0
   Stno: 251
   Obj: 0
   Lfa: 61
   Lfo: 124
   LastPath: [ado]SADI;D_INVENTORY
   LastWinErr: 0
   Module: pvxado.c
   Line: 2063
   CTL: 10001

===================================================

Would this have anything to do with the ODBC License? I have yet to activate that, not sure where or how.

Or maybe it has to do with maybe the directories?

PVX is on C:\
The project is on C:\InventoryApp
and the SQL Server is installed on C:\Program Files\Microsoft SQL Server

I will check about the user and password as you mentioned though.

Thank you

[Devon Austen]

No when using the [ADO] interface ODBC is not used.

The error 15 means the error is coming not from PxPlus so the important error info is:

HY000: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

It sounds like it can't find the SQL server using the server url SADI.

I think when you used SADI\SQLEXPRESS it gaev an error about a login which means it connected and tried to login.

[nrh7]

Yes it does, with SADI\SQLEXPRESS gives log in error, I will follow up with the software developer on the user/pass of sql server.


Side question, when I open the SQL Server manager it is set up so that I can use the Windows Authentication as log in method, so I use the PC name/password to connect to the database, can ADO command be used in such way too?
That it connects using my windows PC username/pass instead of the sa/sa123@123@ user?

[nrh7]

Extra info:

The developer uses this connection string on VisualBasic

MSDASQL;DRIVER={SQL Server};SERVER=EliteBox\SQLEXPRESS;trusted_connection=no;user id=sa;password=sa123@123@;database=WMPOS;

He asks me that IF I can connect to the database as a whole instead of trying to connect a specific table but that answer I do not know.

I shared his code so maybe it gives us a hint.

[martinp]

If you don't provide a table it should connect to database itself then you can send SQL statements manually.

[nrh7]

I created another user in the SQL Server called
pvx with password pvx123@123@ that is NOT an admin user like "sa"

I used the following code:
OPEN (CHAN,OPT=";USER=pvx;PSWD=pvx123@123@;Connect='Provider=SQLOLEDB';DB=WMPOS;")"[ado]SADI\SQLEXPRESS"
Then got this:
Error 15 - Dump = OSerr: HY000: Cannot open database "WMPOS" requested by the login. The login failed.

Then used:
OPEN (CHAN,OPT=";USER=pvx;PSWD=pvx123@123@;Connect='Provider=SQLOLEDB;';CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]SADI\SQLEXPRESS"

It gave no error, so I'm assuming it connected correctly, I will try to read any record now and show it on screen.
Let's see how it goes.

[nrh7]

This is the final product:

00041 LET CHAN=HFN
00042 MULTI_LINE READ MULTILINE.CTL,Y$
00043 OPEN (CHAN,OPT=";USER=pvx;PSWD=pvx123@123@;Connect='Provider=SQLOLEDB;';CONCURRENCY=LOCK;EXTROPT=(UPDLOCK)")"[ado]SADI\SQLEXPRESS"
00044 READ (CHAN,KEY="!SELECT [ITEM_ID],[ITEM_NAME],[ITEM_DESCRIPTION],[ITEM_ID_ORIGINAL],[LAST_UNIT_COST] FROM [WMPOS].[dbo].[D_INVENTORY] WHERE ITEM_ID_ORIGINAL ='"+Y$+"'")C1$,C2$,C3$,C4$,C5$
00045 PRINT C1$
00046 PRINT C2$
00047 PRINT C3$
00048 PRINT C4$
00049 PRINT C5$
00050 PRINT "========================================="
00051 CLOSE (CHAN)
00052 RETURN

It works wonderful because I input the internal code in the screen and run the code and it prints the info about the internal id.

Runs smooth!!! Finally....

However when I tried to run that same window via inomads it did not run nor it gave me any errors.

What changes do I have to make to inomads so that it can run, I use EZWeb Server on local network and google chrome.

THANKS GUYS!

[nrh7]

Nevermind guys!
Ado is working well with chrome, its just the command "print" doesnt show anything but i can display information to a multiline or msgbox just fine.

So I guess im set for now, thanks!!!

[nrh7]

Ok, stuck again 

I'm using the following code to read data from SQL Server DB using ADO:
READ (CHAN,KEY="!SELECT [ITEM_ID],[ITEM_NAME],[ITEM_ID_ORIGINAL] FROM [WMPOS].[dbo].[D_INVENTORY] WHERE ITEM_ID_ORIGINAL ='"+VALUE$+"'")F1$,F2$,F3$

Works well, it brings the item data, now I need to modify say, the name variable ITEM_NAME, how can I write/remove from SQL DB using ADO?

I tried to use the UPDATE SQL command instead of SELECT but it doesn't work.

I can read already, I need to be able to:
Write New Record
Update Existing Record
Delete Record

Thanks for the help team!

[Devon Austen]

You can execute SQL directly via the WRITE RECORD:

WRITE RECORD (CHAN) "INSERT INTO table_name VALUES (value1, value2, value3)"

[nrh7]

Neat, I'll try that thanks!