How to fill the parameter of insert service via jdbc

, ,
1

Hello,

I try to use the insert service instead of exeSQL to insert rows in the table of MS-SQL server 2000,

Database: Northwind
Table: table1

I got a DB Alias named “ms_jdbc”
DB URL: jdbc:microsoft:sqlserver://localhost:5555
DB Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver

I fill the parameters as following:
$dbAlias: ms_jdbc
$dbTable: table1
$data: (a record list and table1 have the same columns)

Would appreciate if someone could tell me how to set up the database name in which parameter?

Looking forward your feedback, Tks!!

floydliao

2

The database name usually is part of the DB URL in your DB alias definition…possibly at the end with a “:DBNAME”…I am not sure of the exact syntax…It depends on the driver you are using…

On a separate but related note: I would NOT recommend using the “Insert” service…The performance is VERY bad…Every time it is invoked, it issues a separate query to the DB to read the table schema before executing the insert…The same is true for the other DB ops except for “execSQL”…

So if you care about performance you should always use “execSQL”…

Hope this helps…

Tony

3

Floyd, if you are having trouble connecting to the database, make sure that you have provided the proper database connection properties. Have you successfully tested your alias using the Administrator?

Tony, does every WmDB service (except pub.db:execSQL) read the table schema before executing or just the pub.db:insert service?

Thanks.

4

Thanks for your response.
Dan, yes, I had tested my DB Alias with Server Administrator, and it worked.
I try to insert rows over 60 columns, it is not convenient to use the exeSQL service.
Tony, Can I execute sql statement in different database with the same DB Alias?

Thanks

5

I type the wrong information in the first post:

DB URL: jdbc:microsoft:sqlserver://localhost:5555 (wrong)
DB URL: jdbc:microsoft:sqlserver://localhost:1433 (correct)

6

If you are using the TYPE IV MS driver, then port 1433 is correct. If you are using Sequelink or other client/server DB driver, then you may want to look at it again. Sequelink is 19996.

In the WmDB interface that is accessible through the Admin console, you should be able to connect and obtain a list of tables. Then, you can run your sql statement against the specific table/tables through the web interface. If you cannot see the tables, check to make sure that the db user has permission.

If you cannot connect at all to the db, then start there.

7

I can connect through the Server Administrator,
and I can use the exeSQL service, too.

But I don’t know how to setup the database name if I use the “insert” service. $dbCatalog? or $dbSchemaPattern? or else?

Actually if I fill “Norwind” in $dbCatalog or $dbSchemaPattern
I’ll get the error messages: No info available for column ‘col1’ in table ‘table1’

Any comments will appreciate

8

Floyd,

Those parameters you mention for “insert” are all optional…If the “execSQL” works then the “insert” should work as well with just the $dbAlias and $dbTable inputs…

Dan,

I believe they all do…
It has been awhile since I looked at this (IS 4.6 version)…

Tony

9

Tony,

In this case,
I can use a SQL statement such as
“insert into Northwind.dbo.table1 (col1) values (?)”
in a “exeSQL” service, which can point out which database (ex. Northwind) i want to insert.

But I don’t know how to (or I cannot) set the database name in “insert” service. Can you give me a simple example to show how to setup the database name? Thanks a lot!

Floyd

10

Did you try this for your DB URL:
jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=Northwind